Python + Google Sheet | How to Update specific cells

Question

I'm learning how to use Google Sheet API and Python.

Firstly, I need to update specific cells in Google Sheet, not a range of cells.

I could use update_cell(), but each update will take up a Write Request, which is not optimal.

sheet_ELCSV.update_cell(str(x),1,OH_row_Side)
sheet_ELCSV.update_cell(str(x),str(PairA_Idx),str(OH_row_Executed))
sheet_ELCSV.update_cell(str(x+1),1,"Fee")
sheet_ELCSV.update_cell(str(x+1),11,str(Sum_Fee))

I read the developers guide on spreadsheets().values().update() and batchUpdate. If I'm not mistaken, they only take 1 write request. But I don't get how to do specific cells update.

ssName = sheet_ELCSV.title + '!'
cell_range = 'A2,B2,D2,A3,K3'
 
values = (
    ('Sell','-2.17','27760.0476'),
    ('Fee', '-2.38285229')
)
value_range = {
    'majorDimension' : 'ROWS',
    'values': values
}
service.spreadsheets().values().update(
    spreadsheetId = ssID,
    valueInputOption = 'USER_ENTERED',
    range = ssName + cell_range,
    body = value_range
).execute()

Any guidance is appreciated!

Answer 1

The spreadsheets.values.update is a method used for updating single range.

A range can be a single cell in a sheet or a group of adjacent cells in a sheet.

Here are the example of valid ranges:

• Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.

• Sheet1!A:A refers to all the cells in the first column of Sheet1.

• Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.

• Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.

• A1:B2 refers to the first two cells in the top two rows of the first visible sheet.

• Sheet1 refers to all the cells in Sheet1.

• Sheet1!A1 refers to the A1 cell of Sheet1

To update multiple ranges at once, you can use batchUpdate():

Try this code below:

batch_update_values_request_body = {
    "valueInputOption": "RAW",
    "data": [
        {
            'range': 'Sheet1!A2',
            'values': [['Sell']]
        },
        {
            'range': 'Sheet1!B2',
            'values': [['-2.17']]
        },
        {
            'range': 'Sheet1!D2',
            'values': [['27760.0476']]
        },
        {
            'range': 'Sheet1!A3',
            'values': [['Fee']]
        },
        {
            'range': 'Sheet1!K3',
            'values': [['-2.38285229']]
        },
    ]
}
service.spreadsheets().values().batchUpdate(
   spreadsheetId=spreadsheet_id, 
   body=batch_update_values_request_body
).execute()

Output:

Reference:

batchUpdate()

A1 Notation