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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    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

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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭