AppsScriptPulse

Discover how to interact with Coda webhook-based automations using Google Apps Script

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.

Source: Coda webhooks 💙 Google Apps Script!

Encouraging users to run setup scripts in Google Sheets container bound projects when they first make a copy

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.

Scott has also shared a solution for removing a Google Sheets button after a script has run, which you might want to also check out if you are thinking of developing this process further.

Source: One Approach to Encourage Users to Run Google Sheet-Bound Apps Script When They First Make a Copy of the File – Yagisanatode

Hackaday Prize 2022: BinPal – A convenient recycling reminder

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.

Source: Hackaday Prize 2022: BinPal Is A Convenient Recycling Reminder

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)

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

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