Unable to input external data to SmartSheet through API

Chris Chen

Hi community members,

I'm working on a project to fetch our students' quiz score data from Blackboard, an education platform, and send it to Smartsheet using the API. I've successfully retrieved the data from Blackboard and prepared it for transfer to Smartsheet, ensuring the data is correct and the column IDs match those on the target sheet in Smartsheet.

However, I'm encountering an issue where Smartsheet does not seem to receive any data at all, or it receives it in an unrecognizable format, leaving the target sheet blank, despite a "sending" action is conducted. I've verified that the data format is correct and all columns in my target sheet are set to "Text/Number," which should be compatible with all data formats.

I've included the code and the output with this post for reference. Any insights or suggestions would be greatly appreciated.

Thank you!

jmyzk_cloudsmart_jp

Hi @Chris Chen

If you are okay with using Smartsheet Python SDK, here is my demo code.

https://pypi.org/project/smartsheet-python-sdk/

import smartsheet
import os

# Replace with your API token
my_token = os.environ.get("a_token")
smartsheet_client = smartsheet.Smartsheet(my_token)

# Get the sheet
sheet_id = 8253396163186564
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)

# Retrieve column IDs for "ID" and "Score"
id_column_id = next((column.id for column in sheet.columns if column.title == "ID"),None)
score_column_id = next((column.id for column in sheet.columns if column.title == "Score"),None)
print(id_column_id, score_column_id)

# List of grades to add
grade_list = [
    {'userID': "_168_1",
     'score': 94.8
    },
    {'userID': "_168_2",
     'score': 95.7
    },
    {'userID': "_168_3",
     'score': 93.9
    },
]

# Create rows to add to the sheet
rows = []
for grade in  grade_list:
    student_id = grade['userID']
    score = grade['score']
    print(student_id, score)
    new_row = smartsheet.models.Row()
    new_row.to_bottom = True
    new_row.cells.append({
        'column_id': id_column_id,
        'value': student_id
    })
    new_row.cells.append({
        'column_id': score_column_id,
        'value': score
    })
    rows.append(new_row)


# Add rows to the sheet
try:
    response = smartsheet_client.Sheets.add_rows(sheet_id, rows)
    print(f"Rows added successfully: {response}")
except smartsheet.exceptions.SmartsheetException as e:
    print(f"Error adding rows: {e}")
    raise

The sheet with sheet_id: 8253396163186564

jmyzk_cloudsmart_jp

Hi @Chris Chen

If you are okay with using Smartsheet Python SDK, here is my demo code.

https://pypi.org/project/smartsheet-python-sdk/

import smartsheet
import os

# Replace with your API token
my_token = os.environ.get("a_token")
smartsheet_client = smartsheet.Smartsheet(my_token)

# Get the sheet
sheet_id = 8253396163186564
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)

# Retrieve column IDs for "ID" and "Score"
id_column_id = next((column.id for column in sheet.columns if column.title == "ID"),None)
score_column_id = next((column.id for column in sheet.columns if column.title == "Score"),None)
print(id_column_id, score_column_id)

# List of grades to add
grade_list = [
    {'userID': "_168_1",
     'score': 94.8
    },
    {'userID': "_168_2",
     'score': 95.7
    },
    {'userID': "_168_3",
     'score': 93.9
    },
]

# Create rows to add to the sheet
rows = []
for grade in  grade_list:
    student_id = grade['userID']
    score = grade['score']
    print(student_id, score)
    new_row = smartsheet.models.Row()
    new_row.to_bottom = True
    new_row.cells.append({
        'column_id': id_column_id,
        'value': student_id
    })
    new_row.cells.append({
        'column_id': score_column_id,
        'value': score
    })
    rows.append(new_row)


# Add rows to the sheet
try:
    response = smartsheet_client.Sheets.add_rows(sheet_id, rows)
    print(f"Rows added successfully: {response}")
except smartsheet.exceptions.SmartsheetException as e:
    print(f"Error adding rows: {e}")
    raise

The sheet with sheet_id: 8253396163186564

Chris Chen

Thank you so much! The code is working. Appreciated!

jmyzk_cloudsmart_jp

Happy to help😁, @Chris Chen .