Loading…
Settings
Next session — Week —
Week Data
| Week | Roy att |
Roy paid |
Ryan att |
Ryan paid |
Tom att |
Tom paid |
Fletcher att |
Fletcher paid |
Quincy att |
Quincy paid |
|---|
Balances
| Person | Total Paid | Total Fair Share | Balance |
|---|
Google Sheets Sync
Apps Script setup guide
This creates a live Google Sheet that stays in sync with the website in both directions — edits in the sheet update GitHub, and edits on the site update the sheet.
1Create a new Google Sheet. Rename the first sheet tab to
Chips.2Open Extensions → Apps Script. Delete the default code and paste the script below.
3In Apps Script: open Project Settings (⚙) → Script Properties → add a property with name
GITHUB_TOKEN and the GitHub token as its value.4Click Run → setupTrigger once. This installs the on-edit trigger that syncs sheet changes back to GitHub.
5Click Deploy → New deployment → Web app. Set Execute as: Me, Who has access: Anyone. Click Deploy and copy the URL.
6Paste the deployment URL into the Google Sheets URL field above and press Enter. That's it — sync is live.
Sheet layout: Row 1 = settings, Row 2 = blank, Row 3 = headers, Row 4+ = weekly data. Do not move these rows.
// ═══════════════════════════════════════════════════════════
// Chips Tracker — Google Apps Script
// Paste this into Extensions → Apps Script in your Google Sheet
// ═══════════════════════════════════════════════════════════
const PEOPLE = ["Roy", "Ryan", "Tom", "Fletcher", "Quincy"];
const DATA_START = 4; // first data row (1-indexed)
const GH_OWNER = "mclarenproductions1-afk";
const GH_REPO = "RyansSITE";
const GH_BRANCH = "main";
const GH_PATH = "chips/data.json";
// ── Web app endpoints ────────────────────────────────────────
function doGet(e) {
try {
return jsonOut(readSheetData());
} catch (err) {
return jsonOut({ error: err.message });
}
}
function doPost(e) {
try {
writeSheetData(JSON.parse(e.postData.contents));
return jsonOut({ ok: true });
} catch (err) {
return jsonOut({ error: err.message });
}
}
function jsonOut(obj) {
return ContentService
.createTextOutput(JSON.stringify(obj))
.setMimeType(ContentService.MimeType.JSON);
}
// ── Read ─────────────────────────────────────────────────────
function readSheetData() {
const sheet = getSheet();
const currentWeek = Number(sheet.getRange("B1").getValue()) || 1;
const tqRaw = sheet.getRange("D1").getValue();
const tqAttending = tqRaw === true || String(tqRaw).toLowerCase() === "true"
|| String(tqRaw).toLowerCase() === "yes";
const sheetsUrl = String(sheet.getRange("F1").getValue() || "");
const lastRow = sheet.getLastRow();
const weeks = [];
for (let r = DATA_START; r <= lastRow; r++) {
const weekNum = Number(sheet.getRange(r, 1).getValue());
if (!weekNum) continue;
const attendance = {}, payments = {};
let col = 2;
for (const p of PEOPLE) {
attendance[p] = Number(sheet.getRange(r, col).getValue()) || 0; col++;
payments[p] = Number(sheet.getRange(r, col).getValue()) || 0; col++;
}
weeks.push({ weekNumber: weekNum, attendance, payments });
}
return { currentWeek, nextWeekTomQuincyAttending: tqAttending,
bowlCost: 13, sheetsUrl, weeks };
}
// ── Write ────────────────────────────────────────────────────
function writeSheetData(data) {
const sheet = getSheet();
// Row 1: settings
sheet.getRange("A1").setValue("Current Week");
sheet.getRange("B1").setValue(data.currentWeek || 1);
sheet.getRange("C1").setValue("TQ Attending");
sheet.getRange("D1").setValue(data.nextWeekTomQuincyAttending !== false);
sheet.getRange("E1").setValue("Sheets URL");
sheet.getRange("F1").setValue(data.sheetsUrl || "");
// Row 3: column headers
const headers = ["Week"];
for (const p of PEOPLE) headers.push(p + " Att", p + " Paid");
sheet.getRange(3, 1, 1, headers.length)
.setValues([headers])
.setFontWeight("bold")
.setBackground("#f0f0f0");
// Clear old data rows, then write fresh
const lastRow = sheet.getLastRow();
if (lastRow >= DATA_START) {
sheet.getRange(DATA_START, 1, lastRow - DATA_START + 1, 11).clearContent();
}
(data.weeks || []).forEach((week, i) => {
const row = [week.weekNumber];
for (const p of PEOPLE) row.push(week.attendance[p] || 0, week.payments[p] || 0);
sheet.getRange(DATA_START + i, 1, 1, row.length).setValues([row]);
});
}
// ── Sheet → GitHub sync ───────────────────────────────────────
function onSheetEdit(e) {
// Installable trigger — use a lock to debounce rapid edits
const lock = LockService.getScriptLock();
if (!lock.tryLock(0)) return;
try {
Utilities.sleep(1500);
syncToGitHub();
} catch (err) {
Logger.log("onSheetEdit error: " + err);
} finally {
lock.releaseLock();
}
}
function syncToGitHub() {
const token = PropertiesService.getScriptProperties()
.getProperty("GITHUB_TOKEN");
if (!token) {
Logger.log("GITHUB_TOKEN not set in Script Properties — skipping sync");
return;
}
const data = readSheetData();
const jsonStr = JSON.stringify(data, null, 2);
const content = Utilities.base64Encode(
Utilities.newBlob(jsonStr, "text/plain").getBytes()
);
const apiUrl = "https://api.github.com/repos/" + GH_OWNER + "/" + GH_REPO
+ "/contents/" + GH_PATH;
const headers = {
Authorization: "Bearer " + token,
Accept: "application/vnd.github+json",
"Content-Type": "application/json"
};
// Get current SHA
const getRes = UrlFetchApp.fetch(apiUrl + "?ref=" + GH_BRANCH,
{ headers, muteHttpExceptions: true });
const sha = getRes.getResponseCode() === 200
? JSON.parse(getRes.getContentText()).sha
: null;
const body = {
message: "Sync from Google Sheet — " + new Date().toISOString(),
content,
branch: GH_BRANCH
};
if (sha) body.sha = sha;
const putRes = UrlFetchApp.fetch(apiUrl, {
method: "put",
headers,
payload: JSON.stringify(body),
muteHttpExceptions: true
});
Logger.log("GitHub sync: " + putRes.getResponseCode());
}
// ── Helpers ──────────────────────────────────────────────────
function getSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
return ss.getSheetByName("Chips") || ss.getActiveSheet();
}
// Run this ONCE from the editor (Run → setupTrigger) to install
// the on-edit trigger. Simple onEdit() triggers cannot call
// external services; an installable trigger can.
function setupTrigger() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
ScriptApp.getProjectTriggers()
.filter(t => t.getHandlerFunction() === "onSheetEdit")
.forEach(t => ScriptApp.deleteTrigger(t));
ScriptApp.newTrigger("onSheetEdit")
.forSpreadsheet(ss)
.onEdit()
.create();
Logger.log("Trigger installed ✓");
}