Setup The Form
To simplify and since it is free, a Google Form is being used and a small script is created.
function myFunction(){
var emailTemp = HtmlService.createTemplateFromFile("Email");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Olah Data");
var lr = getlastrow(sheet,"A2:M");
var data = sheet.getRange(2,1,lr, sheet.getLastColumn()).getValues();
for (var i in data){
var col = data[i];
}
data.forEach(function(col){
emailTemp.nama = col[1];
emailTemp.from = col[2];
emailTemp.status = col[4];
emailTemp.score = col[3];
emailTemp.ket = col[5];
});
var htmlMessage = emailTemp.evaluate().getContent();
MailApp.sendEmail ("info@exinity.co", "Self Assesment Report", "Test", {name :"Admin", htmlBody: htmlMessage});
}
function run(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Form Responses 1");
var lr = getlastrow(sheet,"A2:M");
var data = sheet.getRange(2,1,lr, sheet.getLastColumn()).getValues();
for (var i in data){
var col = data[i];
}
if(col[1]>5){myFunction();}
}
function getlastrow(sheet,rangestring){
var rng = sheet.getRange(rangestring).getValues();
var lrIndex;
for (var i = rng.length-1;i>=0;i--){
lrIndex = i;
if(!rng[i].every(function(c){return c == "";})){
break;
}
}
return lrIndex +1;
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<p>Dear Safety Department </p>
<p>Berikut Merupakan Hasil Self Asessment Covid19 <b><?= nama ?></b> From <b><?= from ?></b> </p>
<ul>
<li>Status :<?= status ?></li>
<li>Score anda :<?= score ?></li>
<li>Keterangan <?= ket ?></li>
</ul>
<p>Best Regards</p>
</body>
</html>
The Script
With Apps Script, using a new Trigger OnFormSubmit, simple automation can be tested. The script will work automatically whenever a new form is received.
Create Html Email Body
To have a better view when the email notification is received, a little html code is utilized.
Integration
This is a typical sample of small integration when requiring data collection and some email notifications. With a small adjustment, the script can be utilized for some other purposes since the Sheet is published as internal domain template. We are planning to share more about this typical integration, but we are going to do it as part of our Google Form Indonesia babagi.
