Skip to content

Instantly share code, notes, and snippets.

@haziqfiqri
Created November 28, 2023 20:05
Show Gist options
  • Save haziqfiqri/ef885c0f5bd58b0c44023e9af6174da6 to your computer and use it in GitHub Desktop.
Save haziqfiqri/ef885c0f5bd58b0c44023e9af6174da6 to your computer and use it in GitHub Desktop.
Google AppScript RSVP
function getDate() {
return new Date(new Date().toLocaleString('en', {timeZone: 'Asia/Kuala_Lumpur'}))
}
function formatTelephone(telephone) {
if (telephone.startsWith("+6")) {
return telephone.substring(1);
} else if (telephone.startsWith("0")) {
return "6" + telephone;
} else if (telephone.startsWith("5")) { // +65 singapore
return "6" + telephone;
}
return telephone;
}
function findRowByTelephone(sheet, telephone) {
var telephoneColumn = 2;
var lastRow = sheet.getLastRow();
for (var i = 2; i <= lastRow; i++) {
var phoneValue = sheet.getRange(i, telephoneColumn).getValue();
if (phoneValue == telephone) { // loose equality because we're comparing type number and string
return i;
}
}
return null;
}
function updateSheet(sheet, data) {
try {
var formattedTelephone = formatTelephone(data.telephone);
var foundRow = findRowByTelephone(sheet, formattedTelephone);
var timeNow = getDate(); // for timezone consistency
if (foundRow !== null) {
sheet.getRange(foundRow, 1, 1, 7).setValues([
[data.fullname, formattedTelephone, data.attendance, data.paxAdult, data.paxChild, sheet.getRange(foundRow, 6).getValue(), timeNow]
]);
return "updated";
}
sheet.appendRow([
data.fullname, formattedTelephone, data.attendance, data.paxAdult, data.paxChild, timeNow, timeNow
]);
return "created";
} catch (error) {
return "failed";
}
}
function doPost(e) {
var result = '';
try {
result = updateSheet(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(), JSON.parse(e.postData.contents));
} catch (error) {
result = "critical";
}
return ContentService.createTextOutput(
JSON.stringify({ "result": result })
).setMimeType(ContentService.MimeType.JSON);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment