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

Vinyl

This won't be simple and I believe there is no such "converters", I suggest you read thoroughly the documentation and try a few tutorials. – Philis

Apps Script uses JavaScript. You'll need to learn JavaScript. – Fulminant

If you've been programming in VBA, you won't find Google Apps Script that difficult. Take a look at developers.google.com/apps-script/reference/spreadsheet/sheet and you'll see most of the functions you need there. When you go to 'Tools|Script Editor...' and choose a 'Spreadsheet' script you'll get some template code to start with. Reading the 'Spreadsheet', 'Sheet' and 'Range' bits of Google's documentation should get you the rest of the way. – Nutwood

Related... "My company is switching from Office to Google Docs" - that could be a bad idea. It is usually ill advised to place sensitive company documents in someone else's control whose primary security model is that of the web. The web security model sucks. When they can't deal with a threat, they simply remove it from the model. – Recuperator

I'm voting to close this as this is a code translation request, which is offtopic: meta.stackoverflow.com/a/265834 – Monolith

I'm voting to close this as this is a code translation request, which is offtopic: meta.stackoverflow.com/a/265834 – Blocked

Answer 1

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.

Legendre

Answer 2

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

Magdalenamagdalene

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.

Fulminant

Answer 4

Dim fname As String
Dim FPath As String

FPath = "E:\salaryslips\march22\"
fname = Sheets("Slip").Range("B6").Text

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False

Hereinbefore

Convert this code in to app script – Hereinbefore

Please edit instead of commenting to update. – Beshore

Answer 5

This is currently impossible in google app script as there is neither worksheet selection change event nor selection change event in spreadsheet services. List of Google Spreadsheet events are Here.

But we have time driven triggers in GAS, through this you will be able to run a function at some particular time. In your case lets say morning 12AM. You have to write a function which will run at 12AM to go through all the sheets and then format Cells(Target.Row, 1) and Cells(Target.Row, 2) on to the format which you like.

Definitely you have to restructure your current spreadsheet... Welcome to Google App Scripting.

Profant