KB: Dropdown Sync – Sheet Summary Fields

Summary

This script syncs dropdown option lists into Sheet Summary fields (PICKLIST, MULTI_PICKLIST, CONTACT_LIST, or MULTI_CONTACT_LIST) on destination sheets. It's the Summary-field counterpart to the existing column-based Dropdown Syncs (Sync 1–8) — same idea, different destination type.

What It Does

1. Reads a config sheet of source → destination rules (same format as Sync 1–8, with one key difference: it targets a summary field on the destination sheet instead of a grid column).
2. For each active, enabled row, gathers unique values from the specified source column (sheet or report) — deduped, optionally sorted, with special handling for contact columns.
3. Looks up the named Summary Field on the destination sheet by title.
4. Updates that field's dropdown options to match the current source values.
5. Uses a content hash to skip writing if nothing has changed since the last run — reduces unnecessary API calls.

Requirements Before Running

• The Summary Field must already exist on the destination sheet, matched by exact title — the script does not create new summary fields. If the title doesn't match anything on the sheet, that row errors out.
• The field does not need to already be a dropdown type. If it exists but isn't already PICKLIST/MULTI_PICKLIST, the script automatically converts it to MULTI_PICKLIST when writing. Contact-type fields (CONTACT_LIST/MULTI_CONTACT_LIST) are preserved as-is.
• The config sheet needs the columns below.

Config Sheet Columns

• In Id - ID of the source sheet or report to read values from.
• In Id is Report - true/yes/1 if the source is a report instead of a sheet.
• In Column - Name of the column on the source to pull values from.
• Out Sheet Id - ID of the destination sheet.
• Out Field - Title of the summary field to update on the destination sheet.
• Sorted - true/yes/1 to sort the options alphabetically (natural sort).
• Active - true/yes/1 to enable this row. Blank/false rows are skipped.
• Primary Contact - Used for contact-type field handling.
• OutFieldID (auto) - Cached summary field ID, populated automatically after the first successful run.
• OutFieldType (auto) - Cached summary field type, populated automatically.
• Last Script Update (auto) - Timestamp written after each run.
• Error Description (auto) - Error message written if the row fails.
• SourceDropdownHash (auto) - Hash of the last computed source values.
• LastAppliedHash (auto) - Hash of the values last written to the destination — used for the skip-if-unchanged check.

Important: How the "No Change" Skip Works

The script only compares the current source values against the last known applied hash on the config row — it does not verify what's actually sitting in the destination field right now. If you manually clear or edit the destination field's options, the script will not notice or fix it as long as the source hasn't changed. To force a refresh, manually clear the LastAppliedHash (and optionally SourceDropdownHash) cell on that row before the next run.

Troubleshooting

• "Summary field '...' not found on sheet ..." → the Out Field title doesn't match any existing summary field on the destination sheet. Create the field first, then re-run.
• "Missing required config: ..." → one of In Id, In Column, Out Sheet Id, or Out Field is blank on that row.
• "Input list is empty" → the source column/report returned no values to sync.
• Row seems stuck / not updating → check LastAppliedHash — if the source truly hasn't changed, the row is intentionally skipped to avoid redundant API calls.

Related

• Config sheet: https://app.smartsheet.com/sheets/3vHcvCm76QxXpj6Pc7h3j4Q26gmqvRgMRMpw5Vc1
• Counterpart scripts: Dropdown Sync Sync 1–8 (column-based, same overall pattern)