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

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

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

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

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

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

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

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

copt between drive and gcs
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 […]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

No Picture
Apps Script & Java Script

d3.js and parse mashup

This is an interesting d3.js and parse.com 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 […]

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

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

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

No Picture
Apps Script & Java Script

Learn how to transition from VBA to Google Apps Script

Migrating from VBA to Google Apps Script, or using them simultaneously can be hard to get started with. Although this material has been largely superceded by Going Gas, there’s stll some useful stuff here. […]

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

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

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

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

No Picture
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 Google Apps with other Platforms

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

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

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

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