// Configuration object to store all email addresses and other settings
const CONFIG = {
SHEET_NAME: "Form Responses 1",
STAFF_EMAILS: {
"xxx xxx xxx": "
xx...@gmail.com",
// Add other staff emails here...
},
SUPERVISOR_EMAILS: {
"xxxxx xx": "
xxxx...@gmail.com",
// Add other supervisor emails here...
},
WEB_APP_URL: "
https://script.google.com/macros/s/AKfycbxT-t9PthasxdMLDc2xJHI60axifXDjjJN5Gosrhr1IlggCBou0XuDfdh9JZrbfgpmPDQ/exec" // Web app URL
};
/**
* Sends a notification email to the supervisor with Approve and Reject buttons.
* @param {Object} e - The event object from spreadsheet edit.
*/
function sendNotificationEmail(e) {
const sheet = e.source.getActiveSheet();
const range = e.range;
const row = range.getRow();
const formData = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const staffName = formData[6]; // Assuming "NAMA PEMOHON" is in the 7th column
const staffEmail = CONFIG.STAFF_EMAILS[staffName]; // Get staff email from CONFIG
const tarikhFormatted = sheet.getRange(row, 2).getDisplayValue(); // "TARIKH" (2nd column)
const keluarTimeString = sheet.getRange(row, 5).getDisplayValue(); // "WAKTU MULA KELUAR PEJABAT" (5th column)
const kembaliTimeString = sheet.getRange(row, 6).getDisplayValue(); // "WAKTU KEMBALI KE PEJABAT" (6th column)
const sebab = formData[3]; // "SAYA MEMOHON KEBENARAN MENINGGALKAN PEJABAT UNTUK" (4th column)
// Email body for the staff when the form is submitted
const senderMessage = `Salam sejahtera ${staffName},\n\n` +
`Permohonan baru telah diterima dengan maklumat berikut:\n\n` +
`Tarikh: ${tarikhFormatted}\n` +
`Waktu mula keluar pejabat: ${keluarTimeString}\n` +
`Waktu kembali ke pejabat: ${kembaliTimeString}\n` +
`Sebab: ${sebab}\n\n` +
`Sila semak permohonan anda.\n\nTerima kasih.`;
// Send email to staff
if (staffEmail) {
MailApp.sendEmail({
to: staffEmail,
subject: "Permohonan Keluar Pejabat Anda",
body: senderMessage
});
}
// Optionally, send notification to the supervisor as well
const supervisorName = formData[2]; // "KEPADA" (supervisor name)
const supervisorEmail = CONFIG.SUPERVISOR_EMAILS[supervisorName];
if (supervisorEmail) {
const approveUrl = `${CONFIG.WEB_APP_URL}?action=approve&row=${row}`;
const rejectUrl = `${CONFIG.WEB_APP_URL}?action=reject&row=${row}`;
const supervisorMessage = `
<!DOCTYPE html>
<html>
<head>
<style>
/* ... (keep other styles unchanged) ... */
.button-container {
text-align: center;
margin-top: 40px;
margin-bottom: 40px;
}
.button {
font-size: 18px;
text-decoration: none;
color: white;
padding: 15px 30px;
border-radius: 8px;
font-weight: bold;
display: inline-block;
width: 150px;
}
.approve {
background-color: #4CAF50;
}
.reject {
background-color: #f44336;
}
</style>
</head>
<body>
<div class="container">
<div class="header">Salam sejahtera ${supervisorName},</div>
<div class="content">
<p>Satu permohonan baru telah dihantar oleh ${staffName}.</p>
<div class="info-item"><strong>Tarikh:</strong> ${tarikhFormatted}</div>
<div class="info-item"><strong>Waktu mula keluar pejabat:</strong> ${keluarTimeString}</div>
<div class="info-item"><strong>Waktu kembali ke pejabat:</strong> ${kembaliTimeString}</div>
<div class="info-item"><strong>Sebab:</strong> ${sebab}</div>
</div>
<p>Sila semak permohonan ini dan ambil tindakan dengan mengklik salah satu pautan di bawah:</p>
<div class="button-container">
<a href="${approveUrl}" class="button approve">LULUS</a> <a href="${rejectUrl}" class="button reject">TIDAK LULUS</a>
</div>
</div>
</body>
</html>
`;
MailApp.sendEmail({
to: supervisorEmail,
subject: "Notifikasi Permohonan Keluar Pejabat",
htmlBody: supervisorMessage
});
}
}
/**
* Ensures that the CATATAN column exists in the sheet.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - The sheet to check and update.
*/
function ensureCatatanColumnExists(sheet) {
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
if (headers.indexOf("CATATAN") === -1) {
sheet.insertColumnAfter(sheet.getLastColumn());
sheet.getRange(1, sheet.getLastColumn()).setValue("CATATAN");
}
}
/**
* Generates the HTML for the comment form.
* @param {number} row - The row number of the application.
* @return {string} The HTML string for the comment form.
*/
function generateCommentFormHtml(row) {
return `
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
font-family: Arial, sans-serif;
display: flex;
justify-content: center;
align-items: center;
height: 100vh;
margin: 0;
background-color: #f0f0f0;
}
.container {
background-color: white;
padding: 20px;
border-radius: 8px;
box-shadow: 0 2px 10px rgba(0,0,0,0.1);
}
textarea {
width: 100%;
height: 100px;
margin-bottom: 10px;
}
input[type="submit"] {
background-color: #4CAF50;
color: white;
padding: 10px 20px;
border: none;
border-radius: 4px;
cursor: pointer;
}
input[type="submit"]:hover {
background-color: #45a049;
}
</style>
</head>
<body>
<div class="container">
<h2>Masukkan Catatan Penolakan</h2>
<form id="commentForm" method="post" action="${ScriptApp.getService().getUrl()}">
<textarea name="catatan" required></textarea>
<input type="hidden" name="row" value="${row}">
<input type="hidden" name="action" value="reject">
<input type="submit" value="Hantar">
</form>
</div>
</body>
</html>
`;
}
/**
* Handles the approval/rejection process.
* This function should be set as the web app entry point.
*/
function doGet(e) {
const action = e.parameter.action;
const row = parseInt(e.parameter.row, 10);
const catatan = e.parameter.catatan ? decodeURIComponent(e.parameter.catatan) : '';
if (!action || !row) {
return ContentService.createTextOutput("Parameter permintaan tidak sah.");
}
if (action === 'reject' && !catatan) {
// If it's a rejection without a comment, show the comment form
return HtmlService.createHtmlOutput(generateCommentFormHtml(row))
.setTitle('Masukkan Catatan Penolakan');
} else {
// Process the action directly if it's an approval or a rejection with a comment
return processAction(action, row, catatan);
}
}
/**
* Handles form submissions (POST requests).
*/
function doPost(e) {
const action = e.parameter.action;
const row = parseInt(e.parameter.row, 10);
const catatan = e.parameter.catatan;
return processAction(action, row, catatan);
}
/**
* Processes the action (approve or reject) and updates the sheet.
*/
function processAction(action, row, catatan) {
try {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
ensureCatatanColumnExists(sheet); // Ensure CATATAN column exists
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const statusIndex = headers.indexOf("KELULUSAN");
const catatanIndex = headers.indexOf("CATATAN");
if (statusIndex === -1 || catatanIndex === -1) {
throw new Error("Lajur yang diperlukan tidak ditemui dalam hamparan.");
}
// Update status based on action
const newStatus = action === "approve" ? "LULUS" : "TIDAK LULUS";
sheet.getRange(row, statusIndex + 1).setValue(newStatus);
// Update CATATAN
sheet.getRange(row, catatanIndex + 1).setValue(catatan);
// Send status update email
sendStatusUpdateEmail(row);
// Return HTML with centered text
return HtmlService.createHtmlOutput(`
<div style="
font-size: 250%;
color: red;
font-weight: bold;
display: flex;
justify-content: center;
align-items: center;
height: 100vh;
background-color: white;
text-align: center;
">
Permohonan telah diambil tindakan
</div>
`);
} catch (error) {
Logger.log(`Ralat dalam processAction: ${error.message}`);
return ContentService.createTextOutput(`Satu ralat berlaku: ${error.message}`);
}
}
/**
* Sends status update emails to staff members about the approval/rejection decision.
* @param {number} row - The row number of the updated application
*/
function sendStatusUpdateEmail(row) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
const values = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const columnIndices = {
status: headers.indexOf("KELULUSAN"),
staffName: headers.indexOf("NAMA PEMOHON"),
catatan: headers.indexOf("CATATAN")
};
const status = values[columnIndices.status];
const staffName = values[columnIndices.staffName];
const catatan = values[columnIndices.catatan];
const staffEmail = CONFIG.STAFF_EMAILS[staffName];
if (status && staffEmail) {
let emailBody = `Salam sejahtera ${staffName},\n\n`;
if (status === "LULUS") {
emailBody += `Permohonan anda telah diluluskan.`;
} else if (status === "TIDAK LULUS") {
emailBody += `Permohonan anda tidak diluluskan.`;
}
if (catatan) {
emailBody += `\n\nCatatan: ${catatan}`;
}
emailBody += `\n\nSila hubungi pejabat jika anda mempunyai sebarang pertanyaan.\n\nTerima kasih.`;
MailApp.sendEmail({
to: staffEmail,
subject: "Kemaskini Status Permohonan Keluar Pejabat",
body: emailBody
});
}
}
this code run perfectly using computer browser but when using handphone to run it, the action clicking "TIDAK LULUS" button wont open the html. possible Cross-Origin Resource Sharing (CORS) problem? help