Creating rows as children using the API

Lucas Rayala

I've successfully created a Python script to update a sheet in Smartsheets based off a table in postgres. The postgres table updates every hour, so I have the code running every hour after the update. It works great. I have the code set up to create my dataframe, delete all the rows in the sheet, then append all the new rows from postgres, basically:

create df
clear sheet
append new rows

My table is about 5,000 rows, so it takes about 1.5 minutes to run. Mostly for practice (and a slight nudge by the database admin), I'm trying to reduce that time. One idea I was working on is to create all the new rows as children to a single parent row -- then, instead of deleting all the rows, I would just delete the parent.

What I'm trying to do now is this:

create df
clear sheet by deleting single parent row
create new header row and get ID for that row
append new rows as children to new header row 

To do this, I create a single row at the top of the page with the content "Header" in the primary column. I retrieve the row ID for that row, which I assign to the variable parent_row. I thought I could just set parent_id to parent_row when I was uploading all the new rows as follows:

    for i, rows in df.iterrows():                                      
        temp_row = ss.models.Row()                                      
        temp_row.to_bottom = True                                            
        for j, columns in enumerate(df):                                     
            temp_row.cells.append({
                'column_id': list_of_columns[j],                            
                'value': str(df.iat[i,j]),                                    
                'parent_id': parent_row
            })
for x in range(0, len(list_of_rows), chunk_interval):
        ss.Sheets.add_rows(sheet_id, list_of_rows[x:x + chunk_interval]) 

The code doesn't error out, but the newly created rows are not indented under the header row. What do I need to do to get them to indent as children to that parent row? Thanks!

Lucas Rayala

Got it! I was trying to assign the parent ID at the cell level, not the row level. Did this instead and it worked:

  for i, rows in df.iterrows():                                      
        temp_row = ss.models.Row()                                      
        temp_row.to_bottom = True
        temp_row.parent_id = parent_row                      
        for j, columns in enumerate(df):                                     
            temp_row.cells.append({
                'column_id': list_of_columns[j],                            
                'value': str(df.iat[i,j])
            })
for x in range(0, len(list_of_rows), chunk_interval):
        ss.Sheets.add_rows(sheet_id, list_of_rows[x:x + chunk_interval]) 

Lucas Rayala

Got it! I was trying to assign the parent ID at the cell level, not the row level. Did this instead and it worked:

  for i, rows in df.iterrows():                                      
        temp_row = ss.models.Row()                                      
        temp_row.to_bottom = True
        temp_row.parent_id = parent_row                      
        for j, columns in enumerate(df):                                     
            temp_row.cells.append({
                'column_id': list_of_columns[j],                            
                'value': str(df.iat[i,j])
            })
for x in range(0, len(list_of_rows), chunk_interval):
        ss.Sheets.add_rows(sheet_id, list_of_rows[x:x + chunk_interval])