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, click cell A2 of the question's tab, 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! with an Allow access prompt. Click it once and the values appear. That prompt only shows to someone who can open both spreadsheets.
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, every hour, and after every submission), 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.
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.