AppsScriptPulse

12 Years and 1000 pages in Office, Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

I’ve been running this site for about 12 years ago. with over 1000 pages of content, here’s some of the high (and low) lights. I came to Apps Script not long after it was available, my first foray into it was probably around 2010, and I started writing about it not long afterwards.

I’m sure many Google Workspace developers are familiar with the work of Bruce Mcpherson. Regardless of whether or not you have, this is a nice summary of the last 12+ years of work published by Bruce last year but well worth revisiting. It covers everything from his move from VBA to focus on Apps Script, useful script libraries and code as well as explorations into other Google Cloud products.

Source: 12 Years and 1000 pages in Office,Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

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

Using Google Sheets via BigQuery from Google Apps Script with service accounts

I’m using the JSON API for BigQuery rather than the Apps Script advanced service, since I have centralized all my BigQuery datasets in one project, and want to use a Service Account for authorization

Google Apps Script includes an Advanced service to interact with BigQuery. A limitation with both the Workspace services and Advanced Services don’t play nice if you need to use a service account. This post from Bruce Mcpherson provides details of how you can setup your Google Apps Script project to use a service account with BigQuery.

Source: Using Google sheets via Bigquery from Apps Script – Desktop Liberation

SuperFetch plugin – Firebase client for Google Apps Script – Desktop Liberation

Another in the SuperFetch (a proxy for Apps Script UrlFetchApp) plugins series, Frb is a plugin to access a Firebase Real time database.

If you want to take your use of APIs a little further Bruce Mcpherson is continuing his series exploring his recently published SuperFetch library showing how a client can be setup to interact with Firebase. As Bruce highlights: “Firebase is pretty fast, so there’s not a huge speed benefit from caching, but if you’re on a pay as go plan, SuperFertch caching can reduce your Firebase costs.”

The source post provided by Bruce provides everything to need to set up the SuperFetch client and Firebase project.

Source: SuperFetch plugin – Firebase client for Apps Script – Desktop Liberation

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

Opportunities and workflows inlining Google Apps Script libraries – Desktop Liberation

This article will look at some of the opportunities you’ll have when you pull in your libraries inline rather than leaving them as references to external files, all without leaving the IDE

For the more advanced Google Apps Script developer this is a great article to get your teeth into which should also hopefully give you some tips to help manage and maintain complex Apps Script projects.

Source: Opportunities and workflows inlining Apps Script libraries – Desktop Liberation

Pull libraries inline to your Google Apps Script project – Desktop Liberation

Inline converter

This piece of work was both challenging and a lot of fun to produce, and it’s something I’ve wanted to get round to for a long time. Apps Script libraries are a great way to reuse work you and others have done, but you have to be careful that they don’t get out of date. Libraries that refer to other libraries are complex to keep up to date, and worst of all they may disappear or their permissions change at any time.

Google recommend you don’t use them in Add-ons, and although they focus on ‘load efficiency’ (I did a study on this a few years back and found absolutely no evidence of a measurable load penalty for libraries: see Measuring library load speed), the above are probably better reasons to bundle all the code you use in your app or Add-on.

This builds on the work from Import, export and mix container bound and standalone Apps Script projects and I release it as a library (which you can of course use to inline it to your own project if you want!)

As noted by Bruce there may be a number of reasons you might want to inline libraries as part of your Apps Script projects, particularly if you are reusing third-party libraries where there is a risk the code could disappear.

Source: Pull libraries inline to your Apps Script project – Desktop Liberation

Import, export and mix container bound and standalone Google Apps Script projects – Desktop Liberation

This article covers how to pull scripts from multiple projects and import them into another project. You can even use this to make container bound script standalone, or visa versa. … This article will cover the library that does all that, along with various other usage examples – for example, pulling in code snippets from or libraries, merging manifests, or testing add-ons.

Some more magic from Bruce Mcpherson this time creating and documenting a library that can be used to copy/replace scripts. Bruce’s illustrates this with an example of pushing a standalone script to a container bound Google Sheet project and I’m sure you can find many other ways this could be useful to maintain script projects.

Source: Import, export and mix container bound and standalone Apps Script projects – Desktop Liberation

Google Workspace Editor Add-on for encrypting/decrypting columns in Google Sheets – Desktop Liberation

In ‘Merging sheets from multiple sources and encrypting selected columns’ I published some code for selectively copying columns from multiple input spreadsheets/sheets to create summary sheets, and optionally encrypting columns. The idea was to distribute the same sheet to multiple people, along with private key(s) to decrypt columns to which they should have access. This seems a pretty handy thing to make into an Editor Add-on. I’m too impatient and life’s too short to bother getting into the Add-on publishing process, so I haven’t officially released this one personally, but here is a fully functional decryption Add-on that any of you are welcome to fiddle with and publish yourself.

Having previously featured some of Bruce Mcphersons previous posts on encrypting/decrypting Google Sheets data it seems fitting to share the post containing the final add-on. Even if you are not interested in the add-on’s functionality this post can still be worth a look to see how Bruce structures an add-on and uses Vue and Vuex to create the UI rendered with HTMLService .

Source: Add-on for decrypting columns in Google Sheets – Desktop Liberation

Merging Google Sheets from multiple sources and encrypting selected columns – Desktop Liberation

This post introduces a library that can summarize selected columns from multiple sheets across multiple spreadsheets into 1 spreadsheet, as well as to optionally apply a public/private key data encryption scheme to selected columns, all via a simple JSON definition file.

Bruce Mcpherson continues to be one of my favourite Google Apps Script authors and often you get to gain an insight into his development process. This latest post is the latest in a series that follows a post on Super simple cipher library for Apps Script encryption and decryption. The source post shared here continues this journey, in particular, highlighting the use of Google Sheets Developer Metadata to store data bound to the spreadsheet.

Source: Merging sheets from multiple sources and encrypting selected columns – Desktop Liberation

Refreshing an oauth token in a Google Workspace Add-on with Google Apps Script – Desktop Liberation

Image credit: Igor Ovsyannykov, CC0, via Wikimedia Commons

It’s very convenient to use ScriptApp.getOAuthToken() in an addon to reuse the token from the server side in your client side add-on code. However, these have a limited time to live (1 hour), so what to do if your add on sits around for longer than that? One solution would be to always go for another token every time you needed one, but that would be wasteful. Luckily, there’s a way to check a token to see how much life it has, and only get a new one if it’s almost expired.

There is also a related discussion thread on this post with the author, Bruce Mcpherson, in the Google Apps Script Google Group.

Source: Refreshing an oauth token in add-on – Desktop Liberation

SQL for Apps Script – it’s here – Desktop Liberation

alaa kaddour, CC BY-SA 4.0, via Wikimedia Commons

While working on Sheets Workbook functions – converted to Apps Script I realized that I’d need to tackle the query language at some point, so I figured I may as well go the whole hog and implement a comprehensive SQL variant for Apps Script. Luckily though, I found alasql so with a few tweaks it was ready to go!

A couple Google Apps Script community contributors have shared solutions for using the AlaSQL.js library. Latest come from Bruce Mcpherson, which is included as the source link. If you use Bruce’s fiddler library his post is worth checking out as he provides examples showing how both libraries can be used together. Another version of AlaSQL.js you should look at is Alex Ivanov’s AlaSQLGS which also includes some data and code samples.

Source: SQL for Apps Script – it’s here – Desktop Liberation

Sheets Workbook functions in Apps Script – Date and Time and Temporal primer – Desktop Liberation

Timezones

If you are handling data from sheets it might be useful to use the same logic as is available in the worksheet for common tasks rather than reinventing them. Dates and Times are especially complicated as JavaScript dates are not good with timezones and date arithmetic, especially since spreadsheets and scripts can exist in different timezones from each other. I’m using Temporal – dates, times and timezones and the proposed new Date system for ECMAScript to emulate what Sheets functions do, and I’ll go through the implementation as a learning aid for Temporal as much as a documentation of the functions.

Source: Sheets Workbook functions in Apps Script – Date and Time and Temporal primer – Desktop Liberation