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. […]
Apps Script & Java Script
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 […]
API Specific
Minimizing maps directionfinder api calls
If you use the Google APIs, you’ll have been hit with the “service invoked too many times in one day” problem. In Backing off on rate limiting I showed how to deal with rate limited […]
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
Creating and working with transposed sheet data arrays
Data from sheets is organized as an array of rows of arrays of column values. Sometimes that’s not convenient and prevents you from effectively using the useful mapping array methods that I covered in Highlight […]
Sheets specific
Highlight duplicate rows in a sheet – map and reduce
I covered this topic in Highlight duplicate rows in a sheet – map, filter and every, so now we’ll build on that a little and introduce the .reduce Array function. As before the problem is […]
Sheets specific
Highlight duplicate rows in a sheet – map, filter and every
Someone asked this question on the forum the other day, so I thought it would make a good example of how to use Array functions to simplify looking at sheet data. I normally would use […]
Sheets specific
Converting timestamps to dates formula
In Apps Script, it’s easy var timeStamp = new Date().getTime(); var date = new Date(timestamp); But sheets doesn’t use JavaScript timestamps for dates, it uses the number of days since 1st Jan 1900, with hours/mins/secs […]
Sheets specific
Detecting Spreadsheet tables automatically with Google Apps Script
Excel Tables are a nice feature. They size automatically and they are easy to reference. Google Sheets doesn’t have that and it can be difficult to find data on a sheet, where there are blank […]
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 […]
Itunes
Using the Itunes API with Apps Script
The Itunes API is very nice. You can find details on it at ITunes API I thought I may as well add an Apps Script wrapper library to it, since it can provide a wealth […]
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 […]
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 […]
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 […]
Sheets specific
Data wrangling with named columns in Google Spreadsheet
In Using a cross filter with Google Apps Script I showed how to use the powerful cross filter library to do some data wrangling. But using some of JavaScript’s Advanced Array functions, and working with […]
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 […]
Apps Script Specific
Checking the argument types in Apps Script
I often waste a lot of time tracking down stupid bugs that are to do with passing the wrong kind of argument to a function. As you know, JavaScript is not strongly typed, and this […]
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
Normalizing the header level of blank paragraphs
Here’s another little Docs annoyance that can easily be cleaned up with a little automation. If you are using headings in your document, and insert blank lines beside a heading, you end up with uneven […]
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 […]
Snippets
Organizing parallel streams of server calls with google.script.run promises
In Organizing asynchronous calls to google.script.run I showed how to avoid spaghetti with google.script.run callbacks to server-side functions that needed to run sequentially. You should take a look at that first if you are unfamiliar […]
Snippets
Organizing asynchronous calls to google.script.run
There’s a lot of questions on the Google Apps Script forum about handling asynchronous calls from an HtmlService client back to the Server. This is done using google.script.run – but it’s important to realize that […]
Snippets
Using promises to orchestrate Html service polling
In Use promise instead of callback for settimeout I showed how a more structured approach to scheduling events could be achieved through the use of promises rather than timeout callbacks. In apps that use HtmlService, […]
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
Copying canvas and svg images from your Add-on
Let’s say that you have an Add-on which creates something in Canvas (or SVG), and you want to now embed that in a Sheet or document. Here’s how. As an aside, the code samples here […]
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 function doGet(e) { return HtmlService .createTemplateFromFile(‘mytemplate’) .evaluate(); } function getValue() […]
Apps Script & Java Script
Getting an htmlservice template from a library
If you use HTMLservice templates, you may want to centralize useful ones and use them in multiple projects, just like you can with library scripts. All you need to make this work is a library […]
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 […]
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
Use Drive properties to find app files
Sheet and document IDs can be a a handful to manage, and if you want to use a different file you may need to update all the scripts that reference it. The Drive API allows […]
Apps Script Specific
Using exponential backoff with github api – dealing with data “in preparation”
Some of the responses from the GitHub API rely on the answers being in its cache. If you make a request and there is no available cache result, it returns an HTTP code (202) to […]
Apps Script Specific
How to get stats about youtube videos in your channel with apps script
There are 3 YouTube related advanced services in Google Apps Script. In this post I’ll show you how to get simple stats about the youtube videos in your channel. The API documentation can be a […]
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
Dealing with objects that are too large for the property or cache store
The Property store has a limit of 9k on individual property items and, the cache store has 100k. You may also be using other stores for this kind of data with similar limitations. Let’s take […]
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 […]
Apps Script Specific
Zipping to make stuff fit in cache or properties service.
If you use caching in Apps Script (and you should), you might hit the 100k limit on cache sizes. I’ve written about how to get round that by writing several cache entries and linking them […]
Apps Script Specific
Creating a key digest to use for a cache key or to compare content
Creating a crypto digest of some content is a neat trick used by github (and torrent sharing sites) to know if the content of two files is identical. There are plenty of other uses for […]
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
Generating test data for sheets and tables
Sometimes you need to make up some random data for a sheet or a docs table just for testing in some volume. I use this snippet to generate random data in the right shape. You’ll […]
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 […]
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 […]
Apps Script Specific
How to determine what kind of object something is in Apps Script
If you liked Reporting file, function and line number in Apps Script, you’ll probably also like this one. This shows how to get some properties about an object in apps script, and can be useful […]
Apps Script Specific
Reporting file, function and line number in Apps Script
If you are using the Logger extensively, it’s sometimes hard to track where the log message is coming from. It would be useful to display line number, file name and function that provoked the log. […]
Music maths and physics
From notes to frequencies and back again
This is part of a bigger project that I’ll write up at some later date. These functions from it might be of some use to anyone who wants to identify a pitch from an audio […]
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 […]
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
Squeezing more into (and getting more out of) Cache services
There are a few articles on this site about getting more into (and out of) cache and property services. Now my cUseful library contains the ability to create caching plugins that take advantage of some […]
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
Calculating image dimensions in server side apps script
I have a Node project that looks at Google Cloud storage and figures out the dimensions of images stored there. This is handy for an API to be able to serve up the right one […]
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 `some text and some more text and even more text` This gets concatenated […]
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. var r = {a:{b:{c:{d:{e:true}}}}; […]
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 – even more
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 […]
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
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
Generating and managing random lists with JavaScript and Apps Script
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 […]
General JavaScript techniques
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, […]
General JavaScript techniques
Calculating the last day of a given weekday in the month
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 […]
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 […]
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
Converting and copying Google Apps files to Microsoft OneDrive
In Using Advanced Drive service to convert files I covered how to use the Advanced Drive Service to convert Sheets to Excel and write the result back to Drive. It occurred to me that, since Microsoft OneDrive […]
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 Specific
Addressing namespace and library methods from google.script.run
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 […]
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 […]
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 […]
Snippets
Implementing a client side progress bar reporting on server side work for htmlService
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 […]
API Specific
A webapp to share copies of the contents of folders
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 […]
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
JSONP and JSON and Google Apps Script webapps
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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, […]
Apps Script & Java Script
How to pass non stringifyable objects to html service
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. […]
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
Parallel process orchestration with HtmlService
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 […]
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 […]
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 […]
Apps Script & Java Script
Orchestration of Apps Scripts – parallel threads and defeating quotas
Over the years I’ve had a few goes at this – for example Running things in parallel using HTML service and Parallel processing in Apps Script, so I’ve decided to do a bigger and better version of those. […]
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, […]
API Specific
Universal analytics measurement protocol for your GAS libraries
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 […]
API Specific
Extract the plus one counts from a page
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 […]
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 […]
API Specific
Creating a pile of files list from Google Drive
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 […]
Docs Specific
Bulk search and replace images in Google Docs
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 […]
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 […]
Snippets
Creating an index sheet for a workbook
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 […]
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 […]
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 […]
Snippets
How to define your vizMap Application – visualizations
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 define your vizMap application – Dictionary
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 prepare for your VizMap application – Parameters
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 […]
Snippets
How to prepare for your vizMap application – Data
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 […]
Snippets
How to modify geocoding and mapping parameters
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 […]
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 […]
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 […]
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
How to Geocode addresses using Google 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 geocoding can be found in Complete Excel Address Data with […]
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
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. […]