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 […]
Apps Script & Java Script
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. […]
Apps Script & Java Script
Get Started Snippets
What can you learn here? Get started Short code snippets Abbreviated explanations Quick examples Get it now This articles on this site are largely about how to do quite big projects. Realizing that you may […]
Apps Script & Java Script
Cloud Storage and Apps Script
There are SDKs available for a number of languages, but not for Apps Script. . Since Google Cloud Storage is neither a built-in nor an advanced Apps Script service, for these examples I’ll be using […]
Apps Script & Java Script
Node focus on Blogger posts
In Node focus explorer without excel I showed a d3.js app that could navigate a google site using tags and nodes. This was actually pretty generalized, so it was a minor modification to add blogger post navigation capability also. Again we’ll […]
Apps Script & Java Script
End to end analysis, Excel and explorer
This is a blog post of which the excerpt is below. Click on the title to read the full post. Read the post Visualization of Sites to Tags with d3.js
Apps Script & Java Script
Scheduling GAS
We looked at how to create a json file on Google Drive containing the analysis of a google site in Analyzing site content with GAS. The script that creates that takes a whole lot of parameters, […]
Apps Script & Java Script
Site data to sheets
In Analyzing site content with GAS I showed how to analyze a Google Site and get the data as json, or file it on Google Drive. Next we want to do something with it. Here I’ll use […]
Apps Script & Java Script
Analyzing site content with GAS
One the things I wanted to play around with was visualizing this site content with d3. But first I needed to create something which would generate data from the site (any domain site actually). It’s easy […]
Apps Script & Java Script
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 […]
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 […]
Apps Script & Java Script
javaScript functions
functions are Objects too. As I mentioned in Learning javaScript every variable in js is an object. A javaScript function is also an object. This means it can be assigned to a variable. consider this.. function x() […]
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 […]
Fiddler
Formatting sheet column data with fiddler
Header formatting with fiddler shows how to use fiddler to easily set formats for your sheet headings. here’s how to do the same for column formats. This is quite a long read, as there’s a […]
Fiddler
Header formatting with fiddler
When playing around with sheet data, I always find myself using the Fiddler object – some more posts on this below if you are not familiar with it already. More on this topic available here […]
Fiddler
Populating sheets with API data using a Fiddler
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 […]
Fiddler
A functional approach to updating master sheet with Fiddler
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 […]
Fiddler
Fiddling with text fields that look like dates
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 […]
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 […]
Fiddler
Unique values with data fiddler
Fiddler is a way to handle spreadsheet data in a functional way, as described in A functional approach to fiddling with sheet data This class – Fiddler can be found in my cUseful library. Here’s […]
Apps Script & Java Script
cScriptdbCom client
Here’s the arguments to the cScriptDbCom class of A VBA API for scriptDB Here’s the VBA client code for A VBA API for scriptDB And the code for many of the examples mentioned for this topic For […]
Apps Script & Java Script
GAS API for handler
As covered in Handler for cScriptDbCom requests, a handler will deal with REST requests from VBA API for scriptDB. In addition to a reference to the dispatcher library, it needs a reference to a GAS LIbrary for the […]
Apps Script & Java Script
Google Apps Script VBA equivalents
When converting from VBA, unless you are abandoning VBA, it’s just as well to mimic some of the functions that are missing from GAS so that you can minimize your script Conversion work. Here is […]
Recursion
How to traverse a treeview
Treeview traversal and recursion In Rest Results Explorer i showed how to Create a treeview from json using the treeView control. Objects like the treeview are generally dealt with recursively – see Getting started with recursion and Getting to Grips with recursion for […]
Apps Script & Java Script
Using Apps Script for xml json conversion
One of the great things about Apps Script is that you can use the content service to create your own apis, without the need for a server infrastructure. In From Xml to JSON and Convert […]
Apps Script & Java Script
use cJobject to create a unique list
Here’s something I do in many pieces of code on this site. Let’s say that you have a list of things, lets say an array or a collection and you want to create a list […]
Integrating Excel with Maps and Earth
Using Twitter in Earth and Maps
javaScript: Twitter API integration with google Earth and maps This relates to Data Driven Mapping applications and the Javascript HowTo section Twitter API Using the Twitter RESTful API, it is very straightforward to add relevant tweets to […]
Microsoft Office & VBA
Using cRest directly
What can you learn here? sing cRest Advanced capabilities Building new libraries Accessing the cRest class directly get it now Up till now we’ve been looking at the modules in the Rest to Excel library as the integration […]
Json
Populating sheets with Rest responses
What can you learn here? Using cDataSet Using cRest Using cJobject Putting the Rest response on the sheets get it now Now that we have the tools to generate existing or adhoc rest queries, let’s look at how the data […]
Apps Script & Java Script
cJobject and javascript equivalents
There is plenty of reference information cJobject on this site, starting with How to use cJobject, or just enter cJobject in the search box. Here’s a quick reminder of what you’ll need to understand to follow […]
Apps Script & Java Script
Using an intention to update to preserve atomicity in cache updates
Ephemeral Exchange is a cross platform cache with a REST API and some useful SDK clients for various platforms. In this example I’ll look at how to use intents to preserve cache atomicity, and the code […]
Apps Script & Java Script
Sheets, Maps, Slides, Docs , Maps and Streetview, effex collaboration demo
In the demo Sheets and Maps collaboration with cache – effex demo I showed a maps app that was being used across multiple platforms with the Ephemeral Exchange store as the data exchange mechanism. This […]
Apps Script & Java Script
Testing unpublished add-on
Installable triggers and unpublished add-ons As described in Setting up onChange trigger and When test add-ons doesn’t work you can’t install a trigger in test mode. The solution is to use your add-on as a library to a container […]
Apps Script & Java Script
Setting up onChange trigger
Installable change trigger Sheets has both an onEdit and an onChange trigger to detect changes in a sheet. We’ll be using the onChange trigger, which is an installable trigger. The important difference between an installable […]
Apps Script & Java Script
Reacting to push notifications
Receiving notifications Initializing the server shows how the process starts off by the Server writing something to Ephemeral exchange (see also Getting started with Ephemeral Exchange for push notification) This is provoked from initializing the client ns.init […]
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 […]
Apps Script & Java Script
Getting started with Ephemeral Exchange for push notification
What is Ephemeral Exchange efx is a cross platform cache, based on Firestore, that can be used to exchange data between unconnected platforms. It has push notification, which means that co-operating processes can be informed […]
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
Set initial streetview orientation towards a map marker
This is a snippet from the series described in Playing around with maps and streetview, which will use the service to find an appropriate panorama, given a location, and set the initial heading and pitch so […]
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
Playing around with maps and streetview
In Sheets and Maps collaboration with cache – effex demo, I showed how you might use a loosely coupled app to enrich data in various platforms which used the Ephemeral Exchange store to collaborate. It’s always fun to […]
Apps Script & Java Script
Merging data with a slides template
There are a few tutorials out there about how to use replaceAllText with a Slides template in order to generate a deck where placeholder values are replaced with values with a data set. The problem […]
Apps Script & Java Script
Ephemeral exchange JSON editor and viewer
The Ephemeral Exchange Console allows you to create, read and edit JSON data directly in the store. This makes it a great too for checking data for apps that need to read or write JSON data. As […]
Apps Script & Java Script
Mapping the demo
By the time that A gigantic pub crawl gets here, then various components will have suggested pubs inside an area and declared pub crawls. We can use the Maps API to work out the optimal route between […]
Apps Script & Java Script
Examining the data
Since all the data for A gigantic pub crawl is held in Efx cache, we can look at it from anywhere that has a client SDK or can make an HTTP request. Let’s start by using the […]
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 […]
Apps Script & Java Script
A gigantic pub crawl
I wanted to put together a gigantic demo to show (and test) the push notification capability of Ephemeral Exchange to orchestrate many things across many platforms. We’ll be linking up all these things, with the orchestration […]
Apps Script & Java Script
HTML service without caja
If you are reading this in 2015, you probably won’t have come across caja, since you’ll be using this, as was announced today var html = HtmlService.createHtmlOutputFromFile(‘foo’); html.setSandboxMode(HtmlService.SandboxMode.IFRAME); For those of us still in 2014, this […]
Apps Script & Java Script
Github service for Goa examples
This describes how to authenticate with github using Goa, as described in Oauth2 for Apps Script in a few lines of code (which you should read first for background). The library, cGoa, is available under this project […]
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 […]
Apps Script & Java Script
Response format from apps script project query
Format of Info.json Each project directory contains an info file that describes the scripts for this project. It is also used to decide whether the sources in the project need to be refreshed. To force […]
Apps Script & Java Script
Making sense of gwt rpc dependency service
Obsolete This post is now pretty much obsolete. As of April 2016, the dependency service seemed to disappear. Next attempt was via a hacked autocomplete service to discover dependencies, which worked until 2018. Now this […]
Apps Script & Java Script
Extracting the source from apps script project
In Getting your apps scripts to Github I introduced the idea of using Drive JSON API for apps script to get your source code out of the Apps Script environment as a first step to […]
Apps Script & Java Script
Gasgit – frictionless bulk maintenance of your Apps Script gitgub repos and integration with Clasp
GasGit For the past few years, I’ve been using gasGit (any many of you have too Getting your apps scripts to Github) to get all my public stuff to github. I have over 150 Apps Script repos […]
Apps Script & Java Script
Creating a Google Apps Script Library
mcpher Library All Google apps script library code is in the mcpher library. If you already know about google sctipt libraries you can get it as below, and read about it in this post. Project Key […]
Apps Script & Java Script
Let Google Apps Script do the work
For this site, I usually create a Google Apps Script version and a VBA version of the projects and libraries discussed. This is not so hard, since there are utility libraries in both languages, so it’s […]
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 […]
Apps Script & Java Script
Using the mcpher library in your code
This Google Apps Script library contains a significant amount of the code you will need for most samples on this site. You access access my full library here su_button url=”https://docs.google.com/spreadsheets/d/1DlKpVVYCrCPNfRbGsz6N_K3oPTgdC9gQIKi0aNb42uI/edit#gid=0″ target=”blank” style=”glass” center=”yes” icon=”icon: download” […]
Apps Script & Java Script
Store data in scriptdb
Create a module that stores some test data in a public scriptdb. In Hosting your public scriptdb you will have created a public scriptDB, Here’s how to store data in a public scriptdb. We are going to […]
Apps Script & Java Script
Hosting your public scriptdb
You will need a script to host the scriptdb that contains your public data. In the step by step guide, Delegation to Google Apps Script, you should have created a script, pubstuff and added the mcpher library. […]
Apps Script & Java Script
Enhancing the urbarama mashup
In Urbarama mashup we looked at how to mashup mutiple APIS and serve up the result. Now we are going to use that to further enhance and publish the results. The problem with the Urbarama api is that […]
Apps Script & Java Script
Delegation to Google Apps Script
Step by Step guide for Google Apps Script proxying In a number of articles, I’ve covered how to use GAS for jsonp, OAuth, as a lockbox and various other useful things. There are many threads to pull together in […]
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 […]
Apps Script & Java Script
Copy databases using OAuth2
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 […]
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 […]
Apps Script & Java Script
Summarize email thread recipients to a spreadsheet
If you followed Do something useful with GAS in 5 minutes, you’ll already know how to use a spreadsheet as a database. This time, here’s how to search your mail for some threads on a particular subject […]
Apps Script & Java Script
Fusion and crossfilter
If you followed Do something useful with GAS in 5 minutes, you’ll already know how to use a spreadsheet as a database. This time we are going to get data from Fusion Tables, play around with […]
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 […]
Apps Script & Java Script
Get analytics profiles in 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. Here’s how to use that along with Google Analytics to get all your profile […]
Apps Script & Java Script
A simple invoice app
If you followed Do something useful with GAS in 5 minutes, you’ll already know how to use a spreadsheet as a database. Now let’s use that in an app. Tis is a simple app that matches […]
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 […]
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, […]
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 […]
Apps Script & Java Script
Converting from scriptb to parse.com – multiple simultaneous threads
As the scriptDB deprecation date approaches, you may have decided which database to move to and are making plans for the switch over. The problem is though that if you have many thousands of records […]
Apps Script & Java Script
parse.com – noSQL database for GAS
Google Apps Script already has a noSQL database – scriptDB. I use it a lot, and have plenty of examples on this site. When I was developing parse.com api class for VBA , part of the motivation was […]
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
Implementing fisheye projection with D3 for google sheets add-on
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 […]
Apps Script & Java Script
Proof of concept Sheets navigation with D3 fisheye distortion and ephemeral exchange
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 […]
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 […]
Apps Script & Java Script
d3 concept browser
In Node focus on Blogger posts and Node focus explorer without excel I showed how you might browse sites by connected topic, rather than hierarchically. This used a D3.js force diagram as the navigation tool. I got to thinking […]
Apps Script & Java Script
Node focus explorer without excel
Up till now we’ve been considering a solution for Focus node explorer with d3.js that leverages massaging the data from Analyzing site content with GAS using the stuff we already had from d3.js. That solution also means that you can […]
Apps Script & Java Script
GAS and Sites
Using Google Apps Script with Google Sites This site was hosted on Google Sites but I decided not long ago to migrate it to WordPress. Although Sites has improved significantly since I started to use […]
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), […]
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
Centering text in arc textpath with D3
You’d think this would be easier than it turned out to be. I want to do this. In other, words to use a textPath to label up an arc, then to center that label. The obvious […]
Apps Script & Java Script
Zoomable Partition Charts
How to create a d3 zoomable partition chart straight from Excel. As always, acknowledgement to Mike Bostock on whose zoomable partition chart this is based. A partition chart is a way of showong heirarchical data. The zoomable part is […]
Apps Script & Java Script
Developer information for d3.js force diagrams
To develop Excel applications that generate web pages incorporating d3.js force diagrams from Excel data, first read this Excel tables to d3.js Force diagram. You will also need to be familiar with How to use cDataSet and How to […]
Apps Script & Java Script
Adding markers to d3.js force diagrams
How to add markers to links In Excel tables to d3.js Force diagram I showed how to create a basic force diagram. Since that time, I’ve added the capability to add markers (arrows) to the end of […]
Apps Script & Java Script
Creating d3.js Tree diagrams
Acknowledgement d3.js is from Mike Bostok’s site. If you use any of this material, please be sure to maintain the acknowledgement for his work. I also found out how to create tree diagrams on Pavan Podila’s blog. […]
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 […]
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 […]
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 […]
API Specific
Loading large JSON datasets into BigQuery with Apps Script
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 […]
Apps Script & Java Script
Using the People API with Apps Script
The BigQuiz app uses the People API to identify who is using it so that it can keep track of game and category scores. I could have used the Gplus api – which has an Apps Script advanced service, […]
APIS and techniques
BigQuery and the Execution API
The data for BigQuiz app is held in BigQuery. In Loading large JSON datasets into BigQuery with Apps Script I showed how you could maintain data in BigQuery with Apps Script, but now when I come to use it, […]
Apps Script & Java Script
Sharing code between client and server and using libraries with html service
Normally when you write code for apps that have a server and client component, you write the code for one or the other. The client javascript code goes in an html file (usually called something […]
Apps Script & Java Script
Firebase JSON REST access library for Apps Script
The BigQuiz app uses Firebase for keep track of the question, category and game scores of individual players. In Firebase custom authentication with goa I showed how to use Goa to manage authentication for Firebase. This firebase access library works with […]
Apps Script & Java Script
Firebase custom authentication with goa
The BigQuiz app uses Firebase for keep track of the question, category and game scores of individual players. In JSON web tokens I showed an example of how to authenticate and authorize when making a call to the Firebase JSON API. […]
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. […]
Apps Script & Java Script
Color Ramp useful scripts
Color manipulation library scripts First off for this project we are going to need to figure out how to manipulate colors in Google Apps Script. As per the approach in Roadmapper migration my objective is to make […]
Apps Script & Java Script
Gas hosted scripts
In Color scheme explorer the main color processing scripts are hosted in Google Apps Script. There’s particular no reason to do this, except that it means that I can ensure I use the same algorithms in JavaScript as I do in GAS. Building […]
Apps Script & Java Script
Color scheme explorer
In this section, we’ll build an application that pulls together many concepts already covered elsewhere on this site. You can run it standalone at xliberation.com, or below, embedded in this page The target application Pulling together Playing […]
Apps Script & Java Script
d3.js and parse mashup
This is an interesting d3.js and parse.com mashup of a number of topics covered on this site. The target is to build an app that Retrieves color scheme data from a parse.com database, much like […]
Apps Script & Java Script
Google Script Color Ramp
Project conversion We will use the Color ramp library project as the basis for this migration. The complete code can be found here Google Apps Script Color Ramp workbook Status of conversion General libraries You can read about these […]
Apps Script & Java Script
Step by step module updating
In How to update modules automatically in VBA I showed how you could get code from github into VBA and set up a kind of .require approach to which classes, modules and references are needed. Let’s go […]
Apps Script & Java Script
Migrating from cEzyOauth2
This describes how to migrate from cEzyOauth2 while using the Goa library as described in Oauth2 for Apps Script in a few lines of code (which you should read first for background). The library, cGoa, is available under this […]
Apps Script & Java Script
Trying to like OAuth2
Every time I think about what my next project will be, I almost always use whether or not I need to use Oauth2 as the deciding factor, because I know I’ll spend more time wrestling […]
adsense
VizMap javaScript
VizMap: the generated javaScript This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example Parameter WorkSheet is […]
Apps Script & Java Script
javaScript ‘howTo’
VizMap: Javascript howTos This relates to Data Driven Mapping applications Firstly – I am not an expert in javaScript, but in the accelerated learning process to discover what was necessary to create this application generator, there […]
adsense
Using Google Maps
javaScript: Flying around and asynchronicity in google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Google Maps Api You will find tons of content on Google Maps, most of which is described […]
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 […]
Apps Script & Java Script
Google Apps Script HTML service
One of the most powerful features of Apps Script is the HTML service. This allows you to create html/JavaScript applications which can be served up as webapps, run in sidebars or as add-ons and created great […]
Add-ons
Running things in parallel using HTML service
Apps Script is inherently synchronous – you cant do lots of things at once. Primarily to get round the execution limit, I did a science project using triggers for parallel processing but the triggers are fairly unpredictable […]
Libraries
EzyOauth2 – taking some pain out of Apps Script API authentication
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 […]
Apps Script & Java Script
Integrate VBA with Github
VBAGit After Getting your apps scripts to Github I thought I’d have a go at doing something similar for VBA. If you are reading this, you’ll know that it’s very difficult to manage shared code, […]
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 […]
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 […]
Apps Script & Java Script
Learn how to transition from VBA to Google Apps Script
Migrating from VBA to Google Apps Script, or using them simultaneously can be hard to get started with. Although this material has been largely superceded by Going Gas, there’s stll some useful stuff here. […]
Apps Script & Java Script
scriptDB API for JavaScript
We have CRUD natively to scriptDB from Google Apps Script, A VBA API for scriptDB gave us one for Excel – now here’s one for JavaScript. That means you can use the same noSQL database for all […]
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 […]
Apps Script Specific
Anonymous user registration with the Apps Script PropertiesService
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 […]
Apps Script & Java Script
Generating coupon codes with expiry dates
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 […]
Apps Script & Java Script
Using a service account
In Borrowing an access token from Apps Scripts I demonstrated the simplest way to get a token to use with a Google API, by borrowing one from Apps Script. However to access APIS that have no equivalent in […]
Apps Script & Java Script
Borrowing an access token from Apps Scripts
The simplest way to get a token to use with a Google API is to borrow the one from Apps Script – assuming that you can persuade Apps Script to enter an authorization dialog for […]
Sheets specific
Keeping track of columns for onEdit triggers using sheets metadata
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 […]
Sheets specific
Sheets Developer Meta Data – Apps Script advanced service
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 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 […]
Apps Script & Java Script
Sheets and Maps collaboration with cache – effex demo
It’s pretty easy to use data from Sheets to drive an App that enriches that data using the Maps API. But let’s say that you want to use this same app in multiple circumstances – […]
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 […]
Add-ons
Using promises with apps script
Using promises rather than callbacks is a much cleaner way of dealing with asynchronous activities. Promises were introduced in EcmaScript 6, and of course are not implemented in Server side Apps Script. Since everything is […]
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 […]
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 […]
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 […]
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 […]