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

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

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

No Picture
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 `some text and some more text and even more text` This gets concatenated […]

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

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

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

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

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

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

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

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

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

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

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
General JavaScript techniques

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

No Picture
General JavaScript techniques

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

No Picture
General JavaScript techniques

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

No Picture
General JavaScript techniques

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; var myBox = function () { var self = this, _height, _width; self.setHeight = […]

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
Docs Specific

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
Docs Specific

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

No Picture
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

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

General JavaScript techniques

Publishing ES6 code on NPM

I’ve covered a couple of client libraries in VBA library for Ephemeral exchange and Apps Script library for Ephemeral exchange, and this article will talk a little about the Node library, but more specifically, a general discussion on […]

Apps Script & Java Script

Convert field of view to zoom and back again

This is a snippet from the series described in Playing around with maps and streetview, and shows the calculation to convert between fov and zoom Objective The Streetmap image API uses field of view to figure […]

Apps Script & Java Script

Controlling the demo

With a system with many moving parts, we need a way to see progress and to control the behavior as we go. For that I’m using  A google sheet to display results so far A […]

No Picture
API Specific

Get GAS library info

There are many references to shared libraries on this site. You can either take a copy of them or use them in place as you wish. I’m always hitting a few problems with a) versions […]

No Picture
Apps Script Specific

Proxy jsonp

If you are reading this, I guess you are familiar with this error message XMLHttpRequest cannot load http://dl.dropbox.com/u/14196218/Share/somedata.json. Origin null is not allowed by Access-Control-Allow-Origin. This is of course the same origin policy that prevents you being […]

Javascript

Pixels needed to render a string

Why would you need to ? Perhaps for layout, you may want to place text where it will fit, or change the font size if it doesn’t. On example I came across was using when […]

General JavaScript techniques

jquery promise snippet

Promises are an elegant way of providing for the handling of the future completion of an action in JavaScript. You will be familiar with the concept of a callback. This allows your app to get […]

Apps Script & Java Script

Deduce column headings from rest Query

In Rest to Excel library there are plenty of examples of populating sheets from rest queries. Usually you simply name the columns to match the data you want to extract and you are good to go. However, you […]

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

Getting Analytics Pageviews into 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, and in Get analytics profiles in a sheet you found out how to get Google Analaytics […]

No Picture
Libraries

Using named locks with Google Apps Scripts

The LockService gives the capability to do this This service allows scripts to prevents concurrent access to sections of code. This can be useful when you have multiple users or processes modifying a shared resource […]

No Picture
API Specific

Multiple inserts in Fusion Tables

Inserting rows with the Google Apps Script FusionTables service is pretty slow. There are also a bunch of quotas to worry about. Here’s what is says. Note: You can list up to 500 INSERT statements, […]

No Picture
Snippets

Exponential backoff

I’m refactoring my cUseful library for some upcoming articles. This means updating some long standing functions that some of you may be using. All the existing ones will continue to work as before, but the […]

Apps Script Specific

Migrating from Parse.com to MongoDB

You probably read the announcement that Parse.com is closing its hosting service. This is probably a good time to look at firebase, but if you are using my dbabstraction library, you can change to some other backend […]

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

Backing off on rate limiting

There are many rates and quotas in the Google Apps Script environment. One of the usual ways of dealing with it is to sleep between instructions, but that seems to be a rather blunt instrument. […]

Apps Script & Java Script

Pushing changes from Google Sheets server to client

Detecting changes from an add-on sidebar or dialog You’ll find various techniques for this around this site, but they are all based on polling. This means that the client app running in the browser occasionally […]

No Picture
General JavaScript techniques

Apps Script as a proxy

If you try to access data in a different domain from a client-side app, you’ll often get an error about CORS (cross-origin resource sharing). I’ve dealt with this topic before in Cross Origin Resource sharing (CORS), […]

No Picture
Libraries

Using crossfilter with Google Apps Script

Visitors to this site will know that I’m a big fan of d3.js. Crossfilter.js (see the link for details, credits, licensing and API usage) is a library to play around with large data sets, often […]

Apps Script & Java Script

Using es6 promises server side in Apps Script

In Using promises with apps script I showed how to use promises with HTML service as a better way of managing asynchronous functions (especially google.script.run) from the client. Promises are a great way to express the order […]

D3

Big quiz score reports

‘m showing score reports for the BigQuiz app. This is an individual players score compared to the maximum he could have got as well as the number of questions that were correctly answered, and also comparing […]

D3

Quick doughnut and pie charts with a radial timer

If you’ve been trying out the Configurable circular timer with D3 or playing around with Example of clock using d3 configurable timer you’ll have seen how easy it is to create a versatile timer by just configuring a few […]

No Picture
D3

Example of clock using d3 configurable timer

In Configurable circular timer with D3 I showed a circular timer made with D3.js . Although it’s not really what it’s for, this simple example shows how to make a complex viz. This is a clock […]

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
BigQuery

BigQuiz app

This is a webapp that touches on a number of different Apps Script capabilities such as.. The app structure OAuth2, and multiple providers and roles Firebase authentication Firebase to manage player data Using libraries to […]

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

Snippets

Formatting range addresses

What can you learn here? Easy format for debugging Remove dollars for fill Shorten addresses This article shows some techniques to manipulate text representations of range addresses. The functions mentioned are all included in the downloadable getting […]

No Picture
General JavaScript techniques

Flattening and unflattening objects to spreadsheets

In Flattening an object with dot syntax I showed how to take an object of more than 1 level deep and flatten it so it could be represented in a two-dimensional object like a spreadsheet. Now here’s […]

No Picture
General JavaScript techniques

Advanced Array functions

There are a number of useful array functions that have been around since EmacsScript5.1. This means that they are not available in some older browsers, but have been implemented in Google Apps Script. Here’s a few […]

Docs Specific

Getting started with pinyin helper

You can get an example document which contains some sample pinyin here, and which you can copy and use to become familiar with this add-on, as well as read more about how pinyin works. Alternatively create […]

No Picture
Sheets specific

Sheets Meta Data Advanced service library utilities

Sheets Developer Meta Data – Apps Script advanced service, shows how to used the Sheets Advanced service to work with Sheets DeveloperMetada. This post goes a little further and adds a library to simplify access […]

No Picture
API Specific

Sheets API – Developer Metadata

  Why Sheets Developer Metadata ? With Sheets API developer metadata, you can associate keys and data with a spreadsheet, sheet, row or column. Previously, if you wanted to ‘remember’ some data attribute, let’s say […]

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

Snippets

Getting started with recursion

What can you learn here? Recursion coding techniques When to use recursion Dealing with tree structures Usually recursion is considered to be a rather advanced topic, but actually it’s pretty straightforward – just easy to […]

No Picture
General JavaScript techniques

Flattening an object with dot syntax

Sometimes you need to blow out a JavaScript object so that the keys are fully qualified. For example, if you are doing a rest query on some third party database, or to represent a more […]

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

No Picture
Fiddler

Sorting Google Sheet DisplayValues

If you use getDisplayValues to retrieve data from sheets, you can hit a problem with sorting. Perhaps numbers have been converted to strings, or dates are in an unsortable format for example 1,2,3,101 would get sorted […]

Apps Script & Java Script

Pseudo binding in HTML service

One of the good things about Microsoft apps for office Add-ins (these are similar to Apps Script Add-ons in that you get a sidebar where you can extend apps with HTML apps), is that data […]

No Picture
General JavaScript techniques

Namespaces in libraries and scripts

As you know, in Apps Script you can have libraries that can be accessed from other scripts. Apps Script automatically generates a namespace for that library (it calls it an identifier) which you prefix calls […]

No Picture
Apps Script & Java Script

More client server code sharing

In Sharing code between client and server I wrote about how you could write code that could be used by both your client JavaScript and your server Apps Script, so you could pool utility functions when writing […]

Full Stack

React, Redux, Redis, Material-UI and Firebase

A whole bunch of front end platform libraries that go together really well, but that are hard to grasp and get started with. Here’s a bunch of snippets to help show how all these things interact.. […]

Snippets

One Liners

What can you learn here? Using ramblings libraries Short code snippets Abbreviated explanations Complex stuff in a few lines of code  get it now This section is going to cover how to do complex stuff in […]

No Picture
Microsoft Office & VBA

jSon and Dynamic Forms

  Now we know how about Hiding data in Excel Objects, Excel JSON conversion and Dynamic Forms let’s combine all that as an enhancement to the Roadmap Generation project. The objective is to embed traceability data in the generated roadmap shapes so […]

Apps Script & Java Script

Implementation example of form at mouse position

Implement a popup form at the mouse position  get it now Now that we know  How to position a userform on the mouse, here’s a simple example of an implementation that uses such a form to […]

Snippets

Getting Started with argument passing

What can you learn here? Argument passing ByREF versus byVAL Optional and variable arguments     Argument passing Nowadays passing arguments to procedures is intuitive and the mechanics of how that happens is mainly the […]

No Picture
Apps Script & Java Script

Asynchronicity and Scheduling

What can you learn here? Timers Scheduling tasks Progress bars Dealing with asynchronous events VBA has Event Processing capabilities. This is how it is able to deal with mouse clicks, form events and so on. […]

Apps Script & Java Script

Google Apps Script snippets

There are many Google Apps Script projects and snippets scattered around this site. I’m going to try to organize them in this page, but it will take a while. If you find something on the […]