Introducing AppSheetApp: A Google Apps Script library/helper class for the AppSheet API

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.

Source: Introducing AppSheetApp: A Google Apps Script library/helper class for the AppSheet API

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

📮`MMailApp` for Google Apps Script: GmailApp+MailApp+Gmail API

The library lets you send emails, using MailApp, GmailApp, and Gmail API. The library takes care of parameters, so you do not need to care about which method to use. I had trouble with my project when I wanted to automatically send emails and suddenly for me we’ve reached the daily limit. This is why I’ve decided to make some research and be sure fewer bad things happen in the future.

If you are looking for a deep dive into Google Apps Script powered email sending options you are at the door of enlightenment. In this post from Max Makrov you not only to get an explanation of the options and benefits for sending email with MailApp, GmailApp and the Gmail API, but also a handy library, MmailApp. which makes it easy to switch between all three.

Source: 📮`MMailApp` for Google Apps Script: GmailApp+MailApp+Gmail API

SuperFetch Plugins: Apps Script streaming with Tank and Drive (Copying very large files to/from Google Drive with Apps Script)

Tank and Drv are SuperFetch plugins to emulate streaming and use the Drive REST API with Apps Script … This article will cover how to copy very large files using Tank to stream and Drv to upload and download partial content. The Apps Script Drive services have a limit on the size of files you can write, and very large memory usage can potentially cause Apps Script to fall over mysteriously

Clever stuff as always from Bruce Mcpherson, this time looking at how you can handle large files with Google Apps Script. We’ve featured some of Bruce’s other SuperFetch posts on Pulse, but developers can benefit from exploring the entire series so far on Bruce’s website. As well as SuperFetch plugins for Firebase and Twitter I’m personally interested in the evolution of the Google Drive client (Drv).

Source: SuperFetch Plugins: Apps Script streaming with Tank and Drive – Desktop Liberation

Happy Birthday Google Apps Script – In celebration a searchable community database of Apps Script libraries..

Since Google Apps Script was released on August 19th, 2009, it is used by a lot of users. By this, now there are a lot of useful libraries of Google Apps Script (GAS) in all over the world. But when I want to search a GAS library, I always use Google search engine. Unfortunately, in the current stage, the libraries cannot be directly searched by a database. On January 11th, 2020, a proposal for the database of Google Apps Script Library has been proposed by Andrew Roberts. When I have discussing about this with him, I thought that I tried to think of a sample database. So I prepared this…

Google Apps Script turns 13 years old today and in celebration we are highlighting this combined community contribution. As you will see from the source repo commit history this searchable database of Google Apps Script libraries has been around for a while, but given what it represents we thought worth celebrating.

Source: GitHub – tanaikech/Google-Apps-Script-Library-Database: This is for the Google Apps Script Library Database and a web application for searching the libraries..

Google Apps Script library if you need to get a Google Sheet as a PDF

Library for converting Google Sheets Into PDF


  • All PDF settings including colontitles = custom headers and footers.
  • Input parameters is a single plain object.

The library uses the printing features of Google Spreadsheets to provide a complete representation of a document in a different format. It contains a huge amount of features There are settings such as page size, headers and footers, colontitles, gridlines, notes and more.

Source: Max-Makhrov/sheets2pdf_gs: Library for converting Google Sheets Into PDF

SuperFetch – a proxy enhancement to Apps Script UrlFetch – Desktop Liberation

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.

Source: SuperFetch – a proxy enhancement to Apps Script UrlFetch – Desktop Liberation

Simple but powerful Apps Script Unit Test library – Desktop Liberation

Image credit: the JavaScript Code CC-BY Dmitry Baranovskiy

Unit testing your code with this Apps Script Unit test library as you go along will makes it easy to immediately catch errors, and keeping a running test repertoire ensures that you don’t break anything.

When you start developing more complex Google Workspace solutions like add-ons and Chat apps it’s worth considering how you will test, debug and refactor your code. As noted in this post from Bruce Mcpherson the Apps Script community has published a number of different solutions/approaches to unit tests, a number of these appearing in Pulse.  This latest post from Bruce looks at the bmUnitTest library he has developed highlighting how it can be set up and used.

Source: Simple but powerful Apps Script Unit Test library – Desktop Liberation

JSONata – JSON query and transformation language in Google Apps Script

One of the benefits of Google moving Apps Script to the V8 engine is the possibility to drop in existing JavaScript libraries. Max Makhrov recently highlighted on Twitter how JSONata, which can be used to query and transform JSON data can be used in Google Apps Script:

JSONata is a lightweight query and transformation language for JSON data. Inspired by the ‘location path’ semantics of XPath 3.1, it allows sophisticated queries to be expressed in a compact and intuitive notation. A rich complement of built in operators and functions is provided for manipulating and combining extracted data, and the results of queries can be formatted into any JSON output structure using familiar JSON object and array syntax. Coupled with the facility to create user defined functions, advanced expressions can be built to tackle any JSON query and transformation task. – JSONata

JSONata is a solution better understood by trying it out, which you can do thanks to the script project shared by Max on Twitter or on the JSONata website. If you are interested in data query/manipulation solutions for Google Apps Script you might also want to check out our previous post on using AlaSQL for Apps Script.

Update: Max has published JSONata as an Apps Script library 

SheetQuery: An ORM-Like Query Builder for Google Sheets and Google Apps Script

Image credit: Vance Lucas

A simple and small Google Apps Script library for quickly and easily finding and updating records in Google Sheets with a familiar ORM-like syntax

Following our previous post on Converting Google Sheets cell values to an object array, Vance Lucas (@vlucas) got in touch to highlight the SheetQuery library he has created which as well as being able to get Google Sheet data as an object array has some additional nice features for updating cell values. As Vance highlights:

sometimes working with spreadsheets to find and update specific rows of data can be awkward and tedious. There is no direct built-in way to search for specific values in rows by headings. To do this with the Google-supplied APIs, you have to keep track of row index numbers, column index numbers, and arrays of row data while planning your updates. It’s not fun code to write, and it’s relatively error-prone, especially if you are deleting rows, which causes the row index numbers to shift dynamically.

The library includes a .where method which can be used to apply a filtering function to select the rows of a spreadsheet to be read and/or updated. If you are regularly developing scripts that interact with Google Sheets data this can be a great library to keep in mind.

Source: SheetQuery: An ORM-Like Query Builder for Google Sheets and Google Apps Script