Paste the backend code
Copy the code below and paste it into the Apps Script editor, then click Save (Ctrl+S) .
// ============================================================
// GIG TRACKER — Google Apps Script Backend
// Paste this entire file into your Apps Script editor
// ============================================================
const SHEET_NAME = 'GIG_TRACKER_DATA';
function doGet(e) {
return handleRequest(e);
}
function doPost(e) {
return handleRequest(e);
}
function handleRequest(e) {
const headers = {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Methods': 'GET, POST, OPTIONS',
'Access-Control-Allow-Headers': 'Content-Type',
'Content-Type': 'application/json'
};
try {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(SHEET_NAME);
// Create sheet if it doesn't exist
if (!sheet) {
sheet = ss.insertSheet(SHEET_NAME);
sheet.appendRow(['waybill', 'cod_status', 'tracking_status', 'tracking_desc', 'last_updated']);
}
const action = (e.parameter && e.parameter.action) ||
(e.postData && JSON.parse(e.postData.contents).action);
if (action === 'load') {
// Return all saved data
const data = getAllData(sheet);
return ContentService
.createTextOutput(JSON.stringify({ ok: true, data: data }))
.setMimeType(ContentService.MimeType.JSON);
}
if (action === 'save') {
// Save a single waybill update
const body = JSON.parse(e.postData.contents);
upsertRow(sheet, body.waybill, body.cod_status, body.tracking_status, body.tracking_desc);
return ContentService
.createTextOutput(JSON.stringify({ ok: true }))
.setMimeType(ContentService.MimeType.JSON);
}
if (action === 'save_bulk') {
// Save multiple waybill updates at once
const body = JSON.parse(e.postData.contents);
const rows = body.rows || [];
rows.forEach(r => upsertRow(sheet, r.waybill, r.cod_status, r.tracking_status, r.tracking_desc));
return ContentService
.createTextOutput(JSON.stringify({ ok: true, saved: rows.length }))
.setMimeType(ContentService.MimeType.JSON);
}
if (action === 'clear') {
// Clear all data rows (keep header)
const lastRow = sheet.getLastRow();
if (lastRow > 1) sheet.deleteRows(2, lastRow - 1);
return ContentService
.createTextOutput(JSON.stringify({ ok: true }))
.setMimeType(ContentService.MimeType.JSON);
}
return ContentService
.createTextOutput(JSON.stringify({ ok: false, error: 'Unknown action' }))
.setMimeType(ContentService.MimeType.JSON);
} catch(err) {
return ContentService
.createTextOutput(JSON.stringify({ ok: false, error: err.toString() }))
.setMimeType(ContentService.MimeType.JSON);
}
}
function getAllData(sheet) {
const lastRow = sheet.getLastRow();
if (lastRow < 2) return {};
const rows = sheet.getRange(2, 1, lastRow - 1, 5).getValues();
const result = {};
rows.forEach(row => {
if (row[0]) {
result[row[0]] = {
cod_status: row[1] || '',
tracking_status: row[2] || '',
tracking_desc: row[3] || '',
last_updated: row[4] || ''
};
}
});
return result;
}
function upsertRow(sheet, waybill, cod_status, tracking_status, tracking_desc) {
const lastRow = sheet.getLastRow();
const timestamp = new Date().toISOString();
if (lastRow >= 2) {
const waybills = sheet.getRange(2, 1, lastRow - 1, 1).getValues().flat();
const rowIdx = waybills.indexOf(String(waybill));
if (rowIdx !== -1) {
// Update existing row
sheet.getRange(rowIdx + 2, 2, 1, 4).setValues([[cod_status || '', tracking_status || '', tracking_desc || '', timestamp]]);
return;
}
}
// Insert new row
sheet.appendRow([String(waybill), cod_status || '', tracking_status || '', tracking_desc || '', timestamp]);
}
Copy