Any way to use VBA on Google Sheets?

Question

Anyway to convert excel VBA to Google Appscript so that I can use it on Google spreadsheet? I need following code to convert to google script

    Sub ReCalculate()

    Dim i As Integer, j As Integer, NoOfRows As Integer

' Get total number of rows

        With Sheet1
            NoOfRows = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With

' add all the duplicated values and replace the duplicates with "DELETE" to be deleted later.

    For i = 2 To NoOfRows
        For j = i + 1 To NoOfRows
            If Cells(i, 1).Value = Cells(j, 1).Value And Cells(j, 1).Value <> "DELETE" Then
                Cells(i, 2).Value = Cells(i, 2).Value + Cells(j, 2).Value
                Cells(j, 1).Value = "DELETE"
                Cells(j, 2).Value = "0"
            End If
        Next j
    Next i

' Get total number of rows

        With Sheet1
            NoOfRows = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With

' Delete all rows with word "DELETE"

    For i = 2 To NoOfRows + 1
        If Cells(i, 1).Value = "DELETE" Then
            Rows(i).EntireRow.Delete
            i = i - 1
        End If
    Next i

    End Sub

This should be asked in the Web Applications Stack Exchange. http://webapps.stackexchange.com/ With a tag, Google-Spreadsheets — bp_, Nov 05 '14 at 16:17
@bp_ - there are some 7,700+ [tag:google-apps-script] questions here; SO is the right place. That doesn't mean it's a good question for the forum, of course. — Mogsdad, Nov 05 '14 at 16:44
Thanks @Mogsdad. I didn't find that tag in my search. Me being a rookie. I read _[this info about the tag](http://stackoverflow.com/tags/google-spreadsheet/info)_ and maybe jumped the gun. — bp_, Nov 05 '14 at 17:02
@bp_ - No problem, common mistake - and the fact that there are hundreds of Google Spreadsheet questions on 3 different Stack Exchange sites doesn't help! — Mogsdad, Nov 05 '14 at 19:38

score 0 · Answer 1 · answered Nov 07 '14 at 18:03

The most important consideration if you want to do this is that Google Apps Scripts utilizes JavaScript, whereas Excel utilizes VBA. So you are going to end up with the following differences:

Object Orientation. This is a gross over-simplification, but JavaScript is object-oriented.
Syntax. VBA and JavaScript allow you to do many similar things w.r.t. the related spreadsheet applications, but the syntax is going to be different.
Libraries. If you've been coding in VBA for awhile, your "dictionary" for go-to terms is going to be different than the "dictionary" you'll need to build basically the same functionality in Google Apps Scripts.

So here is what I would do in your case:

Learn JavaScript (or at least check out some of the basics)
Check out Google Apps Scripts: https://developers.google.com/apps-script/
Check out Google Apps Scripts - Spreadsheets Reference: https://developers.google.com/apps-script/reference/spreadsheet/

More specifically, see if you can find the specific code snippets you need to reference a specific Google Spreadsheet sheet and to delete rows.

Let me know if this helps.

score 0 · Answer 2 · answered Jun 01 '16 at 10:56

You needn't rewrite all code. You can re-use the old code to access Google Spreadsheets. Look for opensource GSpread.NET. It library allows read and write Google Spreadsheets by using Microsoft Excel API.

Use CreateObject(GSpreadCOM.Application) instead of CreateObject(Excel.Application).

GSpread.NET examples: http://scand.com/products/gspread/tutorial.html

Any way to use VBA on Google Sheets?

2 Answers2