Use a variable instead of the actual sheetname

[Nigel Hunt]

I have various bits of script in a function that refer to a sheet by it's name.

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

  ss.activate();

& 

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

  ss.activate();

  var rangeToCopy = ss.getRange(StartRow, 1, ss.getMaxRows(), 1);

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

  ss.activate();

  rangeToCopy.copyTo(ss.getRange(TargetRow, 1));

It is the sheetname Math that I'd to be set by a variable. I can then set up a loop to scroll through the sheets with different subjects. (Responses stays the same)

Is this possible?

[cbmserv...@gmail.com]

Hi Nigel,

 

If you use the following:

 

Var ss = SpreadsheetApp.getActiveSpreadsheet();

Var sheets = ss.getSheets();

 

This will return to you all the sheets in the spreadsheet as an array. So you can use that in the loop you mention to go through one sheet at a time. So sheets[0] would be the first, then sheets[1] second, and so on.

 

This would be better than using sheetname as a variable.

 

Hope this helps.

--
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/0849487f-3d8a-4fc8-8629-d4aca4b5a0c9n%40googlegroups.com.

[Nigel Hunt]

Thanks cbm.

I've seen code that tells me the sheetnames, but that doesn't help me rewrite the bits of script I showed as an example, where I need to actually use the sheetname, rather than step through them.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/006701d84541%24ace37c10%2406aa7430%24%40gmail.com.

[Scott Bennett]

You could create an array of the sheet names and loop through that. 

var ss = SpreadsheetApp.getActiveSpreadsheet();

var namesArr=[‘math’ , ’english’ ,’science’];

for(var i =0;i<namesArr.length;i++){

    var sheet = ss.getSheetbyName(namesArr[i])

//code to do things in the sheet. 

}

Scott Bennett

Sent from my iPhone 

On Mar 31, 2022, at 4:24 PM, Nigel Hunt <nigelha...@gmail.com> wrote:



To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHy69YpUiyo-8kmr2fGxMvW6%3D8hMEmOUHuLWmjvf3mk%3DPwxiYA%40mail.gmail.com.

[Nigel Hunt]

Thanks Scott,

That's another way to loop through the sheets, but it doesn't solve the issue of referring to the sheet directly in the script for each sheet.

I need a way to rewrite this bit of code

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

  ss.activate();

  var rangeToCopy = ss.getRange(StartRow, 1, ss.getMaxRows(), 1);

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

  ss.activate();

  rangeToCopy.copyTo(ss.getRange(TargetRow, 1));

where Math is referred to by the variable that is the sheet name.

Maybe it just isn't possible!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/67E8C432-7CC2-4159-84D5-112D06F10385%40bbchs.org.

[Martin Molloy]

var sheetName = 'Math'

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

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHy69YoccVjZG_nOAvtUkq9j_-WW_2F5LDc0T2%3DA-1qe3mKKng%40mail.gmail.com.

[cbmserv...@gmail.com]

You can derive the sheetnames from the sheets objects themselves. This would be a better practice then hardcoding the software with the sheetnames.

 Here is an example:

 

function myFunction() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheets = ss.getSheets();

  var sheetName;

  for (var i =0;i<sheets.length;i++)

  {

    sheetName = sheets[i].getSheetName();

    // here do what you want with that sheet

    // next loop will get sheetname of next sheet in spreadsheet.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHy69YpUiyo-8kmr2fGxMvW6%3D8hMEmOUHuLWmjvf3mk%3DPwxiYA%40mail.gmail.com.

[Nigel Hunt]

Thank you George; worked perfectly

So easy when you know how!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/006501d8457a%243b7946c0%24b26bd440%24%40gmail.com.

[CBMServices Web]

You are welcome.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHy69Yoyp7sq2EV%3DzO2zsGbVepo_K9_65X38vviOBdNv3%2B0v%3Dg%40mail.gmail.com.

[Nigel Hunt]

Sorry, I think it should have been Martin that I thanked.

This works fine when hard coded.

I now have a new sheet where the subjects are listed in a column.

A simple loop to scroll through those & show me the values works fine.

So I tried incorporating my main script into the loop.

I've tried using both '' & ' at the start of each cell but neither seems to overcome the problem of  the null.

I'm guessing that means the sheet doesn't exist?

Any more help you can glean from this please?

function Loopy2() {

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

  ss.activate();

  var LastWorkingRow = ss.getDataRange().getNumRows();

  for (var subjectRow = 1; subjectRow <= LastWorkingRow; subjectRow = subjectRow + 1) {

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

  ss.activate();

  var subject = SpreadsheetApp.getActiveSheet().getRange(subjectRow, 1).getValue();

  SpreadsheetApp.getUi().alert(subject);

  //First loop correctly shows as 'Math' when cell starts ''

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

  Logger.log(subject);

  ss.activate();//This fails here whether using ' or ''

I've made a copy of the worksheet, which I think I can share with you if you agree by sending an email to nigelh...@outlook.com.

I promise I won't abuse that info outside the scope of this particular issue.

[NPC Tech]

Hopefully you've already solved this issue, but after reading through this thread I found a solution so i thought I would add to this. Using an arrow function fixed the null issue for me, try this.

Change this line:

'''var subject = SpreadsheetApp.getActiveSheet().getRange(subjectRow, 1).getValue();'''

to this:

'''const subject = () => SpreadsheetApp.getActiveSheet().getRange(subjectRow, 1).getValue();'''

You will also need to add the quotation marks to the value because thats the format it expects.

'''var modSubject = "\"" + subject + "\""'''

I'm not the greatest coder so It can probably be cleaned up more.