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.
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.
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.
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.
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.
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.
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.
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.
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.
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!
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.
