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

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.

CodePudding user response：

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.

CodePudding user response：

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.