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.
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.
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.
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.
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).
I’ve written a few articles about JavaScript proxying on here, and I’m a big fan. I also use a lot of APIs, and it can be time consuming to keep on checking the REST documentation for how to call them and deal with the UrlFetch responses. SuperFetch is a proxy for UrlFetchApp to help.
Bruce Mcpherson has been busy again and this latest post introduces ‘SuperFetch’ the new Google Apps Script library which works as a proxy for UrlFetchApp. SuperFetch has some useful additional functionality including: built in caching and compression, standard response format and error handling, and built-in JSON parsing.
SuperFetch also has some useful features often required when using third party APIs including delaying between requests and rate limiting. Bruce has promised some additional posts detailing more complex API configuration options including authentication.
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.
