How to update one grid with information from another grid

Question

I have one grid called Grid 1, I would like to pass the information to another grid called Grid M.

This Grid M may or may not contain previous data, so what I want is to overwrite these previous values and just leave the new data. Please note that both sheets have the same structure when it comes to column name and their formats.

This is my code:

# Grid IDs
grid1 = 6975487445624708
grid2 = 7306936514307972
grid3 = 1060505730213764
gridM = 4175140851345284

# Read Sheets
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 Column ID from Grid M
columntoRead = []
for column in readColumn_Grid1:
    columntoRead.append(column.id)
print("Column IDs from Grid M: ", columntoRead)

# Get row id from Grid M
rowtoRead_GridM = []
for MyRow_GridM in readSheet_GridM.rows:
    rowtoRead_GridM.append(MyRow_GridM.id)
print("Row IDs from Grid M: ",rowtoRead_GridM)

# Get values from Grid 1
celltoRead_Grid1 = []
celltoRead_GridM = []

for MyRow_Grid1 in readSheet_Grid1.rows:
    for MyCell_Grid1 in MyRow_Grid1.cells:
        celltoRead_Grid1.append(MyCell_Grid1.value)
print("Values from Grid 1: ",celltoRead_Grid1)

# Build new cell value
new_cell = smartsheet.models.Cell()
new_cell.column_id = columntoRead
new_cell.value = celltoRead_Grid1
new_cell.strict = False

# Build the row to update
new_row = smartsheet.models.Row()
new_row.cells.append(new_cell)

print(new_cell)
print(new_row)

This is the output:

Column IDs from Grid M:  [7236841595791236, 1607342061578116, 6110941688948612, 8503502613309316, 3999902985938820, 3859141875263364, 8362741502633860, 1044392108156804]

Row IDs from Grid M:  [7323028036380548, 1693528502167428, 6197128129537924, 3945328315852676, 8448927943223172]

Values from Grid 1:  [3240099.0, 'James', 'Hamilton', 'Male', 197556.0, 18.0, 'Bachelor', 'Medic', 9615534.0, 'Miranda', 'Montgomery', 'Female', 158585.0, 20.0, 'Primary', 'Historian', 9119102.0, 'Vincent', 'Wells', 'Male', 182392.0, 29.0, 'Lower secondary', 'Agronomist', 4533161.0, 'Alen', 'Murray', 'Male', 140853.0, 30.0, 'Doctoral', 'Carpenter', 1010718.0, 'Frederick', 'Farrell', 'Male', 140403.0, 29.0, 'Primary', 'Jeweller']

This is where I start to get lost, find below error code.

ValueError                                Traceback (most recent call last)
Input In [5], in <cell line: 42>()
     40 # Build new cell value
     41 new_cell = smartsheet.models.Cell()
---> 42 new_cell.column_id = columntoRead
     43 new_cell.value = celltoRead_Grid1
     44 new_cell.strict = False

File ~\anaconda3\lib\site-packages\smartsheet\models\cell.py:70, in Cell.__setattr__(self, key, value)
     68     self.format_ = value
     69 else:
---> 70     super(Cell, self).__setattr__(key, value)

File ~\anaconda3\lib\site-packages\smartsheet\models\cell.py:78, in Cell.column_id(self, value)
     76 @column_id.setter
     77 def column_id(self, value):
---> 78     self._column_id.value = value

File ~\anaconda3\lib\site-packages\smartsheet\types.py:165, in Number.value(self, value)
    163     self._value = value
    164 else:
--> 165     raise ValueError("`{0}` invalid type for Number value".format(value))

ValueError: `[7236841595791236, 1607342061578116, 6110941688948612, 8503502613309316, 3999902985938820, 3859141875263364, 8362741502633860, 1044392108156804]` invalid type for Number value

Looks like I can't put in new_cell.column_id a list, only integers, but this makes me wonder the following, how do I let Smartsheet know that I wish to update multiple rows using .value from Grid 1 into Grid M?

If I replace the list with a specific Column ID, like in this code, new_cell.column_id = 7236841595791236 this is the output:

{"columnId": 7236841595791236, "strict": false}
{"cells": [{"columnId": 7236841595791236, "strict": false}]}

This is the desired output in Grid M:

ID Name Last Name Gender Salary Age Education Occupation 3240099 James Hamilton Male 197556 18 Bachelor Medic 9615534 Miranda Montgomery Female 158585 20 Primary Historian 9119102 Vincent Wells Male 182392 29 Lower secondary Agronomist 4533161 Alen Murray Male 140853 30 Doctoral Carpenter 1010718 Frederick Farrell Male 140403 29 Primary Jeweller

I'm not quite understanding what it is you're trying to achieve. Do I understand correctly that the two sheets (the "source" sheet where the data is coming from and the "destination" sheet where you're adding data) have identical structures (i.e., same exact columns in same exact sequence)? And are you just wanting to essentially overwrite all data in the "destination" sheet with data from the "source" sheet"? — Kim Brandl, Jan 31 '23 at 14:40
Okay, do you want to ***copy*** the data from the "source" sheet into the "destination" sheet (such that after your code runs the data exists in both the "source" sheet and in the "destination" sheet)? Or do you want to ***move*** the data from the "source" sheet into the "destination" sheet (such that after your code runs the data no longer exists in the "source" sheet -- it only exists in the "destination" sheet)? — Kim Brandl, Jan 31 '23 at 15:12
And will you be removing **all** data from the "destination" sheet each time (before copying or moving rows into it from the "source" sheet) -- OR do you want to ***only*** remove rows in the "destination" sheet that somehow correspond to the rows you're moving/copying in from the "source" sheet? — Kim Brandl, Jan 31 '23 at 15:18
Indeed, I would like to copy the data from the "source" sheet to the "destination" sheet, removing all the elements that used to be in the "destination" sheet and replace it with the current data from the "source" sheet, while maintaining its values, because this "source" sheet will be used to add new data manually. Ideally this "Grid M" would host not only "Grid 1", but also other grids that have the same structure as well, but for now knowing how to overwrite the data in the destination sheet will suffice. — ralhpq1, Jan 31 '23 at 16:31

score 0 · Accepted Answer · answered Jan 31 '23 at 18:42

If I'm understanding your scenario correctly, the following things are true:

The structure of your source sheet and your destination sheet (number of columns, column types, column sequence) is identical.
Your objective is to delete ALL rows from the destination sheet and then copy all rows from the source sheet into the destination sheet.
You want to the copied data to remain in the source sheet (i.e., you're copying rows from the source sheet into the destination sheet, not moving rows from the source sheet to the destination sheet).

The following code achieves the objective described above.

# specify source info
source_sheet_id = 5169244485773188

# specify destination info
destination_sheet_id = 2486208480733060

'''
STEP 1: 
    Get all rows from the source sheet and build list of Row IDs.
'''
sheet = smartsheet_client.Sheets.get_sheet(source_sheet_id)

# iterate through the rows array and build a list of row IDs
source_sheet_row_ids = []
for row in sheet.rows:
    source_sheet_row_ids.append(row.id)

'''
STEP 2: 
    Get all rows from the destination sheet and build list of Row IDs.
'''
sheet = smartsheet_client.Sheets.get_sheet(destination_sheet_id)

# iterate through the rows array and build a list of row IDs
destination_sheet_row_ids = []
for row in sheet.rows:
    destination_sheet_row_ids.append(row.id)

'''
STEP 3: 
    Delete ALL rows from the destination sheet (using Row IDs from STEP 2).
'''
response = smartsheet_client.Sheets.delete_rows(destination_sheet_id, destination_sheet_row_ids)

'''
STEP 4: 
    Copy all rows from the source sheet (using Row IDs from STEP 1) to the destination sheet.
'''
# copy rows from source sheet to (bottom of) destination sheet
# (include everything -- i.e., attachments, children, and discussions)
response = smartsheet_client.Sheets.copy_rows(
    source_sheet_id,
    smartsheet.models.CopyOrMoveRowDirective({
        'row_ids': source_sheet_row_ids,
        'to': smartsheet.models.CopyOrMoveRowDestination({
            'sheet_id': destination_sheet_id
        })
    }), 
    'all'
    )

It's important to note that this code will delete ALL rows from the destination sheet each time it runs (immediately before it copies all rows from the source sheet into the destination sheet). If you intend for the destination sheet to be the home of data from multiple sheets at some point in the future, then you'll want to modify the code such that it only deletes rows that originated from the specified source sheet sheet. One way to do this would be to:

Add a column to the beginning of the source sheet AND the destination sheet called Source Sheet ID.
In the first row of source sheet, populate this column (cell) with the value of that sheet's ID. In each subsequent row of the source sheet, populate this column (cell) with a formula that pulls the value from that cell in the first row (i.e., =[Source Sheet ID]$1). This will make it so that this cell within any new rows that are added later will automatically be populated with that same value.
You might consider locking this column by using the Smartsheet UI, so it won't be editable (by non-admin users).
Then in the section of code that builds up the list of destination_sheet_row_IDs, add some conditional logic to only append the current row ID if the value of the Source Sheet ID column for that row matches your source sheet ID. That way only rows that originated from the specified source sheet will be deleted from the destination sheet -- any rows there that originated from another sheet will remain untouched.

If you choose to implement this approach -- adding the Source Sheet ID column (containing the ID of the source sheet) as the first column in both the source sheet and the destination sheet -- replace STEP 2 from the code sample above with the following code instead.

'''
STEP 2: 
    Get all rows from the destination sheet and build list of Row IDs.
'''
destination_sheet = smartsheet_client.Sheets.get_sheet(destination_sheet_id)

# iterate through the rows array and build a list of row IDs
destination_sheet_row_ids = []
for row in destination_sheet.rows:
    # only include Row IDs for rows that originated from the specified Source sheet
    if row.cells[0].value == source_sheet_id:
        destination_sheet_row_ids.append(row.id)

Amazing! Your solution worked flawlessly, thank you so much, I learned a couple of new things today, will practice more to fully grasp the api. Have a great day. — ralhpq1, Jan 31 '23 at 23:50
As for the alternative approach, I will keep it in mind to implement. Even though I want to use this Grid M as a repository of data from other sheets, the behavior would be the same, overwrite what used to be in the destination sheet and replace it with new data, but I think now that the sheet's content was deleted and replaced with one of the source sheets, I can append to the bottom these new rows from other sources, I will check and if I get stuck I will make another post. — ralhpq1, Jan 31 '23 at 23:50

How to update one grid with information from another grid

1 Answers1