Problem Uploading Data using API

Bobert

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)

Bobert

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

Lee Joramo

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)`