Problem Uploading Data using API
I am able to pull the data from reports and sheets with little trouble, however I am currently trying to pull the data from a report and then upload it to a sheet. I pull the report and save it as a dataframe with no trouble, however when uploading to the sheet, I keep getting errors. I simplified things by just trying to upload a sample dataframe to a sheet that has no data. I want the script to create the columns if needed or add to existing columns if they exist. I keep getting errors. Could you take a look at this and hopefully give me an idea of where I am going wrong.
import smartsheet
import pandas as pd
# Smartsheet API Key
api_key = 'API Token'
# Smartsheet Sheet ID
sheet_id = 'Sheet ID'
# Initialize Smartsheet API client
smartsheet_client = smartsheet.Smartsheet(api_key)
def get_column_id(sheet, column_name):
for column in sheet.columns:
if column.title == column_name:
return column.id
return None
def create_columns(sheet, column_names):
columns_to_add = []
for column_name in column_names:
column_model = smartsheet.models.Column({'title': column_name, 'type': 'TEXT_NUMBER'})
columns_to_add.append(column_model)
try:
response = smartsheet_client.Sheets.add_columns(sheet_id, columns_to_add)
return [column.id for column in response.result]
except smartsheet.exceptions.ApiError as e:
error_message = e.message if hasattr(e, 'message') else str(e)
print(f"Error creating columns '{', '.join(column_names)}': {error_message}")
return []
def upload_dataframe_to_smartsheet(dataframe):
# Load data into a Smartsheet sheet
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)
# Iterate through columns in the DataFrame
for column_name in dataframe.columns:
column_id = get_column_id(sheet, column_name)
# Check if the column exists
if column_id is None:
# If the column does not exist, create it
column_ids = create_columns(sheet, [column_name])
if not column_ids:
# If column creation failed, skip to the next column
continue
# Use the first column ID from the created columns
column_id = column_ids[0]
# Get the column index in the sheet
column_index = sheet.get_column_index(column_id)
# Extract column data from the DataFrame
column_data = list(dataframe[column_name])
# Update the column in the Smartsheet sheet
smartsheet_client.Sheets.update_cells(
sheet_id,
smartsheet.models.CellMapping({
'column_id': column_id,
'row_id': 0, # Assuming data starts from the first row
'value': column_data
})
)
if __name__ == '__main__':
# Sample DataFrame
sample_data = {'Numbers': [1, 2, 3], 'Letters': ['A', 'B', 'C']}
df = pd.DataFrame(sample_data)
# Upload DataFrame to Smartsheet
upload_dataframe_to_smartsheet(df)
Answers
-
This is the error I am getting
Traceback (most recent call last):
File "W:/Coding/Python Code/Report Extraction and Reupload/Report_Upload_to_Sheet1.py", line 76, in <module>
upload_dataframe_to_smartsheet(df)
File "W:/Coding/Python Code/Report Extraction and Reupload/Report_Upload_to_Sheet1.py", line 45, in upload_dataframe_to_smartsheet
column_ids = create_columns(sheet, [column_name])
File "W:/Coding/Python Code/Report Extraction and Reupload/Report_Upload_to_Sheet1.py", line 28, in create_columns
return [column.id for column in response.result]
TypeError: 'ErrorResult' object is not iterable
-
I have used Python Panda's DataFrames to Export/Import Smartsheet data. We will need to see the Python code to help. At the very least the code for the function `upload_dataframe_to_smartsheet(df)`
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives