Apps Script & Java Script

Goa v8 changes and enhancements

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

Apps Script & Java Script

Apps Script V8: Keystore for global space

One of the challenges with 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 any such […]

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: 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 What are Maps and Sets These are a collection of like […]

Apps Script v8

Apps Script V8: Template literals

V8 adds template literals from  JavaScript ES6. What are template literals It’s a shorthand way of using a template into which variables are subsituted in a string. This allows for better reuse of string structures, […]

Apps Script v8

Apps Script V8: spreading and destructuring

V8 adds destructuring from  JavaScript ES6. Legacy Apps Script already had destructuring of arrays added fairly recently, but v8 gives full a destructuring capabilitity. What is destructuring It’s a way of plucking elements or property […]

Apps Script v8

JavaScript V8 Arrow functions, this and that

function declarations versus arrow functions 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 to […]

Apps Script

Apps Script

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

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

Apps Script v8

Apps script now supports JavaScript ES6 Apps Script 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 […]

Polymer

Multi user apps script logger

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

Add-ons

Color Arranger

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

Apps Script & Java Script

Easy reference for published scripts

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

Apps Script & Java Script

Performance

All about performance analysis, tips and tricks for getting the best out of both Apps Script and Excel. […]

Apps Script & Java Script

The Orchestration namespace

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

No Picture
Apps Script & Java Script

Creating a work package

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

No Picture
Apps Script & Java Script

Namespaces and App structure

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

No Picture
Apps Script & Java Script

Jobs, work, stages, threads and chunks

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

Apps Script & Java Script

Goa in a sidebar

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

Apps Script & Java Script

Getting and using the token

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

Apps Script & Java Script

How OAuth2 works and Goa implementation

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

Apps Script & Java Script

How to customize the consent screen

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

Apps Script & Java Script

Setting an alternative ontoken callback

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

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

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

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

APIS and techniques

Hitching a ride on Goa’s property store

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

Google Authentication

Adding custom services to goa

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

Google Authentication

Google Apps Script Oauth2 for Vimeo with Goa

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

Google Authentication

Google Apps Script Oauth2 for quickbooks with Goa

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

Google Authentication

Goa and the Classy API

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

Google Authentication

Microsoft Live service for Goa Examples

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

Apps Script & Java Script

Google Datastore service for Goa examples

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

Apps Script & Java Script

Google Visualization API data

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

Apps Script & Java Script

Step by Step Gas Publisher

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

Apps Script & Java Script

Goa services and customization

This describes how to set up new (or examine existing) services 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 […]

Apps Script & Java Script

Authentication dialog

This describes how to set up an authentication dialog using 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 […]

Apps Script & Java Script

GOA Setup

This describes how to set up a script to use the Goa library as described in Oauth2 for Apps Script in a few lines of code (which you should read first for background). The library, cGoa, is […]

APIS and techniques

Get web credentials from downloaded file

This describes how to use 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 key, or on github. […]

Apps Script & Java Script

Cloud Storage and Apps Script

There are SDKs available for a number of languages, but not for Apps Script. . Since Google Cloud Storage is neither a built-in nor an advanced Apps Script service, for these examples I’ll be using […]

Apps Script & Java Script

Scheduling GAS

We looked at how to create a json file on Google Drive containing the analysis of a google site in Analyzing site content with GAS. The script that creates that takes a whole lot of parameters, […]

From VBA to Google Apps Script

Google Apps Script Roadmapper code

Where to get the document This page concerns the From VBA to Google Apps Script project.  You can find the code and test data, under the roadmap section in the VBA to Google Apps Script Roadmapper project download. You […]

From VBA to Google Apps Script

Looping in Google Apps Script

Let’s say you pass an array of values to be processed to a custom function, and you want to loop through them.  In these examples, we’ll just go through each value and sum it if […]

From VBA to Google Apps Script

Apps Script custom functions

Just like Excel, you can extend Google Sheets by writing custom functions. These functions are written in Google Apps Script. In this section, I”ll show how to do some common VBA things you might want […]

From VBA to Google Apps Script

A simple Google Apps Script RestQuery

In GAS Rest Library migration, you can see a port of Rest to Excel library from VBA to Google Apps Script. This handles many complex queries and generally can execute them in a few lines of code using […]

From VBA to Google Apps Script

Apps Script timer collection

One thing I’ve found you need when developing for Google Apps Script is a timer. Everything is pretty slow ( see Optimizing access to sheet values) so you need to dig into exactly what is good […]

From VBA to Google Apps Script

Optimizing access to sheet values

Why so slow? Note – this article was written when Apps Script first came out. Since that time, the speed of Sheets access has increased significantly, but I leave this article here for interest. The Data […]

From VBA to Google Apps Script

VBA collection in javaScript

JavaScript does not really have the equivalent of a VBA collection. Since the use of collections is very significant in this project, the best approach is  to emulate a collection object in javaScript so that […]

From VBA to Google Apps Script

Dealing with optional arguments

Arguments to procedures in VBA can be optional, and optionally they can have a default value. For example We can also test for the presence of a variant argument by using isMissing(). See Getting Started with […]

From VBA to Google Apps Script

Some Google Apps Scripts gotchas

Things to look out for As you work with javaScript (most of these gotchas will be about javaScript rather than apps script specifically), you’ll scratch your head about a few things. I know I did. […]

From VBA to Google Apps Script

Google Apps Conversion approach

You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download To minimize the amount of work in converting from VBA to Google Apps […]

From VBA to Google Apps Script

Apps Script versus VBA scope

In Understanding Scope for VBA I showed some examples of the visibility of functions and variables across modules and procedures inside a VBA project. If you are moving to Apps Script (or JavaScript) from VBA, you’ll need […]

scriptDB

Handler for cScriptDbCom requests

The VBA API generates REST API requests from your VBA function calls, and needs a Google Apps Script handler to service those requests by communicating with the required scriptDB instance. You can have multiple handlers, […]

Apps Script & Java Script

Google Apps Script VBA equivalents

When converting from VBA, unless you are abandoning VBA, it’s just as well to mimic some of the functions that are missing from GAS so that you can minimize your script Conversion work. Here is […]

From VBA to Google Apps Script

Scraperwiki data to Google Docs

There is also a Google Apps script version of  Getting data out of scraperWiki. It uses the mcpher library and the test data and examples can be found in this Google Document  There is a rest library entry to […]

Apps Script & Java Script

Github service for Goa examples

This describes how to authenticate with github 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 […]

Apps Script & Java Script

Response format from apps script project query

Format of Info.json Each project directory contains an info file that describes the scripts for this project. It is also used to decide whether the sources in the project need to be refreshed. To force […]

Apps Script & Java Script

Making sense of gwt rpc dependency service

Obsolete This post is now pretty much obsolete. As of April 2016, the dependency service seemed to disappear. Next attempt was via a hacked autocomplete service to discover dependencies, which worked until 2018. Now this […]

Apps Script & Java Script

Creating a Google Apps Script Library

mcpher Library All Google apps script library code is in the mcpher library. If you already know about google sctipt libraries you can get it as below, and read about it in this post.  Project Key […]

Apps Script & Java Script

Let Google Apps Script do the work

For this site, I usually create a Google Apps Script version and a VBA version of the projects and libraries discussed. This is not so hard, since there are utility libraries in both languages, so it’s […]

Apps Script & Java Script

Store data in scriptdb

Create a module that stores some test data in a public scriptdb. In Hosting your public scriptdb you will have created a public scriptDB, Here’s how to store data in a public scriptdb. We are going to […]

Apps Script & Java Script

Copy databases using OAuth2

Because database abstraction is independent of the underlying database selected, the only difference in syntax for operations is for opening the database. That means that copying between databases is straightforward. Here’s an example of copying between a […]

Apps Script & Java Script

Copying databases

Because database abstraction is independent of the underlying database selected, the only difference in syntax for operations is for opening the database. That means that copying between databases is straightforward. Here’s an example of copying between a […]

Apps Script & Java Script

Summarize email thread recipients to a spreadsheet

If you followed Do something useful with GAS in 5 minutes, you’ll already know how to use a spreadsheet as a database. This time, here’s how to search your mail for some threads on a particular subject […]

Apps Script & Java Script

Fusion and crossfilter

If you followed Do something useful with GAS in 5 minutes, you’ll already know how to use a spreadsheet as a database. This time we are going to get data from Fusion Tables, play around with […]

Apps Script & Java Script

Get analytics profiles in a sheet

If you followed Do something useful with GAS in 5 minutes, you’ll already know how to use a spreadsheet as a database. Here’s how to use that along with Google Analytics to get all your profile […]

Databases

Graduate to a database

If you followed Do something useful with GAS in 5 minutes, you’ll already know how to use a spreadsheet as a database. You may want to move on to using a real database as a back […]

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

Drive

Visualizing Apps Script dependencies

dependencyForce If you use a lot of libraries, sometimes things get a bit messy. You delete an old version to clean up and suddenly notice a few days later that things stop working because it’s […]

Google Authentication

Service account

This describes how to get an access token from the Goa library using a service account, as described in Oauth2 for Apps Script in a few lines of code (which you should read first for background). If […]

D3

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

Apps Script & Java Script

Using the People API with Apps Script

The BigQuiz app uses the People API to identify who is using it so that it can keep track of game and category scores. I could have used the Gplus api – which has an Apps Script advanced service, […]

APIS and techniques

BigQuery and the Execution API

The data for BigQuiz app is held in BigQuery. In Loading large JSON datasets into BigQuery with Apps Script I showed how you could maintain data in BigQuery with Apps Script, but now when I come to use it, […]

Apps Script & Java Script

Firebase JSON REST access library for Apps Script

The BigQuiz app uses Firebase for keep track of the question, category and game scores of individual players. In Firebase custom authentication with goa I showed how to use Goa to manage authentication for Firebase. This firebase access library works with […]

Apps Script & Java Script

Complete Apps Script Applications

I’ve found that I have completed applications lying around the site, with no good way of finding them. I’m starting this section to walk through complete Apps Script Applications, which cover a number of concepts. […]

From VBA to Google Apps Script

Roadmapper migration

Project conversion We will use the Roadmap Generation project as a model for learning how to take an existing, complex project and convert it from VBA to Google Apps Script. This is implemented as a one liner […]

Apps Script & Java Script

Color Ramp useful scripts

Color manipulation library scripts First off for this project we are going to need to figure out how to manipulate colors in Google Apps Script. As per the approach in Roadmapper migration my objective is to make […]

Apps Script & Java Script

d3 and parse mashup

This is an interesting mashup of a number of topics covered on this site. The target is to build an app that Retrieves color scheme data from a parse.com database, much like the app in Color […]

Apps Script & Java Script

Google Script Color Ramp

Project conversion We will use the Color ramp library  project as the basis for this migration. The complete code can be found here Google Apps Script Color Ramp workbook Status of conversion General libraries You can read about […]

From VBA to Google Apps Script

Automatic VBA to apps script skeleton

Using the modules from Automatic documentation, here’s how to create a skeleton Google Apps Script Module directly from VBA modules or classes. It will also create basic JSDOC  markup for documenting your new GAS module.  You’ll find all […]

Apps Script & Java Script

Migrating from cEzyOauth2

This describes how to migrate from cEzyOauth2 while using the Goa library 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 […]

Apps Script & Java Script

Google Apps Script HTML service

One of the most powerful features of Apps Script is the HTML service. This allows you to create html/JavaScript applications which can be served up as webapps, run in sidebars or as add-ons and created great […]

Add-ons

Running things in parallel using HTML service

Apps Script is inherently synchronous – you cant do lots of things at once. Primarily to get round the execution limit, I did a science project using triggers for parallel processing but the triggers are fairly unpredictable […]

Drive

Drive JSON API for apps script

In Using Drive SDK I described why you might want to use the SDK JSON API rather than the built in Apps Script Drive Class. Before you can start with this you need to authorize your access with OAUTH2, […]

scriptDB

Google Apps ScriptDB

NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. The versatile scriptDB scriptDB is a javaScript object database for Google Apps Script project. It is a very useful feature to […]

Apps Script & Java Script

Borrowing an access token from Apps Scripts

The simplest way to get a token to use with a Google API is to borrow the one from Apps Script – assuming that you can persuade Apps Script to enter an authorization dialog for […]

Apps Script & Java Script

Configurable canvas meter

Here’s a very simple, but nice looking meter in JavaScript. It’s configurable for a number of uses. There’s no need for any css (styling is by configuration) or additional libraries (it uses Chroma-js), as everything […]

Add-ons

Chord Snip

Chord snip is an add-on for Google Sheets that creates a Chord diagram as you type, gives you the SVG code to embed it in a web site and the ability to embed it in […]

Libraries

Database abstraction and Google Apps Script

Parse.com parse.com as now been closed and moved to parseplatform.org. I will update the content of this page an move the back-end to this platform × Dismiss alert  All code here is unprotected and free to […]

Apps Script & Java Script

Integrating Apps with other Platforms

As Google APIS continue to improve there are more ways to integrate them. The pages on this site reflects the technologies available at the time, so I thought I should bring together the pages that […]

Colors

Coloring maps and other shapes

In Using Google Maps I covered how to create various web based interactive maps. Of course many would like to use Excel to create maps, and the topic of chorpleth maps, has been covered many times by […]

Apps Script & Java Script

Color Matching in GAS

I covered the mathematics of color matching in the VBA implementation in  Find nearest color match. This is the Google Apps Script implementation. I’ll be using the CIEDE2000 algorithm to measure the distance between colors. One thing I […]

Apps Script & Java Script

JSON and VBA

These articles are abridged from my  book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available  from O’Reilly or Amazon VBA does not naturally support JSON, but to be able to use this API (and […]