Select Page
// Google Apps Script to handle form submissions const SHEET_ID = ‘YOUR_SHEET_ID’; // Replace with your actual sheet ID const SHEET_NAME = ‘Entries’; // Main data sheet const CATEGORIES_SHEET = ‘Categories’; const SUBCATEGORIES_SHEET = ‘Subcategories’; function doGet(e) { return handleRequest(e); } function doPost(e) { return handleRequest(e); } function handleRequest(e) { const action = e.parameter.action; try { switch(action) { case ‘getCategories’: return getLookupOptions(CATEGORIES_SHEET); case ‘getSubcategories’: return getLookupOptions(SUBCATEGORIES_SHEET); case ‘addEntry’: return addEntry(e.parameter); case ‘addCategory’: return addLookupOption(CATEGORIES_SHEET, e.parameter.value); case ‘addSubcategory’: return addLookupOption(SUBCATEGORIES_SHEET, e.parameter.value); default: return createResponse(400, {error: ‘Invalid action’}); } } catch (error) { return createResponse(500, {error: error.message}); } } function getLookupOptions(sheetName) { const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(sheetName); const lastRow = sheet.getLastRow(); const options = sheet.getRange(2, 1, lastRow – 1, 1).getValues().flat(); return createResponse(200, {options}); } function addLookupOption(sheetName, value) { if (!value || value.trim() === ”) { return createResponse(400, {error: ‘Value cannot be empty’}); } const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(sheetName); const existingOptions = sheet.getRange(2, 1, sheet.getLastRow() – 1, 1).getValues().flat(); if (existingOptions.some(opt => opt.toLowerCase() === value.toLowerCase())) { return createResponse(400, {error: ‘Value already exists’}); } sheet.appendRow([value.trim()]); return createResponse(200, {message: ‘Option added successfully’}); } function addEntry(params) { const requiredFields = [‘date’, ‘time’, ‘category’, ‘subcategory’]; for (const field of requiredFields) { if (!params[field]) { return createResponse(400, {error: `Missing ${field}`}); } } const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME); const timestamp = new Date(); const datetime = new Date(`${params.date} ${params.time}`); sheet.appendRow([ timestamp, params.date, params.time, params.category, params.subcategory ]); return createResponse(200, {message: ‘Entry added successfully’}); } function createResponse(code, data) { return ContentService .createTextOutput(JSON.stringify(data)) .setMimeType(ContentService.MimeType.JSON) .setStatusCode(code); }