Google Calendar Script

/**

- @fileoverview Google Apps Script for managing appointments from Google Calendar in a Spreadsheet.
- This script provides functions to synchronize calendar events with a Google Sheet,
- manage settings, and transfer appointments at scheduled times.
*/

// --- UI Functions ---

/**

- Adds a custom menu to the Google Sheet UI when the spreadsheet is opened.
*/
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu("📅 Appointment Tools")
.addItem("Open Settings", "showSidebar")
.addItem("Refresh Appointments", "logCalendarAppointments") // Added a direct refresh option
.addToUi();
}

/**

- Displays the custom sidebar for booking settings.
*/
function showSidebar() {
const html = HtmlService.createHtmlOutputFromFile("Sidebar")
.setTitle("Appointment Settings")
.setWidth(300); // Set a reasonable width for the sidebar
SpreadsheetApp.getUi().showSidebar(html);
}

// --- Settings Management Functions ---

/**

- Saves settings (source sheet name and time buffer) from the sidebar form.
- @param {object} form - An object containing form data (e.g., { sourceSheet: "MySheet", buffer: "180" }).
- @returns {string} A confirmation message.
*/
function saveSettings(form) {
const props = PropertiesService.getDocumentProperties();
props.setProperty("SOURCE_SHEET", form.sourceSheet.trim());
props.setProperty("TIME_BUFFER", form.buffer.trim());
console.log("Settings saved:", form); // Log for debugging
return "✅ Settings saved!";
}

/**

- Retrieves saved settings.
- @returns {object} An object containing the sourceSheet and buffer settings.
*/
function getSettings() {
const props = PropertiesService.getDocumentProperties();
return {
sourceSheet: props.getProperty("SOURCE_SHEET") || "Appointments", // Changed default sheet name
buffer: props.getProperty("TIME_BUFFER") || "180", // Default to 3 hours
};
}

/**

- Saves script-level properties like Calendar ID and Target Sheet ID.
- This function should ideally be called once, perhaps from an admin UI or manually.
- @param {string} calendarId - The ID of the Google Calendar to sync from.
- @param {string} targetSheetId - The ID of the Google Sheet to transfer appointments to.
- @param {string} targetSheetName - The name of the sheet within the target spreadsheet.
*/
function saveScriptSettings(calendarId, targetSheetId, targetSheetName) {
PropertiesService.getScriptProperties().setProperties({
calendarId: calendarId,
targetSheetId: targetSheetId,
targetSheetName: targetSheetName || "Transferred Appointments", // Default name for clarity
});
console.log("Script settings saved.");
}

/**

- Retrieves script-level properties.
- @returns {object} An object containing calendarId, targetSheetId, and targetSheetName.
*/
function getScriptSettings() {
const props = PropertiesService.getScriptProperties();
return {
calendarId: props.getProperty("calendarId"),
targetSheetId: props.getProperty("targetSheetId"),
targetSheetName: props.getProperty("targetSheetName") || "Transferred Appointments",
};
}

// --- Appointment Logging and Syncing Functions ---

/**

- Synchronizes Google Calendar appointments with the source spreadsheet.
- It adds new appointments and marks deleted ones.
*/
function logCalendarAppointments() {
const scriptSettings = getScriptSettings();
const calendarId = scriptSettings.calendarId;
if (!calendarId) {
SpreadsheetApp.getUi().alert("❌ Error: Calendar ID is not set. Please configure it in script settings.");
return;
}

const calendar = CalendarApp.getCalendarById(calendarId);
if (!calendar) {
SpreadsheetApp.getUi().alert(`❌ Error: Could not find calendar with ID: ${calendarId}`);
return;
}

const docProps = getSettings();
const sourceSheetName = docProps.sourceSheet;
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName(sourceSheetName);

if (!sourceSheet) {
SpreadsheetApp.getUi().alert(`❌ Error: Source sheet "${sourceSheetName}" not found. Please check your settings.`);
return;
}

// Get the headers for custom fields (D-G) from the sheet.
const sheetCustomHeaders = sourceSheet.getRange(1, 4, 1, 4).getValues()[0]; // D-G headers

// Define ALL the field names that should be extracted from the calendar event description.
// This array will be passed to extractFields.
const descriptionFieldsToExtract = ["Phone", ...sheetCustomHeaders]; // Include "Phone" explicitly

const now = new Date();
const future = new Date(now.getTime() + 1000 * 60 * 60 * 24 * 60); // Look 60 days into the future
const events = calendar.getEvents(now, future);

const existingAppointments = {};
const sheetData = sourceSheet.getDataRange().getValues();

// Map existing sheet data for quick lookup and update
for (let i = 1; i < sheetData.length; i++) { // Start from second row (skip headers)
const row = sheetData[i];
const name = row[0]; // Column A
const appointmentTime = new Date(row[2]); // Column C
const status = (row[7] || "").toLowerCase(); // Column H
// Use a robust key for comparison (name + ISO string of date)
if (name && appointmentTime instanceof Date && !isNaN(appointmentTime.getTime())) { // Use getTime() for robust check
existingAppointments[`${name}_${appointmentTime.toISOString()}`] = {
rowIndex: i + 1, // 1-based index
status: status,
data: row // Store the full row data
};
}
}

const newRows = [];
const updatedRows = []; // Stores { rowIndex, data } for rows that need update
const matchedKeys = new Set(); // Keep track of events found in calendar

events.forEach(event => {
const title = event.getTitle();
const name = extractNameFromTitle(title);
const start = event.getStartTime();
const description = event.getDescription();

```
// Pass the comprehensive list of fields to extract to the function
const allFields = extractFields(description, descriptionFieldsToExtract);
const phone = formatMalaysiaPhone(allFields["Phone"] || "");

const key = `${name}_${start.toISOString()}`;
matchedKeys.add(key);

// This is the desired new data for a row, always with 'CONFIRMED' status initially
const newEventData = [
  name, // A: Name
  phone, // B: Phone
  start, // C: Appointment
  // Ensure the order of custom fields D-G matches sheetCustomHeaders
  ...sheetCustomHeaders.map(h => allFields[h] || ""), // D-G: Custom Fields
  "CONFIRMED" // H: Status (Initial status for newly synced appointments)
];

if (existingAppointments[key]) {
  const existing = existingAppointments[key];
  const existingStatus = existing.status;
  let dataChanged = false;

  // Compare relevant parts of the row (A-G) to detect data changes
  const currentDataInSheetRelevant = existing.data.slice(0, 7); // Columns A-G
  const newDataFromCalendarRelevant = newEventData.slice(0, 7); // Columns A-G

  for (let j = 0; j < currentDataInSheetRelevant.length; j++) {
    const existingVal = currentDataInSheetRelevant[j] instanceof Date ? currentDataInSheetRelevant[j].toISOString() : currentDataInSheetRelevant[j];
    const newVal = newDataFromCalendarRelevant[j] instanceof Date ? newDataFromCalendarRelevant[j].toISOString() : newDataFromCalendarRelevant[j];
    if (existingVal !== newVal) {
      dataChanged = true;
      break;
    }
  }

  if (existingStatus === "reminded" || existingStatus === "sent") {
    // If status is already processed ('reminded' or 'sent'), DO NOT change it back to 'CONFIRMED'.
    // Only update other data (A-G) if it has changed.
    if (dataChanged) {
      // Construct the row with new data for A-G but preserve existing status (H)
      const rowToUpdate = [...newDataFromCalendarRelevant, existing.data[7]];
      updatedRows.push({ rowIndex: existing.rowIndex, data: rowToUpdate });
    }
  } else {
    // Status is not yet processed (e.g., 'confirmed', or 'deleted' / blank/any other).
    // Update the row. If data changed OR status wasn't already 'confirmed', push newEventData.
    // This covers cases where an event reappeared in calendar after being marked 'reminded' (due to removal).
    // It also ensures 'confirmed' is set if it was some other intermediate status.
    if (dataChanged || existingStatus !== "confirmed") { // Also update if the status itself needs to be set to CONFIRMED
        updatedRows.push({ rowIndex: existing.rowIndex, data: newEventData }); // Uses newEventData which has "CONFIRMED"
    }
  }
} else {
  // New appointment, add with 'CONFIRMED' status
  newRows.push(newEventData);
}

```

});

// Batch append new rows
if (newRows.length > 0) {
sourceSheet.getRange(sourceSheet.getLastRow() + 1, 1, newRows.length, newRows[0].length).setValues(newRows);
console.log(`Added ${newRows.length} new appointments.`);
}

// Batch update existing rows
updatedRows.forEach(update => {
sourceSheet.getRange(update.rowIndex, 1, 1, update.data.length).setValues([update.data]);
});
if (updatedRows.length > 0) {
console.log(`Updated ${updatedRows.length} existing appointments.`);
}

// Mark appointments in sheet as 'REMINDED' if no longer in calendar AND not already processed
const rowsToMarkReminded = [];
for (const key in existingAppointments) {
const existing = existingAppointments[key];
// Mark as REMINDED ONLY if it was CONFIRMED and is no longer in calendar.
// DO NOT change status if it's already 'reminded' or 'sent' (transferred)
if (!matchedKeys.has(key) && existing.status === "confirmed") { // Only change from CONFIRMED
rowsToMarkReminded.push(existing.rowIndex);
}
}

// Mark rows as "REMINDED" instead of physically deleting them for data integrity
if (rowsToMarkReminded.length > 0) {
rowsToMarkReminded.sort((a, b) => a - b);
const rangeListForReminded = sourceSheet.getRangeList(rowsToMarkReminded.map(r => `H${r}`));
rangeListForReminded.getRanges().forEach(range => range.setValue("REMINDED"));
console.log(`Marked ${rowsToMarkReminded.length} appointments as REMINDED (due to calendar absence).`);
}

SpreadsheetApp.getUi().alert("✅ Appointments refreshed successfully!");
}

// --- Data Extraction and Formatting Utility Functions ---

/**

- Extracts custom fields from a Google Calendar event description.
- It assumes a "Key\nValue" format within the description.
- @param {string} description - The HTML description of the calendar event.
- @param {string[]} headersToFind - An array of expected header names (e.g., ["Phone", "Address", "Purpose"]).
- @returns {object} An object with extracted field names as keys and their values.
*/
function extractFields(description, headersToFind) { // Changed parameter name for clarity
const result = {};
// Convert HTML <br> to newlines and strip all other HTML tags
const plainText = description.replace(/<br\s*\/?>/gi, '\n').replace(/<[^>]*>/g, '').trim();
const lines = plainText.split('\n').map(line => line.trim()).filter(line => line.length > 0);

for (let i = 0; i < lines.length; i++) {
// Check if the current line exactly matches one of the headers we are looking for (case-insensitive)
const matchedHeader = headersToFind.find(h => lines[i].toLowerCase() === h.toLowerCase());
if (matchedHeader && lines[i + 1]) { // Ensure there's a next line for the value
result[matchedHeader] = lines[i + 1].trim();
i++; // Skip the next line since it's the value for the current header
}
}
return result;
}

/**

- Formats a phone number to Malaysia's international format (e.g., "60123456789").
- @param {string} phone - The raw phone number string.
- @returns {string} The formatted phone number.
*/
function formatMalaysiaPhone(phone) {
phone = phone.trim().replace(/\D/g, ""); // Remove all non-digits
if (phone.startsWith("60")) return phone;
if (phone.startsWith("0")) return "60" + phone.slice(1); // e.g., 012... -> 6012...
// If it starts with 1 (e.g., 12...), assume it's a local mobile number without leading 0
// and prepend 60. This is a common pattern in Malaysia.
if (phone.startsWith("1")) return "60" + phone;
return phone; // Return as is if no known pattern matches
}

/**

- Extracts a name from a string that typically looks like "Appointment Type (Name)".
- @param {string} title - The title of the calendar event.
- @returns {string} The extracted name.
*/
function extractNameFromTitle(title) {
const match = title.match(/\(([^)]+)\)/); // Matches content inside parentheses
return match ? match[1].trim() : "";
}

// --- Appointment Transfer Function ---

/**

- Transfers eligible rows from the source sheet to a target sheet based on a time buffer.
- This function is intended to be run by a time-driven trigger.
*/
function transferRowsAtScheduledTime() {
const docProps = getSettings();
const sourceSheetName = docProps.sourceSheet;
const buffer = parseInt(docProps.buffer, 10); // Ensure buffer is a number
if (isNaN(buffer)) {
console.error("Invalid TIME_BUFFER setting. Please set a numeric value.");
return;
}

const scriptSettings = getScriptSettings();
const targetSpreadsheetId = scriptSettings.targetSheetId;
const targetSheetName = scriptSettings.targetSheetName;

if (!targetSpreadsheetId || !targetSheetName) {
SpreadsheetApp.getUi().alert("❌ Error: Target Spreadsheet ID or Sheet Name is not set. Please configure script settings.");
return;
}

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName(sourceSheetName);
if (!sourceSheet) {
console.error(`Source sheet "${sourceSheetName}" not found.`);
return;
}

let targetSpreadsheet, targetSheet;
try {
targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);
targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);
if (!targetSheet) {
// If target sheet doesn't exist, create it
targetSheet = targetSpreadsheet.insertSheet(targetSheetName);
// Optionally, copy headers from source to target if target sheet is new
const sourceHeaders = sourceSheet.getRange(1, 1, 1, sourceSheet.getLastColumn()).getValues();
targetSheet.getRange(1, 1, 1, sourceHeaders[0].length).setValues(sourceHeaders);
console.log(`Created new target sheet: "${targetSheetName}" and copied headers.`);
}
} catch (error) {
console.error(`❌ ERROR opening target spreadsheet or sheet: ${error.message}`);
SpreadsheetApp.getUi().alert(`❌ ERROR: Could not access target spreadsheet or sheet. Details: ${error.message}`);
return;
}

const now = new Date();
const sourceData = sourceSheet.getDataRange().getValues();
const rowsToTransfer = [];
const rowsToUpdateStatusIndices = []; // To store 1-based row indices to update status in source sheet

for (let i = 1; i < sourceData.length; i++) { // Skip header row
const row = sourceData[i];
const appointmentTime = new Date(row[2]); // Column C
const status = (row[7] || "").toUpperCase(); // Column H, ensure uppercase for comparison

```
// Only process rows that are CONFIRMED and have a valid appointment time
if (status !== "CONFIRMED" || !(appointmentTime instanceof Date) || isNaN(appointmentTime.getTime())) {
  continue;
}

const minutesDiff = (appointmentTime.getTime() - now.getTime()) / 60000;

// Trigger if appointment is within buffer time (e.g., 3 hours from now)
// Adjust buffer for a small window of activation for the script (e.g., +/- 5 minutes)
if (minutesDiff >= (buffer - 5) && minutesDiff <= (buffer + 5)) {
  rowsToTransfer.push(row);
  rowsToUpdateStatusIndices.push(i + 1); // Store 1-based row index for updating status later
}

```

}

// Batch transfer rows to the target sheet
if (rowsToTransfer.length > 0) {
const lastRowTarget = targetSheet.getLastRow();
targetSheet.getRange(lastRowTarget + 1, 1, rowsToTransfer.length, rowsToTransfer[0].length).setValues(rowsToTransfer);
console.log(`Transferred ${rowsToTransfer.length} appointments to target sheet.`);

```
// Batch update status in the source sheet to "REMINDED"
const rangesToUpdateH = rowsToUpdateStatusIndices.map(rowIndex => `H${rowIndex}`);

if (rangesToUpdateH.length > 0) {
  const rangeList = sourceSheet.getRangeList(rangesToUpdateH);
  const ranges = rangeList.getRanges();
  ranges.forEach(range => range.setValue("REMINDED"));
  console.log(`Updated status to "REMINDED" for ${rowsToUpdateStatusIndices.length} appointments in source sheet.`);
}

```

} else {
console.log("No eligible appointments to transfer at this time.");
}
}

// --- Trigger Management (New Section) ---

/**

- Creates a time-driven trigger to run `transferRowsAtScheduledTime` every 15 minutes.
- This function should be run once, manually, to set up the trigger.
*/
function createTransferTrigger() {
// Delete existing triggers to prevent duplicates
deleteTransferTrigger();

ScriptApp.newTrigger("transferRowsAtScheduledTime")
.timeBased()
.everyMinutes(15) // Adjust frequency as needed, but consider quota limits
.create();
SpreadsheetApp.getUi().alert("✅ Transfer trigger created to run every 15 minutes.");
}

/**

- Deletes all existing triggers for `transferRowsAtScheduledTime`.
- Useful for resetting or reconfiguring triggers.
*/
function deleteTransferTrigger() {
const triggers = ScriptApp.getProjectTriggers();
for (let i = 0; i < triggers.length; i++) {
if (triggers[i].getHandlerFunction() === "transferRowsAtScheduledTime") {
ScriptApp.deleteTrigger(triggers[i]);
}
}
SpreadsheetApp.getUi().alert("🗑️ All existing transfer triggers deleted.");
}