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

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

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

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

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

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

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

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

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..

did […]

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

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

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

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

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

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

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

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

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

Apps Script & Java Script

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

From VBA to Google Apps Script

Add-ons versus add-ins

Both Apps and Office offer the capability of adding extra functionality by bringing up some client side JavaScript which can communicate back to main application. In the case of Apps  this would be the server […]

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

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

FAA airport status API: The Daily REST library entry for Excel and GAScript

Today’s API is  “FAA airport status”  – a dataSet query API, populating rows in a spreadsheet using the Airport IATA code as a key Here’s the Excel code (included in cDataSet.xlsm downloadable from the ramblings site)

[…]

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

  Then you can access employee.id etc […]

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