In Google Workspace gam is probably the ‘go to’ tool command line tool which allows administrators to easily manage domain and user settings. Recently I was asked about how you can keep gam generated reports up-to-date using Google Apps Script. It’s worth remember that gam uses public Google Workspace APIs when it performs actions and reports. In this post I show you a pattern for building script to keep gam reports fresh.
AppSheet is headlined as a no-code platform but this doesn’t mean for developers there aren’t coding opportunities. In this post I’ll be sharing AppSheetApp, a Google Apps Script helper library we’ve made at CTS which makes it easy to use the AppSheet API in your own AppSheet apps. As part of this I’ll highlight how this library has made our AppSheet powered Grab n Go Chromebook loaner solution immediately scalable and data source agnostic.
In this post the worlds of AppSheet and Apps Script collide, the resulting fusion is a library which makes it easier to integrate external data and events into your AppSheet app. This solution hopefully makes it a lot easier for developers and low coders to do more with AppSheet creating the possibility to use one (or a couple) of lines of code to interact with your app and not worry which data source your app is using. This post has more details of how you can get started and an example of the impact its already making with an alternative Grab n Go Chromebook loaner solution I’ve been part of at CTS.
When I create some applications using Google Drive, there are often the case which is required to retrieve the file list and folder list. I had prepared the script each time for each case so far. But recently, I thought that if there is a library for retrieving the file and folder list (as a tree), it will be useful for me and other developers.
There’s a long standing issue with triggers installed programmatically by other triggers, that was kind of fixed last year, but users are still reporting problems, at least in certain locations (Japan, India and others). The issue in short: triggers installed programmatically would not be created, or fail systematically.
In many cases this affects add-ons (and, to be noted, the issue still persists when testing them, see Testing details); I had a slightly different scenario: a webapp that, when executed (doGet), would install a time based trigger for the user executing the script.
This article provides two solutions to an annoying issue and unlocks many possibilities for programmatically installed triggers.
Image credit: Martin Hawksey (with the help of DALL·E 2)
Think 10x — supercharging your Google Apps Script solutions by directly calling Google Workspace Enterprise APIs.
The Google Apps Script built-in services like SpreadsheetApp, Maps and GmailApp are a great onramp for users with limited coding experience, the flip side is you can find yourself easily getting results but not in the most efficient way. DriveApp is a great example where Google have made it easy to iterate across folders and files, but when you have lots of folders and files it becomes a time consuming process and you hit execution limits.
An alternative approach is using Google Apps Script to make direct calls to the Drive API. The benefits of this approach is you can be more specific in the data you want back and it gives more flexibility with how you call the API, in some cases with the ability to make batch or asynchronous processes.
In this post I highlight a method ideal for scenarios when you want to index larger volumes of My Drive files and folders to a Google Sheet with calls directly to the Drive API. The post includes some sample code you can use which instead is able to reduce a 4 minute runtime to index 10,000 files and folders to one that can complete in under 40 seconds!
This is a sample script for putting the values of all Spreadsheets in a folder to the master Spreadsheet with a low process cost using Google Apps Script.
Some clever scripting from Kanshi Tanaike to combine individual Google Sheets in a Google Drive folder into a single master sheet. To achieve this the solution uses the Google Sheets Advanced Service combined with UrlFetchApp.fetchAll() to asynchronous process the source Google Sheets. It’s reported that this method was able to process 50 source sheets in 10 seconds!!! There are some limitations to be aware of highlighted in the source post, but for a method to quickly process a lot of data this solution is definitely one to keep in mind.
You get subtotal sums for each of the selected columns.
The code speaks for itself in this post from Max Makhrov in which he shows how to create a custom function for Google Sheets to subtotal groups of columns. There is some impressive data manipulation/reshaping going on in the function and some nice use of using arrays in formula created with brackets { }. Some of the code is also written with OpenAI/ChatGPT (I’m guessing anything with a for loop). The icing on top is the conditional formatting for group totals. Follow the source link for all the code.
An approach to process hundreds of thousands of rows of data (say, on Google Sheets) without running into the script execution timeout error.
TL;DR — the typical challenge we face with tasks that take longer to execute is that of running into the execution timeout error (that’s at 6 minutes — Script runtime — as of 10-Feb-2023) and this article … elaborates on how to bypass script execution timeout with a detailed example.
As noted in this post by Sourabh Choraria the Apps Script runtime limit can be a bit of a challenge. If you’re encounter problems my first tip would be review your script and see what you can optimize. A common problem I often see in Google Sheets is using .getValue()/.setValue() (singular) not .getValues()/.setValues() (plural).
If even with script optimization you are still having issues Sourabh provides a very adaptable ‘design pattern’ for Google Sheets. The pattern is one I can personally relate too and very similar to the approach I used in my own projects. The source post has everything you need to try this out for yourself.
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.
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.
