Unable to input external data to SmartSheet through API
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!
Best Answer
-
Hi @Chris Chen
If you are okay with using Smartsheet Python SDK, here is my demo code.
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
Answers
-
Hi @Chris Chen
If you are okay with using Smartsheet Python SDK, here is my demo code.
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
-
Thank you so much! The code is working. Appreciated!
-
Happy to help😁, @Chris Chen .
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