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