Convert VBA to Java Google Script

-3

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,

edited Nov 18 '18 at 13:47

asked Nov 18 '18 at 13:34

Marwan Ayoub

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 , How to Ask and Minimal, Complete, and Verifiable example
– 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

2

Edit your question to include all the comments here.
– TheMaster
Nov 18 '18 at 14:17

|
show 1 more comment

-3

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,

edited Nov 18 '18 at 13:47

asked Nov 18 '18 at 13:34

Marwan Ayoub

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 , How to Ask and Minimal, Complete, and Verifiable example
– 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

2

Edit your question to include all the comments here.
– TheMaster
Nov 18 '18 at 14:17

|
show 1 more comment

-3

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,

edited Nov 18 '18 at 13:47

asked Nov 18 '18 at 13:34

Marwan Ayoub

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,

javascript vba google-apps-script

edited Nov 18 '18 at 13:47

asked Nov 18 '18 at 13:34

Marwan Ayoub

edited Nov 18 '18 at 13:47

asked Nov 18 '18 at 13:34

Marwan Ayoub

edited Nov 18 '18 at 13:47

asked Nov 18 '18 at 13:34

Marwan Ayoub

asked Nov 18 '18 at 13:34

Marwan Ayoub

asked Nov 18 '18 at 13:34

Marwan Ayoub

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 , How to Ask and Minimal, Complete, and Verifiable example
– 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

2

Edit your question to include all the comments here.
– TheMaster
Nov 18 '18 at 14:17

|
show 1 more comment

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 , How to Ask and Minimal, Complete, and Verifiable example
– 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

2

Edit your question to include all the comments here.
– TheMaster
Nov 18 '18 at 14:17

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 , How to Ask and Minimal, Complete, and Verifiable example
– 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

Edit your question to include all the comments here.
– TheMaster
Nov 18 '18 at 14:17

|
show 1 more comment

1 Answer
1

active

oldest

votes

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();

}

answered Dec 2 '18 at 23:02

Tedinoz

81521016

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53361440%2fconvert-vba-to-java-google-script%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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);

}

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();

}

answered Dec 2 '18 at 23:02

Tedinoz

81521016

add a comment |

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);

}

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();

}

answered Dec 2 '18 at 23:02

Tedinoz

81521016

add a comment |

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);

}

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();

}

answered Dec 2 '18 at 23:02

Tedinoz

81521016

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);

}

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();

}

answered Dec 2 '18 at 23:02

Tedinoz

81521016

answered Dec 2 '18 at 23:02

Tedinoz

81521016

answered Dec 2 '18 at 23:02

Tedinoz

81521016

answered Dec 2 '18 at 23:02

Tedinoz

81521016

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Cfrgtkky