mardi 29 décembre 2020

Send Email with the Google Leave request form submitted by the user, using app script, google form, spreadsheet

I am working on developing a simple Leave management system where we use Google form, Google spreadsheet, and App scripts.

In Google form for leave request, we have the confirmation at the beginning like whether you are a Requester or Approver.

  1. Leave request First Confirmation Screenshot of the form
  2. Requester form
  3. Approver form

The goal is:

  • Whenever the requester submits the leave request form an email with the detail is sent to the approvers.
  • The approver receives the email from the requester along with the edit link.

With the edit link clicked by the approver, the approver is redirected to the same form and the approver has to select the Approver in the form and decide whether to approve or decline the request. When the response is filled and submitted, an email is sent to the requester by the approver regarding the response like, if her/his request is approved or decline.

Here is the code:

var EMAIL_TEMPLATE_DOC_URL = 'Template link of the doc for requester email';
var EMAIL_TEMPLATE_DOC_URL2 = 'Template link of the doc for approver email';
var EMAIL_SUBJECT = 'Leave Request';

function installTrigger() {
ScriptApp.newTrigger('onFormSubmit')
.forSpreadsheet(SpreadsheetApp.getActive())
.onFormSubmit()
.create();
}

function onFormSubmit(e) {
var responses = e.namedValues;
var status = '';

var email = 'Approver email Address';

if( e.range.columnStart != 3 || e.value == "Requester")
{
MailApp.sendEmail({
to: email,
subject: EMAIL_SUBJECT,
htmlBody: createEmailBody(responses)
});

status = 'sent';

var sheet = SpreadsheetApp.getActiveSheet();
var row = sheet.getActiveRange().getRow();
var column = 18;

sheet.getRange(row, column).setValue(status);

Logger.log('status=' + status + '; responses=' + JSON.stringify(responses));



function createEmailBody(responses) {


var docId = DocumentApp.openByUrl(EMAIL_TEMPLATE_DOC_URL).getId();
var emailBody = docToHtml(docId);

var form = FormApp.openById('edit form id');
var formResponses = form.getResponses();
for (var i = 0; i < formResponses.length; i++) {
var formResponse = formResponses[i];
// Logger.log(formResponse.getEditResponseUrl());
}

var timestamp = responses['Timestamp'][0].trim();
var name = responses['Name'][0].trim();
var leavetype = responses['Leave Type'][0].trim();
var starttime = responses['Start Time'][0].trim();
var endtime = responses['End Time'][0].trim();
var halfday = responses['Half day include'][0].trim();
var addinfo = responses['Additional Information'][0].trim();
var emailadd = responses['Email address'][0].trim();
var editurl = formResponse.getEditResponseUrl();

emailBody = emailBody.replace(//g, timestamp);
emailBody = emailBody.replace(//g, name);
emailBody = emailBody.replace(//g, leavetype);
emailBody = emailBody.replace(//g, starttime);
emailBody = emailBody.replace(//g, endtime);
emailBody = emailBody.replace(//g, halfday);
emailBody = emailBody.replace(//g,addinfo);
emailBody = emailBody.replace(//g,emailadd);
emailBody = emailBody.replace(//g, editurl);


return emailBody;
}
}

else(  e.value == "Approver")
{
var email2= 'requester email address';

MailApp.sendEmail({

to: email2,
subject: EMAIL_SUBJECT,
htmlBody: createEmailBody(responses)
});

status = 'send 2';

// Append the status on the spreadsheet to the responses' row.
var sheet = SpreadsheetApp.getActiveSheet();
var row = sheet.getActiveRange().getRow();
var column = 19;
//var column = e.values.length + 1;
sheet.getRange(row, column).setValue(status);

Logger.log('status=' + status + '; responses=' + JSON.stringify(responses));



function createEmailBody(responses) {


var docId = DocumentApp.openByUrl(EMAIL_TEMPLATE_DOC_URL2).getId();
var emailBody = docToHtml(docId);

var dec = responses['Approval Decision'][0].trim();
var exp= responses['Explanation'][0].trim();


emailBody = emailBody.replace(//g, dec);
emailBody = emailBody.replace(//g, exp);

return emailBody;
}
}
}




function docToHtml(docId) {

var url = 'https://docs.google.com/feeds/download/documents/export/Export?id=' +
docId + '&exportFormat=html';
var param = {
method: 'get',
headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
muteHttpExceptions: true,
};
return UrlFetchApp.fetch(url, param).getContentText();
}

Is there something wrong with my if-else condition? The problem am facing is that both the if and else condition runs at the same time and I get two emails send to the requester as well as the approver. I am new to app script and my knowledge is zero in terms of all these so I don't know what should I do with the code I have to make the system work properly.

Can anyone help me to filter the submitted request like approver and requester so that I can send an email to the actual person who needs to get it? Any tips on if-else condition?

Aucun commentaire:

Enregistrer un commentaire