英文:
Smartsheet API Python update columns 4004 error
问题
我最近一直在使用Python为客户开发一些自动化功能。客户有新的活动，他们希望将这些新活动动态添加到一个用于跟踪活动的工作表的下拉列中。我已经开发了一个脚本来完成这个任务，它可以正常工作。脚本从一个工作表中提取数据（客户通过表单发送新的活动名称），然后更新另一个工作表中的下拉列表。
我的问题是，我偶尔会遇到500 4004错误，但我不知道原因。我可以在Jupyter Notebook中一整天运行这个脚本而不会出现问题。然而，当我将脚本设置为cron定时运行时，就会出现错误。我不确定这会如何改变代码执行和授权令牌的使用。感谢任何帮助或见解。
脚本：
import smartsheetimport os# 将访问令牌设置为操作系统环境变量os.environ['SMARTSHEET_ACCESS_TOKEN'] = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'# 初始化Smartsheet模块smart = smartsheet.Smartsheet()# 获取将接收新下拉选项列表的主工作表# 定义工作表ID############################## 插入下面的工作表ID #################################main_sheet = smart.Sheets.get_sheet(XXXXXXXXXXXXXXXX)sheet_id = main_sheet.id# 定义主工作表的下拉列号# 插入从零开始的列号 #################################pick_col = 6# 定义下拉列的列IDcol = main_sheet.columns[pick_col].id#### 从单独的选项工作表中提取选项### 获取包含新下拉选项列表的列的工作表，用于FOR循环范围opt_sheet = smart.Sheets.get_sheet(XXXXXXXXXXXXXXXX)opt_ID = opt_sheet.idusda_opt = smart.Sheets.get_sheet(opt_ID, column_ids=opt_sheet.columns[0].id)# 定义用于FOR循环范围的行数变量num_rows1 = usda_opt.total_row_count# 获取包含下拉选项列表的列中的所有值。# 检查值是否为None类型，如果是，则中断for循环。这确保了所有最近添加的值都包括在其中，直到随后的空单元格。# 注意：根据您的工作表，更改单元格下标数字以正确的从零开始的单元格编号new_options = []for i in range(num_rows1):if opt_sheet.rows[i].cells[0].value == None:passelse:new_options.append(opt_sheet.rows[i].cells[0].value)# 使用新列选项和标题构建列对象。# 索引是从左到右计算的下拉列的位置（从零开始）col_list = smart.models.Column({'title': main_sheet.columns[pick_col].title,'type': 'PICKLIST','options': new_options,'index': pick_col})# 将列对象发送以更新列下拉选项updated_col = smart.Sheets.update_column(sheet_id, col, col_list)
该代码使用update_column而不是update_rows，所以我不确定它为什么会偶尔抛出错误。我已经调整了cron的频率，但似乎没有明显的影响。
英文:
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 smartsheetimport os# Set access token to OS environmentos.environ['SMARTSHEET_ACCESS_TOKEN'] = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'# Initiate Smartsheet modulesmart = 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 columncol = 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 rangeopt_sheet = smart.Sheets.get_sheet(XXXXXXXXXXXXXXXX)opt_ID = opt_sheet.idusda_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 loopnum_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 SHEETnew_options = []for i in range(num_rows1):if opt_sheet.rows[i].cells[0].value == None:passelse: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 optionsupdated_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.
答案1
得分: 0
Ok，我认为我解决了这个问题。我有两个不同的Python脚本在使用相同的API令牌。我将它们合并成一个单独的脚本。到目前为止一切都很好！
英文:
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!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论