Google Apps Script getSheetByName returns null when using a variable as argument

Question

I have a script that is set to run whenever a trigger is sent, and it needs to sort through the data it receives to determine which sheet to write data to.

I used to hard-code it with the exact names and it worked fine, but now I'm trying to make it easier to scale. Variable value in the switch should be a number.

Below is a snippet of the code.

var myIds = [111, 222, 333, 444];
var myUsers = ['aaa', 'bbb', 'ccc', 'ddd'];

...

switch (param) {
  case 1:
    break;
  case 2:
    for (let i = 0; i < myIds.length; i++) {
      if (value == myIds[i]) {
        userName = myUsers[i];
        break;
      }
    }
    userName = 'ETC';
    break;
}

if (userName != 'ETC') {
  let inSheet = 'In '  + userName;
  let outSheet= 'Out ' + userName;
  if (checkIn == true)
    sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName(inSheet));
  else
    sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName(outSheet));
}
else
  sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName('ETC'));

Rather than going to their respective In / Out sheets, everything goes to the ETC sheet instead. Some other times, there would not be any change on the sheets at all. It would be greatly appreciated if someone could explain why this happens, in addition to the question.

Thanks in advance.

T.J. Crowder · Accepted Answer · 2021-12-02 09:54:43Z

3

The break; inside your for loop only breaks out of the for loop, not the case. So you break out of the for and end up on userName = "ETC";, which overwrites any value the loop may have set.

Instead, you can use indexOf or findIndex:

case 2:
    const index = myIds.indexOf(value); // Or `.findIndex(id => id == value)` if you need `==` not `===`
    userName = index === -1 ? "ETC" : myUsers[index];
    break;

If you really wanted the for loop, you could assign userName before the loop, so that the loop overwrites "ETC":

case 2:
    userName = "ETC";
    for (let i = 0; i < myIds.length; i++) {
        if (value == myIds[i]) {
            userName = myUsers[i];
            break;
        }
    }
    break;

Side note: I recommend avoiding parallel arrays like myIds and myUsers. It's really easy to end up updating one and not the other. Instead, consider an array of objects:

const users = [
    {id: 111, name: "aaa"},
    {id: 222, name: "bbb"},
    {id: 333, name: "ccc"},
    {id: 444, name: "ddd"},
];

Then the first example above would use find:

case 2:
    const user = users.find(({id}) => id == value);
    userName = user ? user.name : "ETC";
    break;

In a really modern environment with optional chaining and nullish coalescing, you could change userName = user ? user.name : "ETC"; to userName = user?.name ?? "ETC"; but there's nothing wrong with the conditional version.

Side note 2: You've used let within your for loop, so you seem to be using the more modern GAS that supports ES2015+ features. I suggest not using var in new code, always use let or const.

edited Dec 2, 2021 at 9:54

answered Dec 2, 2021 at 9:27

T.J. Crowder

1.1m201 gold badges2k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shironats Over a year ago

thanks for the great answer! I would like to expand on the original question though, where now after putting everything in an array of objects, when i try using getSheetByName("In " + users[i].name), it returns an error saying that it can't read the property 'name' of undefined. I have the users array as a global variable so that shouldn't be a problem.

T.J. Crowder Over a year ago

@Shironats - The problem there isn't users, it's users[i] -- or more specifically, that i is not a valid index into the users array. I can't tell you why not, because I have no idea what the value of i is there. But that line of code doesn't exist in the question or answer. You'd still use userName there, not users[i].name; it's just how you set userName that changes.

Shironats Over a year ago

Thanks for the clarification, I cleared up that problem. However, now that I'm using the find(({id}) => ... method, I'm not getting any new input even though logically things should go into the 'ETC' sheet. Still currently trying to figure out what went wrong.

Shironats · Accepted Answer · 2021-12-03 03:05:12Z

0

Apparently the problem was in another part of the code that I did not include in the question, where I re-initialized the userName variable every loop. This caused the userName variable to be undefined in some loops, in turn causing the getSheetByName function to return an error.

answered Dec 3, 2021 at 3:05

Shironats

1372 silver badges7 bronze badges

Collectives™ on Stack Overflow

Google Apps Script getSheetByName returns null when using a variable as argument

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related