How to activate a sheet using a variable....

[Nigel Hunt]

....instead of it's actual name. My function involves lines like   var ss = SpreadsheetApp.getActive().getSheetByName('Responses');

  ss.activate();

I found some code & amended it to show what was happening.

function SheetNames() {

var ss = SpreadsheetApp.getActive();

var allsheets = ss.getSheets();

SpreadsheetApp.getUi().alert(allsheets);

//shows sheet sheet sheet sheet

for (var s in allsheets){

var sheet=allsheets[s] //Use Subject instead of sheet here?

// & exit loop if sheet = Reponses

//your  code here ...

  var ss = SpreadsheetApp.getActive().getSheetByName(s);

  ss.activate();//doesn't recognise s as a sheet

SpreadsheetApp.getUi().alert(s);

//shows 0 1 2 3 4, but which is which?

}//end of  sheets loop.

}// end of function

The loop shows me the numbers 0 1 2 & 3, but which is which?

How can I activate each sheet in turn, using this variable s

Thanks

Nigel

[Clark Lind]

Hello Nigel,
I was able to get this to work. Depending on what you want, you can either list all sheet names one by one, or actually switch sheets while showing their names:

function SheetNames() {

var ss = SpreadsheetApp.getActive();

var allsheets = ss.getSheets();

for (var s in allsheets){

    var sheet = allsheets[s];

    sheet.activate();  //use this if you just want to show all the sheet names one by one without switching sheets

  //  sheet.activate().showSheet(); //use this if you want to move from sheet to sheet & comment out the line above this one

    SpreadsheetApp.getUi().alert(sheet.getName());

    }//end of  sheets loop.

}// end of function

[Nigel Hunt]

The looping through sheets is perhaps a distraction. 

I thought it showed how to access a sheet by a variable.

I actually want to do....

var subject = Math //perhaps as a string?

I plan to change the subject by looking down a column of cells via a loop.

The object of that is to avoid having an almost identical chunk of code for each of the 10 subjects.

If I want to make a small tweak I've then got to do it 10 times

  var ss = SpreadsheetApp.getActive().getSheetByName('Math');

  ss.activate();

but replace 'Math' with subject & not have it think that subject is a sheet name (which doesn't exist)

I tried

var ss = SpreadsheetApp.getActive().getSheetByName([subject]);

ss.activate();

but it cannot read property of null.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/097c307b-cd2c-4110-9d56-1eccdcde65bdn%40googlegroups.com.

[Nigel Hunt]

That revised loop code you sent me works a treat though!

I just have to work out how to use it to change the sheets when I'm doing copy & paste from a mixed name sheet to the various subject ones.

[Clark Lind]

I think I understand now..  

If you have the subject (which is also the sheet name), then yes:
var subject = "Math";

var ss = SpreadsheetApp.getActive().getSheetByName(subject);

ss.activate();

should work to activate the sheet called "Math".

If you had a function, you could pass in the subject like:

function activateSheet(subject) {

var ss = SpreadsheetApp.getActive().getSheetByName(subject);

ss.activate().showSheet();   //showSheet is optional.. it is there for testing.

}

//and activate it from somewhere else:

function sheetActivator() {

  activateSheet("Sheet7");

}

[Nigel Hunt]

"Math" doesn't work any better than 'Math' when used as a variable in a loop.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/626c246c-4e33-46c6-8e0d-2c8f140b11ffn%40googlegroups.com.

[Bennett, Scott]

You don't need to have quotes around them.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHy69YpNzi2a1jogWt0UgVYs_nq6y67RN08SyJUGTDUTiRq9Eg%40mail.gmail.com.

--

Scott Bennett

Data and Assessment Coordinator/Math Teacher

Bradley-Bourbonnais Community High School

P 815.937.3707 ext. 6010