- JavaScript 82.6%
- HTML 15.3%
- Shell 2.1%
| Filename | Latest commit message | Latest commit date |
|---|---|---|
Added Total: Settings key and documented all Range: ... generated keys for Labels, Lookup sheets and per-depth lookups with proper formulas. Co-Authored-By: Claude Haiku 4.5 <noreply@anthropic.com> |
||
| docs | ||
| scripts | ||
| tests | ||
| .gitattributes | ||
| .gitignore | ||
| .zipignore | ||
| appsscript.json | ||
| config.js | ||
| package.json | ||
| README.md | ||
| SyncProgress.html | ||
Gmail Labels Sync: depth-aware flattened lookups
This Apps Script syncs Gmail labels to Labels and generates a grouped three-column Lookup, a pipe-delimited Lookup: Compact, and one fully row-aligned depth tab per active hierarchy level. Inbox Sorter is the intended consumer; no Inbox Sorter files are modified by this project.
Active depth: not hardcoded
Sync: Labels determines the maximum nonempty path depth from the Gmail labels fetched during that sync. A hierarchy of 2 levels generates Lookup: Top Level and Lookup: Sub Folder 1; 6 levels generates the depth tabs through Sub Folder 5; 8 levels generates them through Sub Folder 7. There is no fixed six-level limit in the source reader, generator, headers, or tab naming. New Sub Folder N columns are created in Labels automatically.
Sync: Lookup from Current Labels (rebuildLabelLookups) rebuilds from existing Labels rows using its consecutive hierarchy headers (Top Level Folder, Sub Folder 1, etc.). It does not fetch Gmail. A fresh Gmail sync will also resize those headers to the maximum depth of the fetched labels; rebuilding without Gmail leaves the depth indicated by existing headers unchanged.
After a successful sync, obsolete depth tabs are deleted and old generated header/data columns are cleared (not deleted) from Labels. User-managed columns outside generated hierarchy are preserved. If new depth would collide with a user header, the script stops rather than overwrite it. An empty Gmail label collection retains a single Top Level sheet, and Reset preserves the current declared source depth.
Output sheets
Labels:Full Path | Top Level Folder | Sub Folder 1 | ...(number of folder columns follows active depth).Lookup:Key | Depth | Options(visible, exactly three columns). Every key has one row for every active depth, even if Options is blank. Key appears only on the first row for its group. A populated Options cell starts with|to include the blank choice (e.g.|A2P|Double Calls|Forms); cells without choices remain empty. Columns: Key 400px, Depth 200px, Options 1000px; freeze row 1 and columns A:B.Lookup: Compact: the original horizontalKey | Top Level Folder Options | Sub Folder 1 Options | ...pipe-delimited layout. Every nonempty option list has one leading|to represent a blank choice; no-choice entries remain empty. Retained under its new name and hidden.Lookup: Top Level,Lookup: Sub Folder 1, …: hidden, fully aligned depth tabs, one per level. These are the old(Full)tabs renamed in place; the old sparse-key tabs are removed. All depth tabs contain every key in the same row order asLookup: Compact, including keys with no options. Each tab retains itsKey | Optionsstructure, with a merged Options header. For rows with choices, B is intentionally blank and real options start in C; rows with no choices remain blank after the key.Settings:Type | Key | Value, frozen first two columns, at least 20 total rows and exactly three columns (A:C). Generated keys includeTotal: Labels,Total: Settings,Total Combinations: Labels,Total Depth: Labels(named rangetotal_depth_labels), and oneTotal Options: ...key per active depth. Labels count nonemptyLabels!A2:Afull paths; combinations countLookup: Compact!A2:Akeys. Depth counts hierarchy headers (e.g.=COUNTA(Labels!B1:I1)for eight levels). Every option total reads the corresponding aligned lookup's all-wildcard options row with an unbounded end (=IFERROR(COUNTUNIQUE('Lookup: Top Level'!B2:2)+1,0)), adding one for the blank choice. Types and keys are alphabetized, and each Type is displayed once in a vertically merged block.
Key semantics and consumer contract
All lookup tabs use the same positional wildcard key for the current number of levels. Keys are separated by /; an empty slot means wildcard. The all-empty key for depth 6 is /////, but at depth 8 it is ///////. The last selected level offers the full set of alternatives matching its selected ancestors. Label option sets are alphabetized. Every depth sheet retains all keys, even when options are empty.
Important for Inbox Sorter: When the depth changes, keys contain a different number of slots. Consumers must generate keys using total_depth_labels instead of assuming six positions. The original horizontal Lookup is now Lookup: Compact. Consumers of the old horizontal format should use that renamed tab.
Install / use
- Replace
Code.gsin your existing Gmail Labels Sync Apps Script project and addSyncProgress.htmlas a new HTML file. The spreadsheet ID is unchanged. - Run Gmail Labels → Sync: Labels to discover the current hierarchy depth and generate all outputs. For later changes, run the same command or a time trigger (
syncGmailLabelsScheduled). - Use Gmail Labels → Sync: Lookup from Current Labels to regenerate outputs from existing Labels without making a Gmail API call. Sync: Labels gets the current labels from Gmail, updates
Labels, then regenerates all lookup tabs and Settings. Sync: Lookup from Current Labels reads theLabelstab as-is and updates only the lookups and Settings. - Repair: All Sheets first displays a YES/NO confirmation explaining the scope; on approval it repairs generated sheet headers, merged Options headers, blue header styles, widths, freezes, Settings label/combination/depth/option-count formulas and depth named range, tab order and hidden-tab state without fetching Gmail or clearing Labels or saved compact options. It also normalizes older
Lookup: Compactrows to include the new blank choice (without adding duplicates), then recreates/re-aligns the grouped and depth tabs from those saved options. It uses the sync write lock and skips repair if a sync is running. Reset: All Sheets clears Labels and lookup values but retains current declared depth.
Column widths (pixels)
| Sheet | Column A | Remaining generated columns |
|---|---|---|
| Labels | Full Path: 400 | Folder columns: 300 |
| Lookup | Key: 400 | Depth: 200; pipe-delimited Options: 1000 |
| Lookup: Compact | Key: 400 | Folder-option columns: 300 |
| All per-depth lookup tabs | Key: 400 | Every option: 300 |
| Settings | Type: 150 | Key: 200; Value: 300 |
Widths are re-applied by the respective sync/formatting functions. Settings is A:C, starting at 20 rows (growing for very deep hierarchies). Repair: All Sheets corrects generated header titles and all generated Settings totals and per-level lookup option-count formulas without re-fetching Gmail; it can recreate missing aligned depth tabs from saved Lookup: Compact data.
Menu navigation and progress
All four custom-menu commands now activate each tab while processing it. Generated Lookup: sheets are temporarily shown for menu-driven processing, then hidden again; the three main tabs (Labels, Lookup, Settings) remain visible. Scheduled syncs do not reveal generated tabs unless tab ordering needs a temporary unhide, and finish with them hidden. Sync: Labels, Repair: All Sheets, and Reset: All Sheets return to Labels at completion, while Sync: Lookup from Current Labels returns to Lookup, including after errors when possible. Original rebuildLabelLookups, formatSheet, and resetSheet function entry points are retained, and their direct/trigger invocation skips the new tab-switching behavior.
Sync: Labels displays SyncProgress.html automatically. The dialog starts the sync and polls progress from CacheService, including stages, current sheet, an approximate progress bar, summary, and copyable detailed errors. If another sync is running, the popup attaches to that active run (including a scheduled Gmail sync or a Lookup rebuild) and shows its progress rather than displaying the "already running" error. The shared active-run pointer and cooperative Restart requests use PropertiesService (no extra worksheet). Progress stays in temporary Script Cache and is not written to Settings.
The popup buttons are ordered Restart, then Close. Close only dismisses the dialog; it does not terminate the server execution. Restart marks the observed existing run for cooperative cancellation, waits until its lock has actually been released, and starts a new Sync: Labels run. Restart can also be used after a completed/failed run. When an execution is inside an uninterruptible Gmail/Sheets service call, it must finish that call (or time out) before the cancellation checkpoint takes effect. Status and cancellation are scoped by unique run ID to avoid races between dialogs, and a stale active marker is cleared if no lock is held. The scheduled sync path remains headless and never changes the active tab, though it now publishes attachable status.
Note: Restart always runs Sync: Labels (refresh Gmail + rebuild lookup sheets). If the old operation was Sync Lookup Table, Restart switches to the full Gmail sync. If you need only a Lookup rebuild, continue to use its menu command.
Runtime note: This modal does not extend Google Apps Script’s execution limit; a single server sync can still time out if it runs longer than the account’s execution limit. Progress percentages represent stages, not a calculation of exact remaining time.
Successful sync, lookup rebuild, formatting, and reset reorder sheets as Labels, Lookup, Lookup: Compact and then the depth tabs, each in ascending level order (hidden), then any other non-generated sheets, with Settings always last. Lookup: Compact and the generated depth tabs are hidden; manually created tabs are left alone. The active visible sheet is restored afterward (or Labels if the previously active tab was a generated depth tab). The hidden sheets remain available for XLOOKUP/IMPORTRANGE and can be manually unhidden, but the next sync/format/reset hides them again. The Settings formatter keeps exactly three columns and at least 20 rows; it grows beyond 20 when the number of generated settings requires it. Non-generated settings beyond row 20 are rejected to avoid silent loss.
Both sync entry points perform incremental reconciliation; unchanged cells are not rewritten. Lookup: Compact uses key-based reconciliation with native sorting after key membership changes. The grouped Lookup never sorts the blank continuation rows. Each aligned depth tab follows the Lookup: Compact key order, even after a manual re-sort. Existing Lookup - ... legacy-generated tabs and excess Lookup: ... depth tabs and obsolete (Full) names are removed only after a successful lookup write and spreadsheet flush.
Limits and safeguards
The number of tabs is data-driven, but Google Sheets' 10-million-cell workbook limit still applies. Retaining every key in each aligned depth tab increases workbook cells, especially with wide option lists; the added blank option requires one extra option column for each populated level, and the preflight capacity check accounts for it before changing lookup sheets. The wildcard algorithm generates a subset of each path's selected levels (2^d combinations for a path of depth d); unreasonably large combinations are rejected with a clear error rather than silently truncate paths. The | and / delimiters remain reserved within folder names.
Tests
Run npm test with Node.js. Offline tests cover six-level compatibility, two-level shrink, eight-level growth, aligned depth-tab key/row alignment, migration of sparse and (Full) sheets, empty-option keys, missing aligned-depth-tab repair, generated settings, Settings size and dynamic depth capacity, tab ordering (including hidden sheets), generated-tab visibility, merged headers, stale-tab removal, no-op updates, menu/trigger wiring, capacity failures, attached progress, concurrent-lock denial, cooperative cancellation of active and queued executions, and safe reruns. Tests do not connect to your live spreadsheet.
See docs/LOOKUP-USAGE.md, docs/SHEET-STRUCTURE.md, and docs/QUICKSTART.md for consumer and deployment details.
Settings named ranges and lookup range text
Formatting, repairing or resetting Settings generates Range: Top Level and Range: Sub Folder N keys for each active lookup depth. Their formulas use total_combinations_labels and total_options_<level> named ranges to output a quoted A1 lookup range as text. Every Settings value has a lowercase underscore-separated named range (for example total_depth_labels, range_top_level, total_options_sub_folder_1). Names are repointed after alphabetical sorting.
Complete generated sheet-range settings
Every Format/Repair/Reset refresh generates Range: Labels, Range: Lookup, Range: Lookup: Compact, Range: Settings, plus each active Range: Top Level / Range: Sub Folder N. Each value is a quoted A1 sheet range string built with ADDRESS(...,4) from named Settings totals. Range: Labels uses total_labels+1 rows and total_depth_labels+1 columns. Range: Lookup uses total_combinations_labels*total_depth_labels+1 rows and 3 columns. Range: Lookup: Compact uses total_combinations_labels+1 rows and total_depth_labels+1 columns. Range: Settings uses total_settings+1 rows and 3 columns. Total: Settings is =COUNTA(Settings!B2:B) and has named range total_settings. All Settings keys are sorted by Type/Key and have repaired named ranges.