FilesApp is a GAS library for retrieving file and folder list in Google Drive

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.

The FilesApp library by Kanshi Tanaike has been around since 2018. I have to confess I wasn’t aware of this library until I read a more recent post, Retrieving Total File Sizes in Specific Folder of Google Drive using Google Apps Script. Similar to my own post on Creating a Google Drive report in Google Sheets the FilesApp library directly communicates to the Drive API, this library however goes a step further in terms of performance by using UrlFetchApp.fetchAll to handle some asynchronous Drive API calls.

Source: GAS Library – FilesApp

Check external links in Google Docs with Google Apps Script widget

Image credit: Mykyta Khmel

Google Docs is a popular document editor for creating and editing text documents, spreadsheets, and presentations. With the use of Google Apps Script, a scripting language for automating tasks in Google Apps, users can enhance their document editing experience by adding custom functionality to Google Docs. One such functionality is the ability to check URLs within a Google Document.

Nice little project that has some useful code snippets/functionality you might find useful in your own projects which extracts and tests hyperlinks included in a Google Doc. The post provides an outline of how the script is setup and lets the user interact via a sidebar. I always find interacting with Google Docs a little daunting, particular parsing the document structure. The post links to a useful gist by Mogsdad (David Bingham) with some basic Google Document utility scripts.

Source: Check link statuses in Google Docs with Apps Script widget

Apps Script: solutions to the issue “installable triggers failing when created from another trigger function in V8”

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.

Source: Apps Script: solutions to the issue “installable triggers failing when created from another trigger function in V8”

Creating a Google Drive report in Google Sheets: Making Google Workspace Enterprise solutions with Google Apps Script

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!

Source: Creating a Google Drive report in Google Sheets: Making Google Workspace Enterprise solutions with Google Apps Script

Putting values from of all Google Sheets in Google Drive folder into a master Sheet with low process cost using Google Apps Script


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.

Source: Putting Values of All Spreadsheets in Folder to Master Spreadsheet with Low Process cost using Google Apps Script

How to combine arrays of data into one Google Sheet with Google Apps Script

In this post, we’re going to look at how we can easily combine arrays of data on separate sheets in a Google Sheet and add them on one single sheet. We’re going to use the relatively new spread operator (…) to do this.

A little knowledge about data manipulation can get you a long way, particularly if you are doing lots of work in Google Sheets. This post from Baz Roberts is an opportunity to learn about spread syntax (also known as the spread operator) in arrays. Have a read and hopefully you’ll see the benefits.

Source: How to combine arrays of data into one sheet – this post is also available to read on

11 new analytical functions in Google Sheets for 2023

Get the lowdown on the 11 new analytical functions introduced to Google Sheets in 2023, including LET and array manipulation functions.

For developers Google Sheets is becoming an increasingly rich playground. Features like the SQL like QUERY function have enabled users to do more with their data. More recently Google have released a range of new functions which I’m sure many developers and power users will be able to relate to. New functions like EPOCHTODATE , LET as well as other recent additions like MAP and REDUCE will be familiar concepts to many developers. In this post from Ben Collins the latest Google Sheet functions are reviewed.

Source: 11 New Analytical Functions In Google Sheets For 2023

Custom function for get subtotals for each group in Google Sheets using Google Apps Script

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.

Source: Subtotals for each group in Google Sheets

Programmatically handle long running Google Sheets tasks with Google Apps Script


Image credit: Sourabh Choraria

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.

Source: Programmatically handle long running tasks in Apps Script

An introduction into using Cloud Run to control Google Sheets - Part 1


Image credit: Google

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.

Source: Cloud Run for Google Sheets — Part 1