Simple Automation With Form

Self Risk Assessment

Simple Automation With Form

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

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

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.

Silahkan click gambarnya atau hover menggunakan mouse.

No items found.
Google
Rocket Hart Exinity