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

Question

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

what is xlDown ? this is incomplete code. Also, sometimes it's better create something from scratch rather than convert, so what does this code do exactly? — vanowm
– vanowm, Commented Apr 10, 2022 at 1:10
It's rather straightforward to "port" code from one language to another, provided you either know the target language, or are willing to learn the target language as you go along. On the latter, all you would need is a "command reference" for Javascript. Mozilla has a good one. — mardubbles
– mardubbles, Commented Apr 10, 2022 at 1:18
Selection.End(xlDown).Select seems to be correct code to me. It should find the last non-empty cell in the selected range and select it. — Chris Strickland
– Chris Strickland, Commented Apr 10, 2022 at 1:40

Chris Strickland · Accepted Answer · 2022-04-10 01:41:09Z

2

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

answered Apr 10, 2022 at 1:41

Chris Strickland

3,5101 gold badge20 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mardubbles Over a year ago

Agree since its not a lot of code, should be straightforward to port (+1). But even if it were a lot of code, the porting will be painless if OP utilized a command reference from the source language to a command reference to the target language. The "google sheets object model" coming into play of course.

Chris Strickland Over a year ago

Yeah, I have real doubts that it will convert anything at all complex, but I just see a for loop and an if and a couple of selects and cell manipulations. It just might work for him. If not it will be easy enough to walk him through. Those SELECTs aren't necessary at all, if it gets tripped up there.

mardubbles Over a year ago

OP seems dead on arrival in any event (no feedback), but it was a good discussion

Mike Steelson · Accepted Answer · 2022-04-10 04:13:20Z

1

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

edited Apr 10, 2022 at 4:13

answered Apr 10, 2022 at 4:08

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related