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.
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.
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’:
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.
A community connector with overridable config parameters to track recent Twitter trends for a query.
For the most part this connector is pretty similar to the one I’d created for visualising global stats of SendGrid user’s email statistics but with a key difference of making use of overridable config parameters — this is what gives the connector the capability to accept a query from the report directly and return/visualize the data based on a user’s input
This post from Sourabh Choraria is a useful reminder of what is possible with Data Studio Community Connectors, in this case connecting to the Twitter API v2 to visualise the tweet count for a defined search term.
The code for this connector is available from the source post and includes lots of inline comments to help you work out what is going on making it easy to modify if you have other APIs you would like to connect.
I quite often get called upon by clients to create Google Sheet templates that have Google Apps Script Automations bound to them. Sometimes these Google Sheets require an automated setup process to run things like gathering initial data, setting up time triggers, approving scopes connected to onEdit() or onOpen() triggers or renaming connected forms and their contents.
One of the challenges is getting new owners of the duplicated template Google Sheet to run the bound script before they dive into working on the Google Sheet. … After quite a lot of trial and error, I have devised a pretty solid approach that seems to have the most success in getting users to run through the authorisation of scopes and then run the startup script.
Scott Donald shares his approach for helping users get setup when first using a container bound script project in Google Sheets. The process has some nice features including revealing additional sheets, changing the active sheet and cell as well as removing the setup sheet. The post also highlights some useful tips and considerations as part of the user experience including the type and placement of supporting information.
Recently, I answered this thread. In that case, in order to convert the sample python script to Google Apps Script, the script for retrieving the signature might be a bit complicated. So, here, I would like to introduce this.
Gate.io is a cryptocurrency exchange which supports trading of a wide range of blockchain based currencies. The platform provides an API allowing users to interact and use features of Gate.io. Whilst it is unlikely that the majority of Google Workspace developers will be interested in interacting with the Gate API this post from Kanshi Tanaike might still be interesting to see how Python code has been refactored for Google Apps Script. It might also be useful should you encounter other APIs that require similar signature requests.
Learn how to automatically preserve the formatting in Google Sheet when new Google Form responses are submitted.
Handy little Apps Script snippet from Amit Agarwal should you need to keep any custom formatting applied to linked Google Forms responses in Google Sheets. Another way you can approach this is using ARRAYFORMULA to reference the form responses in another sheet and apply your desired formatting.
The default ‘Form responses’ sheet can be hidden if needed. Downside of using ARRAYFORMULA is you are referencing a cell range which can cause confusion when using features like sort. See the source link for all the code used in Amit’s solution.
