One-time · from inside a Google Sheet
Create Your Google Sheet Backend
1. Go to
sheets.new → name it
NerdyTeam KPI
2. Click
Extensions → Apps Script
3. Delete everything → paste the code below →
Ctrl+S
4.
Deploy → New deployment → Web app → Execute as: Me · Who has access: Anyone → Deploy → Authorise → Allow
5. Copy the
Web app URL → paste below
const SHEET_NAME='KPI Data',CONFIG_SHEET='Config';
function toDateStr(v){if(!v)return'';if(typeof v==='string'){if(/^\d{4}-\d{2}-\d{2}$/.test(v))return v;const d=new Date(v);return isNaN(d)?v:fd(d)}return v instanceof Date?fd(v):String(v)}
function fd(d){return d.getFullYear()+'-'+String(d.getMonth()+1).padStart(2,'0')+'-'+String(d.getDate()).padStart(2,'0')}
function doGet(e){return handleRequest(e)}
function doPost(e){return handleRequest(e)}
function handleRequest(e){
const out=ContentService.createTextOutput();out.setMimeType(ContentService.MimeType.JSON);
try{
const ss=SpreadsheetApp.getActiveSpreadsheet();
let sheet=ss.getSheetByName(SHEET_NAME);
if(!sheet){sheet=ss.insertSheet(SHEET_NAME);sheet.appendRow(['empId','empName','date','revenue','incomeSource','hr','hrSource','minutes','minutesTarget','productive','productiveTarget','demos','demosDesc','notes','inTime','outTime','attendanceStatus','savedAt']);sheet.getRange('C:C').setNumberFormat('@STRING@')}
const headers=sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
const ec=(name,after)=>{if(!headers.includes(name)){const ai=headers.indexOf(after);if(ai>=0){sheet.insertColumnAfter(ai+1);sheet.getRange(1,ai+2).setValue(name);headers.splice(ai+1,0,name)}else{const lc=sheet.getLastColumn()+1;sheet.getRange(1,lc).setValue(name);headers.push(name)}}};
ec('minutesTarget','minutes');ec('productiveTarget','productive');ec('demos','productiveTarget');ec('demosDesc','demos');ec('inTime','notes');ec('outTime','inTime');ec('attendanceStatus','outTime');
let action='getAll',body={};
if(e.parameter&&e.parameter.action)action=e.parameter.action;
if(e.postData&&e.postData.contents){try{body=JSON.parse(e.postData.contents);if(body.action)action=body.action}catch(x){}}
if(action==='getAll'){
const data=sheet.getDataRange().getValues();
if(data.length<=1){out.setContent(JSON.stringify({ok:true,rows:[]}));return out}
const h=data[0];const rows=data.slice(1).map(r=>{const o={};h.forEach((k,i)=>{o[k]=k==='date'?toDateStr(r[i]):(r[i]!==undefined&&r[i]!==null)?String(r[i]):''});return o});
out.setContent(JSON.stringify({ok:true,rows}))
}else if(action==='saveEntry'){
const d=body.data;if(!d||!d.empId||!d.date){out.setContent(JSON.stringify({ok:false,error:'Missing fields'}));return out}
const ds=toDateStr(d.date),eid=String(d.empId);
const h=sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
const data=sheet.getDataRange().getValues();let fr=-1;
for(let i=1;irm.hasOwnProperty(k)?rm[k]:'');
if(fr>0)sheet.getRange(fr,1,1,row.length).setValues([row]);else sheet.appendRow(row);
sheet.getRange('C:C').setNumberFormat('@STRING@');out.setContent(JSON.stringify({ok:true,date:ds}))
}else if(action==='deleteEntry'){
const eid=String(body.empId||''),ds=toDateStr(body.date);
const h=sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
const data=sheet.getDataRange().getValues();let del=0;
for(let i=data.length-1;i>=1;i--){if(String(data[i][h.indexOf('empId')])===eid&&toDateStr(data[i][h.indexOf('date')])===ds){sheet.deleteRow(i+1);del++}}
out.setContent(JSON.stringify({ok:true,deleted:del}))
}else if(action==='saveConfig'){
let cs=ss.getSheetByName(CONFIG_SHEET);if(!cs)cs=ss.insertSheet(CONFIG_SHEET);
cs.clearContents();cs.getRange('A1').setValue(JSON.stringify(body.config));out.setContent(JSON.stringify({ok:true}))
}else if(action==='getConfig'){
let cs=ss.getSheetByName(CONFIG_SHEET);
if(!cs){out.setContent(JSON.stringify({ok:true,config:null}))}
else{const v=cs.getRange('A1').getValue();let c=null;if(v){try{c=JSON.parse(v)}catch(x){}}out.setContent(JSON.stringify({ok:true,config:c}))}
}else{out.setContent(JSON.stringify({ok:false,error:'Unknown: '+action}))}
}catch(err){out.setContent(JSON.stringify({ok:false,error:err.toString()}))}
return out}
Copy Code