Field Boss Timer - full setup from scratch
Everything needed to run the timer on a fresh Google account.
There are two independent pieces. The timer works with only the first one; the second only powers the "Submit timers" form.
Piece Spreadsheet Script Purpose
Data API Feild Boss Timer.xlsx Apps Script.txt Reads schedules, feeds the site
--- --- --- ---
Submit API Feild Boss Timer_Submit Timer.xlsx Feild Boss Timer_Submit Timer_Apps Script.txt Receives reader submissions
--- --- --- ---
Do part 1 completely and confirm the timer loads before starting part 2.
Before you start: use one account only
Apps Script opens under the browser's default Google account, not the one that owns the file. Signed in to more than one account, you get:
Não foi possível abrir o arquivo. Verifique o endereço e tente novamente. (Sorry, unable to open the file at this time.)
It looks like a broken link. It is not - the default account simply cannot see the file.
Pick one:
Sign out of every other Google account, or
Use a separate Chrome profile for this account, or
Work in an incognito window signed in only to this account.
A separate Chrome profile is worth it if you will maintain this regularly.
Quick check: look at the spreadsheet URL. A /u/1/ or /u/2/ in it means you are not on the default account and Apps Script will fail.
Part 1 - Data API
1.1 Create the spreadsheet
Go to drive.google.com, signed in to the new account.
New → File upload → pick code/Feild Boss Timer.xlsx.
Once uploaded, double-click it, then File → Save as Google Sheets.
Delete the leftover .xlsx - from here on only the Google Sheets version matters.
Copy the spreadsheet ID from the address bar. It is the long string between /d/ and /edit:
https://docs.google.com/spreadsheets/d/THIS_PART_IS_THE_ID/edit
The file should have six tabs: GUIDE, Silverfrost, Dawncrest, Ritual, Event, WorldBoss.
1.2 Create the script
In the spreadsheet: Extensions → Apps Script.
Delete whatever is in Code.gs.
Paste the whole contents of code/Apps Script.txt.
On line 6, replace SPREADSHEET_ID with the ID you copied in 1.1.
Rename the project (top left) to something like Boss Timer API.
Save.
1.3 Enable the advanced Sheets service
This lets the script read all five tabs in one request instead of five.
Left sidebar, next to Services, click +.
Select Google Sheets API.
Leave the identifier as Sheets.
Add, then Save.
Skipping this does not break anything - readAllSheets() falls back to reading one tab at a time, just slower.
1.4 First run and authorization
In the function dropdown pick testAPI and click Run.
Google asks for authorization: Review permissions → your account → Advanced → Go to (project name) → Allow.
Open Execution log. You should see the schedule printed as JSON.
1.5 Rebuild the GUIDE tab
The GUIDE tab in the uploaded file is outdated - it predates the SEQUENCE, END, ALTERNATIVE and CONTRIBUITOR columns.
Run the function buildGuideSheet once.
Open the spreadsheet and check the GUIDE tab.
The text lives in the script, not in the sheet, so run this again after any future column change instead of editing the tab by hand.
1.6 Reload the spreadsheet
Close the spreadsheet tab and open it again.
The ⏱ Timer Admin menu is created by onOpen(), which only runs when the file is opened. If the tab was already open while you pasted the script, the menu is not there yet - and it will not appear until a reload.
If it is still missing after reloading, the script is probably not bound to the spreadsheet. See the note below.
Careful - a working timer does not prove the script is bound.
The code uses SpreadsheetApp.openById(...), which works from any project, including a standalone one created at script.google.com. But onOpen() and the spreadsheet triggers only work from a container-bound script, created from inside the spreadsheet via Extensions → Apps Script.
To check: in the spreadsheet, open Extensions → Apps Script. If it opens an empty project with a blank myFunction, your script is standalone - redo step 1.2 from inside the spreadsheet, then repeat 1.3 to 1.9. The new deployment gets a new URL, so step 1.9 has to be redone with it.
1.7 Triggers
Two triggers, and the difference between them matters.
onEdit must stay a simple trigger - do not create an installable copy of it. A simple trigger runs as whoever made the edit, which is exactly what the CONTRIBUITOR auto-fill needs: it reads that person's saved username. An installable trigger runs as its owner, so one installed by you would stamp your name on everyone else's contributions.
That is why the code has two separate functions. onEdit handles the name; onEditInstalled only clears the cache.
Create these three, all installable:
Function Event source Event type / interval
onEditInstalled From spreadsheet On edit
--- --- ---
onChangeClearCache From spreadsheet On change
--- --- ---
watchdogClearCache Time-driven Minutes timer → Every minute
--- --- ---
For each one: Triggers (clock icon) → Add Trigger → set Function, Deployment = Head, Event source and Event type as in the table → Save.
onChangeClearCache covers inserting and deleting rows, which onEdit never sees.
watchdogClearCache is the safety net for everything neither of them sees. Find & replace (Ctrl+H) does not fire onEdit - neither does an import, an API write, or another script. Without the watchdog those edits stay invisible to readers until the 600s TTL expires, with no error anywhere. The watchdog compares the file's last-modified stamp once a minute and clears the cache only when it actually changed, so the worst case drops from 10 minutes to about 1.
It needs the Drive scope, so the first save asks for authorization again.
1.8 Deploy the web app
Deploy → New deployment.
Type (gear icon) → Web app.
Execute as: Me.
Who has access: Anyone.
Deploy, authorize, and copy the /exec URL.
"Anyone" is required - visitors are not signed in to your account.
1.9 Point the site at the new URL
The URL goes in two files and they must match exactly:
File What to replace
code/FIELD-BOSS-TIMER-POST.html FBT_DATA_API
--- ---
code/TESTE.txt featured.prefetch, inside PORTAL_CONFIG
--- ---
If they differ the timer still works, but loses the parallel prefetch and gets noticeably slower - with no error to tell you.
Then upload the template and republish the post.
Part 2 - Submit API
Only needed for the "Submit timers" button.
2.1 Create the second spreadsheet
Same as 1.1, using code/Feild Boss Timer_Submit Timer.xlsx. Copy its ID.
2.2 Create the second script
This is a separate Apps Script project, not another file in the first one.
Open the submissions spreadsheet → Extensions → Apps Script.
Paste code/Feild Boss Timer_Submit Timer_Apps Script.txt.
Replace SPREADSHEET_ID on line 44 with the ID from 2.1.
Save, then run setupSpreadsheet once to create the headers.
2.3 Deploy and connect
Deploy exactly as in 1.8 (Web app, Execute as Me, Access Anyone).
Copy the /exec URL into FBT_SUBMIT_API in code/FIELD-BOSS-TIMER-POST.html.
Republish the post.
Test by submitting a timer through the site and checking that a row appears.
Each contributor does this once
Send this to the two people who help with the timers:
Open the spreadsheet (they need edit access).
Menu ⏱ Timer Admin → Set my username. If the menu is not there, reload the page.
Type the name to display, for example @Pokoju.
Authorize when asked.
The name is stored per Google account. From then on, filling a TIME writes their name into CONTRIBUITOR automatically. They do not create any triggers.
How the 30 second guarantee works
The cache lasts 10 minutes, but readers never wait that long:
Event What clears the cache
Editing any cell in a boss tab onEdit and onEditInstalled
--- ---
Inserting or deleting rows onChangeClearCache
--- ---
Weekly or event reset resetWeekly / resetEvent
--- ---
Find & replace, import, API write watchdogClearCache (within 1 min)
--- ---
After an edit the cache is dropped immediately, the reader's page polls every 30 seconds, and that poll reads fresh data. The 10 minute TTL is only a safety net for a change no trigger caught.
Checks and troubleshooting
Run testCache and read the log. It reports the cache miss time, the cache hit time, and whether batch reading is active.
Symptom Cause
Everyone's edits credited to one person An installable trigger points at onEdit. Delete it; keep only onEditInstalled.
--- ---
Timer loads but is slow The two URLs in 1.9 do not match, or the Sheets service in 1.3 is off.
--- ---
A column stopped reaching the site Its header was renamed. Columns are matched by name.
--- ---
Header row moved Fine, as long as it stays within the first 10 rows. Update CONFIG.HEADER_ROW to match anyway - it is the fallback.
--- ---
Edits take minutes to show Triggers missing. Check Triggers and Executions.
--- ---
One bulk edit took ~10 min to show, a reload fixed it The edit did not fire onEdit - typically Find & replace. Add the watchdogClearCache trigger from 1.7.
--- ---
Site shows an error box Deployment access is not set to Anyone.
--- ---
"Não foi possível abrir o arquivo" opening Apps Script Signed in to several Google accounts. See "Before you start".
--- ---
Extensions → Apps Script does nothing The file is still an .xlsx. Use File → Save as Google Sheets first.
--- ---
Redeploying after code changes
Deploy → Manage deployments → pencil → Version New version → Deploy.
Always edit the existing deployment. A new deployment gets a new URL, which means redoing step 1.9.