/** * Skootly → Google Sheets connector (version 2) * Paste this whole file into your sheet: Extensions → Apps Script. * Then Deploy → New deployment → Web app → Execute as: Me, Who has access: Anyone → Deploy. * Copy the Web app URL (ends in /exec) into Skootly. * * Already set up with an older version? Paste this over it, Save, then Deploy → Manage deployments → ✏️ (edit) → * Version: "New version" → Deploy. Your Web app URL stays the same, so nothing changes in Skootly. * * What it does: when Skootly sends Skool members (new approvals, or existing members from "Extract current members"), * it adds one row per member to the "Skootly" tab of THIS spreadsheet, and keeps that tab first. * It cannot read or change anything else. */ function doPost(e) { var lock = LockService.getDocumentLock(); lock.waitLock(20000); // one write at a time, so rows never overlap try { var data = JSON.parse(e.postData.contents); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = getOrCreateTab_(ss, data.tab || 'Skootly', data.header || []); if (data.action === 'append') { var rows = (data.rows || []).map(safeRow_); if (rows.length) { var width = Math.max.apply(null, rows.map(function (r) { return r.length; })); rows = rows.map(function (r) { while (r.length < width) r.push(''); return r; }); sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, width).setValues(rows); } } return reply_({ ok: true, scriptVersion: 2, spreadsheetId: ss.getId(), spreadsheetName: ss.getName(), spreadsheetUrl: ss.getUrl(), added: (data.rows || []).length }); } catch (err) { return reply_({ ok: false, error: String(err) }); } finally { lock.releaseLock(); } } function doGet() { return reply_({ ok: true, message: 'Skootly connector is installed. Paste this URL into Skootly.' }); } function getOrCreateTab_(ss, name, header) { var main = name === 'Skootly'; var sheet = ss.getSheetByName(name) || (main ? ss.insertSheet(name, 0) : ss.insertSheet(name)); // The Skootly tab is always the FIRST tab (other tabs, like "Facebook", go after it). if (main && sheet.getIndex() !== 1) { ss.setActiveSheet(sheet); ss.moveActiveSheet(1); } if (sheet.getLastRow() === 0 && header.length) { sheet.getRange(1, 1, 1, header.length).setValues([header]).setFontWeight('bold'); sheet.setFrozenRows(1); } else if (header.length && sheet.getLastColumn() < header.length) { // New columns added by Skootly (e.g. "Member Type"): fill in their header labels, keep everything else. var current = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; var same = current.every(function (h, i) { return String(h) === String(header[i]); }); if (same) sheet.getRange(1, 1, 1, header.length).setValues([header]).setFontWeight('bold'); } return sheet; } // Text starting with = + - @ would be treated as a formula; prefix ' so it shows as plain text. function safeRow_(row) { return row.map(function (v) { var s = v === null || v === undefined ? '' : String(v); return /^[=+\-@]/.test(s) ? "'" + s : s; }); } function reply_(obj) { return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON); }