CODEWITHSUNDEEP
javascriptexcelvba
Marlon Spinola
Marlon Spinola

Reputation: 1

How to transform excel VBA code into Javascript for Google Sheets?

everyone. I need to change a code that I created in VBA to javascript and put it into Google Sheets. Does anyone know how I can do this? The code is below

Sub Influencers_automacao()
  Sheets(1).Activate
  Range("A1").Select
  Selection.End(xlDown).Select
  k = Selection.Row
  For t = 2 To Sheets.Count
    j = 2
    For i = 2 To k
      If Sheets(1).Cells(i, 1) = Sheets(t).Name Then
        Sheets(t).Cells(j, 1) = Sheets(1).Cells(i, 2)
        Sheets(t).Cells(j, 2) = Sheets(1).Cells(i, 3)
        j = j + 1
      End If
    Next
  Next
End Sub

Upvotes: 0

Views: 2974

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15308

Try

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var output = []
  ss.getSheets().forEach(function (sh, i) {
    if (i > 0) output.push([
      sh.getName(), sh.getRange('A1').getValue(), sh.getRange('B1').getValue()
    ])
  })
  ss.getSheets()[0].getRange(2, 1, output.length, output[0].length).setValues(output)
}

change A1, B1 as necessary

Upvotes: 1

Chris Strickland
Chris Strickland

Reputation: 3490

I'm afraid the answer is that you have to learn javascript and the google sheets object model, or at least enough of it to figure out how to do the same things in js that you're doing in VBA.

However, you can try using the macro converter, and your existing VBA is pretty simple so it might work without alterations, but you might have to tweak it. Let me know if this works, or if you encounter errors with the converted code and we can try to work through them.

https://developers.google.com/apps-script/guides/macro-converter/convert-files

Upvotes: 2

Related Questions