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 v8

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

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: 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

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

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

No Picture
Apps Script & Java Script

Setting up blister lists

In Using blister custom functions I showed examples of how to lookup scriptDB lists as custom functions.  Here’s how to create lists. In each case, to access lists in your sheet you need to add a reference […]

No Picture
Apps Script & Java Script

Blister validation code

Here is the code for each of the example mentioned in GAS lists and validation. The custom functions You need this in your local workbook. You can find it in the examples sheet. These are the custom […]

No Picture
Apps Script & Java Script

Using blister custom functions

Examples Let’s take an example. Say you are a small garage, with a stock list of cars and you work exclusively with spreadsheets. You don’t want to take copies of the list, but you want […]

No Picture
Apps Script & Java Script

Blister custom functions

The custom functions These are the custom functions you can call directly from your sheets. The function documentation is here and you can find examples of use in Using blister custom functions and you’ll need the code described in Blister […]

No Picture
Apps Script & Java Script

GAS lists and validation

It’s very common to want to lookup some list, or apply them as validation to data entry when playing around with Spreadsheets. Between them, a search for Spreadsheet related VLOOKUP, INDEX and MATCH return 30 […]

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

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

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

scriptDb JavaScript api examples

The JavaScript client is very similar to A VBA API for scriptDB, so reading that will give you a more detailed flavor of what can be done. The implementation is very different though, and there are […]

Apps Script & Java Script

Testing the API

This is a ScriptDB API for JavaScript. The tests that are referred to in scriptDB API for JavaScript are implemented in https://storage.googleapis.com/toasty/t/gas/scriptdbapi.html. Code is below

These are the results you should see, once the copy has completed […]

Apps Script & Java Script

scriptdbcom implementation

Here is the javascript implementation for the scriptDB API. It needs jQuery.

For help and more information join our forum, follow the blog or follow me on Twitter  

Apps Script & Java Script

Authenticating scriptDb JavaScript API

As per this blog post, Google Apps Script does not support CORS (cross origin resource sharing) to allow access from cross domain client apps. The API uses JSONP for GET requests to get over this, but […]

Apps Script & Java Script

cParseCom implementation

I use scriptDB a lot , both with Google Apps Script and from other sources, including VBA, and in this blog post, I  compare parse.com performance with script DB. One of the things that Parse.com has going […]

Apps Script & Java Script

Some simple examples

Because the GAS API is so similar to parse.com – nosql database for VBA, you may want to read that too, which has similar examples. Note that virtually all methods can be chained leading to pretty […]

Apps Script & Java Script

creating Parse Classes from Google Spreadsheets

Just as in parse.com – nosql database for VBA, you’ll probably want to load up some test data from a Google Spreadsheet to a Parse.com class.  You’ll find some test data in this workbook – it’s same data […]

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

Using Google Earth

javaScript: Flying around and asynchronicity in google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Google Earth Api You will find tons of content on Google Earth , most of which is described […]

Apps Script & Java Script

Using Google Visualization DataViews

javaScript: creating google visualization Data Views This relates to Data Driven Mapping applications and the Javascript ‘HowTo’ section Google Visualization dataViews As mentioned in Using Google Visualization DataTables a master google DataTable is created for all data in […]

Apps Script & Java Script

Using Google Visualization DataTables

javaScript:creating google visualization DataTables This relates to Data Driven Mapping applications and the Javascript HowTo section Google Visualization Tables I have covered Google Visualization in various forms with an ‘Excel slant’ on this site. Let’s take a look […]

Apps Script & Java Script

Providing VizMap Data without Excel

VizMap: the data javaScript This relates to Data Driven Mapping applications For this example I had created a  Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example Parameter WorkSheet is […]

Apps Script & Java Script

Creating VizMap framework without Excel

VizMap: the framework javascript This relates to Data Driven Mapping applications For this example I had created a  Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example Parameter WorkSheet is […]

Apps Script & Java Script

VizMap Application Filename

VizMap: the Viz Html parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and is used to define the filename of the to be generated application. It also contains customization […]

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

Apps Script & Java Script

VBA and jsdoc

For an overview of what VbaGit is, please read Integrate VBA with Github. If you are working with Google Apps Script as well as VBA, then you will probably be familiar with JDSOC. This is a way […]

Apps Script & Java Script

Getting started with VbaGit

For an overview of what VbaGit is, please read Integrate VBA with Github. Getting the code You can get the code from github or you can get a premade bootstrap workbook from the github stuff section in the  downloads […]

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

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

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

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

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

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

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

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

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

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

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

the template

I get a correct hello world […]

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

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

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

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

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

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

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

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

Apps Script & Java Script

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

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

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