AppsScriptPulse

How to automate Google Apps Script deployments with GitHub Actions

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.

Source: How to automate Google Apps Script deployments with GitHub Actions (also published on Medium)

Automatically running a Google Apps Script function every quarter or another monthly period greater than one month

One of the great things about Google Apps Script is the way you can automate tasks. I’ve previously written about how I automate reporting and other examples like running backup processes. These usually run daily or once a month which is very straight forward to setup in Google Apps Script. If you want to run a script automatically every x number of months such as quarterly it gets a little harder. If you only want you script to run every three months … another option is to manage triggers programmatically which allows you to specify the date a function should be run again.

I’ve recently been revisiting some of my old Google Apps Script posts to do a bit of housekeeping. I thought this was a nice little snippet should you want to schedule a function to run on a time-driven trigger greater than one month. The trick used is to recursively create a time-driven trigger when the function is called. The solution comes with some caveats :)

Source: Running a Google Apps Script function every quarter or x months

Requesting to Gate API v4 using Google Apps Script (example of refactoring Python to Apps Script and signature requests)

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.

Source: Requesting to Gate API v4 using Google Apps Script

Create and run polls in Google Slides using Google Forms and Google Sheets

Image credit: Prateek Sharma

I wanted to give a presentation to a group of people. In order to make the presentation more engaging & eliminate silences during the slideshow, I thought of conducting polls in between. … Since I am giving presentation using Google tools, I thought let’s try it out with Google Apps Script.

I thought this was a nice little Apps Script snippet from Prateek Sharma which hooks into a .onFormSubmit() trigger to update all charts embedded from Google Sheets in a Google Slides presentation. Prateek provides detailed steps including the code for setting this up. Unfortunately, a limitation of Google Slides is once you go into ‘slideshow’ mode all charts become static images so you need to jump back to the editor view to show real-time results.

Source: Create and Run Polls in Google Slides using Google Forms & Google Sheets

How to preserve formatting of Google Forms responses in Google Sheets with Google Apps Script

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.

Source: How to Auto Format Google Form Responses in Google Sheets – Digital Inspiration

A beginners API authentication cheat sheet for Google Apps Script

See four different API Authentication methods presented in Apps Script, including authentication in query string, headers, and OAuth2.

I got fed up digging around in my Drive folder for old scripts to refresh my memory on the syntax, so I created this reference.

It’s not a comprehensive post on how to connect to APIs, instead, it’s a short summary of common protocols for easy reference.(If you’re new to APIs, start with my Apps Script API tutorial for beginners.)

We are currently spoilt for choice with Google Apps Script community contributions. This is a great post from Ben Collins for Google Apps Script beginners highlighting different patterns used to interact with third party websites with APIs.

An API is essentially an interface that can be used by a computer programme to retrieve or interact with another application.

What is an API?

If you would like to find out more about API Ben links to his API tutorial for beginners or I have shared workshop materials for ‘Machina a machina: An introduction to APIs with Google Sheets‘.

Check out the source link for Ben’s cheat sheet and other resources 👇.

Source: API Authentication Cheat Sheet for Apps Script

SuperFetch plugin – Firebase client for Google Apps Script – Desktop Liberation

Another in the SuperFetch (a proxy for Apps Script UrlFetchApp) plugins series, Frb is a plugin to access a Firebase Real time database.

If you want to take your use of APIs a little further Bruce Mcpherson is continuing his series exploring his recently published SuperFetch library showing how a client can be setup to interact with Firebase. As Bruce highlights: “Firebase is pretty fast, so there’s not a huge speed benefit from caching, but if you’re on a pay as go plan, SuperFertch caching can reduce your Firebase costs.”

The source post provided by Bruce provides everything to need to set up the SuperFetch client and Firebase project.

Source: SuperFetch plugin – Firebase client for Apps Script – Desktop Liberation

Retrieving and parsing XML RSS feeds in Google Sheets using Google Apps Script

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.

Source: Retrieving and Parsing XML data from Google Workspace Update Blog and Putting it to Google Spreadsheet using Google Apps Script

Dynamically resizing dialogs in Google Workspace documents using Google Apps Script and google.script.host

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).

Source: Cuadros de diálogo de tamaño dinámico en Apps Script

Google Workspace Add-On Walkthroughs (GWAOw!) 2 – ImportFromWeb by NoDataNoBusiness – Yagisanatode

In this episode of Google Workspace Add On Walkthroughs (GWAOw!), we take a look at ImportFromWeb by NoDataNoBusiness.

ImportFromWeb is a powerful web scraping tool for Google Sheets that allows you to grab data from any website. The creators call it IMPORTXML on steroids.

The latest episode from Scott Donald’s GWAOw! is now available. As explained by Scott ImportFromWeb is a Google Sheets add-on which allows users to use the custom function to import data from other websites into Google Sheets.

Even though this add-on is primarily used to add a custom function to Google Sheets the developers, NoDataNoBusiness, have taken the time to use the sidebar with some useful UI elements to help users get started.

Source: GWAOw! 2 – ImportFromWeb by NoDataNoBusiness – Yagisanatode

Subscribe to AppsScriptPulse...