Convert VBA to Java Google Script

Question

Have the below VBA and i need to insert it into a Google Sheet,

Can somebody help with the conversion?

Sub Activate_Sheet()
    Sheets(Sheets("Main").Range("A1").Value).Activate
End Sub

Thanks,

You may wish to add Google Apps Script tag and show what research you have conducted. We are here to help with problems you are having with code you have written. There are help resources to give guidance on posting. [Tour] , [ask] and [mcve] — QHarr, Nov 18 '18 at 13:41
I think the gist of it will be something like: `var spread = SpreadsheetApp.getActiveSpreadsheet();`, then `var sheetName = spread.getSheetByName('Main').getRange('A1').getValue();`, then `spread.setActiveSheet(sheetName);`. Untested though. — chillin, Nov 18 '18 at 14:04
/** @OnlyCurrentDoc */ function UntitledMacro() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('H12').activate(); }; SpreadsheetApp.getActiveSpreadsheet() var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Main').getRange('A1').getValue(); SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); When i try to run it it says reference error A1 is not defined The purpose of this script is when i put a sheet number in A1 and i press the button assigned to this script it will take me directly to the requested sheet. — Marwan Ayoub, Nov 18 '18 at 14:05
The file i have contains 300 sheets, it is like a filing system What i need is to be able to insert the number of a sheet and just press a button and get redirected automatically to it — Marwan Ayoub, Nov 18 '18 at 14:11
So if i put 204 in A1 and i press the assigned button the file should take me directly to the sheet with the name 2014 — Marwan Ayoub, Nov 18 '18 at 14:12

score 1 · Answer 1 · answered Dec 02 '18 at 23:02

This script gets the value in cell A1 in sheet()[0], and then moves to that sheet number.

function so_53361440() {
  // set up spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    // set sheet 0 as the active sheet
  var sheet = ss.getSheets()[0];

    // get the value in cell A1
  var cell = sheet.getRange("A1").getValue();
  // Logger.log("value of cell = "+cell);// DEBUG

    // Convert number to string
  var sheetname = ""+ cell;
  // Logger.log("sheetname = "+sheetname);// DEBUG

    // set sheet by name and move to new sheet
    var mysheet = ss.getSheetByName(sheetname);
    ss.setActiveSheet(mysheet);
}

Variation on a theme
With 300 sheets, going back to sheet()[0] will get frustrating. So this small variation is designed to create a custom menu that will request the sheet number in an inputbox. The rest of the code is the same

function so_53361440_01() {
    // setup ui
  var ui = SpreadsheetApp.getUi(); 

  var result = ui.prompt(
      'What sheet do you want?',
      'Please enter a number:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    //ui.alert('Sheet is ' + text + '.');
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
    ui.alert('Operation Cancelled.');
  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.
    ui.alert('Input Box closed - no action taken.');
  }

    // set up spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

    // assign the UI value to a variable
  var cell = text;
  // Logger.log("value of cell = "+cell);//DEBUG

    // convert the variable to a string
  var sheetname = ""+ cell;
  // Logger.log("sheetname = "+sheetname);// DEBUG

    // set the sheetname to the variable and goto that sheet
    var mysheet = ss.getSheetByName(sheetname);
    ss.setActiveSheet(mysheet);

}

function onOpen() {
 SpreadsheetApp.getUi() 
      .createMenu('Change Sheet')
      .addItem('Pick a sheet', 'so_53361440_01')
      .addToUi();
}

Convert VBA to Java Google Script

1 Answers1