Advanced: Options From Another Spreadsheet
Your options sheet is the single place the add-on reads from: you type options in column A, capacities in column B, and the add-on writes sign-up counts into column C. Nothing else on your Drive is touched.
If the list you want already lives in another spreadsheet (a roster, an inventory, a sheet someone else maintains), you don't have to retype or copy it. Point column A at that sheet with a formula, and your form follows it.
The Formula
Open your options sheet and go to the question's tab. Connecting seeded column A with the question's current choices, so first clear everything in column A below the header. A formula fills the cells below it as it expands, and it stops with #REF! if any of those cells is occupied.
Then click cell A2 and enter:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit", "Sheet1!A2:A")
Replace the URL with your source spreadsheet's, and Sheet1!A2:A with the tab and column holding your options. Start the range at row 2 if your source has a header row.
The first time you do this, the cell shows #REF!. Pressing Enter moved your selection one row down, and Sheets only shows the Allow access prompt while the formula cell is selected, so click A2 again to bring it up. Click Allow access once and the values appear. The grant is per pair of spreadsheets, and the prompt only shows to someone who can open both.
Leave A1 as Options. The add-on checks that header to be sure the tab's layout is intact, and syncing stops if it changes.
What Happens Next
Nothing else changes. Your form syncs on the normal schedule (when the sidebar opens and every hour), and each sync reads whatever the formula currently resolves to. Edit the source spreadsheet and the new values reach your form on the next sync.
Capacities Need a Second Look
Capacities in column B are matched to options by row: the number in B5 caps whatever option sits in A5.
A hand-typed list doesn't move, so this is invisible in normal use. A formula-fed list can: if rows are inserted, removed, or re-sorted in the source, the options shift and your capacities stay where they were, attached to the wrong rows.
Two ways to stay safe:
- Add or remove rows at the end of the source list rather than in the middle
- After any reordering, open the options sheet and check that each capacity still sits beside the right option
If you don't use capacities at all, none of this applies.
When the Formula Breaks
If access is revoked, the source file is deleted, or the range becomes invalid, the cell resolves to an error like #REF! or #N/A.
The add-on will not write that into your form. The sync stops, the question keeps the options it already has, and the sidebar shows a sync error telling you to fix the options sheet. Open the sheet, repair the formula, and sync again.
When the erroring cell holds an IMPORTRANGE, the sidebar says so and points you back to the Allow access prompt, because a grant that was never given looks exactly like a broken formula. Click A2 in the options sheet, allow access if the prompt appears, and sync again.
Why a Formula Instead of a Picker
Connecting to outside spreadsheets from inside the add-on would mean granting it access to those files, and it would make the options sheet mean two different things depending on a setting you can't see when you're looking at it. A formula keeps one rule true: you own Options and Capacity, the add-on owns Taken, and the add-on never reads any spreadsheet except the one it created for your form.