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);
}
doGetfunction. How have you deployed your webApp? Are you executing this as yourself or as the person doing the request?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.