// 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);
}
