filmid
Apps Script

Proxy magic with Google Apps Script

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 […]

fake news analysis with apps script
API Specific

Detect fake news with Google Fact Check tools

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 […]

random contrasting font colors
Apps Script

Calculate contrasting font colors for Sheets.

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 […]

Superfetch plugin
Apps Script

SuperFetch caching: How does it work?

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 […]

superfetch tank drive
Apps Script

SuperFetch plugins: Tank events and appending

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 […]

Goa Oauth2 for Apps Script
Apps Script & Java Script

Apps Script Oauth2 library Goa: tips, tricks and hacks

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 […]

Apps Script & Java Script

Iterator magic – Splitting an array into chunks

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 […]

Add-ons

Refreshing an oauth token in add-on

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 […]

apps script v8
Apps Script

Proxy implementation of Apps Script GraphQL Class

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 […]

apps script crusher on github
Apps Script

Github as an Apps Script cache platform

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 graphql explorer
Apps Script

Upstash as an Apps Script cache platform

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 on hover or click
Apps Script

4 ways to customize scrviz info card behavior

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 […]

Add-ons

Handly helper for fiddler

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. […]

apps script v8
Apps Script & Java Script

Apps Script V8: Keystore for global space

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 […]

apps script v8
Apps Script & Java Script

Qottle recipe: Managing asynchronous queues with duplicates

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 […]

apps script v8
Apps Script & Java Script

Qottle recipe: How to manage an asynchronous polling queue

  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 […]

apps script v8
Apps Script v8

Apps Script V8: Arraybuffers and Typed arrays

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
Apps Script v8

Apps Script V8: Maps and Sets

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 […]

apps script v8
Apps Script v8

Apps Script V8: Template literals

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
Apps Script v8

Apps Script V8: spreading and destructuring

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 […]

apps script v8
Apps Script v8

JavaScript V8 Arrow functions, this and that

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 […]

apps script v8
Apps Script v8

JavaScript V8 variable scopes

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 v8
Apps Script

Apps Script v8

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 […]

Apps Script & Java Script

Measuring sheet operation performance

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 […]

No Picture
Apps Script & Java Script

Unnesting data to sheet values

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 […]

Sheets specific

cOauth2 class implementation

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 […]

No Picture
Libraries

EasyCron Library

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, […]

Libraries

SunCalc

I came across this useful javascript and node.js function the other day. You can find the details of the original on github here. Here is the license – please respect the terms of the author […]

No Picture
Snippets

Copying to new host location

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 […]

No Picture
Snippets

Identifying hosted files

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 […]

No Picture
API Specific

Patching site html

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. […]

No Picture
API Specific

Direction minimizer – other usages

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 […]

No Picture
API Specific

Migrating user and script properties

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 […]

No Picture
API Specific

Finding a Drive App folder by path

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 […]

No Picture
Sheets specific

Converting timestamps to dates formula

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 […]

No Picture
Sheets specific

Optimizing showing and hiding rows and columns

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 […]

No Picture
Sheets specific

Filling ranges in Google Sheets

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 […]

No Picture
Docs Specific

Getting insights into Sheets performance

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 […]

Docs Specific

Using array formulas to improve performance

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 […]

No Picture
Docs Specific

Transposing sheet data

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 […]

No Picture
Docs Specific

Importing Predictwise data

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 […]

No Picture
Apps Script & Java Script

Column numbers to characters

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 […]

No Picture
Docs Specific

How to transpose spreadsheet data with apps script

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 […]

No Picture
Docs Specific

Removing duplicate paragraphs

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 […]

No Picture
Fiddler

Optimizing sheet formatting with Apps Script

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 […]

No Picture
Fiddler

Fiddler and rangeLists

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 […]

Docs Specific

Cleaning up heading levels

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 […]

D3

Emulating worksheet functions in apps script with d3

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 […]

Apps Script & Java Script

Reusing html stuff between Apps Script projects

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 […]

No Picture
Apps Script & Java Script

Measuring library load speed

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 […]

No Picture
Apps Script & Java Script

Passing data to html service

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() […]

No Picture
Apps Script Specific

What JavaScript engine is Apps Script running on?

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 […]

No Picture
Apps Script Specific

Apps Script const scoping problems

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 […]

No Picture
Apps Script Specific

Using Es6 with Apps Script

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. […]

Apps Script Specific

Simple server side polling

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 […]

No Picture
Apps Script Specific

Logging differences in strings in Apps Script

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 […]

No Picture
Apps Script Specific

Identify duplicates on Drive

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 […]

No Picture
Apps Script Specific

Counting script and library usage

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 […]

No Picture
Apps Script Specific

From Xml to JSON

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 […]

No Picture
Apps Script Specific

Convert JSON to XML

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 […]

No Picture
Apps Script Specific

Including the stack in custom errors

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 […]

No Picture
Apps Script Specific

Why Base64

In this article we’ll take a look at Base64, what it is and why we use it. Although there are Base64 encoding and decoding available in the Apps Script Utilities service, we’ll write an encoder […]

Apps Script & Java Script

Create sha1 signatures with apps script

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 […]

No Picture
Apps Script Specific

Transform dates for add-on transfer

If you getValues from a spreadsheet, and those values contain dates, and you expect to be able to send them to an add-on, then you’ll find you get an error about unsupported data types. This […]

General JavaScript techniques

Recursive async functions

Background I have a GraphQL server on which I’ve deliberately limited the amount of data that can be returned in a single query to avoid daft requests. That means that you need to do paging […]