Use data from one grid to fill master grid

lschek1

Please consider this scenario:

I'd like to update the data available in Grid 1 to a grid called Grid M. Considering that Grid M might have previous values, I'd like to overwrite them with Grid 1's data.

I prefer overwriting instead of adding or copying rows because I want to use this Grid M as a master file that copies everything that is located in Grid 1, so previous typos, changes, won't matter, only the actual data that shows Grid 1. Do note that structure is the same for both sheets.

Consider the following code:

# Grids grid1 = 6975487445624708 gridM = 4175140851345284 # Initialize get sheet properties / get columns readSheet_Grid1 = smart.Sheets.get_sheet(grid1) readColumn_Grid1 = readSheet_Grid1.get_columns().data readSheet_GridM = smart.Sheets.get_sheet(gridM) readColumn_GridM = readSheet_GridM.get_columns().data # Get Columns ID from Grid 1 columntoRead = [] for column in readColumn_Grid1: columntoRead.append(column.id) print(columntoRead)

This is the output containing column IDs within lists.

[7236841595791236, 1607342061578116, 6110941688948612, 8503502613309316, 3999902985938820, 3859141875263364, 8362741502633860, 1044392108156804]

Getting cell values

# Get values celltoRead = [] for MyRow in readSheet_Grid1.rows: for MyCell in MyRow.cells: if MyCell.column_id == columntoRead: if (MyCell.value): celltoRead.append(MyCell.value) print(MyCell)

This is the output (sample data):

{"columnId": 7236841595791236, "displayValue": "3240099", "value": 3240099.0} {"columnId": 1607342061578116, "displayValue": "James", "value": "James"} {"columnId": 6110941688948612, "displayValue": "Hamilton", "value": "Hamilton"} {"columnId": 8503502613309316, "displayValue": "Male", "value": "Male"} {"columnId": 3999902985938820, "displayValue": "197556", "value": 197556.0} {"columnId": 3859141875263364, "displayValue": "18", "value": 18.0} {"columnId": 8362741502633860, "displayValue": "Bachelor", "value": "Bachelor"} {"columnId": 1044392108156804, "displayValue": "Medic", "value": "Medic"} {"columnId": 7236841595791236, "displayValue": "9615534", "value": 9615534.0} {"columnId": 1607342061578116, "displayValue": "Miranda", "value": "Miranda"} {"columnId": 6110941688948612, "displayValue": "Montgomery", "value": "Montgomery"} {"columnId": 8503502613309316, "displayValue": "Female", "value": "Female"} {"columnId": 3999902985938820, "displayValue": "158585", "value": 158585.0} {"columnId": 3859141875263364, "displayValue": "20", "value": 20.0} {"columnId": 8362741502633860, "displayValue": "Primary", "value": "Primary"} {"columnId": 1044392108156804, "displayValue": "Historian", "value": "Historian"} etc,

Above snippet is mostly for reference. Note that this grid contains 8 columns, starting from this ColumnId 7236841595791236, and ending with this one 1044392108156804.

现在,我想乌利希期刊指南ate rows in Grid M with data from Grid 1 by usingupdate_rows(sheet_id, list_of_rows), but I can't wrap my head around mapping the necessary steps to build it.

I think there are two things I need to know to accomplish this task (and would like your insights on how to tackle them).

• One, look for a way to "translate" the columns IDs from Grid 1 to Grid M, maybe creating a function pairing those values with if conditions (?
• Two, mapping column and cell in a way that these values are considered when using the update_rows function.

Sorry if my question is confusing, I'm still a beginner in Python and data structures in general.

Emilio Wright

lschek1,

A couple of things:

• I don't believe column IDs will be the same across sheets, so assuming both sheets have the same name columns, I would create a column map so that you can get the column id on both sheets and match the data that you will be updating
• I have provided my answer below assuming that grid 1 and grid M will always have the same number of rows. If Grid M has fewer rows than Grid 1, you will run out of rows in the loop to update. You will then have to create brand new rows. If it is the other way around, you will have to delete any remaining rows to keep both sheets consistent.

readSheet_Grid1 = smart.Sheets.get_sheet(grid1) columnMapGrid1 = {} for column in readSheet_Grid1.columns: columnMap[column.title] = column.id

In my experience with updating values inside sheets with the API, I found that you will first need to build the cell you want to update, then build the row, and finally update the row.

#snippet from Smartsheet.redoc.ly for Update Row # Build new cell value new_cell = smartsheet.models.Cell() new_cell.column_id = 7036894123976580 new_cell.value = "new value" new_cell.strict = False # Build the row to update new_row = smartsheet.models.Row() new_row.id = 6809535313667972 new_row.cells.append(new_cell) # Update rows updated_row = smartsheet_client.Sheets.update_rows( 2068827774183300, # sheet_id [new_row])

Let me know if this helps. I can always answer a bit more.