How to update one grid with information from another grid-CodePudding

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

CodePudding user response：

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)