Creating rows as children using the API
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!
Best Answer
-
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])
Answers
-
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])
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives