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 few months ago I released a publicly shared bigquery dataset all about the Apps Script ecosystem on Github, with reports available on looker. For details of how it all works see Gassypedia – public […]
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 […]
Apps Script doesn’t do asynchronous – so what’s this all about? Since the move to v8, Apps Script supports the Promises as well as the related async/await syntax. Normally we don’t have to worry about […]
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. […]
All the Google Apps Script related github activity is now on Google Bigquery and Google Looker. Here’s how it was done. […]
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. […]
Anaylze and colorize the sentiment of inidivual sentences in Google using Documents Google Language API […]
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 […]
A recent social network analysis project meant I had to find a way to convert Google Form data for visualization in NodeXL. Normally I’d use D3 for this kind of thing (you’ll find plenty of […]
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. […]
Accidentally accessing non-existent properties and tracking down where that is happening is a big time waster that a JavaScript proxy can help you track down. In Fix Apps Script file order problems with Exports I […]
I’ve written many times about various Apps Script caching techniques such as how to deal with size limits and use different back ends for more persistent caching. In this article, I’ll bring together a few […]
Motivation I’ve been working on a CardService Add-on lately which also uses HtmlService, and also runs quite a few things Server side. All of these modes need to share state, and it always ends up […]
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 […]
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 […]
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 […]
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 […]
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 […]
SuperFetch is a proxy for UrlFetchApp with additional features – see SuperFetch – a proxy enhancement to Apps Script UrlFetch for how it works and what it does. In this article I’ll cover how to use […]
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 […]
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 […]
This piece of work to create inline libraries 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 […]
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, […]
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. My initial motivation for this […]
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 series of articles will work through how to create a connector for Data Studio in Apps Script. For an introduction into the structure I’ll be following see Creating a connector for Data Studio and for a […]
This series of articles will work through how to create a connector for Data Studio in Apps Script. For an introduction into the structure I’ll be following see Creating a connector for Data Studio This article […]
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 […]
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 […]
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 […]
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, […]
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 […]
I’ve been doing an annual update called ‘a year in Apps Script’ for a number of years now, but I didn’t get round to the 2021 post. This year I thought I’d do something different. […]
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 […]
This is part of the series on sharing data between Apps Script and Node on various backends, Apps script library with plugins for multiple backend cache platforms but the Drive API introduces a number of tricky […]
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 […]
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 […]
OneDrive 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 in plugins […]
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 […]
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 […]
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 […]
In Every Google Apps Script project on Github visualized I demonstrated an app that could be used to explore what every Apps Script developer who has shared their code is working on. It can be used […]
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. […]
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 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 […]
What is Apps Script It’s the language used for extending GSuite and is based on JavaScript. Unlike the JavaScript that runs in your browser, it runs on a Google Server in the cloud, but unlike […]
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 […]
One of the interesting challenges, especially if you are using webapps, triggered tasks or running things in parallel is the persistence of log files. Logger.log only has one instance so it gets overwritten by the latest thing […]
Important note on my add-ons I’ve (reluctantly) decided to stop supporting all my Apps Script add-ons, as I no longer have the time or inclination to put them through the various verification processes and upcoming […]
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 […]
All about performance analysis, tips and tricks for getting the best out of both Apps Script and Excel. […]
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 […]
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 […]
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 […]
If you use Github (or bit torrent sites), you’ll notice that there is sometimes a code called a sha used to reference files. This is actually a hash of the contents of the file. If […]
There are a few articles on this site about getting more into (and out of) cache and property services. Now my cUseful library contains the ability to create caching plugins that take advantage of some […]
I use Cloud storage quite extensively as it’s a great way to share data across platforms and projects. Apps Script projects can share data with Node apps for example. There are quite a few articles […]
Goa is a library to simplify Oauth2 with both Google services and other Oauth2 providers, many of which are natively supported. It takes care of dealing with HTML dialogs, refreshing tokens, getting service account data […]
Goa knows how to talk to a number of different Oauth2 providers using various flavours and varieties of OAuth flow. Goa library is available with this key. MZx5DzNPsYjVyZaR67xXJQai_d-phDA33 You just have to specify the service […]
The Vimeo Rest API is a simple, standard API. It’s been added to the Goa library list of services. Goa library is available with this key, and on github MZx5DzNPsYjVyZaR67xXJQai_d-phDA33 Vimeo dashboard There are various kinds […]
The quickbooks Rest API is a simple, standard API. It’s been added to the Goa library list of services. Goa library is available with this key. MZx5DzNPsYjVyZaR67xXJQai_d-phDA33 Quickbooks dashboard Like other APIS, there’s a dashboard […]
The Classy Rest API uses OAuth2 a little like a Service Account, which means there’s typically no user dialog. It uses a grant type called client_credentials. This is probably the most straightforward of all OAuth2 variants, so […]
In Using Advanced Drive service to convert files I covered how to use the Advanced Drive Service to convert Sheets to Excel and write the result back to Drive. It occurred to me that, since Microsoft OneDrive […]
This describes how to authenticate with Google Datastore using Goa along with a service account, as described in Oauth2 for Apps Script in a few lines of code (which you should read first for background) and Goa services […]
This describes how to authenticate with Microsoft Live 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 […]
This describes how to authenticate with Google Datastore 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 […]
The source data colorTable we want to load to our parse.com database is currently in a Google Spreadsheet. With scriptDB, we can access it directly. However, to load data into parse we need to use the google visualization query […]
I’m deprecating this shortly. Better to use Enhanced Github gadget if your source is on git hub. For how to do that see, Getting your apps scripts to Github In Gas Publisher I described the detail of how the Gas […]
Over the years I’ve had a few goes at this – for example Running things in parallel using HTML service and Parallel processing in Apps Script, so I’ve decided to do a bigger and better version of those. […]
A little while ago, I did a an article on Instrumenting VBA for Google Analytics, which uses Universal Analytics to track the use of sections of VBA code. One of the things that is missing when […]
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