Why is not the doGet trigger function able to access parameters passed in the request?

Question

I am trying to write a leave request system and have copied code from other examples in order to get where I am.

The largest problem I am having is the doGet seems not to be able to read the parameters. If I plug the parameters into the code, it works fine, but when I try to pass the variables with https link it doesn't work. Any ideas?

I have published it as a web App and given anyone in my domain access to it.

Here is the https link: https://script.google.com/a/olemissalumni.com/macros/s/mywebappURL/exec?id=2_ABaOnue6Zr2ADAQPNUo-NdwmIgOL-J4Gk9H6ihNVkqZwLs2ubWh_0SaXYdllDpb_1r6AyCA&status=Approved

Here is the doGet trigger function.

function doGet(e) {

  //testing variables

  //  var responseID = '2_ABaOnue6Zr2ADAQPNUo-NdwmIgOL-J4Gk9H6ihNVkqZwLs2ubWh_0SaXYdllDpb_1r6AyCA';
  //  var status = 'Approved';


  // Retrieve the parameter information for response ID and status
  var responseID = e.parameters.id;
  var status = e.parameters.status;

  // Open the sheet which stores the responses
  var ssID = 'spreadsheet id' // Replace with spreadsheet ID
  var ssName = 'Form Responses' // Replace with sheet name
  var sheet = SpreadsheetApp.openById(ssID).getSheetByName(ssName);

  // Find the row where the ID in the URL equals the ID in the sheet and update the
  // status to the parameter value
  var data = sheet.getDataRange().getValues();
  Logger.log('Email' + data[1][1]);
  Logger.log('type' + data[1][9]);
  //console.log(data);
  for (var i = 0; i < data.length; i++) {
    if (data[i][11] == responseID) {
      sheet.getRange(i, 12, 1, 1).setValue(status);
      var date = Utilities.formatDate(data[i][0], "GMT+0200", "EEE, MMM d, yyyy");
      var type = data[i][10];
      var email = data[i][1]; //get the actual email address of the person who filled out the e
      sheet.getRange(i, 13, 1, 1).setValue(email);
    }
  }

  if (status == 'Approved') {
    var subject = 'Your e for time-off has been approved';
    var message = 'Your e for time-off (' + type + ') on ' + date + ' has been approved.';

  } else {
    var subject = 'Your e for time-off has been denied';
    var message = 'Your e for time-off (' + type + ') on ' + date + ' has been denied.';
  }

  // Send email to employee alerting them of the approval/denial of e
  MailApp.sendEmail(email, subject, message);

  // Show message to manager that the response has been updated.
  var result = "The e has been '" + status + "'.";
  return ContentService.createTextOutput(result);

}

Answer 1

Your WebApp accesses the parameters correctly, the error source is different

• While in general for retrieving strings opposed to arrays, you should use e.parameter instead of e.parameters, in your case Apps Script will convert the array to a string for you, so that the comparison of type if (['Approved'] == 'Approved') will return true
• However, in general be careful with comparing values to arrays and use e.parameter instead of e.parameters for accessing single values
• Now the real problem of your code is that MailApp.sendEmail(email, subject, message); is outside of the for loop
• Within the for loop and inner if statement you overwrite the value of email (and date and type) and send the email after exiting the loop to the last saved email
• This will lead to problems if

• data[i][11] == responseID statement is fulfilled more than once
• If there is no valid email address in data[i][1]
• If the if condition is never fulfilled
• In the latter case the variable email does not exist, but the script still tries to send an email and errors

Solution:

• Move the email sending funciton into the if statement
• Also, incorporate logs throughoout the code to make sure that your retrieve the correct data (in one line you define Logger.log('type' + data[1][9]); and later: var type = data[i][10];
• Make sure that you deploy your WebApp as a new version after incorporating the changes.

Sample:

function doGet(e) {
  
  //testing variables
  
  //  var responseID = '2_ABaOnue6Zr2ADAQPNUo-NdwmIgOL-J4Gk9H6ihNVkqZwLs2ubWh_0SaXYdllDpb_1r6AyCA';
  //  var status = 'Approved';
  
  
  // Retrieve the parameter information for response ID and status
  var responseID = e.parameter.id;
  var status = e.parameter.status;
  
  // Open the sheet which stores the responses
  var ssID = 'spreadsheet id' // Replace with spreadsheet ID
  var ssName = 'Form Responses' // Replace with sheet name
  var sheet = SpreadsheetApp.getActive().getActiveSheet()//.openById(ssID).getSheetByName(ssName);
  
  // Find the row where the ID in the URL equals the ID in the sheet and update the
  // status to the parameter value
  var data = sheet.getDataRange().getValues();
  Logger.log('Email' + data[1][1]);
  Logger.log('type' + data[1][9]);
  //console.log(data);
  for (var i = 0; i < data.length; i++) {
    if (data[i][11] == responseID) {
      sheet.getRange(i, 12, 1, 1).setValue(status);
      var date = Utilities.formatDate(data[i][0], "GMT+0200", "EEE, MMM d, yyyy");
      // 9 or 10???!!!
      var type = data[i][10];
      var email = data[i][1]; //get the actual email address of the person who filled out the e
      sheet.getRange(i, 13, 1, 1).setValue(email);      
      if (status == 'Approved') {
        var subject = 'Your e for time-off has been approved';
        var message = 'Your e for time-off (' + type + ') on ' + date + ' has been approved.';
        
      } else {
        var subject = 'Your e for time-off has been denied';
        var message = 'Your e for time-off (' + type + ') on ' + date + ' has been denied.';
      }
      
      // Send email to employee alerting them of the approval/denial of e
      Logger.log("email will be sent");
      MailApp.sendEmail(email, subject, message);
    }
  }
  
  // Show message to manager that the response has been updated.
  var result = "The e has been '" + status + "'.";
  return ContentService.createTextOutput(result);  
}