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!

Nikko J. Nikko J. · Accepted Answer · 2020-12-16T17:27:44

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

Python + Google Sheet | How to Update specific cells

1 Answers

Reference: