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

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

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

API Specific

Using sheets via Bigquery from Apps Script

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

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

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

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

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

Sheets specific

Converting timestamps to dates formula

In Apps Script, it’s easy

But sheets doesn’t use JavaScript timestamps for dates, it uses the number of days since 1st Jan 1900, with hours/mins/secs represented as a fraction of a day (an idea […]

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

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

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

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

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

Docs Specific

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

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

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

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

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

D3

Using D3 in server side Gas

There are plenty of examples on this site of using D3 for its visualization capabilities, whether from Excel, Apps Script or some other platform. I like D3 a lot, and now with the release of […]

HTMLService

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

HTMLService

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

the template

I get a correct hello world […]

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

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

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

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

Apps Script Specific

Identifying duplicates on 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. Heres how to get a handle on that, without having […]

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

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

Apps Script Specific

Convert JSON to XML

There are a few examples of XML to JSON conversion around, but not so many going the the other way. In case you need to convert Apps Script objects to XML, here’s a snippet to […]

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

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

General JavaScript techniques

Google Drive as cache

If you are after performance and self cleansing then the cacheservice is the best solution for caching, and if you are after permanence and small amounts of data, the properties service is a good solution. […]

General JavaScript techniques

Improved namespace pattern for Apps Script

If you are a regular reader of these pages, you’ll know that I prefer to encapsulate all my code namespaces for all my JavaScript and Apps Script projects. This makes for better organization and avoids […]

General JavaScript techniques

Roughly matching text

Rough Matching When dealing with matching in sheets, you sometimes need to get close matches. This post shares the “Rough” namespace of the cUseful library, available here. Some examples Let’s start with this sheet – […]

General JavaScript techniques

Formatting GraphQL queries

Queries with older JavaScript If you are using a newer flavour of JavaScript you can do multiline literals using backticks like this

This gets concatenated to look like this

This makes building a […]

General JavaScript techniques

Shortcut for adding nested properties to a JavaScript object

When working on Sheets API – Developer Metadata it becomes clear that some of the request objects can go to quite a depth, so you end up doing something like this.

This becomes more […]

General JavaScript techniques

Composing functions and functional programming

In JavaScript currying and functional programming I looked at an Apps Script example using currying (embedding values that would normally be arguments in a function). Another functional programming topic that’s becoming popular is the idea […]

General JavaScript techniques

JavaScript currying and functional programming

In Abstracting services with closures I showed how you could get more functional by using closures. Curried functions are another approach to encapsulating values in a function – minimizing the number of arguments and variables […]

General JavaScript techniques

Exponential backoff for promises

I’m sure you’re all familiar with both Promises and exponential backoff. If you’re a regular visitor to this site, you’ll know these are two topics I often write about. In this post, I’ll combine the […]

General JavaScript techniques

Canvasser

One of the fiddly things about using Html5 canvas is dragging and dropping. It’s something I needed to do a fair bit, so I thought I’d write a configurable function to do it. You can […]

General JavaScript techniques

Changing class properties dynamically

If you have settings in an app that can be changed dynamically (for example background colors) during use, then it can be tricky to do that without applying the specific styles to each affected element. […]

General JavaScript techniques

Use promise instead of callback for settimeout

You should all be familiar with ES6 promises by now as a way of handling asynchronicity in a more organized way. The simplest kind of asynch that we regularily come across in JavaScript is setTimeout, […]

General JavaScript techniques

Dynamically creating tables with clusterize.js

Quite often you need to present dynamic tables in Html Service. They can be laborious to code and can get sluggish if large. Clusterize.js gives some great capabilities to help with that. Imagine you have […]

General JavaScript techniques

Converting SVG to PNG with JavaScript

I’ve been using canvg in the past to convert SVG to PNG, but as you’ll see from the link, it doesn’t support all of SVG capabilities. If you use D3 or any library that likes […]

No Picture
General JavaScript techniques

Using timing functions to get insight into Sheets

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

Apps Script Specific

Serving apps script to JavaScript app

Here’s an interesting idea. Let’s say you want to use Google Apps Script as the master for a library of functions (since you can’t use script tags in apps script) that you would like to […]

Apps Script Specific

Apps for Office – binding example comparison

This page is still being written. In Pseudo binding in HTML service I showed how to simulate Sheets cell binding in an Apps Script Add-on. Office for apps includes binding out of the box. We’ll use that […]

HTMLService

Add-on spinner

If your Add-on needs to execute a google.script.run() to go back to Apps to do something, like get or update data, that might take a little while to do. In these cases, it’s good to […]

General JavaScript techniques

Abstracting services with closures

You probably all use cache service, property services and maybe some others too. Abstracting away which one you are using so that your code doesn’t need to bother about the details can be a good […]

General JavaScript techniques

A recursive extend function for Apps Script

If you’ve worked through JavaScript recursion primer and More recursion – parents and children you should be pretty comfortable with how recursion works by now. Now we’re going to apply it to create a simple version of jQuery.extend(). If you […]

Apps Script Specific

Cross Origin Resource sharing (CORS)

What is CORS In summary it’s a way of securely enabling a client side app to exchange data with a server that is not in the same domain – which is of course against cross […]

Snippets

Custom checking for exponential backoff

If you are using my exponential backoff function from the cUseful library you will be aware that an apps script error that qualifies as a something worth retrying will provoke retries. The benefit of this […]

General JavaScript techniques

Traversing a tree

A common pattern is traversing a tree, and you find yourself writing the same recursive code over an over. Although it’s a very simple problem, people often have trouble with it. Here’s a general pattern […]

General JavaScript techniques

More recursion – parents and children

In JavaScript recursion primer I introduced a simple example to show how recursion works. We are going to develop some of those ideas in this post, so you should first read that. We’ll use the same object […]

General JavaScript techniques

JavaScript recursion primer

What is recursion Most modern coding languages allow recursion – in other words allow a function to call itself. This concept is central to being able to deal with object structures that are linked or […]

General JavaScript techniques

Chaining JavaScript

One of my favorite JavaScript things is the ability to chain things together. Consider this function, where the methods return the instance itself;

We can call it, chaining together multiple actions.

In my opinion (some […]

General JavaScript techniques

Untangling with promises

In my Ephemeral Exchange project, I use socket.io to handle push notifications when any cache values are updated, are deleted or expire. Where you have a lot of asynchronicity going on, it can be hard to deal with all […]

Docs Specific

Large document performance

One of the concerns I have about add-ons is that the there is no context maintained between invocations. It’s the same thing with custom functions. What that means is that the document or spreadsheet structure […]

Docs Specific

Extracting images from a document

If you have a document or book manuscript, you may need to extract and attach figure references to inline images in a document. If you want to give them names associated with the chapter in […]

Libraries

CryptoJS libraries for Google Apps Script

CryptoJS is pretty much the gold standard for JavaScript cryptography. Working with Oauth2 you don’t have to worry about all that, except when you are dealing with service accounts. I’ll get to that in a […]

HTMLService

Running things in parallel that need oAUTH2 – datastore

If you are using Parallel process orchestration with HtmlService along with Database abstraction with google apps script you’ll need to know how to work with access tokens for oAuth2. Assuming you’ve used EzyOauth2 patterns to set up your application to be […]

Snippets

Summarizing emails to a sheet

In Inviting members to Google Plus from Groups I showed how to use htmlservice to send a templated email to multiple recipients. Let’s say you want to create a spreadsheet of people you’ve sent a […]

API Specific

Inviting members to Google Plus from Groups

If you are a regular here, you may have noticed that our forum  has moved from a google groups forum to a Google+ community. I had never noticed before, but there is a groups  service in Apps Script that allows […]

API Specific

Universal analytics for checking co-operating processes

In Instrumenting VBA for Google Analytics and Universal analytics measurement protocol for your GAS libraries I showed how you could us Google Analytics to measure usage of modules and libraries. In db access to a variety of databases from Excel, […]

Docs Specific

Converting Google Docs table to JSON and back

It’s pretty common to convert spreadsheet values to an array of JSON objects using the header row as the property keys, and you’ll find many examples of that around this site. It’s a little less […]

General JavaScript techniques

JavaScript closures – how, where and why

These articles are abridged from my  book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available directly from O’Reilly or Amazon. People usually have a lot of trouble understanding closures in JavaScript. In this post […]

Docs Specific

Adding abstracts to documents

DuckDuckGo has a pretty good api for getting short abstracts given a query. I figured that it might be nice to use this to demonstrate how the caching can be used to pass in lieu […]

Apps Script & Java Script

Get Started Snippets

What can you learn here? Get started Short code snippets Abbreviated explanations Quick examples Get it now This articles on this site are largely about how to do quite big projects. Realizing that you may […]

Docs Specific

Sorting bookmarks in a document

Here’s how to sort bookmarks into position order in a document.  We’ll use Advanced Array functions heavily here. First we get the bookmarks positions and the elements they refer to, and add a path to them to […]

Fiddler

Styling Gmail html tables

When you use Gmail (or Sites), you are allowed to provide HTML input so you can have control over the content. So if you wanted to send an email with red text, you could do […]

Fiddler

Formatting sheet column data with fiddler

Header formatting with fiddler  shows how to use fiddler to easily set formats for your sheet headings. here’s how to do the same for column formats. This is quite a long read, as there’s a […]

Fiddler

Header formatting with fiddler

When playing around with sheet data, I always find myself using the Fiddler object – some more posts on this below if you are not familiar with it already. More on this topic available here […]

Fiddler

More sheet data fiddling

In A functional approach to fiddling with sheet data and Unique values with data fiddler I showed a way of working with sheet data without bothering too much about column numbers and all that stuff […]

Fiddler

Unique values with data fiddler

Fiddler is a way to handle spreadsheet data in a functional way, as described in A functional approach to fiddling with sheet data This class – Fiddler can be found in my cUseful library. Here’s […]

No Picture
Javascript

Using Twitter in Earth and Maps

This relates to Data Driven Mapping applications and the javaScript ‘howTo’ section. Twitter API Using the Twitter RESTful API, it is very straightforward to add relevant tweets to an infoWindow or infoBubble. Narrowing down the the query through Twitter […]

General JavaScript techniques

Publishing ES6 code on NPM

I’ve covered a couple of client libraries in VBA library for Ephemeral exchange and Apps Script library for Ephemeral exchange, and this article will talk a little about the Node library, but more specifically, a general discussion on […]

API Specific

Get GAS library info

There are many references to shared libraries on this site. You can either take a copy of them or use them in place as you wish. I’m always hitting a few problems with a) versions […]

API Specific

Tracking github pageviews

Apps Script (advanced level) posted on 16th Oct 2017 Github pageviews You can get things like number of stars and watchers etc from the GitHub REST API repository data, but pageviews are not tracked there. […]

Apps Script Specific

Proxy jsonp

If you are reading this, I guess you are familiar with this error message

This is of course the same origin policy that prevents you being able to access jSon data that is stored on a […]

Libraries

Using named locks with Google Apps Scripts

The LockService gives the capability to do this This service allows scripts to prevents concurrent access to sections of code. This can be useful when you have multiple users or processes modifying a shared resource […]

API Specific

Multiple inserts in Fusion Tables

Inserting rows with the Google Apps Script FusionTables service is pretty slow. There are also a bunch of quotas to worry about. Here’s what is says. Note: You can list up to 500 INSERT statements, […]

Snippets

Exponential backoff

I’m refactoring my cUseful library for some upcoming articles. This means updating some long standing functions that some of you may be using. All the existing ones will continue to work as before, but the […]

Libraries

Parallel processing in Apps Script

There’s no getting away from the fact that Apps Script is slower than the equivalent client based JavaScript processing. It is fundamentally synchronous in implementation, and also has limits on processing time and a host […]

Apps Script Specific

Backing off on rate limiting

There are many rates and quotas in the Google Apps Script environment. One of the usual ways of dealing with it is to sleep between instructions, but that seems to be a rather blunt instrument. […]

General JavaScript techniques

Apps Script as a proxy

If you try to access data in a different domain from a client side app, you’ll often get an error about CORS (cross origin resource sharing). I’ve dealt with this topic before in Cross Origin Resource […]

Libraries

Using crossfilter with Google Apps Script

Visitors to this site will know that I’m a big fan of d3.js. Crossfilter.js (see the link for details, credits, licensing and API usage) is a library to play around with large data sets, often […]

Apps Script & Java Script

Zoomable Partition Charts

How to create a d3 zoomable partition chart straight from Excel. As always, acknowledgement to Mike Bostock on whose zoomable partition chart this is based.  A partition chart is a way of showong heirarchical data. The zoomable part is […]

D3

Quick doughnut and pie charts with a radial timer

If you’ve been trying out the Configurable circular timer with D3 or playing around with Example of clock using d3 configurable timer you’ll have seen how easy it is to create a versatile timer by just configuring a few […]

D3

Example of clock using d3 configurable timer

In Configurable circular timer with D3 I showed a circular timer made with D3.js . Although it’s not really what it’s for, this simple example shows how to make a complex viz. This is a clock […]

Apps Script & Java Script

Configurable circular timer with D3

I don’t use jQuery, neither in regular webapps nor in Apps Script HtmlService apps. No particular reason – I just prefer to use vanilla JavaScript. I often use d3.js, mainly for visualization as in these examples elsewhere […]