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.

Now, I would like to update rows in Grid M with data from Grid 1 by using update_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.