Pandas DataFrame to Smartsheet sheet

Question

I have figured out how to add new data to a Smartsheet sheet in bulk, but only with a single column data set. The closest that I have gotten to pushing multiple columns in bulk to a sheet using a for loop, it pushes the values to the correct columns, but each value is a new row. I need the loop to run across all columns for a row, building up the row each time, then moving to the next row. I feel like I am so close but don't know what to do now.

key = 'super-secret-key'
smart = smartsheet.Smartsheet(key)

# test sheet
sheet_id = 111111111111
sheet = smart.Sheets.get_sheet(sheet_id)

# translate column names to column id
column_map = {}
for column in sheet.columns:
    column_map[column.title] = column.id

df = pd.DataFrame({'item_id': [111111, 222222],
                    'item_color': ['red', 'yellow'],
                    'item_location': ['office', 'kitchen']})

data_dict = df.to_dict('index')

rowsToAdd = []
for i, i in data_dict.items():
    for k, v in i.items():
        def res(k, v): 
            # Build new cell value
            new_cell = smart.models.Cell()
            new_cell.column_id = column_map[k]
            new_cell.value = v

            # Build the row to update
            new_row = smart.models.Row()
            new_row.to_top = True
            new_row.cells.append(new_cell)
            return new_row
        rowsToAdd.append(res(k, v))

result = smart.Sheets.add_rows(sheet_id, rowsToAdd)

Result:

Answer 1

Your code (specifically, the code within the nested for loops) is creating a new row object each time it reads a key/value pair from the data -- thereby causing the result you're seeing (i.e., one row in Smartsheet for each key/value pair in the data).

Instead, you only want to create a new row in Smartsheet once you've added data for all (three) key/value pairs to the row object. The following code should give the result you're after.

# goal is to create a row for each object in data_dict
for i, i in data_dict.items():

    # create a new row object
    new_row = smart.models.Row()
    new_row.to_top = True

    # for each key value pair, create & add a cell to the row object
    for k, v in i.items():

        # create the cell object and populate with value
        new_cell = smart.models.Cell()
        new_cell.column_id = column_map[k]
        new_cell.value = v

        # add the cell object to the row object
        new_row.cells.append(new_cell)

    # add the row object to the collection of rows
    rowsToAdd.append(new_row)

# add the collection of rows to the sheet in Smartsheet
result = smart.Sheets.add_rows(sheet_id, rowsToAdd)