From Office 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 Office 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 Office 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 big […]

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

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

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

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

From Office to Google Apps Script

Apps Script versus VBA scope

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

From Office to Google Apps 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..

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

No Picture
From Office to Google Apps 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 […]

From Office 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 Office to Google Apps Script

Array formulas and large ranges

What can you learn here? array formulas VBA using VBA profiler using worksheet profiler Are Array formulas viable with large ranges   On the Excel Blackbelts forum, someone asked a very interesting question about how to analyze relationships […]

From Office 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 Office 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 Office 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 – […]