passing arguments in google apps script

Question

I am trying to simplify my google apps script code and increase its readability. To this end, instead of calling setActiveSheet() function each time I want to activate a new sheet in my code, I decided to write a function that does the job and just call that function and include the name of the sheet as an argument like this:

function setSheet (sheetName) {
  var sheetName;
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName(sheetName));
  var sheet = spreadsheet.getActiveSheet();
};

Then for example if I want to activate the sheet named "Students", I would write the following simple code:

setSheet("Students");

The above code won't work. Any help?

This has to do with local and global variables and within google-apps-script this is not as easy as usual. See [this SO question](https://stackoverflow.com/questions/26352289/global-variable-defined-in-function-appears-not-defined) — Casper, Jul 02 '18 at 07:45
A better question is why do you need to use `setActiveSheet` so much that refactoring its usage even occurs to you. The only reason you need to change the active sheet is if you need to move the user's view, or the Sprreadsheet Service methods you want to use can only be called in the active sheet. For most, this is not the case. PS: **it doesn't work** is not a valid problem or issue description. — tehhowch, Jul 02 '18 at 10:17
@tehhowch I'm just playing with the code to better grasp the logic behind. — Omid, Jul 04 '18 at 05:03
@Casper actually it was easily done. The problem was redeclaring the same variable which reset the variable. — Omid, Jul 04 '18 at 05:05

score 3 · Answer 1 · answered Jul 02 '18 at 11:35

3

Why are you declaring a variable in your function that has the same name as the function parameter that you are passing? This will reset the value that was just passed. Try removing var sheetName; from your function.

answered Jul 02 '18 at 11:35

stepvda

79
7

1

A common term for that is "shadow" – tehhowch Jul 04 '18 at 12:22
1

Thank you. This was exactly the problem of my code. Now it works fine. – Omid Jul 11 '18 at 09:05

score 2 · Accepted Answer · answered Jul 02 '18 at 11:42

2

I did check up the above code and it works fine to me. I will attach the code snippet used.

function setSheet (sheetName) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName(sheetName));
  var sheet = spreadsheet.getActiveSheet();
}

function myfunction(){
setSheet("Students");
}

I also noted there is an extra semi-colon used after the function is terminated which is not required.

answered Jul 02 '18 at 11:42

lalala

71
3

Read about JavaScript ASI – tehhowch Jul 04 '18 at 12:23
2

Thanks it works fine. My problem was that I called the same passing variable again inside the function and this resulted in resetting the variable and caused a problem. – Omid Jul 11 '18 at 09:04

passing arguments in google apps script

2 Answers2