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.
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.
Discover how to interact with Coda webhook-based automations using Google Apps Script! In this post, we’ll explore different code patterns in both Coda’s formula language and Google Apps Script to send data contained in a Google Spreadsheet to a Coda table, and also perform other related tasks, using the new (May 2022) webhook-powered Coda automations.
For those unfamiliar with Coda:
Coda provides word-processing, spreadsheet, and database functions. It’s a canvas that blends spreadsheets, presentations, apps, and documents together. The software can integrate with third-party services like Slack and Gmail.
This post from Pablo Felip is a great opportunity to see what is possible using the new Coda webhook-powered automations. We’ve previously featured a couple of Coda/Apps Script integrations on Pulse, that have highlighted solutions using the Coda REST API and Coda Packs. This is the first we’ve seen using webhooks, which have a ‘no-code’ when/if/then setup interface. This route may provide the quick glue required for your Coda/Google Workspace integration. Pablo includes all the source code in the post as well as detailed instructions for setting up.
While curbside pickup of recyclables is convenient, it does require you to keep track of which type of waste is picked up when: miss the biweekly paper pickup and you’ll soon find yourself stockpiling four weeks’ worth of boxes and newspapers. When [Dominic Buchstaller]’s stack of cardboard began to reach his ceiling, he decided to take action by building himself BinPal: a fridge magnet that helps you remember when to take out which bin.
At heart of the simple but effective BinPal is an ESP32 board that connects to Google Apps Script and retrieves the pickup schedule from Google Calendar. If one of four categories of waste is due for pickup, its icon is highlighted on an LCD screen.
BinPal is an entry to the 2022 Hackday Prize which uses Google Apps Script to return Google Calendar data to an IoT device. The project page for BinPal has instructions and all the files to remake, including the file for laser-cutting/engraving the front plate. Browsing the Apps Script file for the project you’ll see it is a basic web app which returns data from a named calendar. Hackday challenges are open for set dates throughout the year so there is still a chance to submit your own projects – visit hackaday.io/prize for more info.
Build an automated system that will be automatically deployed to one of the destination spreadsheets when the code is committed to the GitHub repository.
Managing code particularly in container bound projects can be a real headache for Apps Script developers. The Script REST API has made this a lot easier both is terms of managing scripts but also opening up options for different development environments, including local development using clasp and your preferred IDE.
In terms of version control there are a number of solutions Apps Script developers can now consider. The Google Apps Script GitHub Assistant Chrome Extension is a popular option as it extends the existing online Script Editor with integration with GitHub and several other Source Code Management services (GitHub Enterprise/Bitbucket/GitLab).
For developers interested in developing projects locally there is perhaps even more choice. One solution we’ve featured a couple of times in Pulse is the use of GitHub Actions:
GitHub Actions makes it easy to automate all your software workflows, now with world-class CI/CD. Build, test, and deploy your code right from GitHub. Make code reviews, branch management, and issue triaging work the way you want.
The latest example for using GitHub Actions comes from Goran Kukurin (gorankukurin.com). Goran has shared a setup for developing code in Google Sheets with development and production versions. As well as using GitHub Actions to automatically push code to the correct Google Sheet version a shell script is used to modify the custom menu name as a useful reminder so you can see what version you are testing:
You can visit Goran’s post (linked below) for an example repo and instructions on how to setup. If you are using a UNIX based operating system it should be straightforward. If like me you are on a Windows machine there are some minor tweaks and possibly big node-gyp headaches to overcome, which I’ve highlighted below (in Goran’s website post some commands with -- are displaying as - – this GitHub repo and Medium version are ok).
Windows Tips
You are better using PowerShell rather than CMD so you don’t have to modify things like $HOME to %HOMEPATH%.
If you’ve not already got node-gyp installed the setup that worked for me on Windows 11 was:
Node 14.19.3
Python 3.10
Visual Studio Build Tools 2017
After installing with npm install -g node-gyp there are Windows specific setup instructions (don’t forget npm config set msvs_version 2017).
After creating the spreadsheets I needed to specify the directory by including src (I think this is a nit) e.g.:
mv src\.clasp.json .clasp-prd.json
To encrypt your clasp credentials you might need to install install GnuPG.
If you are running the setup build tasks in VS Code and get:
'.' is not recognized as an internal or external command
I solved this by configuring npm to use bash.exe, some other options are give in this SO answer.
Final thought
There is a lot more you could do with GitHub Actions like pushing to multiple production spreadsheets and much more. This post from Goran Kukurin is a great insight to what is possible and we look forward to seeing where other Apps Script devs go with it.
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.
This is a sample script for retrieving and parsing the XML data from Google Workspace Update Blog and putting it to Google Spreadsheet using Google Apps Script.
While this post from Kanshi Tanaike focuses on parsing the XML feed from the Google Workspace Update Blog the code can easily be modified to pull other XML feeds.
Even if you are not interested in parsing XML to Google Sheets the code pattern in this solution is worth looking at as it uses a destructuring assignment and reduce() to construct the .setValues() array for writing data to Google Sheets.
In Kanshi Tanaike’s script they clear the contents each time the script runs. If you would like insert new posts keeping a record of previous blog updates here is a forked version which will insert new data.
In this article, we’ll go over the different ways a GAS script can relate to the user using it, and show an easy way to dynamically resize dialogs built using the Apps Script HTML Service. To achieve this, we will introduce some basic concepts related to the object model of HTML documents and their manipulation using JavaScript. – [Google Translated]
A couple of community contributions for custom dialogs have landed in the Pulse inbox recently. The official documentation on Dialogs and Sidebars in Google Workspace Documents is an excellent starting point, but if you prefer learning from video tutorials Chanel Greco has recently published Google Apps Script Alert – Deep Dive on the saperis YouTube channel.
If you’d like to go a little further this post from Pablo Felip details how custom dialogs can be dynamically resized using the methods in google.script.host and client-side JavaScript (for non-Spanish speakers you’ll have to view this post via Google Translate).
If you selected 1 or more rows, Publigo lets you generate a personalized document for those rows only or all visible rows in your sheet. Image credit: Romain Vialard
It is super easy in Google Sheets to select only specific rows, even if they are not adjacent. Simply hold down the CTRL / command key while making your selections. And those selections are also available in Google Apps Script via the method getActiveRangeList() … For Google Apps Script developers, here is a quick snippet showing how to process only selected rows.
Following the previous Pulse post on Get all selected Ranges in Google Sheets Romain Vialard got in touch to highlight the approach they have implemented to include this feature in the Mergo and Publigo Google Workspace Add-ons. This source post from Romain includes a snippet of code which might be useful for other Google Apps Script developers.
Unit testing your code with this Apps Script Unit test library as you go along will makes it easy to immediately catch errors, and keeping a running test repertoire ensures that you don’t break anything.
When you start developing more complex Google Workspace solutions like add-ons and Chat apps it’s worth considering how you will test, debug and refactor your code. As noted in this post from Bruce Mcpherson the Apps Script community has published a number of different solutions/approaches to unit tests, a number of these appearing in Pulse. This latest post from Bruce looks at the bmUnitTest library he has developed highlighting how it can be set up and used.
