Case statement won't match text from spreadsheet column in Google Apps script (script inside)

Question

I have a google form and the corresponding results spreadsheet. I have a google apps script on the sheet that is intended activate on form submit. It reads the entry, puts formats the filled in columns into an email, and sends that email out to a specified address. All that works as intended.

However, there is a case statement that is supposed to set the target e-mail address & subject line based on the text in one of the columns. I can't get it to read anything but the default case. The text in the column (the 'campus' column) seems to match the case statements? Anyone have advice? Thanks!

function SendGoogleForm(e) {

  if (MailApp.getRemainingDailyQuota() < 1) return;

  var s = SpreadsheetApp.getActiveSheet();
  var columns = s.getRange(1, 1, 1, s.getLastColumn()).getValues()[0];     
  var email = "";
  var subject = "";
  var message = "";

  switch (e.namedValues["Campus"]){
    case "Lower School":
      email = "xxxx@*****.org";
      subject = "LS Ticket Created";
      message = "Campus column is: " + e.namedValues["Campus"] + "\n";
      message += "Case is: Lower School"+"\n";
      break;
    case "Upper School":
      email = "yyyy@*****.org";
      subject = "US Ticket Created";
      message = "Campus column is: " + e.namedValues["Campus"] + "\n";
      message += "Case is: Upper School"+"\n";
      break;        
    case "S*******":
      email = "zzzz@*****.org";
      subject = "SC Ticket Created";
      message = "Campus column is: " + e.namedValues["Campus"] + "\n";
      message += "Case is: S*******"+"\n";
      break;
    default:
      email = "xxxx@*****.org";
      subject = "General Ticket Created";
      message = "Campus column is: " + e.namedValues["Campus"] + "\n";
      message += "Case is: Default"+"\n";
      break;
  }

  for (var keys in columns) {
        var key = columns[keys];
        if (e.namedValues[key] && (e.namedValues[key] !== "")) {
            message += key + ' :: ' + e.namedValues[key] + "\n\n";
        }
    }

    MailApp.sendEmail(email, subject, message);
}

It's definitely a problem with the case statement. The following code returns true `if (e.namedValues["Campus"] == "Lower School"){` (assuming "Lower School" is in the campus column, so they should be matching. I'm clearly not understanding something about how the case statement does matching. I can do nested if-else statements, but it bugs me that I don't understand 'case' properly. Thoughts? — Brian King, Sep 22 '15 at 19:57

score 0 · Answer 1 · answered Sep 22 '15 at 20:20

e.namedValues returns the values in an Array, rather than as simple strings. This allows it to support multiple values for a single field.

This means you need to reference the first item in the array in your switch statement, at index 0:

  switch (e.namedValues["Campus"][0]){
     ...
  }

Otherwise you are using Switch correctly.

Regarding your comment, I'm not sure why the IF would be working when the Switch isn't, it should also require you to access the Array element. It must be that the IF statement is implicitly converting the Array to a string for the sake of the comparison, while the switch statement doesn't have that behaviour.

See e.namedValues here, with the values shown in Array notation:

https://developers.google.com/apps-script/guides/triggers/events

Rolo · Answer 2 · 2017-02-07T16:11:05.200

The problem is that when you are reading the parameters from the URL (Also applies to "namedvalues") you need to parse to int or string or the appropriate type, beyond that the switch/case usage is correct.

Options:

 1. var input = 1; //int
 2. var input = "myvalue"; //string
 3. var input = parseInt(e.parameters.input); 
    //parsed int from querystring. http://.../?input=1
 4. var input = e.parameters.input.toString(); 
    //parsed string from querystring. http://.../?input=myvalue
 5. var input = e.parameters.input;  
    //Not parsed int or string from query string. 
    //http://.../?input=1 or http://.../?input=myvalue
    //This option will not work unless you parse to the appropriate type.

switch (input){
    case 1:
    //This works for option 1 and 3;
    break;
    case "myvalue":
    //This works for option 2 and 4;
    break;
    default:
    //Option 5 will fall here;
    break;
}

References:

URL Parameters

Case statement won't match text from spreadsheet column in Google Apps script (script inside)

2 Answers2