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

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

Identify duplicates on Drive

Identify Duplicates on Google 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. Here’s how to get a […]

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

Apps Script Specific

Convert JSON to XML

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

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

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

Apps Script & Java Script

Cloud print connector for Google Apps Script

Lenny Cunningham, created this handy Cloud Print Connector for Google Apps Script, using the library from EzyOauth2 – taking some pain out of Apps Script API authentication. For more contributions like these, why not join […]

No Picture
Google API

Connecting Google Sheets to APIs, and building apps with Retool

This is a repoduction of the tutorial, which can be found at https://tryretool.com/blog/google-sheets-app-tutorial/ Access the tutorial and documentation. Contents: Background Live demo Reading data from Google Sheets Displaying data in a table Creating a form […]

No Picture
Snippets

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

Snippets

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

Snippets

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

Snippets

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

No Picture
Snippets

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

No Picture
Snippets

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

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

Google Apps Script Publisher

In a number of articles and blog posts I’ve been looking at using Google Apps Script to publish data, to store it a scriptDB and to behave as a proxy for both access and processing. Pulling it all together, […]

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

Apps Script & Java Script

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

Apps Script & Java Script

Running parallel tasks as a web app

Up till now we’ve been dealing with running this stuff as a sidebar on  spreadsheet. Now we’ll take the example in Parallel implementation and getting started – doing some cryptography exercises in parallel and run it as a […]

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

Apps Script & Java Script

Dealing with rate limited services

In Running GmailApp in parallel we found that throwing additional parallel executors at a rate limited service doesn’t help much – it will only let you do so much at the same time. However Running things in parallel […]

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

Snippets

Diminishing Returns

Diminishing returns for more effort You will be very familiar with the law of diminishing returns, where you get a lot of payback for your initial efforts (sometimes called ‘low hanging fruit’), then you have […]

No Picture
Microsoft Office & VBA

Which VBA references you are using

VBA references Quite often you need to add some reference to be able to get some code to compile. This is because the code is referencing some external object that the Excel VBA does not […]

No Picture
Snippets

Excel Comment shapes

What can you learn here? Interrupt comments Change shape characteristics Add timestamp Modifying the behavior of Cell Comment Processing Download now This article shows some techniques to interrupt the processing of cell comments in Excel […]

Snippets

How to position a userform on the mouse

Working with cursor position in Excel/VBA Occasionally you might need to know the mouse position, for example if you need to position a form there. An example of a project using this technique is here jSon and […]

Snippets

How to add references by guid

In Which VBA references you are using we looked at how to get details about references active in a project. Let’s look at how to add those references to a project using code. This approach is needed […]

Apps Script & Java Script

Click events in Google Earth

javaScript: Click events in Google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Events in Google Earth I deal with event handling in Google earth in Using Google Earth but dealing […]

Apps Script & Java Script

Using Google Visualization charts and tables

javaScript: creating google visualization charts and tables This relates to Data Driven Mapping applications and the Javascript HowTo section Google Visualization tables and charts All visualizations are created from the dataViews mentioned in Using Google Visualization DataViews. Once […]

Apps Script & Java Script

Creating a tabbed Google Mapping InfoWindow

javaScript: creating tabbed content This relates to Data Driven Mapping applications and the javaScript ‘howTo’ section. For this example I had created a  Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and […]

Snippets

How to control your vizMap application

This is part of the Step by Step Guides to get started with some of the more complex topics on this site. The full story on vizMap applications can be found at Data Driven Mapping applications   […]

Apps Script & Java Script

How to add flightPaths on a Map

This is part of the Step by Step Guides to get started with some of the more complex topics on this site. The full story on flightPaths can be found at Adding flight paths to Maps   […]

Snippets

How to Overlay circles on a Map

This is part of the Step by Step Guides to get started with some of the more complex topics on this site. The full story on marker quicklinks can be found at Overlaying circles and heatmaps What you […]

No Picture
Snippets

How to use alternate Map icons

This is part of the Step by Step Guides to get started with some of the more complex topics on this site. The full story on marker quicklinks can be found at Overlaying circles and heatmaps What you […]

Snippets

How to filter markers by Category

This is part of the Step by Step Guides to get started with some of the more complex topics on this site. The full story on marker quicklinks can be found at Quicklinks and Categories   What you […]

No Picture
Snippets

How to add quickLinks to a Map

This is part of the Step by Step Guides to get started with some of the more complex topics on this site. The full story on map quicklinks can be found at Quicklinks and Categories What you need […]

Snippets

How to put markers on maps

How to put markers on maps This is part of the Step by Step Guides to get started with some of the more complex topics on this site. The full story on map markers can be found […]

Snippets

Clean up Pivot Cache

What can you learn here? Space used by pivot cache Match cache to pivots Clear out cache errors Using Pivot Caches Excel Introduced the concept of pivot caches to try to minimize duplicated data when […]

Recursion

Sorting Collections

Sorting collections is something you might need to do. In this example, we’ll use the cMyClass object we created in Get Started Snippets to demonstrate sorting a collection of objects and how to use recursion to sort the object’s […]

Apps Script & Java Script

Asana service for Goa examples

This describes how to use the Asana service to authenticate with Asana 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 […]

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

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

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

Node focus on Blogger posts

In Node focus explorer without excel I showed a d3.js app that could navigate a google site using tags and nodes. This was actually pretty generalized, so it was a minor modification to add blogger post navigation capability also. Again we’ll […]

Apps Script & Java Script

End to end analysis, Excel and explorer

This is a blog post of which the excerpt is below. Click on the title to read the full post.  Read the post Visualization of Sites to Tags with d3.js

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

Apps Script & Java Script

Site data to sheets

In Analyzing site content with GAS I showed how to analyze a Google Site and get the data as json, or file it on Google Drive. Next we want to do something with it. Here I’ll use […]

Apps Script & Java Script

Analyzing site content with GAS

One the things I wanted to play around with was visualizing this site content with d3. But first I needed to create something which would generate data from the site (any domain site actually). It’s easy […]

Apps Script & Java Script

Search

Search() and Find() are pretty much the same. The only difference is that Find worries about matching case (upper/lower) and search doesn’t. That means that Brass is the same as brass as far as Search is concerned, but find thinks they are different. So you can […]

Sheets specific

Get data from the old Google sheets

It is all about Google Wire Connect to Google Docs Get live data into Excel Use data from Gadgets NOTE:  with the new Google Sheets, this method has been largely deprecated. There is a complete […]

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

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

javaScript functions

functions are Objects too. As I mentioned in Learning javaScript every variable in js is an object. A javaScript function is also an object. This means it can be assigned to a variable. consider this..

did […]

From VBA to Google Apps Script

classes and javaScript

Creating and working with classes in VBA is very different from working with js classes. In fact, JavaScript is a classless language – there is no such thing as a class. This is not so […]

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

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

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

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

Apps Script & Java Script

cScriptdbCom client

Here’s the  arguments to the cScriptDbCom class of A VBA API for scriptDB Here’s the VBA client code for A VBA API for scriptDB And the code for many of the examples mentioned for this topic For […]

Apps Script & Java Script

GAS API for handler

As covered in Handler for cScriptDbCom requests, a handler will deal with REST requests  from VBA API for scriptDB. In addition to a reference to the dispatcher library, it needs a reference to a GAS LIbrary for the […]

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

Recursion

How to traverse a treeview

Treeview traversal and recursion In Rest Results Explorer i showed how to Create a treeview from json using the treeView control. Objects like the treeview are generally dealt with recursively – see Getting started with recursion and Getting to Grips with recursion for […]

Apps Script & Java Script

Using Apps Script for xml json conversion

One of the great things about Apps Script is that you can use the content service to create your own apis, without the need for a server infrastructure. In From Xml to JSON and Convert […]

Apps Script & Java Script

use cJobject to create a unique list

Here’s something I do in many pieces of code on this site. Let’s say that you have a list of things, lets say an array or a collection and you want to create a list […]

Integrating Excel with Maps and Earth

Using Twitter in Earth and Maps

javaScript: Twitter API integration with  google 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 […]

Microsoft Office & VBA

Using cRest directly

What can you learn here? sing cRest Advanced capabilities Building new libraries Accessing the cRest class directly get it now Up till now we’ve been looking at the modules in the Rest to Excel library as the integration […]

Json

Populating sheets with Rest responses

What can you learn here? Using cDataSet Using cRest Using cJobject Putting the Rest response on the sheets get it now Now that we have the tools to generate existing  or adhoc rest queries, let’s look at how the data […]

Apps Script & Java Script

cJobject and javascript equivalents

There is plenty of reference information cJobject on this site, starting with How to use cJobject, or just enter cJobject in the search box. Here’s a quick reminder of what you’ll need to understand to follow […]

Apps Script & Java Script

Testing unpublished add-on

Installable triggers and unpublished add-ons As described in Setting up onChange trigger and When test add-ons doesn’t work you can’t install a trigger in test mode. The solution is to use your add-on as a library to a container […]

Apps Script & Java Script

Setting up onChange trigger

Installable change trigger Sheets has both an onEdit and an onChange trigger to detect changes in a sheet. We’ll be using the onChange trigger, which is an installable trigger. The important difference between an installable […]

Apps Script & Java Script

Reacting to push notifications

Receiving notifications Initializing the server shows how the process starts off by the Server writing something to Ephemeral exchange (see also Getting started with Ephemeral Exchange for push notification)  This is provoked from initializing the client

[…]

Apps Script & Java Script

Initializing the server

When you’re ready to start watching for changes, the server will emulate a change to get the whole ball rolling. Here’s the Server.init code, which will provoke a push back to the client with the […]