Authentication and Authorization are probably the most confusing aspects of working with APIS. There are so many different options that it’s hard to pick which one to use, and the examples and documentation often refer […]
We’re all used to dealing with iterables such as Arrays, Maps and Sets. Creating a collection of input items, transforming then outputting is what we do all the time. However, sometimes that’s not possible – […]
My objective for a project I’m working on was to find a secure and simple way to publish ephemeral content without actually sharing files or hosting it anywhere. I’m not going to lie – this […]
A Google Cloud run hosted puppeteer with a graphQL interface and examples in Node and Apps Script. […]
If you’ve used my Convert any file with Apps Script you’ll know that it can take a wide range of files (currently 53 different mimeTypes, 42 types of imports, and 26 kinds of exports) and […]
Shared cached data between multiple scripts with this enhanced Apps Script Cacheservice library […]
Extend a client library object by proxying and intercepting calls to its native methods. This example adds functionality to a redis client, and also gives tips on using GCP secret manger for configuration. […]
How to add Google Material Colors and your own custom schemes to chroma […]
I’ve written a few articles on here about JavaScript proxy and reflect. I use both extensively not only in Node projects but also in Apps Script. In this article I’ll demonstrate how to extend built-in […]
Here’s another article on playing around with color in Apps Script. Let’s say you have a defined list of colors in a scheme (for example paint or textile colors or perhaps a predefined scheme like […]
I came across Google’s FactCheckTools API today, so I thought it might be fun to add to my bmApiCentral Apps Script library. This API can be used to both search popular fake news claims and […]
Apps Script library to convert between file types, including OCR for image to documents, with a huge repertoire of conversion combinations. […]
Apps Script library to derive colors and color mixtures based on text to help contextualize sheets content. […]
Sometimes you want to hide data in a sheet that’s being used for demo purposes.Here’s some options and library to help you do it. […]
Apps Script/JavaScript library to generate prettified HTML Markup from JSON […]
If you are playing around with Sheet colors with Apps Script, you sometimes find yourself with font colors that don’t go well with the background colors you’ve chosen. However, we can use Yiq values to […]
How to make getting JavaScript code in the right order less of a chore, and detect object property typos before they become a problem. […]
SuperFetch is a proxy for UrlFetchApp with additional features such as built-in caching – see SuperFetch – a proxy enhancement to Apps Script UrlFetch for how it works and what it does. This is another […]
Tank and Drv are SuperFetch plugins to emulate streaming and use the Drive REST API with Apps Script. SuperFetch is a proxy for UrlFetchApp with additional features – see SuperFetch – a proxy enhancement to Apps […]
Tank and Drv are SuperFetch plugins to emulate streaming and use the Drive REST API with Apps Script. SuperFetch is a proxy for UrlFetchApp with additional features – see SuperFetch – a proxy enhancement to […]
Drv is a SuperFetch plugin to access the Google Drive API. SuperFetch is a proxy for UrlFetchApp with additional features – see SuperFetch – a proxy enhancement to Apps Script UrlFetch for how it works […]
Twt is a SuperFetch plugin to easily access to the Twitter v2 API. SuperFetch is a proxy for UrlFetchApp with additional features – see SuperFetch – a proxy enhancement to Apps Script UrlFetch for how […]
Motivation Goa is a library to support OAuth2 for Apps Script connecting to a variety of services, using a variety of Authentication flows and processes. There are plenty of other articles on Goa on this […]
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 […]
Motivation Caching is a great way to improve performance, avoid rate limit problems and even save money if you are accessing a paid for API from Apps Script. A big limitation though is that cache […]
It’s a pretty common requirement, especially when you’re posting to an API, to split an array of data into manageable chunks, and most often the solution is to make an array of arrays – with […]
Why unit testing? There are many test packages for Node (my favorite is ava) and there are also a few for Apps Script but I couldn’t find one that was exactly what I was after, […]
This article is a little more advanced than usual, and we’ll cover a number topics in one go. A JavaScript proxy gives us the ability to intercept JavaScript as it attempts to access the properties […]
There’s a really basic thing missing from Google Forms. What’s missing ? A way of stamping responses with some sort of code to be able to link them back to some user or some other […]
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. It’s a follow on from the articles on […]
Sometimes you have a forest of spreadsheets each with multiple sheets, but you only want to share a selection of those sheets. You could cut and paste, but that’s error prone and difficult to repeat. […]
You may have come across my post on SQL for Apps Script it’s here where I shared a library for using SQL syntax in Apps Script to do things like joining tables. For some cases, this […]
Encryption and decryption can be a bit cheesy to come to grips with. The most common library is probably cryptojs which has endless options. Since most people simply want to encrypt some data and decrypt […]
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 […]
I’ve published a few examples of this in other projects (for example Implementing a client side progress bar reporting on server side work for htmlService) but I got a request from someone on LinkedIn to […]
This is (probably) the last in a series of posts describing how Fiddler for Sheets can be used to help with formula manipulation and creation, as well as column insertion and deletion without caring too […]
In 2 ways to create and preserve formulas with fiddler for Google Apps Script I gave some examples of how to preserve formulas if you are using fiddler for Sheets. Fiddler does have a number of […]
In Resuscitating the Apps Script execution transcript – JavaScript Proxy and Reflect to the rescue I showed how we could use the ES6 Proxy global object to intercept calls to Apps Script services so we could […]
I’ve had a few queries from fiddler users about how to deal with formulas. Fiddler is primarly for abstracting spreadsheet values so they can be manipulated and updated without worrying too much about the physical […]
There are quite a few exchange rate APIs out there to get the latest and historic exchange rates. Google used to have a nice, simple and free one – the finance API, but that was […]
If you’ve been using Apps Script for a while, since back when it was running on the Rhino JavaScript emulator; see (What JavaScript engine is Apps Script running on?), you’ll remember the Execution Transcript. This […]
javaWhen I first read about Symbols coming to JavaScript, I couldn’t figure out what the point was or even what they were, so I just kind of parked it. Lately I’ve been doing some work […]
This article will cover the translation of the Sheets workbook database type functions for Apps Script. All of them will be covered. Motivation If you are handling data from sheets it might be useful to […]
This article will cover the translation of the Sheets workbook Array type functions for Apps Script. Most of them will be covered, except for a few exotic ones that I might do later if anybody […]
The method for doing this is actually part of the bmFolderFun library documented in A handier way of accessing Google Drive folders and files from Apps Script but it can be really useful for keeping […]
In Blistering fast file streaming between Drive and Cloud Storage using Cloud Run I showed how you could use Cloud Run to hand off transfers between Drive and Cloud Storage to make them faster and […]
The usual way to access files and folders on Drive from Apps Script is via their ID, yet on other platforms we’re more used to using file and folder paths. This can be a bit […]
Let’s say you need to find out how your function was called, either for in app debugging or some other purpose. A JavaScript Error has a non standard property (stack) that contains some info about […]
Today’s snippet is going to be shorter than usual, because there’s a really easy solution. Many objects have a default method like toString(). You may want to add one of those to your own functions […]
This article will cover the translation of the Sheets workbook functions for Date and Time manipulation to Apps Script. Virtually all of them will be covered, except for a few exotic ones that I might […]
One of the things missing from Apps Script compared to VBA, is the ability to run workbook functions, which you can do in VBA I’ve often thought about implementing the sheets functions from Apps Script, […]
The idea for Temporal came from a conversation about how poor the Date implementation of JavaScript is. Some history of that is in this blogpost, where Brendan Eich reveals he pretty much copied Java date […]
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. Faker This is a node module to […]
Motivation 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 […]
Github as an Apps Script cache platform Another plugin available for Apps script library with plugins for multiple backend cache platforms so we can use Github as a back end for caching large objects across […]
Upstash as an Apps Script cache platform Upstash is a brand new service offering a serverless redis over https via a GraphQL API. Previously redis was hard to use along with Apps Script since we […]
Info Card customization By default the info card appears when you hover over a node in the scrviz visualization. Although the info card is they key tool for investigating scrviz entries, it can be annoying […]
You want to include an Apps Script library, and you know it’s name, but not its id. A pain right? Here’s 3 easy steps to find it in https://scrviz.web.app Step 1 – find the library […]
Google Cloud Storage as Cache platform In Apps script library with plugins for multiple backend cache platforms I covered a way to get more into cache and property services, and the bmCrusher library came with built […]
Motivation This library used to be part of my cUseful collection, but I’ve decided to pull out into a library in it’s own right. The idea is not only to be able to squeeze more […]
In Every Google Apps Script project on Github visualized I introduced Vizzy which uses GitHub data as its source. That same data package is available publicly in case you want to build something on top of […]
Motivation There are so many Apps Script projects out there where the source code is published on Github, but it’s hard to find what you want. Whether it’s a library, an example of an add-on, […]
Motivation for apps script example I originally create both qottle and rottler for various node projects, but realized that I had some Apps Script that could be solved with them too. One of them was […]
If you use some of my libraries, you may have come across the Fiddler class, which abstracts sheet data and provides a more functional way to manage data from multiple sheets in a Google Spreadsheet. […]
rottler – a rate limit helper Working with rate limits can be hard, so the purpose of rottler is to provide no only a way of testing rate limit strategy, but also an helper to […]
Vuex If you use Vue.js, then you probably use (or should consider using) Vuex for state management. Like most Vue.js related things, it’s at its easiest when used with Node tooling, but you can also […]
Include patterns When using HtmlService it can be quite messy to organize your script/js/html/css files and vue components so that they can be inserted into your add-on/webapp. This becomes much more complex when you want […]
When a browser tab goes in and out of view, it’s possible you want to do something differently. If you’re writing Apps Script add-ons this is especially true. TabVisibility is a class to allow […]
If you use some of my libraries, you may have come across the Fiddler class, which abstracts sheet data and provides a more functional way to manage data from multiple sheets in a Google Spreadsheet. […]
Create beautiful Apps Script add-ons with Vuetify and Vue.js The idea of ‘reactive programming’ has been around for many years, but frameworks such as React, Vue and so on have moved it into the mainstream. […]
TimeSimmer When creating simulations, and especially when testing or demoing apps that simulate events over time, you often need to speed up or slow down time. You may even need to slow it up or […]
One of the challenges with Apps Script V8 compared to Rhino is that you can’t be sure of the order of global variable initialization. Personally, this is not an issue for me, as I avoid […]
Qottle recipe for managing asynchronous queues with duplicates Qottle is a queue for asynchronous tasks with prioritization, ratelimit, and concurrency support. It can also detect and reject duplicate requests. This recipe shows an example of […]
Qottle is a queue for asynchronous tasks with prioritization, ratelimit, and concurrency support. This recipe shows an example of a qottle queue for continuous, controlled polling. Qottle recipe for polling You can use qottle […]
Qottle If you are using an API you often need to queue tasks that are asynchronous and constrained by other factors such as rate limits of the API. Qottle is a general purpose queue manager […]
This article looks at how to extend classes and apply that easily handling data structures presented as an array of bytes as you’d have to do when dealing with binary data from a file. […]
There are some new problems to do with the accessibility of functions in the global space which depend on a number of factors such as the order of declaration, and whether they are in a […]
Apps Script V8 implements ArrayBuffers and Typed arrays from ES6. If you’ve used Apps Script Crypto Utilities or done any work with Blobs, then this is what’s going on behind the scenes, now exposed as […]
Apps Script V8 implements a couple of useful new ES6 structures – Maps and Sets. Here’s what they are and how to use them Maps and Sets: What are they? These are a collection of […]
Multiple Script Files: The problem Apps Script V8 doesn’t have a module loader system. If you’re used to developing in NodeJs, you’ll also be familiar with importing and exporting to include required modules in your […]
V8 adds template literals from JavaScript ES6. Template literals: What are they? It’s a shorthand way of using a template into which variables are substituted in a string. This allows for better reuse of string […]
Apps Script V8 adds destructuring from JavaScript ES6. Legacy Apps Script already had destructuring of arrays added fairly recently, but v8 gives full a destructuring capability. Destructuring: What is it? It’s a way of plucking […]
Function declarations versus arrow functions JavaScript V8 adds the arrow function declarator from modern JavaScript. This is a handy shorthand but it’s more than just that. There are some behavioral differences too that you’ll need […]
var, const and let One of the key things that V8 has sorted out is the scope of variables. Using var to declare variables meant that anything declared within the scope of a function could […]
Apps script now supports JavaScript ES6 Apps Script V8 now supports the same modern JavaScript you can use when writing for the browser. Up until now, you had to write in a dialect of JavaScript-based […]
Looking into how various operations perform as the size of the sheet increase provided some interesting data. The objective is to see whether the time to execute particular operations is directly proportional to the size […]
This is an Apps Script version of the code referred to in Flattening arrays for Elastic Search. The point of that article is to prepare data arriving from GraphQL for elastic search, but it also solves […]
In Google Oauth2 VBA authentication I showed how to use this class. All associated libraries are available in the cDataSet.xlsm library downloadable here, or through gistthat, as described here. You’ll need the cRest module. Examples […]
The easyCron service is a cloud based scheduler for recurrent jobs. Google Apps Script has it’s own scheduler for triggering tasks, but you may prefer the management capabilities with easyCron. You have to sign up, […]
In Finding where Drive hosting is being used in Sites, we created a couple of sheets by examining the hosted files referenced in a Google Site so that we could sort out the world after […]
Now that Drive hosting is going away, we face the task of discovering where its used. One place that it may be used a lot is if you are using the old version of Sites. […]
In Finding where Drive hosting is being used in Sites, we created a couple of sheets by examining the hosted files referenced in a Google Site so that we could sort out the world after […]
I used to use Slideshare a fair bit but I’d forgotten all about it until today. I wanted to see what I had there and if anyone was looking at it. I suppose I could […]
The Apps Script team released this post today about integrating BigQuery and sheets. So this means you can use SQL to access any sheets that you’ve linked to bigQuery. You can follow the instructions in […]
If you want to create a file of over 10mb in Drive, you can’t use either the DriveApp service or the Advanced Drive service. Even if you use the JSON API, the restriction of 10mb […]
A question in the Apps Script community prompted me to write this post about file conversion and the nature of files on Drive. If you use the Google Drive client on Windows or Mac, and […]
If you have a fairly large Google Site, and you make mistakes like we all do, at some point you’ll need to do a mass update to all the pages. It happened to me yesterday. […]
In Minimizing maps directionfinder api calls I showed how to make the most of your directions API quota in the context of a spreadsheet. Here’s an example of calling it directly, say when you are […]
If you use the Google APIs, you’ll have been hit with the “service invoked too many times in one day” problem. In Backing off on rate limiting I showed how to deal with rate limited […]
The old UserProperties and ScriptProperties services have been deprecated and replaced by the new PropertiesService. In the old service, a user property was available from all scripts to a particular user, and a script property […]
Using DriveApp service, sometimes you want to specify a folder path like /abc/def/ghi and get the folder object of ‘ghi’. With the old DocsList service this was possible, but it doesn’t exist with the Drive […]
Data from sheets is organized as an array of rows of arrays of column values. Sometimes that’s not convenient and prevents you from effectively using the useful mapping array methods that I covered in Highlight […]
I covered this topic in Highlight duplicate rows in a sheet – map, filter and every, so now we’ll build on that a little and introduce the .reduce Array function. As before the problem is […]
Someone asked this question on the forum the other day, so I thought it would make a good example of how to use Array functions to simplify looking at sheet data. I normally would use […]
In Apps Script, it’s easy var timeStamp = new Date().getTime(); var date = new Date(timestamp); But sheets doesn’t use JavaScript timestamps for dates, it uses the number of days since 1st Jan 1900, with hours/mins/secs […]
Excel Tables are a nice feature. They size automatically and they are easy to reference. Google Sheets doesn’t have that and it can be difficult to find data on a sheet, where there are blank […]
You all know that trying to minimize calls to the spreadsheet API from Apps Script can dramatically speed things up. Let’s take a look at hiding and showing rows and columns, where we’re starting from […]
The Itunes API is very nice. You can find details on it at ITunes API I thought I may as well add an Apps Script wrapper library to it, since it can provide a wealth […]
Sometimes you need to fill a range in a sheet, either with a single value, or with some calculated value. It’s a pretty straightforward pattern, but if you are an Excel user, you’ll be used […]
Here’s a general purpose timer for wrapping functions without having to edit them to put timers around them. This is something I often have to do if tracking down performance problems, so I thought I’d […]
In Getting insights into Sheets performance I mentioned that I was taking a look under the covers of Sheets to see how performance is doing and introduced a useful function for timing stuff. The first […]
You all know that when you use getValues(), you’ll get an array of rows of data, each element of which is an array of columns. There are times that you’d rather have it the other […]
Since it’s almost time for Eurovision again, I thought I’d see if I could find something to do with predicting the result, rather than analyzing the results afterwards (as in this Eurovision results with crossfilter […]
This is a very small Apps Script snippet to generate column addresses from column numbers that can be useful in things like Sheet Addons. For example 1 gives A, 27 – AA, 703 – AAA […]
Although there is a TRANSPOSE function in Sheets, you cant access sheets functions from within Apps Script. However, using Advanced Array functions it’s fairly easy to transpose an array of values. Let’s say we want […]
In Using a cross filter with Google Apps Script I showed how to use the powerful cross filter library to do some data wrangling. But using some of JavaScript’s Advanced Array functions, and working with […]
I’ve called this post ‘removing duplicate paragraphs, but actually it’s a bit more than that – it’s removing paragraphs using a filtering function to compare the current paragraph with the next. By default, it will […]
I often waste a lot of time tracking down stupid bugs that are to do with passing the wrong kind of argument to a function. As you know, JavaScript is not strongly typed, and this […]
If you are using fiddler to format your sheet, as described in Formatting sheet column data with fiddler you probably don’t need to bother with this article, as fiddler already does it behind the scenes, but if […]
Header formatting with fiddler shows how to use fiddler to easily set formats for your sheet headings and Formatting sheet column data with fiddler covers data formatting options. However there are occasions that you might want to […]
Here’s another little Docs annoyance that can easily be cleaned up with a little automation. If you are using headings in your document, and insert blank lines beside a heading, you end up with uneven […]
When creating heading levels in a document, it’s easy to get confused about which level a paragraph should be at. If you reduce a header level, the headings below need to be adjusted also, and […]
For setting up D3Gas you’ll need the library, and for background on D3 and Apps Script read Using D3 in server side Gas Set up You’ll need the d3Gas library. 1vZzEtFKAG_PHn44HgEdSBave5NQ-SprisJ0Ngid0ovahwEOMkBO1s6DX Then you can do this […]
If you are doing anything with HTMLService you have to duplicate a fair amount of stuff, usually by copying them from previous projects. Not only html and css, but also useful code that you’ve built […]
In Organizing asynchronous calls to google.script.run I showed how to avoid spaghetti with google.script.run callbacks to server-side functions that needed to run sequentially. You should take a look at that first if you are unfamiliar […]
There’s a lot of questions on the Google Apps Script forum about handling asynchronous calls from an HtmlService client back to the Server. This is done using google.script.run – but it’s important to realize that […]
In Use promise instead of callback for settimeout I showed how a more structured approach to scheduling events could be achieved through the use of promises rather than timeout callbacks. In apps that use HtmlService, […]
There’s a lot of talk about whether or not to use libraries because of performance issues. When you just load the library once, it probably doesn’t matter, but if you are using Html service, every […]
Let’s say that you have an Add-on which creates something in Canvas (or SVG), and you want to now embed that in a Sheet or document. Here’s how. As an aside, the code samples here […]
One of the challenges when using htmlService is passing data from the server to client. Let’s look at an example. Consider this – the script function doGet(e) { return HtmlService .createTemplateFromFile(‘mytemplate’) .evaluate(); } function getValue() […]
If you use HTMLservice templates, you may want to centralize useful ones and use them in multiple projects, just like you can with library scripts. All you need to make this work is a library […]
Writing Apps Script const scoping problems led to me to wonder how to get exactly which JavaScript features are supported by Apps Script and which are not. By deduction, it seems likely that the current version of […]
Apps Script is based on ES3 JavaScript, with quite a few additions from ES5 and even ES6. In this article we’ll take a look at the implementation of const, and let which were introduced in […]
At the time of writing, the Apps Script JavaScript engine is based on ES3 with a few useful ES5 things added. Much of the rest of the world is at Es6 with Es7 coming along. […]
Sheet and document IDs can be a a handful to manage, and if you want to use a different file you may need to update all the scripts that reference it. The Drive API allows […]
Some of the responses from the GitHub API rely on the answers being in its cache. If you make a request and there is no available cache result, it returns an HTTP code (202) to […]
There are 3 YouTube related advanced services in Google Apps Script. In this post I’ll show you how to get simple stats about the youtube videos in your channel. The API documentation can be a […]
As you know, there isn’t a builtin way to get notified about changes in a host Document from a client-side App. Ideally, there should be some kind of trigger that can be invoked when the […]
The Property store has a limit of 9k on individual property items and, the cache store has 100k. You may also be using other stores for this kind of data with similar limitations. Let’s take […]
It can be hard to notice where two strings are different in the Apps Script logger. We can borrow JsDiff to help with that. I’ll be using the cjsDiff library M9zcCXivXIkjpW_mA_X1Vtiz3TLx7pV4j You can report on […]
Identify Duplicates on Google Drive The problem with Drive is that it’s really easy to have loads of files with the same name spread around in multiple folders on Drive. Here’s how to get a […]
If you use caching in Apps Script (and you should), you might hit the 100k limit on cache sizes. I’ve written about how to get round that by writing several cache entries and linking them […]
Creating a crypto digest of some content is a neat trick used by github (and torrent sharing sites) to know if the content of two files is identical. There are plenty of other uses for […]
One of the problems of measuring script or library usage is the identification of returning users. As you know email addresses or some other identification is not easily accessible (rightly so), and neither should it […]
In Convert JSON to XML I showed how to make XML from JSON. Here’s the opposite. It could be a little more all-encompassing by dealing with dates, and unescaping, but for now, this should serve […]
There are a few examples of XML to JSON conversion around, but not so many going the other way. In case you need to convert Apps Script objects to XML, here’s a snippet to do […]
Sometimes you need to make up some random data for a sheet or a docs table just for testing in some volume. I use this snippet to generate random data in the right shape. You’ll […]
If you throw a custom error, it can be hard to track down where it came from if you’re using a common function. In Reporting file, function and line number in Apps Script I showed […]
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines