In this post, we’re going to see how we can very quickly format multiple Google Sheets with a little help from Apps Script.
The idea of this post, came from the Department of Education in Hawaii, where they have over 300 schools and a guy who works there told me he had to format over 300 Google Sheets the same way, and wondered how this could be done with Apps Script.
Easy I said! And I thought it was a typical example of how a little knowledge of Apps Script can help you speed up your work and allow you to spend less time doing boring, repetitive work, and focus more on the data itself.
Let’s see how it’s done!
This is a nice real world example of how Google Apps Script can be used to make a manual routine task more efficient. The post by Baz Roberts includes a detailed explanation of what is going on in the code making it also a great learning resource. If you have some more complicated Google Sheets formatting in mind you might want to also look at Google Sheets Macros, which lets you record every action you take within the spreadsheet creating a script you could call similar to the format functions included in the post.
Crop Google Sheet Data to the data range or a selection in 2 button click with Crop Sheet. Full Walkthrough
Does this Google Workspace Add-on developer story sound familiar? You are able quickly write the code that executes your add-on functionality, but then you spend days buried in HTML or Card Service making the UI. The Crop Sheet add-on by Eric Koleda highlighted in this latest GWAOw! episode is a great example of what is possible just by using custom menus. For what it is worth the source code (all 73 lines including comments!!!) is on GitHub. Follow the source link for the video demo from Scott Donald and links including the GitHub repo.
Few days ago, with the launch of French region ‘europe-west9’ in GCP, I made an apps script to backup a Drive folder to Cloud Storage. It is a cool script and works nicely but after some exchange, we can make it better.
So here I come back with onleebackup an open source code to backup multiple Google Drive folders to cloud storage with synchronisation.
A very interesting open source project from Stéphane Giron which lets you backup Google Drive folders to Google Cloud Storage. An important caveat is with Google Apps Script limitations like script runtime and URL Fetch POST size this won’t work if you have gigabytes of data. The code has some nice features like handling Google Docs/Sheets/Slides file types, converting them to equivalent MS Office formats as well as management of Google Drive shortcuts, which requires calls to v3 of the Google Drive API. The source post provides details for setting up onleebackup, which also includes a link to a previous post with code highlights.
When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.
A great feature of Google Apps Script is the ability to create and serve custom HTML, often used to interface data you have in Google Workspace such as Google Sheets. Google highlight a coupe of different ways you can mix Apps Script code and HTML. Some of these ways are better in terms of process time and this report from Kanshi Tanaike highlights the cost of calling Apps Script functions as scriptlets in HTML templates. The good news is you can avoid delays in your web app rendering by making asynchronous calls with google.script.run, which you can read more about in Google’s best practices documentation.
Update: I’ve replicated this benchmark (smaller dataset) with google.script.run and it was only marginally slower (0.3s) than the ‘create HTML table with Google Apps Script’:
In this blog I am going to show you how to combine Google Apps Script with Google AppSheet to make automation even more powerful.
Whilst Google AppSheet is marketed as a ‘no-code’ development platform for coders there have been a number of ways to add some customisations to AppSheet apps. Previously webhooks were the main way you could do this, but the recent Apps Script connector for AppSheet makes it possible to call and if required pass parameters from AppSheet into custom Apps Script functions.
If this is something you would like to learn more about Aryan Irani has continued his AppSheet tutorial series on Medium with this post which goes through the step-by-step process for setting up and using Apps Script code in AppSheet. Click through the source link to find out more.
Having spent quite a bit of time working with the Google Drive API Revisions resource in this post I thought it would be useful to share some of the lessons and solutions I’ve picked up along the way. For this I’ll be sharing code snippets for interacting with the Revisions resource with Google Apps Script, but the solutions discussed could easily be applied to your programming language of choice.
In this video you will learn how to access an API with Google Apps Script.
Chanel Greco has created this video tutorial which walks through some different ways you can interact with other sites with Google Apps Script to get data into Google Sheets. For the tutorial Chanel uses the Weather API and by coincidence Luxman Ravindrakumar has also shared a similar tutorial on Medium explaining how to use the OpenWeather API. So if you prefer learning by watching or reading you’ve no excuses :). Check the sources links below for both tutorials.
Are you using Google Drive to share information with your staff? As the number of staff members increases, it can become increasingly difficult to manage permissions properly. I faced a similar issue. As the members of the team changed from time to time, setting access rights for each folder became a cost that could not be ignored. So I used Google Apps Script to create a tool to synchronize the permissions management table with the actual permissions.
We’ve previously featured Inclu Cat’s posts on ways to deal with Google Apps Script’s 6-minute execution limit [Ref 1 & Ref 2]. These posts continue to prove to be incredibly popular and usually feature in our most read list. Even if you aren’t interested in programmatically managing Google Drive file/folder permissions this post is a nice example of using the authors LongRun solution to process large amounts of data with Google Apps Script. From the source link you can find the GitHub repo with all the code you need and setup instructions.
Recently, I got the chance of giving a training on Google AppSheet at O’Reilly Media, Inc. … This was a three hour long training where I talked about the basics of Google AppSheet and how to create apps on Google AppSheet.
Aryan Irani has shared a really helpful post with highlights from an O’Reilly Media hosted course he led focusing on ‘Developing Robust Applications with Google AppSheet’. The post links to a number of other tutorials Aryan has published on how to create various AppSheet based applications including an Expense Tracking App, Vehicle Inspection App, Inventory Management App and more. Aryan touches on how Google Apps Script can be used in combination with AppSheet. If this is a topic you are interested in Chanel Greco recently highlighted a Google I/O session on the topic.
Remove a users edit access to a Google Drive file via an Apps Script Web App.
Web App code sample
The following Google Apps Script is designed to remove a person’s edit access from a Google Drive file via a Web App. The reason for using a Web App in this instance is because removing a person’s access to a file whilst they are running Apps Script code typically results in an error (or most certainly the inability to cleanly end the code). In the project I was working on prior to this step the code needs to send some automated emails before finishing with removing the person’s access.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
