Attachment to Main Sheet Utility

This automation copies row-level attachments from one Smartsheet (input sheet) to another Smartsheet (output sheet), based on a configurable match key.

**The config/setup sheet is the primary control point for this automation.** Everything the script does — which sheets are involved, how rows are matched, and whether a pairing runs at all — is driven entirely by config rows. 

Each enabled config row tells the script:

1. which input sheet to read,

2. which output sheet to write to,

3. which input column value to use for matching,

4. which output column value to match against,

5. and (optionally) where to stamp a Last Updated timestamp.

Config/setup sheet:

The control sheet containing Input Sheet ID, Output Sheet ID, match-column names, and Enabled flags.

Current production config sheet: https://app.smartsheet.com/sheets/J7p4P3PG6jJqwqjJc7X6MRRhvhgX3WwmFqxp6Wh1

Input sheet:

The source sheet where attachments currently exist.

Output sheet:

The destination sheet where missing attachments will be uploaded.

Input match column:

Column on the input sheet whose value is used as the lookup key.

Output match column:

Column on the output sheet whose value must match the input key.

The config sheet must contain these columns exactly:

Enabled logic:

- anything else -> skipped

One config row represents one mapping pair:

- one input sheet

- one output sheet

- one input match column

- one output match column

If you need to process three different sheet pairs, create three separate config rows.

1. Duplicate a known-good config row when possible.

2. Replace only the five required values.

3. Set `Enabled` to `FALSE` while validating IDs and column names.

4. Save the row.

5. Run the script and review the logs for that specific row.

6. Change `Enabled` to `TRUE` once the row's matching behavior looks correct in the logs.

- Must be a valid Smartsheet sheet ID.

- Keep as plain numeric text; do not include links, commas, or notes.

- Must be a valid Smartsheet sheet ID.

- Must be the destination sheet that should receive missing attachments.

- Must exactly match a column title that exists on the input sheet.

- Avoid extra spaces before or after the name.

- Must exactly match a column title that exists on the output sheet.

- Should represent the same business key as the intake comparison column.

- Use `TRUE` or `1` to enable processing.

- Use `FALSE`, `0`, or blank to disable.

`Last Updated` (optional)

- Leave blank; the script stamps this automatically during a live run.

- Do not use this value as a control signal.

1. Input and output sheet IDs open the intended sheets.

2. Both comparison columns exist and are spelled exactly as sheet headers.

3. Comparison columns contain matching values for expected rows.

4. Output comparison key is unique per row in output sheet.

5.`Enabled` is intentionally set.

6. Logs reviewed for this row and matching behavior confirmed correct.

For updates to an existing config row:

1. Set `Enabled` to `FALSE`.

2. Edit IDs/column names.

3. Run the script and confirm logs show expected row matching for that row.

4. Re-enable with `TRUE`.

- Use stable key columns for matching (project ID, record ID, intake ID).

- Do not use free-text columns likely to change.

- Avoid output keys with duplicates; duplicates create ambiguous targets.

- If keys have formatting differences, normalize values at the sheet level first.

Do not:

- paste whole Smartsheet URLs into ID columns

- rename comparison columns in sheets without updating config

- enable brand-new rows without reviewing a validation run first

`DRY_RUN`:

-`True`: no uploads, no Last Updated writes; logs only

-`False`: live uploads and Last Updated writes

Timeout safeguard:

- Script force-exits after 90 minutes.

For each config row:

1. Load input and output sheets.

2. Read input row key from `Intake Comparison Column`.

3. Find output row where `Output Comparison Column` has the same value.

4. Copy only missing attachments from that input row to that output row.

If no output row matches, that input row is skipped and logged.

Attachments are considered duplicates by filename only on the destination row.

- Same filename already exists on output row -> skipped

- Filename not present -> uploaded

Before upload, each missing attachment is evaluated:

Images:

- Re-encoded to JPEG at `quality=75` with `optimize=True`

- If recompressed bytes are not smaller than original, original is kept

PDFs:

- If PDF has extractable text -> compression skipped

- If image-only PDF -> lossless stream compression attempted via `pikepdf`

- If compressed PDF is not smaller -> original is kept

Other file types:

- Uploaded as-is

Log file:

Rotation:

- 5 MB per file

- 5 files total (1 active + 4 backups)

Problem: duplicate output match key

Meaning:

Multiple output rows share the same value in the configured output match column. Script currently warns and uses the last row encountered.

Problem: input match value missing/blank

Meaning:

Input row is skipped because there is no usable key for matching.

Problem: no output row matched

Meaning:

No output row had an exact key match for that input row.

Problem: image already optimally compressed

Meaning:

Recompressing did not reduce size, so original is uploaded.

Problem: PDF contains text, skipping compression

Meaning:

Text PDFs are intentionally not recompressed to avoid quality/structure risk.

1. Bulk attachment listing per sheet reduces API call count.

2. Output sheet and output attachment caches prevent repeated fetches.

3. Per-row/per-sheet exception handling prevents one failure from stopping the run.

4. Config Last Updated can track per-pair attempts (when not in DRY_RUN).

5. 90-minute hard timeout prevents endless hangs.

Problem: no config rows processed

Cause:

No enabled rows, missing required config columns, or invalid sheet IDs.

Fix:

Verify `Enabled`, column names, and IDs in the config sheet.

Problem: nothing uploaded even though rows exist

Cause:

Attachment names already exist on destination rows.

Fix:

Review logs for skipped duplicates; rename/clear destination attachment if overwrite is needed.

Problem: large files still appear after run

Cause:

Expected for text PDFs and already-optimized images.

Fix:

This is normal based on current compression rules.

Problem: script exited unexpectedly

Cause:

Fatal exception or 90-minute timeout.

Fix:

Review log tail and stack trace in `attachment_to_main_sheet.log`.