How to convert VBA to Google Apps Script?

Question

My company is switching from Microsoft Office to Google Docs, I have this (several) code I want to use in Google Spreadsheets but I have no idea on how to do that.

Consider this sample of code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E:J")) Is Nothing Then
        Cells(Target.Row, 1) = MonthName(Month(Date))
        Cells(Target.Row, 2) = Format(Now, "mm/dd/yyyy")
    End If
End Sub

Answer 1

Use GSpread.NET. to work with Google Spreadsheets by using Microsoft Excel API. You needn't rewrite all code, just replace CreateObject(Excel.Application) to CreateObject(GSpreadCOM.Application).

examples

Answer 2

In 2020, Google released Macro Converter add-on, which automatically converts VBA to Apps Script.

Note:

• Only Enterprise users can acess to this add-on.
• Some APIs cannot be converted automatically (e.g. operations on file system). You have to rewrite them by hand.

Answer 3

The function is structured like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Statements here
End Sub

That would probably convert to something like:

function Worksheet_Change(ByVal) {
  Statements Here;
};

Instead of End Sub the function in JavaScript ends with a curly brace }.

Instead of End If, the If, then statement also ends with a curly brace }.

if (condition) {code here;}

That's the syntax for a JavaScript If statement. There is no Then key word in JavaScript.