Help creating Python script to Copy Cell Refence from one sheet to multiple sheets (Sheet IDs) in a text file

heyjay

I need help copying Customer_ID sheet reference from Sheet A to multiple sheets, Sheets B-Z.

Im thinking of using Site IDs stored in text file as my target. I've done similar task (Copying rows using sheet ID to multiple sheets in a text file), but couldnt figure this one out.

For context, Im just using Smartsheets AI in the API documentation to help build my script.

I would like to use Native smartsheet functionalities and not a third party tool that requires payment and installation as we are already a paying customer.

I'm using some basic prompt like ' I need help copying sheet reference name from one sheet to multiple sheets using python. Here is the source sheet ID = xxxxxx , and the list of the target sheets are saved in 'target_sheet.txt'

We are trying to copy 2 sheet reference to 3,000 + sheets. And this might be happen multiple times in the future as we discover more items to add to the 3k sheets.

heyjay

Solved. Incase any one needs the code.

on the same folder, create a text file with the file name destination_ids.txt and paste the target sheets IDs.

"""

add_sheet_references.py

-----------------------

Creates two cross-sheet references (Trxn and Subs) on every sheet ID

listed in destination_ids.txt.


Usage:

    pip install smartsheet-python-sdk

    SMARTSHEET_TOKEN=<your_token> python add_sheet_references.py


Or set the token directly in the SMARTSHEET_TOKEN variable below.

"""


import os

import smartsheet


# ─── Configuration ────────────────────────────────────────────────────────────


SMARTSHEET_TOKEN = '1234yourtoken567'


# Source sheet ID and reference definitions

# If Trxn and Subs point to different source sheets, update source_sheet_id per entry.

REFERENCES = [

    {

        "name": "ref_name_1",

        "source_sheet_id": sheet_ID,

        # Optional: restrict to a specific column range. Set both or leave both None (= entire sheet).

        "start_column_id": 1234ColumnID4567,

        "end_column_id": 1234ColumnID4567,

    },

    {

        "name": "ref_name_2",

        "source_sheet_id": sheet_ID,  # ← change if Subs lives on a different sheet

        "start_column_id": 1234ColumnID4567,

        "end_column_id": 1234ColumnID4567,

    },

]


DESTINATION_IDS_FILE = "destination_ids.txt"


# ─── Helpers ──────────────────────────────────────────────────────────────────


def load_destination_ids(filepath: str) -> list:

    """Read sheet IDs (one per line) from a text file."""

    ids = []

    with open(filepath) as f:

        for line in f:

            line = line.strip()

            if line and not line.startswith("#"):

                ids.append(int(line))

    return ids



def get_existing_reference_names(smart: smartsheet.Smartsheet, sheet_id: int) -> set:

    """Return the set of cross-sheet reference names already on a sheet."""

    try:

        resp = smart.Sheets.list_cross_sheet_references(sheet_id)

        return {ref.name for ref in (resp.data or [])}

    except Exception as exc:

        print(f"    ⚠ Could not list references for sheet {sheet_id}: {exc}")

        return set()



def create_reference(smart: smartsheet.Smartsheet, target_sheet_id: int, ref_cfg: dict):

    """Create a single CrossSheetReference on the target sheet."""

    # CrossSheetReference must be initialised with a dict (not by attribute assignment)

    payload = {

        "name": ref_cfg["name"],

        "source_sheet_id": ref_cfg["source_sheet_id"],

    }

    if ref_cfg.get("start_column_id"):

        payload["start_column_id"] = ref_cfg["start_column_id"]

    if ref_cfg.get("end_column_id"):

        payload["end_column_id"] = ref_cfg["end_column_id"]


    xsr = smartsheet.models.CrossSheetReference(payload)

    return smart.Sheets.create_cross_sheet_reference(target_sheet_id, xsr)



# ─── Main ─────────────────────────────────────────────────────────────────────


def main():

    # Use smart.Sheets (capital S) — the correct SDK accessor

    smart = smartsheet.Smartsheet(SMARTSHEET_TOKEN)

    smart.errors_as_exceptions(True)


    destination_ids = load_destination_ids(DESTINATION_IDS_FILE)

    print(f"Loaded {len(destination_ids)} destination sheet(s) from '{DESTINATION_IDS_FILE}'")

    print(f"References to add: {[r['name'] for r in REFERENCES]}\n")


    success_count = 0

    skip_count = 0

    error_count = 0


    for sheet_id in destination_ids:

        print(f"Sheet {sheet_id}:")

        existing = get_existing_reference_names(smart, sheet_id)


        for ref_cfg in REFERENCES:

            ref_name = ref_cfg["name"]


            if ref_name in existing:

                print(f"  ↳ '{ref_name}' already exists — skipping.")

                skip_count += 1

                continue


            try:

                result = create_reference(smart, sheet_id, ref_cfg)

                print(f"  ✓ '{ref_name}' created (id={result.result.id})")

                success_count += 1

            except Exception as exc:

                print(f"  ✗ '{ref_name}' FAILED: {exc}")

                error_count += 1


    print(f"\nDone. Created: {success_count} | Skipped: {skip_count} | Errors: {error_count}")



if __name__ == "__main__":

    main()