AppsScriptPulse

Using design patterns in Google Apps Script

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.

Source: Using Design Patterns in Google Apps Script

How AppSheet reached new heights in 2022 as part of Google Workspace

Image credit: Google

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.

Source: How AppSheet reached new heights in 2022 as part of Google Workspace | Google Workspace Blog

Get the Creator’s email of a Shared Drive with Google Apps Script

 

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.

Source: Get the Creator’s Email of a Shared Drive with Google Apps Script – Yagisanatode

Restricting the number of times an account can use a feature in a Google Workspace Editor Add-on with Google Apps Script

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.

Source: Restricting the number of times to use a feature in a Google Addon Sidebar | Automagical Apps Blog

Opening and closing Google Forms on time trigger using Google Apps Script

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.

Source: Opening and Closing Google Forms on Time using Google Apps Script

SpeedStore: Blazingly fast Properties storage for Google Apps Script ⚡

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.

Source: GitHub – joshsny/SpeedStore: Blazingly fast Properties storage for Google Apps Script ⚡

Differentiating Google Workspace Add-on sidebar features based on a user licence property in Google Apps Script

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.

Source: Differentiating Google Addon Sidebar features based on a license | Automagical Apps Blog

Announcing: AppSheet Chat Apps now available to preview program!

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…

Source: Announcing: AppSheet Chat Apps Now Available to Preview Program!

Validate postal address with the new Google Maps Address Validation API and Google Apps Script

 

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.

Source: Validate postal address with the new Google Maps Address Validation API and Apps Script

Find all matching values in Google Sheets cells with Google Apps Script

Learn how to search and find all values in an entire Google Sheet spreadsheet, in a specific sheet tab or in a range with Apps Script.

Handy little snippet from Scott Donald on using the Google Apps Script TextFinder class to find all the occurrences of matching text in a Google Sheet. The bit I particularly liked in this solution was how Scott used a .map method to return an object array of cell hits including A1 notation:

Array containing an object for each sheet containing the sheet name and the cell location (Image credit: Scott Donald)

Follow the source link for more information including a video tutorial.

Source: Find All Values in Google Sheets with Apps Script – Yagisanatode

Hiding/deleting non-consecutive rows and columns in Google Sheets using Google Apps Script

These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet [sic] using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this.

In SpreadsheetApp there is the .deleteRow() and .deleteRows() , but when you have a lot of data or non-consecutive rows it can really eat into your Apps Script runtime limit. In searching for a better way of doing this I found this great solution from Kanshi Tanaike on Stack Overflow which uses the Google Sheets Advanced Service to delete multiple rows using one API call!

Using Sheets.Spreadsheets.batchUpdate isn’t just limited to deleting rows and for more examples see the source post which includes examples for hiding and deleting both rows and columns.

Source: Hiding and Deleting Rows and Columns on Google Spreadsheet using Google Apps Script

Organise Google Drive files into a JavaScript object for use with Google Apps Script

Organise Google Drive files into a JavaScript Object so that they can be looped through for each named individual.

Files to loop through and organise

Files to loop through and organise

The following Google Apps Script is designed to go through a folder of Google Drive files where a number of tutors have individual files for groups of students they teach. The code will collate all of the relevant files (specifically their IDs) that belong to each tutor, so that at a later date we can loop through the organised data set and create individual Google Sheets for each tutor and compile their student data into it.

What this code ultimately allows us to achieve is the ability to go through the files and get them organised for further coding.

In this example we have a file name pattern of ModuleCode – Tutor Name – Group Number for example ABC – Jane Doe – Grp 02. So we want to collate all of the files that belong to Jane Doe first, then move on to the next tutor (Micky Mouse in this example).

The collated data will look like this:

{
"Jane Doe":[
"FILE ID HERE",
"FILE ID HERE",
"FILE ID HERE"
],
"Micky Mouse":[
"FILE ID HERE",
"FILE ID HERE"
]
}

Source: The Gift of Script: Organise files into a JavaScript Object

Bulk add files to existing Google Drive folders using Google Apps Script

Take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.

Bulk add files to existing Google Drive folders

Bulk add files to existing Google Drive folders

The following Google Apps Script tool is designed to take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.You can decide if you want the subfolder name appending/prepending/ignoring in the file name of the new copy to help make it unique.

The tool will iterate through a single level of folders (not sub-subfolders) and for each one place a copy of the files into it.

Source: The Gift of Script: Bulk add files to existing Google Drive folders

Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

ChatGPT is pretty much a universal API: one endpoint to get any data.

Fake data is useful for testing purposes. At times, real data is too sensitive, and sometimes you do not have access to real data before production, but you need to begin working with something. Here is where fake data comes in.

We used to have a great library for fake data called Faker.js; however, Marak, the author, became so frustrated that he could not monetize the solution that he pushed an update that broke everything, which led to him being blocked on GitHub and NPM. As a result, the library is no longer maintained. ChatGPT, however, is not going anywhere.

Source: Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

Google Apps Script trends in Stack Overflow for 2022

From the above results, it can say that the current Google Apps Script has still been useful for a lot of users. But, the number of questions for one answerer is increased proportionally to the square of the year. Furthermore, the ratio of the solved questions for the total questions in 2022 is lower than that of 2021. So, in order to improve these issues, it is considered that growing answerers will be one of the important factors for the selectivity of Google Apps Script by users.

Kanshi Tanaike has recently published the 5th annual review of Google Apps Script tagged questions and answers on Stack Overflow [ google-apps-script ] to include data from 2022. This type of data always has to be read with caution. For example, whilst the total number of questions has again declined this year it could be argued this is because there is now a much bigger published knowledge base of both official and community resources. Declines in the number of people answering questions is more concerning. If you are someone who contributes to Stack Overflow a big thank you!

Source: Trend of google-apps-script Tag on Stackoverflow 2023