Need help converting my VBA code to Google Script

Question

This is regarding the following VBA:

Public Sub NewSheet()

   ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
   Worksheets(Worksheets.Count).Name = Format(Date, "m.d.yy")
   Worksheets(Worksheets.Count).Range("A1") = Date
   Worksheets(Worksheets.Count - 1).Visible = xlSheetHidden

End Sub

I have been using this VBA in excel to help duplicate sheets and hide sheets dynamically. But we have decided to use Google Sheets because it is more iOS friendly and it is easier to use across multiple users.

Now I am at a loss as to how to use (convert) this VBA code to Google Script. Any help would be appreciated.

_/ \ _

See https://stackoverflow.com/questions/56910886/google-sheets-script-to-duplicate-rename-spreadsheets — CherryDT, Oct 20 '21 at 21:41
Please start by reading https://developers.google.com/apps-script/guides/sheets then checkout [ask]. — Rubén, Oct 20 '21 at 23:04

score 3 · Accepted Answer · answered Oct 20 '21 at 23:46

Probably it can be done about this way:

function myFunction() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet(); // current spreadsheet
  var old_sheet = ss.getActiveSheet();                   // current (old) sheet
  var new_sheet = old_sheet.copyTo(ss);                  // make a copy

  new_sheet.setName(Utilities.formatDate(new Date(), 'GMT', 'MM.dd.yy')); // set the name
  new_sheet.getRange('A1').setValue(new Date());         // set the cell A1
  old_sheet.hideSheet();                                 // hide the old sheet
}

Need help converting my VBA code to Google Script

1 Answers1