From Excel VBA to Google App Script : Part 1

As a long time (unfortunately read "old") user of Office products, I've done a lot of work in VBA to make the products more functional. I've been using Google Sheets sporadically as well, but never got around to using App Script until now. In the process of getting familiar with it, I found the information was widely spread and not very concise. Below are my observations for other would-be converters of VBA to App Script.

Input parameters are a bit different from Excel VBA. You don’t get ranges but realized arrays of values. This is actually how VBA treats the range declaration but Google Sheets has ranges and arrays which throws the nomenclature just a bit. In the end, it’s passed the same, just watch for the term difference between the systems. 

For instance, in VBA, the function:

Function myFx(findStr As String, r As Range)

Dim X As Long, found As Long

found = 0

For X = 1 To r.Count

   If r(X).Value = findStr Then

      found = found + 1

   End If

Result = found

End

Is effectively identical (except for language differences and 1-based vs 0-based arrays) in App Script:

/**

 * Counts the number of times findStr is found in the array r.

 *

 * @param {string} findStr The string to find.

 * @param {array} r The array to search.

 * @return The number of times findStr was found in the array r.

 * @customfunction

 */

function myFx(findStr, r) {

  var found = 0;

  for (let x = 0; x < r.length; x++) {

    if (r[x] == findStr) found += 1;

  }

  return found;

}

JavaScript of course offers a lot of different ways to write that loop, but as a direct replacement, it’s not terribly far from where things started. Also note that there's nothing that defines the system as a one-dimensional array. You may be better off with a more flexible structure such as:

function myFx(findStr, r) {
  var found = 0;
  r.forEach( function(row) {
     function(col) {
        if (col == findStr) found += 1;
     }
  }
  return found;
}

The only time you care about the difference between range and value is if your App Script is pulling ranges directly from some Sheet. If you do this, make sure you convert the range to a value array. I’ve seen lots of people doing this in their examples but don’t understand why you’d write a one-off, statically linked App Script vs making it dynamic. Maybe I just haven’t gotten to that need yet.

Getting the function to show help / auto-complete:

This is done via a JsDoc compliant header block. From Google's example for a custom function called DOUBLE, the block looks like:

/**

 * Multiplies the input value by 2.

 *

 * @param {number} input The value to multiply.

 * @return The input multiplied by 2.

 * @customfunction

 */

function DOUBLE(input) {

  return input * 2;

}

A couple of clarifications here:

1. The format is finicky.

1. You need the /** header. 

2. You need the @cutomfunction at the end (no, not YOUR custom function name, but literally @customfunction). 

3. You need * on each line

4. You need the */ end

2. The params are defined specifically. Google’s example only shows one input but you can do complex inputs such as:

* @param {string} in1 Input string 1 that does something

* @param {array} in2 Input array 1 to do something

* @param {array} in3 Input array 3 to do something else

Hopefully this helps someone.