From VBA to Google Apps Script
Looping in Google Apps Script
Let’s say you pass an array of values to be processed to a custom function, and you want to loop through them. In these examples, we’ll just go through each value and sum it if […]
apps script
From VBA to Google Apps Script
Apps Script custom functions
Just like Excel, you can extend Google Sheets by writing custom functions. These functions are written in Google Apps Script. In this section, I”ll show how to do some common VBA things you might want […]
From VBA to Google Apps Script
A simple Google Apps Script RestQuery
In GAS Rest Library migration, you can see a port of Rest to Excel library from VBA to Google Apps Script. This handles many complex queries and generally can execute them in a few lines of code using […]
From VBA to Google Apps Script
Apps Script timer collection
One thing I’ve found you need when developing for Google Apps Script is a timer. Everything is pretty slow ( see Optimizing access to sheet values) so you need to dig into exactly what is good […]
From VBA to Google Apps Script
Optimizing access to sheet values
Why so slow? Note – this article was written when Apps Script first came out. Since that time, the speed of Sheets access has increased significantly, but I leave this article here for interest. The Data […]
From VBA to Google Apps Script
VBA collection in javaScript
JavaScript does not really have the equivalent of a VBA collection. Since the use of collections is very significant in this project, the best approach is to emulate a collection object in javaScript so that […]
From VBA to Google Apps Script
Dealing with optional arguments
Arguments to procedures in VBA can be optional, and optionally they can have a default value. For example We can also test for the presence of a variant argument by using isMissing(). See Getting Started with […]
From VBA to Google Apps Script
Some Google Apps Scripts gotchas
Things to look out for As you work with javaScript (most of these gotchas will be about javaScript rather than apps script specifically), you’ll scratch your head about a few things. I know I did. […]
From VBA to Google Apps Script
Google Apps Conversion approach
You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download To minimize the amount of work in converting from VBA to Google Apps […]
From VBA to Google Apps Script
Apps Script versus VBA scope
In Understanding Scope for VBA I showed some examples of the visibility of functions and variables across modules and procedures inside a VBA project. If you are moving to Apps Script (or JavaScript) from VBA, you’ll need […]
scriptDB
Handler for cScriptDbCom requests
The VBA API generates REST API requests from your VBA function calls, and needs a Google Apps Script handler to service those requests by communicating with the required scriptDB instance. You can have multiple handlers, […]
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 […]
From VBA to Google Apps Script
Scraperwiki data to Google Docs
There is also a Google Apps script version of Getting data out of scraperWiki. It uses the mcpher library and the test data and examples can be found in this Google Document There is a rest library entry to […]
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 […]
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
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 & 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
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
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 […]
Databases
Graduate to a database
If you followed Do something useful with GAS in 5 minutes, you’ll already know how to use a spreadsheet as a database. You may want to move on to using a real database as a back […]
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 […]
Drive
Visualizing Apps Script dependencies
dependencyForce If you use a lot of libraries, sometimes things get a bit messy. You delete an old version to clean up and suddenly notice a few days later that things stop working because it’s […]
Google Authentication
Service account
This describes how to get an access token from the Goa library using a service account, as described in Oauth2 for Apps Script in a few lines of code (which you should read first for background). If […]
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 […]
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
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. […]
From VBA to Google Apps Script
Roadmapper migration
Project conversion We will use the Roadmap Generation project as a model for learning how to take an existing, complex project and convert it from VBA to Google Apps Script. This is implemented as a one liner […]
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
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 […]
From VBA to Google Apps Script
Automatic VBA to apps script skeleton
Using the modules from Automatic documentation, here’s how to create a skeleton Google Apps Script Module directly from VBA modules or classes. It will also create basic JSDOC markup for documenting your new GAS module. You’ll find all […]
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 […]
Drive
Oauth2 step by step – apps script authentication for the Drive JSON API
In Drive JSON API for apps script I posted about an apps script library to help with the complexities of using the Drive SDK instead of the Apps Script DriveApp service. It needs Oauth2. Recently, the advanced […]
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 […]
Drive
Drive JSON API for apps script
In Using Drive SDK I described why you might want to use the SDK JSON API rather than the built in Apps Script Drive Class. Before you can start with this you need to authorize your access with OAUTH2, […]
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. […]
scriptDB
Google Apps ScriptDB
NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. The versatile scriptDB scriptDB is a javaScript object database for Google Apps Script project. It is a very useful feature to […]
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 […]
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 […]
Add-ons
Chord Snip
Chord snip is an add-on for Google Sheets that creates a Chord diagram as you type, gives you the SVG code to embed it in a web site and the ability to embed it in […]
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 […]
APIS and techniques
Using the Google Sheets V4 API from VBA to integrate Sheets and Excel
The Google Sheets V4 API is a very nice piece of work that exposes almost all of the Sheets object model in a REST API. Here’s a VBA wrapper for that API. I’ll add to […]
Apps Script & Java Script
Using the Sheets API to make apps script respect filters
In Using the Google Sheets V4 API from VBA to integrate Sheets and Excel I showed a VBA API that could be used to talk to the Sheets API, to create some integration between Excel and Office. […]
Apps Script & Java Script
Integrating Google Apps with other Platforms
As Google APIS continue to improve there are more ways to integrate them. The pages on this site reflect the technologies available at the time, so I thought I should bring together the pages that […]
Colors
Coloring maps and other shapes
In Using Google Maps I covered how to create various web based interactive maps. Of course many would like to use Excel to create maps, and the topic of chorpleth maps, has been covered many times by […]
Apps Script & Java Script
Color Matching in GAS
I covered the mathematics of color matching in the VBA implementation in Find nearest color match. This is the Google Apps Script implementation. I’ll be using the CIEDE2000 algorithm to measure the distance between colors. One thing I […]
Apps Script & Java Script
JSON and VBA
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available from O’Reilly or Amazon VBA does not naturally support JSON, but to be able to use this API (and […]