AppsScriptPulse

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

Google Apps Script – What a difference 5 years make – Episode 2 [Property Service and Spreadsheet Service updates] – Desktop Liberation

Image credit: Bruce Mcpherson

Image credit: Bruce Mcpherson

How google apps script has changed over the past 5 years. Episode 2 in a series of post covers the Properties Service and the Spreadsheet service

Bruce Mcpherson continues his look at changes in Google Apps Script over the last 5 years, this time looking at PropertiesService and SpreadsheetApp. This post is worth a read just to check you’ve not missed any new methods for working with Google Sheets, because over the last 5 years there have been many!

Source: Google Apps Script – What a difference 5 years make – Episode 2 – Desktop Liberation

Random and fake test data in Sheets with Google Apps Script – Desktop Liberation

Image credit: Bruce Mcpherson

Image credit: Bruce Mcpherson

Sometimes you need to generate some fake data for a spreadsheet. In this post I’ll cover a few utilities in 2 separate libraries that can help with this.

Bruce Mcpherson has put together a Google Apps Script library which is a port of Faker.js and elements of d3.js to make it easier to generate random data in your projects. You can see the full list of Faker.js methods which includes a wide range from names/addresses to dates to just lorem text. The library can also generate various fake datasets, strings and selectors.

Source: Random and fake test data in Sheets with Google Apps Script – Desktop Liberation

Google Apps Script – What a difference 5 years make – Desktop Liberation

It’s been over 5 years since my book ‘Going Gas‘ was published, and Apps Script evolution means that a number of sections of it is now pretty out of date. I don’t think I have the energy to do a rewrite – so I’m going to do a critique, chapter by chapter, of where it’s now wrong as a way to show how much Apps Script has improved (or otherewise) over the years.

We recently highlighted how you could preview excerpts from Bruce Mcpherson’s ‘Going GAS’ book. Bruce has now followed up by using the book contents from 2016 to highlight differences in the current version of Google Apps Script.

Source: Google Apps Script – What a difference 5 years make – Desktop Liberation

Apps Script – How to track library and script usage – Desktop Liberation

You’ve written a great Apps Script library and you want to know how many people are using it, and perhaps even which parts of the library they are using, and how often. Perhaps you have a new version out, and need to know whether anybody is still using the old version. You may also have a set of libraries and you want to know whether users have flipped to the new version. All these things are important to know, but impossible to find out using the Apps script platform as is.

Bruce Mcpherson provides a detailed solution for tracking Google Apps Script library usage using the Properties Service. The source post contains everything you need to integrate this solution into your existing libraries as well as a quick way to chart usage.

Source: Apps Script – How to track library and script usage – Desktop Liberation