0

I am working on a script for leave application via Google forms. I found this script online and have been trying to get it working. When a form is submitted, the script retrieves and populates the linked spreadsheet with the transaction ID, requester's email and places the request status as 'pending'. It then sends an email to the manager and includes links in the email to approve or deny the request.

I have it working up to here which is the onFormSubmit function. This is all working.

When I come to the doGet(e) function, I run into issues. It is supposed to retrieve the managers response and update the spreadsheet status from pending to either approve or deny. An email is then sent to the requester with the outcome.

When I click in the email to either approve or deny a request, I get a message "Authorization is required to perform that action."

When I try to run the doGet(request) function, I get the error: "TypeError: Cannot read property 'parameters' of undefined (line 42)".

var responseID = request.parameters.id;

Is it possible I need to reauthorize the script due to new services? But, this is difficult when I keep returning this error.

Any help or ideas would be greatly appreciated.

Here is the full script:

function onFormSubmit(e) {

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

 // #1 - Identify the requestor's email address and save it to the sheet.
 var email = Session.getEffectiveUser().getEmail();
 sheet.getRange(sheet.getLastRow(), 7).setValue(email);

 // #2 - Get the response ID and save it to the sheet.
 var responseID = e.response.getId();
 sheet.getRange(sheet.getLastRow(), 8).setValue(responseID);

 // #3 - Set the status of the request to 'Pending'.
 sheet.getRange(sheet.getLastRow(), 9).setValue('Pending');

 // Create variables for email
 var items = e.source.getItems();
 var manager_email = e.response.getResponseForItem(items[4]).getResponse();
 var subject = "A leave request has been submitted";

 // Include form data in the body of the email
 var message = "";
 for(var i in items)
 message += "\n\n" + items[i].getTitle() + ": " + 
 e.response.getResponseForItem(items[i]).getResponse();

 // #4 - Insert approve and deny links within the body of the email
 message += "\n\nClick link below to approve: https://script.google.com/macros/s/AKfycbxdS3cUP_oSjfphDaSNkn_5GuxxlPG6czVHH_wGJ2OxImqFASmZ/exec?id=" + responseID + "&status=Approved \n\n" + "Click link below to deny: https://script.google.com/macros/s/AKfycbxdS3cUP_oSjfphDaSNkn_5GuxxlPG6czVHH_wGJ2OxImqFASmZ/exec?id=" + responseID + "&status=Denied";

 // #5 - Email the manager the request information
 MailApp.sendEmail(manager_email, subject, message);

}

function doGet(request) {

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

  // Open the sheet which stores the responses
  var ssID = 'My Sheet 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();
  for (var i=1; i < data.length; i++) {
    if (data[i][7] == responseID){
      sheet.getRange(i+1,9,1,1).setValue(status);
      var date = Utilities.formatDate(data[i][3],"NZ", "EEE, MMM d, yyyy");
      var type = data[i][4];
      var email = data[i][6];
    }
  }

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

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

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

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

}
8
  • This is strange, because it seems like this should we working if you have requested with [the right parameter ](developers.google.com/apps-script/guides/web#request_parameters). Are you executing this from the editor? If you do remember that this is expected as you are no introducing the input variables of the doGet function. How have you deployed your webApp? Are you executing this as yourself or as the person doing the request? Commented Mar 16, 2020 at 9:45
  • Hi there, Thanks so much for your help. I was not aware I needed to deploy a web app. How foolish am I?? This is what happens when someone copies a script online without fully understanding how it works. I now have it working so it updates approval status, but have run into issues with onFormSubmit retrieving the requester's email. It is gathering my email and not the email of the active user. I have changed it from getEfctiveUser() to getActiveUser() but receive the same result. Does the webApp conflict with the trigger I have installed, as it was working OK before deploying the webApp? Commented Mar 17, 2020 at 3:31
  • It shouldn't really interfere with the trigger, remember that onFormSubmit() is a install-able trigger and needs to be installed. And that you can retrieve the user directly for the event object (e.user). Not sure what is your problem now. Commented Mar 17, 2020 at 8:53
  • It must be added that this script is running on a business domain. From what I'm reading online, getEffectiveUser() should return the email of the person filing the form as long as they are on the same domain. Please let me know if I am wrong or if you know of a work around. Not sure why getActiveUser() doesn't wok? I guess I could always retrieve the email via the user filling it in on the form. Commented Mar 17, 2020 at 9:04
  • The trigger you use was installed in your account so the effective user will be you. You probably should try to use the event object or try to user other methods. Like the webapp or changing the form to collect user. Commented Mar 17, 2020 at 10:03

1 Answer 1

1

If you are using the right parameters in the WebApp and you are good to go take a look at installable triggers.

The trigger you have used was installed in your account so the effective user will be you. You probably should try to use the event object or try other methods to retrieve the user. Like the webapp or changing the form to collect user information.

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

1 Comment

Thanks for this. I ended up going with the simplest option and changed the form to collect users email. Cheers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.