Go to sheets.new to create a brand-new blank Google Sheet. Do not upload the XLSX file — Apps Script only works in native Google Sheets, not in uploaded Excel files.
In the Sheet, go to Extensions → Apps Script. (If you don't see Extensions in the menu bar, make sure you opened a native Google Sheet, not an uploaded .xlsx file.)
Delete any existing code in the editor, then paste the script below.
Replace YOUR_SECRET_HERE with the value of your SYNC_SECRET Cloudflare secret.
Replace YOUR_SITE_URL with https://pacbrewlab.com (no trailing slash).
Save the script (Ctrl+S / Cmd+S), then close the Apps Script tab.
Back in the Sheet, reload the page — a new BestMovies menu will appear in the menu bar.
Click BestMovies → Export from Site to pull your current movie list into tabs (one tab per category). You may be asked to authorize the script the first time — click Allow.
Edit any tab: Column A = IMDB IDs (e.g. tt0111161), Column B = title (read-only, informational).
Click BestMovies → Sync to Site to push your edits back to the site.
// BestMovies Google Apps Script
// Paste into Extensions > Apps Script in your Google Sheet
var SECRET = "YOUR_SECRET_HERE";
var SITE_URL = "YOUR_SITE_URL"; // e.g. https://pacbrewlab.com
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("BestMovies")
.addItem("Export from Site", "exportFromSite")
.addItem("Sync to Site", "syncToSite")
.addToUi();
}
function exportFromSite() {
var url = SITE_URL + "/api/admin/export-sheets?secret=" + encodeURIComponent(SECRET);
var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
if (response.getResponseCode() !== 200) {
SpreadsheetApp.getUi().alert("Export failed: " + response.getContentText());
return;
}
var data = JSON.parse(response.getContentText());
var ss = SpreadsheetApp.getActiveSpreadsheet();
var slugs = Object.keys(data.categories);
// Remove tabs that no longer exist in DB
var existingSheets = ss.getSheets();
existingSheets.forEach(function(sheet) {
var name = sheet.getName();
if (name !== "Sheet1" && slugs.indexOf(name) === -1) {
ss.deleteSheet(sheet);
}
});
slugs.forEach(function(slug) {
var cat = data.categories[slug];
var sheet = ss.getSheetByName(slug);
if (!sheet) {
sheet = ss.insertSheet(slug);
}
sheet.clearContents();
// Header row
sheet.getRange(1, 1).setValue("imdb_id");
sheet.getRange(1, 2).setValue("title");
sheet.setFrozenRows(1);
// Movie rows
cat.movies.forEach(function(movie, i) {
sheet.getRange(i + 2, 1).setValue(movie.id);
sheet.getRange(i + 2, 2).setValue(movie.title);
});
// Auto-resize columns
sheet.autoResizeColumn(1);
sheet.autoResizeColumn(2);
});
SpreadsheetApp.getActiveSpreadsheet().toast(
"Exported " + slugs.length + " categories from site.",
"BestMovies Export",
5
);
}
function syncToSite() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var categories = {};
sheets.forEach(function(sheet) {
var slug = sheet.getName();
if (slug === "Sheet1") return; // skip default empty sheet
var lastRow = sheet.getLastRow();
var ids = [];
if (lastRow > 1) {
var values = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
values.forEach(function(row) {
var id = String(row[0]).trim();
if (id) ids.push(id);
});
}
categories[slug] = ids;
});
var payload = JSON.stringify({ secret: SECRET, categories: categories });
var options = {
method: "post",
contentType: "application/json",
payload: payload,
muteHttpExceptions: true,
};
var response = UrlFetchApp.fetch(SITE_URL + "/api/admin/sync-sheets", options);
if (response.getResponseCode() !== 200) {
SpreadsheetApp.getUi().alert("Sync failed: " + response.getContentText());
return;
}
var result = JSON.parse(response.getContentText());
SpreadsheetApp.getActiveSpreadsheet().toast(
"Updated: " + result.updated.join(", "),
"BestMovies Sync",
5
);
}