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 […]
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. var r = {a:{b:{c:{d:{e:true}}}}; […]
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 […]
JavaScript currying and functional programming was an introduction to currying. A very useful technique that uses the properties of JavaScript closures – how, where and why to encapsulate argument values to create a new version […]
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 […]
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 […]
This namespace contains a bunch of useful methods to generate random lists and strings. It’s plain JavaScript so works on both Apps Script or client-side JavaScript. The main features are creating random strings, arrays and […]
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. […]
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, […]
The Javacript date object is a thing of wonder. If you are scheduling events in Apps Script, you may want to do something like figure out when the last friday of each month is. Here’s […]
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 […]
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 […]
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 […]
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 […]
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 […]
It’s quite irritating that you can only access functions that are global from the client side when running Html service. Except there is a way round it using regular JavaScript. Let’s say you have a […]
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 […]
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 […]
It’s always a compromise to use a progress bar, as it’s usually not known how long something will take, and therefore how to plot progress. This is especially true in Apps Script when the activity […]
If you want to share files with people that are new to Google Drive there can be some conceptual challenges with the sharing paradigm. When you use Google Docs sharing, you are sharing the document […]
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 […]
If you use any of my webapps that return json data, there will be an option to return jsonp instead. Here’s a quick summary of what it’s all about. Same-Origin security policy. Since JSON is […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 = […]
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 […]
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 […]
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 […]
If you use sidebars with html service, you are probably used to using google.script.run to be able to execute apps script functions from within htmlservice, as well as how to pass arguments back and forwards. […]
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 […]
This section has grown a little beyond a snippet, so I’ve moved the whole story to its own section – please take a look at Running things in parallel using HTML service for more. For […]
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 […]
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 […]
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 […]
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, […]
A little while ago, I did a an article on Instrumenting VBA for Google Analytics, which uses Universal Analytics to track the use of sections of VBA code. One of the things that is missing when […]
When implementing Using the gplus api in Apps Script I noticed that there didn’t seem to be an easy way to just get the the number of plus ones given a URL – but yet […]
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 […]
I often need to find a bunch of files in a particular folder, and if I also need to get files in subfolders also, it can be a little longwinded. I prefer to deal with […]
Let’s say you have a bunch of Google Documents with various images in them – say a logo or photographs – anything, that you’d like to replace with different images. Well, one way would be […]
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 […]
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 […]
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 […]
If you have a workbook with many pages you often would like to create an index sheet with hyperlinks to get to the various pages. This gets to be a pain to maintain, especially if […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 vizMap can be found […]
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 vizMap can be found […]
How to modify geocoding and mapping parameters 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 parameters can be […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 geocoding can be found in Complete Excel Address Data with […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
Another place that Fiddler can be handy is if you are populating a sheet with data retrieved from an API. The examples use the Itunes API which will return tabular data as an array of […]
A common thing to do is to update a master sheet from an update sheet – where you want to replace master items with updates, or add new items if they are not already in […]
Let’s say you have a field in a sheet that says January 2017 that you want to treat as text, but it looks like a date. In the Sheet UI you simply add a single […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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, […]
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 […]
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 […]
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 […]
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. […]
Demo Here’s a video showing the final proof of concept of using fisheye distortion to navigate around a google sheet Implementation The principle of this kind of projection is to enlarge items closer to the […]
Demo This demo is a proof of concept for Ephemeral exchange push notification. The code is on github. Here’s a video showing the capabilities. Continue Reading about 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 […]
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), […]
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 […]
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 […]
‘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 […]
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 […]
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 […]
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 […]
There are a number of ways to get data into BigQuery but one of the most convenient as far as Apps Script is concerned is to write it straight there from data generated during some […]
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 […]
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. […]
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 […]
If you are like me, you will dread projects that involve oAuth2. It never seems to work first time and it’s fiddly. It’s also irritating that UrlFetchApp gives you the impression (false hope) that it’s […]
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 […]
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 […]
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 […]
The User property service is handy to store user specific properties in, but it can also be used to assign a unique anonymous reference to users and track their visits. Here’s a useful namespace in […]
If you have some software or an add-on, and you want to find a way of giving a free trial that expires, one way is to create a token code that has all that built […]
Sheets Developer Meta Data – Apps Script advanced service, shows how to use the Sheets Advanced service to work with Sheets DeveloperMetada, and since the resources from this service are a little complicated you may […]
Recently, developer metadata was added to the capabilities of the Sheets V4 API, as described here Sheets API – Developer Metadata (which used the API directly). If you don’t know about Metadata, take a look at that […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
I wondered if there might be more functional programming, more declarative approach to the problem of fiddling around with spreadsheet data. Using the API to insert rows and worrying about column numbers and ranges can […]
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 […]
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 […]
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 […]
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.. […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
It’s a common pattern in JavaScript to do this to assign a default value to a variable : var theThing = someThing || defaultThing; In other words, if someThing has a ‘falsey’ value (false, null […]
I’ve been dong a lot of writing over on Desktop Liberation looking at techniques to run Apps Script/JavaScript code directly from VBA, In this post I’m going to talk about how useful this can be if […]
In December, Google released new Sheets, saying they are faster and better than the old – including working offline. I’ve made a couple of tests to compare the old and the new – a simple operation […]
If you use blogger.com you may want to get some data about your posts into a spreadsheet for analysis. For example, the d3 concept browser analyses the Excel Liberation site and blog each night. There […]
Someone asked in our forum yesterday about Facebook Query Language – FQL. Could we get data straight into Excel from facebook? I dont use facebook, so I haven’t paid much attention to it. I had […]
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines