This solution updates a column formula across multiple sheets in a workspace. The script finds all sheets with a certain string in the name, finds a certain column by name on those sheets, and then updates all column formulas to a new column formula. At the time of posting, Control Center Global Updates did not allow me to update column formulas using either Update Column or Find / Replace. With this script I was able to quickly update column formulas in hundreds of sheets across hundreds of projects provisioned via Control Center with minimal effort.
Complexity
Moderate
Control Center limitations
I tried these Global Update options by was not able to update the Column Formula.
- Modify Existing Column
- Did not allow me to update the Column Formula
- Find / Replace
- Gave this error when it tried to update a column formula
- Code: 1302 - Message: You cannot edit cells with Column Formula for column {0}
Requirements
- Admin access to the Workspace.
- Smartsheet API access token
- API token stored in Windows Credential Manager with the API token in the password field
- Python
- Python libraries installed using pip install
- Smartsheet Python SDK
- keyring
- To access Windows Credential Manager
How to use
I used Visual Studio Code to run and Copilot to help debug.
- Get the required components.
- Copy the code below and save it to a .PY file
- Adjust the value in these sections
- "Configuration"
- "Test controls"
- Set "Dry run" = True
- Shows the expected changes but does not make them.
- "Max folders" = a small number
- Will test only these first number of folders in the Workspace
- Test run
- Adjust script as needed
I hope this is helpful for you.
Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology
"""
===============================================================================
SMARTSHEET BULK COLUMN FORMULA UPDATE SCRIPT
===============================================================================
By
Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology
Jun 19 2026
SUMMARY
-------
This script updates the column formula for a specific column across multiple
Smartsheet sheets within a given workspace.
It:
1. Scans a workspace and all subfolders
2. Identifies sheets whose names contain a specified string
3. Finds a target column within those sheets
4. Updates the column formula (optionally via DRY RUN)
5. Executes updates in parallel using threading
6. Logs all actions and results
USE CASES
---------
- Standardizing formulas across many sheets
- Fixing broken or outdated column formulas
IMPORTANT NOTES
---------------
- Column formulas overwrite ALL existing cell formulas in that column.
- Ensure your formula is valid in Smartsheet syntax before running.
- Run with DRY_RUN = True first to verify results.
===============================================================================
"""
import smartsheet
import logging
import os
import sys
import keyring
from datetime import datetime
from concurrent.futures import ThreadPoolExecutor, as_completed
from threading import Lock
# Thread lock for safe shared counter updates
lock = Lock()
# CONFIGURATION
# Retrieve API token securely from Windows Credential Manager
# NOTE: This assumes the credential exists and is accessible via keyring and token in in
# the password field.
ACCESS_TOKEN = keyring.get_password("Your windows credential name", None)
# Workspace ID where sheets reside
WORKSPACE_ID = 1123456678
# Name of the column whose formula will be updated
TARGET_COLUMN_NAME = "NAME OF THE COLUMN WITH COLUMN FORMULA THAT YOU WANT TO CHANGE"
# New column formula to apply
NEW_FORMULA = '=Your new formula'
# Filter string used to identify relevant sheets
# (case-insensitive match)
SHEET_NAME_STRING = "string to find" #"String to look for in sheet names"
# Threading configuration
MAX_WORKERS = 5 # Avoid high values to prevent API throttling
# TEST CONTROLS
# If True → logs actions without making changes
# If False → applies updates
DRY_RUN = True
# Limit number of folders processed (for testing)
# Set to None to process entire workspace
MAX_FOLDERS = 5
# LOGGING SETUP
# Create timestamped log file
log_filename = os.path.join(
os.path.dirname(os.path.abspath(__file__)),
f"smartsheet_col_formula_update_log_{datetime.now().strftime('%Y%m%d_%H%M%S')}.log"
)
# Configure logging
logger = logging.getLogger()
logger.setLevel(logging.INFO)
file_handler = logging.FileHandler(log_filename, encoding="utf-8")
console_handler = logging.StreamHandler(sys.stdout)
formatter = logging.Formatter("%(asctime)s | %(levelname)s | %(message)s")
file_handler.setFormatter(formatter)
console_handler.setFormatter(formatter)
logger.addHandler(file_handler)
logger.addHandler(console_handler)
logging.info("===== Script Started =====")
logging.info(f"Log file: {log_filename}")
# INITIALIZE CLIENT
# Initialize Smartsheet client
client = smartsheet.Smartsheet(ACCESS_TOKEN)
client.errors_as_exceptions(True)
# Suppress verbose SDK logging
logging.getLogger("smartsheet").setLevel(logging.WARNING)
# HELPER FUNCTIONS
def get_column(sheet_id, column_name):
"""
Retrieves a column object from a sheet by name.
Parameters:
sheet_id (int): ID of the sheet
column_name (str): Name of the column
Returns:
Column object or None if not found
"""
sheet = client.Sheets.get_sheet(sheet_id)
for col in sheet.columns:
if col.title.strip().lower() == column_name.lower():
return col
return None
def update_column_formula(sheet_id, column_id, formula):
"""
Updates the column formula for a given column.
Parameters:
sheet_id (int): Sheet ID
column_id (int): Column ID
formula (str): New formula string
Returns:
API response
"""
column = smartsheet.models.Column({
"formula": formula
})
return client.Sheets.update_column(sheet_id, column_id, column)
def process_sheet(name, sheet_id):
"""
Processes a single sheet:
- Finds the target column
- Updates the column formula if needed
Returns:
"updated", "skipped", or "failed"
"""
try:
logging.info(f"[START] {name}")
# Locate column
column = get_column(sheet_id, TARGET_COLUMN_NAME)
if not column:
logging.warning(f"[SKIP] Column not found in '{name}'")
return "skipped"
# Skip if already correct
if column.formula == NEW_FORMULA:
logging.info(f"[SKIP] Already up to date: {name}")
return "skipped"
# Dry run mode
if DRY_RUN:
logging.info(f"[DRY RUN] Would update formula in '{name}'")
return "skipped"
# Perform update
update_column_formula(sheet_id, column.id, NEW_FORMULA)
logging.info(f"[UPDATED] {name}")
return "updated"
except Exception as e:
logging.error(f"[FAILED] {name} — {str(e)}")
return "failed"
# WORKSPACE TRAVERSAL
target_sheets = []
def traverse_folder(folder_id):
"""
Recursively traverses folders to find sheets matching criteria.
"""
local_matches = []
last_key = None
while True:
response = client.Folders.get_folder_children(
folder_id,
children_resource_types=["folders", "sheets"],
last_key=last_key
)
subfolders = []
for child in response.data:
name = getattr(child, "name", "")
cid = getattr(child, "id", None)
if not cid:
continue
if isinstance(child, smartsheet.models.Folder):
subfolders.append(cid)
else:
if SHEET_NAME_STRING.lower() in name.lower():
local_matches.append((name, cid))
# Process subfolders in parallel
if subfolders:
with ThreadPoolExecutor(max_workers=5) as executor:
futures = [executor.submit(traverse_folder, fid) for fid in subfolders]
for future in as_completed(futures):
local_matches.extend(future.result())
last_key = getattr(response, "last_key", None)
if not last_key:
break
return local_matches
# STEP 1: COLLECT TARGET SHEETS
logging.info("\nScanning workspace...\n")
last_key = None
folder_count = 0
while True:
response = client.Workspaces.get_workspace_children(
WORKSPACE_ID,
children_resource_types=["folders"],
last_key=last_key
)
for child in response.data:
if not isinstance(child, smartsheet.models.Folder):
continue
if MAX_FOLDERS is not None and folder_count >= MAX_FOLDERS:
break
folder_count += 1
logging.info(f"[TEST] Processing folder {folder_count}: {child.name}")
results = traverse_folder(child.id)
with lock:
target_sheets.extend(results)
if MAX_FOLDERS is not None and folder_count >= MAX_FOLDERS:
break
last_key = getattr(response, "last_key", None)
if not last_key:
break
if SHEET_NAME_STRING:
logging.info(
f"\n[INFO] Total sheets found matching '{SHEET_NAME_STRING}': {len(target_sheets)}"
)
else:
logging.info(
f"\n[INFO] Total sheets found (no name filter applied): {len(target_sheets)}"
)
if not target_sheets:
logging.warning("No sheets found. Exiting.")
sys.exit()
# STEP 2: UPDATE COLUMN FORMULAS
total_sheets = len(target_sheets)
processed_count = 0
updated_count = 0
skipped_count = 0
failed_count = 0
logging.info(f"\nStarting parallel processing with {MAX_WORKERS} workers...\n")
with ThreadPoolExecutor(max_workers=MAX_WORKERS) as executor:
futures = {
executor.submit(process_sheet, name, sid): name
for name, sid in target_sheets
}
for future in as_completed(futures):
result = future.result()
processed_count += 1
if result == "updated":
updated_count += 1
elif result == "skipped":
skipped_count += 1
else:
failed_count += 1
logging.info(f"[PROGRESS] {processed_count}/{total_sheets}")
# =========================
# SUMMARY
# =========================
logging.info("\n===== SUMMARY =====")
logging.info(f"Total sheets processed: {processed_count}")
logging.info(f"Updated: {updated_count}")
logging.info(f"Skipped: {skipped_count}")
logging.info(f"Failed: {failed_count}")
logging.info("===================")
logging.info("\n===== Script Finished =====")
