apps script crusher on github
Apps Script

Github as an Apps Script cache platform

Github as an Apps Script cache platform Another plugin available for Apps script library with plugins for multiple backend cache platforms so we can use Github as a back end for caching large objects across […]

upstash graphql explorer
Apps Script

Upstash as an Apps Script cache platform

Upstash as an Apps Script cache platform Upstash is a brand new service offering a serverless redis over https  via a GraphQL  API. Previously redis was hard to use along with Apps Script since we […]

info card on hover or click
Apps Script

4 ways to customize scrviz info card behavior

Info Card customization By default the info card appears when you hover over a node in the scrviz visualization. Although the info card is they key tool for investigating scrviz entries, it can be annoying […]

Apps Script

Enrich your developer profile on scrviz

In Showcase your Apps Script work and get hired via vizzy I showed how scrviz could be used to show off your Apps Script work to potential clients, so they could see what kind of […]

gapi and vuex
Apps Script & Java Script

Google gapi, Vue and Vuex together

Motivation JavaScript authentication with Gapi is both impressive and frustrating. Frustrating because in most of the examples you come across, and indeed in Google’s own guides, gapi is the center of the universe.  They are […]

sheets google apps script
Add-ons

Handly helper for fiddler

If you use some of my libraries, you may have come across  the Fiddler class, which abstracts sheet data and provides a more functional way to manage data from multiple sheets in a Google Spreadsheet. […]

apps script v8
Apps Script v8

Apps Script V8: Keystore for global space

One of the challenges with Apps Script V8 compared to Rhino is that you can’t be sure of the order of global variable initialization. Personally, this is not an issue for me, as I avoid […]

apps script v8
Apps Script & Java Script

Qottle recipe: Managing asynchronous queues with duplicates

Qottle recipe for managing asynchronous queues with duplicates Qottle is a queue for asynchronous tasks with prioritization, ratelimit, and concurrency support. It can also detect and reject duplicate requests. This recipe shows an example of […]

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

Apps Script V8: Arraybuffers and Typed arrays

Apps Script V8 implements ArrayBuffers and Typed arrays from ES6. If you’ve used Apps Script Crypto Utilities or done any work with Blobs, then this is what’s going on behind the scenes, now exposed as […]

apps script v8
Apps Script v8

Apps Script V8: Maps and Sets

Apps Script V8 implements a couple of useful new ES6 structures – Maps and Sets. Here’s what they are and how to use them Maps and Sets: What are they? These are a collection of […]

apps script v8
Apps Script v8

Apps Script V8: Template literals

V8 adds template literals from  JavaScript ES6. Template literals: What are they? It’s a shorthand way of using a template into which variables are substituted in a string. This allows for better reuse of string […]

apps script v8
Apps Script v8

Apps Script V8: spreading and destructuring

Apps Script V8 adds destructuring from  JavaScript ES6. Legacy Apps Script already had destructuring of arrays added fairly recently, but v8 gives full a destructuring capability. Destructuring: What is it? It’s a way of plucking […]

apps script v8
Apps Script v8

JavaScript V8 Arrow functions, this and that

Function declarations versus arrow functions JavaScript V8 adds the arrow function declarator from modern JavaScript. This is a handy shorthand but it’s more than just that. There are some behavioral differences too that you’ll need […]

apps script v8
Apps Script v8

JavaScript V8 variable scopes

var, const and let One of the key things that V8 has sorted out is the scope of variables. Using var to declare variables meant that anything declared within the scope of a function could […]

apps script v8
Apps Script

Apps Script v8

Apps script now supports JavaScript ES6 Apps Script V8 now supports the same modern JavaScript you can use when writing for the browser. Up until now, you had to write in a dialect of JavaScript-based […]

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