Presensi hanya isi NIS, google form dan kirim WhatsApp ke ortu

Contoh form

Contoh inputan form di google sheet

Sheet data siswa

Sheet data presensi

Script di apppscript

Ganti Device id, nama sheet data siswa dan data presensi disesuaikan

function presensi(event) {
var APIKey = "xxx";
var NIS = event.namedValues["NIS"][0];
var jenisPresensi = event.namedValues["Presensi"][0];

var searchstudent = searchbynis(NIS);
var Nama = searchstudent[1];
//var NIS2 = searchstudent[2];
var Kelas = searchstudent[3];
var Phone = searchstudent[5];
var Ortu = searchstudent[6];

var sheetName = "DATA PRESENSI"; 
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
var currentTime = new Date();
var tanggalPresensi = Utilities.formatDate(currentTime, Session.getScriptTimeZone(), "dd-MM-yyyy");
var jamPresensi = Utilities.formatDate(currentTime, Session.getScriptTimeZone(), "HH:mm");
var batasWaktu = new Date();
batasWaktu.setHours(10, 0, 0); // Jam 7:00 pagi
var status = currentTime > batasWaktu ? "Terlambat" : "Tepat Waktu";
sheet.appendRow([Nama, NIS, Kelas, tanggalPresensi, jamPresensi, status, jenisPresensi]);

var url = "https://app.whacenter.com/api/send"; 
var pesan   = 
`Yth. Bapak / Ibu orang tua / wali murid ${Ortu}, 

Nama : *${Nama}* 
Kelas : ${Kelas}
telah dicatat hadir pada presensi ${jenisPresensi}
Tanggal : ${tanggalPresensi} 
Jam : ${jamPresensi} WIB
Status : ${status}

_*Terima kasih*_
` ;
const payload = {
   message : pesan,
   number : Phone,
   device_id : APIKey
}

var option = {
    "method": "post",
    "headers" : {
    "content-type" : "application/json"
       },
        "payload" : JSON.stringify(payload)
        };

    var response = UrlFetchApp.fetch(url, option);
       Logger.log(response.getContentText());
        Logger.log(Phone);
        }           
           
function searchbynis(nis){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA SISWA")
  var data = sheet.getDataRange().getValues()
  var filterData = data.filter(function(keyword){
    if(keyword[2] == nis){
      return keyword
    }
  })
  
  if(filterData.length>0){
  return filterData[0]
  }
 
 }