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 – […]
In Making a film color DNA I described how I was using both color strips and labelled content to find videos that were duplicates or similar. I’m already using ElasticSearch to disambiguate films using labels and object tracking artefacts identified by the Google Video Intelligence API. The next step is to enhance that similarity matching using the color strips of a film as finger prints. […]
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. […]
Smg is a SuperFetch plugin to access the Google Cloud Secrets API. SuperFetch is a proxy for UrlFetchApp with additional features – see SuperFetch – a proxy enhancement to Apps Script UrlFetch for how it […]
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 […]
Tank is a SuperFetch plugin to emulate streaming with Apps Script. SuperFetch is a proxy for UrlFetchApp with additional features – see SuperFetch – a proxy enhancement to Apps Script UrlFetch for how it works […]
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 […]
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 […]
I covered how to handle the somewhat more complex OAUTH2 authorization flow for the Twitter v2 API (OAuth 2.0 Authorization Code Flow with PKCE) using my Goa library in this article. The purpose of that […]
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 […]
It’s been a few years since I first created the Goa library. Initially it was mainly to provide OAuth2 authorization and authentication to be able to access Google APIS and services unavailable via App Script. […]
Frb is a SuperFetch plugin to easily access a Firebase Real time database. SuperFetch is a proxy for UrlFetchApp with additional features – see SuperFetch – a proxy enhancement to Apps Script UrlFetch for how it […]
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 […]
This is a follow on from the article on Pull libraries inline to your Apps Script project (which you should probably read fiest) to explain a little about how it works. The Script API It uses […]
1000 pages and counting Most years I do a post on ‘a year in Apps Script’, looking back over the changes in the platform over the preceding year. As many of you will probably know, […]
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 […]
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 […]
This article will cover the translation of the Sheets workbook filter type functions for Apps Script. All of them will be covered. Motivation If you are handling data from sheets it might be useful to […]
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 […]
A friend of mine hit an Apps Script problem the other day when transferring large amounts of data between Drive and Cloud Storage. We’ve all had the mysterious ‘unexpected Javacript runtime error’ from the IDE […]
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 […]
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’ll use this format then […]
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 […]
Another quick demo of data sharing Here’s a challenge that shares the data in a spreadsheet with node, set up end to end in less than 5 minutes. This example is almost exactly the same […]
Another quick demo of data sharing There are many ways to accomplish this of course, but using Caching across multiple Apps Script and Node projects using a selection of back end platforms is pretty fast to […]
Quick demo of data sharing There are many ways to accomplish this of course, but using Caching across multiple Apps Script and Node projects using a selection of back end platforms is pretty fast to set […]
Motivation I’ve always been interested in ways to get multiple projects in Apps Script sharing data more easily and in a more standard way, independently of whatever platform is being used to host the data […]
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 […]
Apps Script, Redis and GraphQL – together I’m a great fan of both Redis and GraphQL. You’ll find plenty of articles about them around on this site. Although I’ve showed many examples of GraphQL and […]
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 […]
A few scrviz updates today, with more flexibility around the depth of the repo visualization and more options to enrich your own profile. Less is more for visualization We have a rapidly increasing number of […]
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 Enrich your developer profile on scrviz I showed how scrviz could be used to show off your Apps Script work to potential clients (Showcase your Apps Script work and get hired via vizzy) and how to […]
In Showcase your Apps Script work and get hired via vizzy I showed how scrviz could be used to show off your Apps Script work to potential clients, so they could see what kind of […]
Motivation You’ll know from Every Google Apps Script project on Github visualized that you can get a very large diagram of all known public Apps Script projects and from Find and clone Apps Script projects from github […]
JavaScript authentication with Gapi is both impressive and frustrating. Frustrating because in most of the examples you come across, and indeed in Google’s own guides, gapi is the center of the universe. They are about […]
Motivation Every Google Apps Script project on Github visualized describes how to use https://scrviz.web.app to find and visualize public Apps Script projects on Github. More and more apps script projects are being publicly shared on github. […]
Apps Script Vizzy update Every Google Apps Script project on Github visualized introduces a way of visualizing public Apps Script projects on github. Showcase your Apps Script work and get hired via vizzy shows how […]
Get yourself found as an Apps Script consultant Every Google Apps Script project on Github visualized introduced this vizzy app to give a searchable view into public apps script projects on Github. If you are […]
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 […]
v8 and other htmlservice changes meant I had to make a few small changes to cGoa. The good news it’s easier to use than ever, and supports a few new services too. It’s best to look at the service list on github, as that’ll be kept up to date. Here’s a reminder of how to use it. […]
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 […]
In Using blister custom functions I showed examples of how to lookup scriptDB lists as custom functions. Here’s how to create lists. In each case, to access lists in your sheet you need to add a reference […]
Here is the code for each of the example mentioned in GAS lists and validation. The custom functions You need this in your local workbook. You can find it in the examples sheet. These are the custom […]
Examples Let’s take an example. Say you are a small garage, with a stock list of cars and you work exclusively with spreadsheets. You don’t want to take copies of the list, but you want […]
The custom functions These are the custom functions you can call directly from your sheets. The function documentation is here and you can find examples of use in Using blister custom functions and you’ll need the code described in Blister […]
It’s very common to want to lookup some list, or apply them as validation to data entry when playing around with Spreadsheets. Between them, a search for Spreadsheet related VLOOKUP, INDEX and MATCH return 30 […]
It would be useful to keep a reference guide to all projects and libraries shared on github, and their IDS. If you are using gasgit, then we already have everything needed to make that as […]
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 […]
All about performance analysis, tips and tricks for getting the best out of both Apps Script and Excel. […]
The JavaScript client is very similar to A VBA API for scriptDB, so reading that will give you a more detailed flavor of what can be done. The implementation is very different though, and there are […]
This is a ScriptDB API for JavaScript. The tests that are referred to in scriptDB API for JavaScript are implemented in https://storage.googleapis.com/toasty/t/gas/scriptdbapi.html. Code is below <!DOCTYPE html> <html> <head> <meta charset=”utf-8″ /> <title>scriptDB data input – ramblings.mcpher.com</title> <link […]
Here is the javascript implementation for the scriptDB API. It needs jQuery. * methodType = “GET”; */ var dataType = “json”; if(!postData) { u += “&callback=?”; dataType = “jsonp”; } var d = $.Deferred(); var […]
As per this blog post, Google Apps Script does not support CORS (cross origin resource sharing) to allow access from cross domain client apps. The API uses JSONP for GET requests to get over this, but […]
I use scriptDB a lot , both with Google Apps Script and from other sources, including VBA, and in this blog post, I compare parse.com performance with script DB. One of the things that Parse.com has going […]
Because the GAS API is so similar to parse.com – nosql database for VBA, you may want to read that too, which has similar examples. Note that virtually all methods can be chained leading to pretty […]
Just as in parse.com – nosql database for VBA, you’ll probably want to load up some test data from a Google Spreadsheet to a Parse.com class. You’ll find some test data in this workbook – it’s same data […]
Take a moment to read these if this is a new topic for you. Orchestration of Apps Scripts – parallel threads and defeating quotas Jobs, work, stages, threads and chunks Namespaces and App structure Creating […]
Take a moment to read these if this is a new topic for you. Orchestration of Apps Scripts – parallel threads and defeating quotas Jobs, work, stages, threads and chunks Namespaces and App structure Code […]
Take a moment to read these if this is a new topic for you. Orchestration of Apps Scripts – parallel threads and defeating quotas Jobs, work, stages, threads and chunks Code is on github GasThreader structure […]
Before getting started on how to set up Orchestration of Apps Scripts – parallel threads and defeating quotas let’s get a few definitions clarified. Code is on github This dashboard will provide the reference point for each of […]
This describes how to implement goa in a sidebar. The example will assume that the authorization process should be repeated for each new user (like a web app published in Accessing as the user running the […]
This describes how to get and access token from the Goa library as described in Oauth2 for Apps Script in a few lines of code (which you should read first for background). If you have initialized the […]
There’s always a little bit of work needed if you are planning to do an OAUTH2 flow that might need some user interaction. Goa now is able to create its own UI to manage the […]
Many people are a little intimidated by OAuth2 in Apps Script, and because you don’t need to set it up very often you always need to work from a cheat sheet. Luckily if you use […]
Oauth2 for Apps Script in a few lines of code (which you should read first for background) has many pages of videos and tutorials about different OAuth2 scenarios for Apps Script and is used daily in […]
This describes how to customize a consent screen using the Goa library as described in Oauth2 for Apps Script in a few lines of code (which you should read first for background). What is a consent screen […]
This describes how to authenticate with Podio using Goa, as described in Oauth2 for Apps Script in a few lines of code (which you should read first for background). The library, cGoa, is available under this project […]
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 […]
javaScript: Flying around and asynchronicity in google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Google Earth Api You will find tons of content on Google Earth , most of which is described […]
javaScript: creating google visualization Data Views This relates to Data Driven Mapping applications and the Javascript ‘HowTo’ section Google Visualization dataViews As mentioned in Using Google Visualization DataTables a master google DataTable is created for all data in […]
javaScript:creating google visualization DataTables This relates to Data Driven Mapping applications and the Javascript HowTo section Google Visualization Tables I have covered Google Visualization in various forms with an ‘Excel slant’ on this site. Let’s take a look […]
VizMap: the data javaScript This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example Parameter WorkSheet is […]
VizMap: the framework javascript This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example Parameter WorkSheet is […]
VizMap: the Viz Html parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and is used to define the filename of the to be generated application. It also contains customization […]
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 […]
For an overview of what VbaGit is, please read Integrate VBA with Github. If you are working with Google Apps Script as well as VBA, then you will probably be familiar with JDSOC. This is a way […]
For an overview of what VbaGit is, please read Integrate VBA with Github. Getting the code You can get the code from github or you can get a premade bootstrap workbook from the github stuff section in the downloads […]
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