No Picture
From VBA to Google Apps Script

Google Apps Script Roadmapper code

Where to get the document This page concerns the From VBA to Google Apps Script project.  You can find the code and test data, under the roadmap section in the VBA to Google Apps Script Roadmapper project download. You […]

No Picture
From VBA to Google Apps Script

Finished Roadmapper Script

I pretty much started here. Since the objective was to migrate from a VBA project to a Google Apps script version of the same, my approach was to write the skeleton, and emulate the underlying […]

No Picture
From VBA to Google Apps Script

Color from ScriptDB

  In Playing around with GAS color and Playing around with colors in VBA we looked at how various color schemes could be stored and retrieved by Looking up color table. The trouble is that this table can get pretty […]

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

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

Generate an auto incrementing key with scriptDB

NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. Sometimes you need to create a unique identification for each object. ScriptDB automatically assigns a unique objectId for each object, so […]

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

No Picture
From VBA to Google Apps Script

How to make charts in Google Apps Script

The Charts Service Google Apps Script offers a charts service. Using this you can build a dataTable as input to a chart, as well as the chart itself. Elsewhere on this site I covered how to […]

No Picture
From VBA to Google Apps Script

What to use in place of Excel Shapes

Note that this article uses the Apps Script UI service, which has since been deprecated. I leave it here for interest. The equivalent of inserting a shape in Google Docs is to insert a drawing, […]

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

No Picture
Docs

VBA debug object

There is a debugger for google apps script. Here’s how to use it Compared to the VBA debugger it sucks, so you probably need to be able to log and assert. javaScript does not have […]

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

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

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

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

From VBA to Google Apps Script

Learning javaScript

Google apps script is javaScript, with the addition of a set of classes which allow access to Google apps capabilities such as spreadsheet data structures and so on. This means that to be able write […]

From VBA to Google Apps Script

Trying out your new scriptDB environment

If you’ve followed Creating your own scriptDB environment we are ready to start accessing it from VBA. Registering authentication There are 2 things you need to do, both of which are one-off things. You may have done […]

No Picture
From VBA to Google Apps Script

The scriptDB dispatcher

Why a dispatcher Because you may want to have multiple handlers, protected in different ways, as described in Handler for cScriptDbCom requests you need to centralize ‘air traffic control’ for the one or more scriptDB instances you […]

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

No Picture
From VBA to Google Apps Script

cJobject Class in Google Apps Script

The Rest to Excel library is a set of classes and modules that simplify the populating of Excel sheets with data from RestFul APIs. Google Apps Script of course already has plenty of much better ways to […]

From VBA to Google Apps Script

How to populate Excel from jSon

As covered in jSon there are many ways to get jSon data into excel. You may be tempted to try to use Excel JSON conversion or get data from google docs. These techniques are for very specific use cases – […]

From VBA to Google Apps Script

Storing objects as values

Storing other objects in cJobject If cJobject was just about getting stuff in and out of JSON, then we’d stop here, but you’ll find that I use it in pretty much all the VBA examples […]

From VBA to Google Apps Script

Navigation

Iteration As per the examples in cJobject deep dive the most common navigation of the cJobject is by iteration through the children, or by direct access as covered in How to access children. However there are a few […]

No Picture
From VBA to Google Apps Script

Google apps script Rest Library migration

The excel- rest library is a way to easily populate Excel worksheets with data returned from Rest APIS. Here I will cover the migration from Excel to Google Apps Script. You can get the shared restLibrary […]

No Picture
From VBA to Google Apps Script

Tracking activity with ScriptDB

NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. I leave this article here for interest.In Using scriptDB, I covered some usages of scriptDB,along with a method of siloing data […]

From VBA to Google Apps Script

Using scriptDB

scriptDB is a javaScript object database for Google Apps Script project. Here are a few key facts Every project can have its own scriptDB There are no Tables (this is a noSQL database). Instead it is […]

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

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

No Picture
From VBA to Google Apps Script

Data manipulation Classes in Google Apps Script

These Data Manipulation Classes have been migrated from VBA so that the Google Docs spreadsheet data can be abstracted  from its physical location in the same way as most of the projects on this site abstract data from Excel. […]

No Picture
From VBA to Google Apps Script

Apps for Office

Apps for Office (the JavaScript API for office), is a way of using JavaScript along with Office. This section is all about Microsoft APIs that you might need for working with this.  Office Add-ins – first […]

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

No Picture
From VBA to Google Apps Script

Add-ons versus add-ins

The fundamental difference between them though, is that although superficially they seem to do the same thing, operations performed on the target workbook from Office is limited to what the API can do. […]

No Picture
From VBA to Google Apps Script

Create a treeview from json

What can you learn here? Populate a treeview Use a cjobject Serialize anything to a treeview How to turn a cJobject into a treeview get it now Since the cJobject is just a tree type structure, it […]

No Picture
From VBA to Google Apps Script

Playing around with GAS color

Using a color table In Looking up color table, we covered how to use a color table to reference to make palettes using the functions described in Playing around with colors in VBA. But that was in Excel – […]

No Picture
Execution

Execution API and Office to Apps migration

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 What is the Execution API ? This is a brand new API that enables the running […]

No Picture
Daily REST library

Urbarama API mashup – The daily rest library API

Today’s entry is pretty cool. I came across the urbarama site, which describes itself as “a collaborative world atlas of architecture and civil infrastructure”. What’s more it has an API, which will return details on […]

No Picture
From VBA to Google Apps Script

Page Rank REST API from Excel and Google Apps Script

I haven’t posted anything on new entries to the rest-excel-gas library for a while. As I mentioned in this post the library itself is available as a rest query. There have been quite a number of new APIs added […]

No Picture
From VBA to Google Apps Script

Rest Library for Google Apps Script

In previous posts I covered a couple of conversions of Excel VBA projects to Google Apps Script, with particular emphasis on maintaining compatibility between the solutions to allow for dual maintenance. Previous conversions have been a GAS […]

No Picture
From VBA to Google Apps Script

How to create javaScript like objects in VBA

In javaScript, everything is an object. Classes don’t really exist  because they don’t have to. To create an class like structure, you just do something like this var employee = {}; employee[‘id’] = 1; employee[‘grade’] […]

No Picture
From VBA to Google Apps Script

Migrating to Google Docs

Ive been using Google Docs as well as Excel for a while now, and certainly Excel is more comfortable a user experience. However it’s clear that Google Docs offer a great opportunity to offer services […]