Attach Images — Google Drive → URL stored in Column L of sheet
📎 Click to select or drag & drop images Uploads to Google Drive folder "WNW INV Images" · URL saved in Column L
* Required fields
⚙️ Settings & Integration
Live Data Connection
Checking…
Auto-connects on load via Netlify proxy → Apps Script → Google Sheet
Netlify Environment Variable required:
Key: APPS_SCRIPT_URL
Value: Your deployed Apps Script Web App URL Set this in Netlify → Site → Environment Variables → then redeploy.
Apps Script — Copy → Deploy as Web App
Extensions → Apps Script → paste code below → Deploy → New Deployment → Web App
Execute as: Me · Who has access: Anyone · Deploy → Authorize → Copy URL
Paste URL as APPS_SCRIPT_URL in Netlify Environment Variables.
// ═══════════════════════════════════════════════════════════════
// WNW INV OPTIMISER — Apps Script
// Sheet ID is hardcoded here. No manual connection needed from dashboard.
// doGet → reads all data → returns JSON to Netlify proxy
// doPost → writes add / update / upload_image
// ═══════════════════════════════════════════════════════════════
var SHEET_ID = '1l2Kl7CCOuuCQ1QMzGuYHDOpKAHjVU52cqTKoPSceXQs'; // ← your sheet ID
var TAB_NAME = 'KOL INV OPT'; // ← your sheet tab name
var IMG_FOLDER = 'WNW INV Images'; // ← Google Drive folder for images
// ── READ: returns all rows as JSON ──────────────────────────────────────────
function doGet(e) {
try {
var ss = SpreadsheetApp.openById(SHEET_ID);
var sheet = ss.getSheetByName(TAB_NAME);
var all = sheet.getDataRange().getValues();
// Row 1 = MCOP filter chips, Row 2 = column headers → data starts row 3 (index 2)
// Columns: B=SI C=Store D=Date E=SKU F=Priority G=Type H=Items I=Remarks J=Status K=ResolveDate L=ImageURL
// getValues() gives 0-based: col B is index 1 in each row
var rows = [];
for (var i = 2; i < all.length; i++) {
var r = all[i];
var sku = r[4] ? String(r[4]).trim() : ''; // col E = index 4
if (!sku || sku === 'SKU') continue; // skip empty / header rows
rows.push({
si: r[1] ? parseInt(r[1]) || i-1 : i-1,
store: r[2] ? String(r[2]).trim() : 'KOL',
reportDate: fmtDate(r[3]),
sku: sku,
priority: r[5] ? String(r[5]).trim() : '',
type: r[6] ? String(r[6]).trim() : '',
items: r[7] ? String(r[7]).trim() : '',
remarks: r[8] ? String(r[8]).trim() : '',
status: r[9] ? String(r[9]).trim() : '',
resolveDate: fmtDate(r[10]),
imageUrl: r[11] ? String(r[11]).trim() : '',
});
}
return json({success: true, data: rows, count: rows.length});
} catch(e) {
return json({success: false, error: e.message});
}
}
// ── WRITE: add / update / upload_image ─────────────────────────────────────
function doPost(e) {
try {
var ss = SpreadsheetApp.openById(SHEET_ID);
var sheet = ss.getSheetByName(TAB_NAME);
var data = JSON.parse(e.postData.contents);
var resp = {success: false};
if (data.action === 'add') {
var lastSI = sheet.getLastRow() - 2; // subtract 2 for MCOP row + header row
sheet.appendRow([
'', lastSI + 1, data.store, data.reportDate, data.sku,
data.priority, data.type, data.items, data.remarks,
data.status, data.resolveDate || '', data.imageUrl || ''
]);
resp.success = true;
}
else if (data.action === 'update') {
var rows = sheet.getDataRange().getValues();
for (var i = 2; i < rows.length; i++) { // start from row 3 (index 2)
if (String(rows[i][1]) === String(data.si)) { // col B = index 1 = SI
var r = i + 1; // 1-based row number for setRange
sheet.getRange(r,3).setValue(data.store);
sheet.getRange(r,4).setValue(data.reportDate);
sheet.getRange(r,5).setValue(data.sku);
sheet.getRange(r,6).setValue(data.priority);
sheet.getRange(r,7).setValue(data.type);
sheet.getRange(r,8).setValue(data.items);
sheet.getRange(r,9).setValue(data.remarks);
sheet.getRange(r,10).setValue(data.status);
sheet.getRange(r,11).setValue(data.resolveDate || '');
sheet.getRange(r,12).setValue(data.imageUrl || ''); // col L = index 12 (1-based)
resp.success = true; break;
}
}
if (!resp.success) resp.error = 'Row with SI='+data.si+' not found';
}
else if (data.action === 'upload_image') {
// Find or create the WNW INV Images folder
var folderIter = DriveApp.getFoldersByName(IMG_FOLDER);
var folder = folderIter.hasNext()
? folderIter.next()
: DriveApp.createFolder(IMG_FOLDER);
// Unique filename: timestamp + original name (prevents overwrites)
var ts = new Date().getTime();
var safeName = 'WNW_' + ts + '_' + (data.filename || 'image.jpg').replace(/[^a-zA-Z0-9._-]/g,'_');
var decoded = Utilities.base64Decode(data.base64);
var blob = Utilities.newBlob(decoded, data.mimeType, safeName);
var file = folder.createFile(blob);
// Try public sharing — may fail on restricted Google Workspace domains
// File is still created and URL returned even if sharing fails
try {
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
} catch(shareErr) {
Logger.log('setSharing skipped: ' + shareErr.message);
}
resp.success = true;
resp.fileId = file.getId();
resp.filename = safeName;
resp.url = 'https://drive.google.com/uc?export=view&id=' + file.getId();
}
return json(resp);
} catch(e) {
return json({success: false, error: e.message});
}
}
// ── Helpers ─────────────────────────────────────────────────────────────────
function fmtDate(v) {
if (!v) return '';
// Use duck-typing instead of instanceof (instanceof Date unreliable in Apps Script V8)
if (typeof v === 'object' && typeof v.getFullYear === 'function') {
var y = v.getFullYear();
var m = v.getMonth() + 1;
var d = v.getDate();
return y + '-' + (m < 10 ? '0' : '') + m + '-' + (d < 10 ? '0' : '') + d;
}
// Handle string dates like "Sat Apr 04 2026 00:00:00 GMT+0530..."
var s = String(v).trim();
if (/^\d{4}-\d{2}-\d{2}/.test(s)) return s.slice(0, 10);
// Parse any date string
var dt = new Date(s);
if (!isNaN(dt.getTime())) {
// Use IST offset (+5:30) to get correct India date
var ist = new Date(dt.getTime() + 5.5 * 60 * 60 * 1000);
var y2 = ist.getUTCFullYear();
var m2 = ist.getUTCMonth() + 1;
var d2 = ist.getUTCDate();
return y2 + '-' + (m2 < 10 ? '0' : '') + m2 + '-' + (d2 < 10 ? '0' : '') + d2;
}
return s;
}
function json(obj) {
return ContentService.createTextOutput(JSON.stringify(obj))
.setMimeType(ContentService.MimeType.JSON);
}
Sheet Read Range
Read range is B2:L (columns B through L = 10 data columns + Image URL in L).
Row 2 = headers. Data starts Row 3.
Column order: SI | Store | Report Date | SKU | Priority | Type | Items | Remarks | Resolution Status | Resolve Date | Image URL