Google Apps Script is great and all (and it’s a great starting point for writing code to control spreadsheets) but it doesn’t do all of the things I want it to. Namely, it doesn’t run Python, only Javascript (actually App Script but it’s a small distinction).
We are going to write some App Script in this series, but it won’t be the powerhouse of our logic. Instead, we will use it to capture user input and proxy requests to our own API.
If you’ve spent a bit of time with Apps Script it’s very likely that you’ve used SpreadsheetApp or the Sheets advanced service to interact with Google Sheets. If you are interested in expanding your horizons and using different tooling for this job here’s a starter tutorial showing you how you can spin up a Google Cloud Run service using Terraform which will let you deploy a Python API application.
Google Apps Script is a JavaScript-based language that has access to Google Workspace-specific libraries for Gmail, Google Sheets, Google Forms, Google Drive, etc., and allows you to quickly and efficiently automate your tasks and program business applications.
A lot of users try and quickly learn GAS and use it to make their lives easier. It’s all great, however the code we sometimes tend to come across on StackOverflow and other sites lacks best practices, hence I thought it was time to start bringing them up and I will start today with design patterns.
I’m all for copy/paste coding and it one of the things I love about the Google Apps Script developer community, there are lots of great snippets out there and in Pulse we’ve now over 800 posts and counting. When you start going beyond quick script solutions into more complex projects investing time planning how you’ll structure your code can save you headaches and frustrations further down the line.
Using design patterns are one way to produce better code that is more readable which in turn is more maintainable and can lead to faster development. This post from Dmitry Kostyuk a nice opportunity to learn about a design pattern for a very common use case of maintaining data in a Google Sheet from a third party API.
As the way we work continues to change, the need for no-code and low-code tools that enable hybrid work and empower the workforce across all industries is on the rise. The democratization of software creation is also gaining ground within organizations, and by 2024 more than 65% of applications will be developed by low-code tools.
I was late to AppSheet, my journey only really starting in May 2022. Like all platforms, even no/low-code, there is a bit of a learning curve to get your head around how it works, but once you do there is a world of opportunities for a wide range of users. Even for seasoned developers features like the Apps Script integration and AppSheet API mean there is plenty of scope to extend the capabilities of AppSheet.
This post from Google provides some useful reference cases, evidencing the impact the platform has had on a range of customers as well has highlighting some recently developments in the AppSheet platform including the opportunity to deploy AppSheet powered Google Chat apps.
At CTS, where I work, we have a range of services to support AppSheet adoption ranging from hackathons, training and development. Feel free to get in touch if you would like to find out more.
Learn how the access the creator’s email of a Google Shared Drive with Google Apps Script using the Drive Activity API & Admin Directory SDK.
Scott Donald has found a clever way to get the Shared Drive creator using Apps Script and the Google Drive Activity API. Whilst there is a Google Drive ‘Drives’ endpoint the response doesn’t include the creator in the Drives Response object. This post is a nice example of how you can combine data from different Google Workspace APIs. Follow the source link for a detailed explanation.
One model of monetization for a Google Addon is to allow a certain number of free uses before restricting that feature. This post shows one way to restrict a feature in a Google Editor Addon sidebar.
John McGowan is continuing his Google Workspace Add-on development tips at pace. You can read the story so far on the Automagical Apps Blog. In the latest post you can find out how John uses the Properties Service, to record the number of times an account has used a feature in your add-on by communicating between the sidebar and Apps Script using google.script.run. A reminder as well that you can see how you can boost User Property read/write with the SpeedStore library.
This is a sample script for opening and closing Google Forms on time using Google Apps Script.
Here’s a handy little snippet if you would like to programmatically open/close one of your Google Forms to responses for specific hours of the day. The script includes another trigger that will repeat opening/closing the Google Form for responses each day. As this snippet uses .timeBased().at(date) it’s easy for you to modify if you want to only have the form open to responses between two specific dates/times.
Retrieving and saving properties in Google Apps Script can be slow, especially if there are a lot of them. SpeedStore is a blazingly fast in memory properties store which you can use to make retrieving and saving properties much easier.
Continuing yesterday’s theme highlighting some of the components for developing a Google Workspace Add-on where John McGowan highlighted how Properties Service can be used to store and use a “licence” property to customise your add-on UI, we continue by looking at how you can handle property storage.
There have been a number of community contributions in this area such as Bruce Mcpherson’s bmCrusher. Another option is SpeedStore from Joshua Snyder. Not as feature filled as bmCrusher but the benefit is the library is more compact. SpeedStore still comes with some very useful features including automatically handling properties over 9kb and JSON encoding/decoding. Perhaps the biggest benefit is speed particularly when you are using a single store for all your properties.
I always get requests as to how people can add a license to their Google Add-on. There are a few different steps and here I will show how you can share different information in the Sidebar based on a license status.
In Pulse we’ve previously featured a couple of community contributions on how to monetize your Google Workspace Add-On. Corentin Brossault’s How to monetize your Google Workspace add-on? provides some great code snippets and tips for handling user authentication and payment. We’ve also featured Riël Noterman’s solution for Using JWT as a license key in Google Apps Script Google Workspace Add-ons. This related post from John McGowan (Automagical Apps) provides another piece to the puzzle demonstrating how you can use templates in HTMLService to switch user messaging based on stored user properties.
We’re happy to announce that AppSheet-powered no-code chat apps are now available in preview for Workspace customers in AppSheet!
You can access this feature right now if you are in the Preview Program, and you can provide any feedback in this thread. If needed, additional information can be found in our help center articles. We’re targeting a full launch for later this year.
I’ve been spending a fair bit of time in Google’s ‘no-code’ platform AppSheet. Even for developers there is plenty to get your teeth into with features like Apps Script integration. One of the incredibly powerful core features of AppSheet is the ability to send dynamic emails which include forms designed in AppSheet. These allow users to interact with your app without leaving their inbox.
Whilst the inbox dominates in many business sectors, support for frontline workers increasingly leans on other communication channels including Google Chat. With the announcement of AppSheet powered no-code Google Chat apps there is now an opportunity to also push dynamic forms, send data and interact with Chat users in that space. For developers there is also the opportunity to do even more with the Apps Script integration. Follow the link to the announcement post to find out more…
Validate addresses using Google Maps Address Validation and Apps Script. Discover onleeaddress the add-on for Google Workspace.
I missed the announcement in November 2022 that there is a new Google Maps Address Validation API. I also missed this post from Stéphane Giron showing how you can use the Address Validation API in Google Apps Script.
The concept is simple, you provide the Address Validation API with what you think is a correct address and the API returns information on each component of the address and additional metadata. Visit the source post for more details on setup and some example code.
