Create Google Apps Script Web App
In your Sheet: Extensions → Apps Script. Delete the default code and paste:
const SHEET_NAME = 'Sheet1';
const SS = SpreadsheetApp.getActiveSpreadsheet();
function doGet(e) {
const action = e.parameter.action;
if (action === 'login') return loginUser(e.parameter.username, e.parameter.password);
if (action === 'getAll') return getAll();
if (action === 'getBySection')return getBySection(e.parameter.section);
if (action === 'getMine') return getMine(e.parameter.section, e.parameter.by);
return ContentService.createTextOutput(JSON.stringify({error:'Unknown action'})).setMimeType(ContentService.MimeType.JSON);
}
function doPost(e) {
const data = JSON.parse(e.postData.contents);
if (data.action === 'add') return addDoc(data);
if (data.action === 'update') return updateDoc(data);
return ContentService.createTextOutput(JSON.stringify({error:'Unknown action'})).setMimeType(ContentService.MimeType.JSON);
}
function getAll() {
const rows = SS.getSheetByName(SHEET_NAME).getDataRange().getValues();
const [headers, ...data] = rows;
const result = data.map(row => {
const obj = {};
headers.forEach((h, i) => obj[h] = row[i]);
return obj;
});
return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}
function getMine(section, by) {
const all = JSON.parse(getAll().getContent());
return ContentService.createTextOutput(JSON.stringify(all.filter(d => d.Section === section && d.SubmittedBy === by))).setMimeType(ContentService.MimeType.JSON);
}
function _json(obj){ return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON); }
function loginUser(username, password) {
const sheet = SS.getSheetByName('Users');
if (!sheet) return _json({success:false, error:'No "Users" sheet found. Create a tab named Users.'});
const rows = sheet.getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
const uname = String(rows[i][0]).trim();
const pw = String(rows[i][1]);
const name = rows[i][2];
const role = String(rows[i][3]).trim();
const sect = rows[i][4];
const active = rows[i][5];
const isActive = active === true || String(active).toUpperCase() === 'TRUE';
if (uname === String(username).trim() && pw === String(password)) {
if (!isActive) return _json({success:false, error:'This account is disabled. Contact your administrator.'});
return _json({success:true, fullName:name, role:role, section:sect});
}
}
return _json({success:false, error:'Incorrect username or password.'});
}
function getBySection(section) {
const all = JSON.parse(getAll().getContent());
if (!section || section === 'All') return _json(all);
return _json(all.filter(d => d.Section === section));
}
function addDoc(data) {
const sheet = SS.getSheetByName(SHEET_NAME);
const last = sheet.getLastRow();
const id = last > 1 ? last : 1;
sheet.appendRow([id, new Date().toISOString(), data.date, data.section, data.by, data.desc, data.type, data.priority, 'Pending Review', data.encRem || '', '', '', '']);
return ContentService.createTextOutput(JSON.stringify({success:true, id})).setMimeType(ContentService.MimeType.JSON);
}
function updateDoc(data) {
const sheet = SS.getSheetByName(SHEET_NAME);
const rows = sheet.getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
if (String(rows[i][0]) === String(data.rowId)) {
sheet.getRange(i+1, 9).setValue(data.status);
sheet.getRange(i+1, 11).setValue(data.supRem);
sheet.getRange(i+1, 12).setValue(data.dateRev);
sheet.getRange(i+1, 13).setValue(data.revBy);
return ContentService.createTextOutput(JSON.stringify({success:true})).setMimeType(ContentService.MimeType.JSON);
}
}
return ContentService.createTextOutput(JSON.stringify({error:'Not found'})).setMimeType(ContentService.MimeType.JSON);
}
Click Save, then Deploy → New deployment → Web app.
Set Execute as: Me, Who has access: Anyone. Click Deploy and copy the Web App URL.