I've recently been developing some automation for a client using Python. The client has new events they put on and wants those new events dynamically added to a drop down column in a sheet for tracking events. I've developed a script to do just that, and it works. The script pulls data from one sheet (client sends new event names via a form) and then updates the drop down in another sheet.
My issue is that I get intermittent 500 4004 errors and I'm not sure why. I can run this all day through Jupyter Notebook with no issue. However, when I set the script up to run as a cron I get the error. Not sure what that would change in the code execution and auth token usage. Any help or insight is appreciated.
Script:
import smartsheet
import os
# Set access token to OS environment
os.environ['SMARTSHEET_ACCESS_TOKEN'] = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
# Initiate Smartsheet module
smart = smartsheet.Smartsheet()
# Get the main sheet which will receive the new dropdown option list
# Define the sheet ID
############################## INSERT SHEET ID BELOW #################################
main_sheet = smart.Sheets.get_sheet(XXXXXXXXXXXXXXXX)
sheet_id = main_sheet.id
# Define the dropdown column number for main sheet
# INSERT ZERO-BASED COLUMN NUMBERS BELOW #################################
pick_col = 6
# Define the column ID of the dropdown column
col = main_sheet.columns[pick_col].id
##
## Pull options from separate options sheet
##
# Get the sheet containing the columns with the lists of new dropdown options for FOR loop range
opt_sheet = smart.Sheets.get_sheet(XXXXXXXXXXXXXXXX)
opt_ID = opt_sheet.id
usda_opt = smart.Sheets.get_sheet(opt_ID, column_ids = opt_sheet.columns[0].id)
# Define the number of rows variable used for the range in the FOR loop
num_rows1 = usda_opt.total_row_count
# Grab all the values in the column which holds the list of dropdown options.
# Check if the value is None type and break the for-loop if it is. This ensures
# all recently-added values are included up to the subsequent empty cell.
# NOTE: CHANGE THE CELL SUBSCRIPT NUMBER TO THE CORRECT ZERO-BASED CELL NUMBER FOR YOUR SHEET
new_options = []
for i in range(num_rows1):
if opt_sheet.rows[i].cells[0].value == None:
pass
else:
new_options.append(opt_sheet.rows[i].cells[0].value)
# Build the column object with the new column options and title.
# The index is the location of the dropdown column counting from left to right (zero-based)
col_list = smart.models.Column({
'title':main_sheet.columns[pick_col].title,
'type':'PICKLIST',
'options': new_options,
'index': pick_col
})
# Send the column object off to update the column dropdown options
updated_col = smart.Sheets.update_column(sheet_id, col, col_list)
The code uses update_column
instead of update_rows
, so I'm not sure why it will intermittently throw the error it does. I've adjusted the frequency of the cron, which doesn't seem to have any noticeable effect.
Ok I think I solved the issue. I had two separate Python scripts that were utilizing the same API token. I was running them concurrently as two separate cron jobs. I combined them into a single script. So far so good!