'How to bulk update cells to the same value in google sheets using gspread
I am trying to write a 0 into a predefined range of cells in google sheets. Using gspread I am able to blank clear all the cells I want but I am unable to figure out how to batch update all the cells to one value. Iterating through the cells and updating them one by one is too slow. I'm sure its just a simple formatting error but I can't find documentation on the proper way
sheet.batch_clear(['A2:A50']) #works fine
sheet.batch_update(['B2:B50'], 0) #does not work
Solution 1:[1]
In your situation, I thought that when the batch_update method of Class Spreadsheet is used, your goal can be achieved by one API call. When this is reflected in a sample script using gspread, it becomes as follows.
Sample script:
client = gspread.authorize(credentials) # Please use your authorization script.
spreadsheetId = "###" # Please set your Spreadsheet ID.
sheetName = "Sheet1" # Please set your sheet name.
spreadsheet = client.open_by_key(spreadsheetId)
sheet_id = spreadsheet.worksheet(sheetName).id
requests = {
"requests": [
{
"repeatCell": {
"cell": {
"userEnteredValue": {
"numberValue": 0
}
},
"range": {
"sheetId": sheet_id,
"startRowIndex": 1,
"endRowIndex": 50,
"startColumnIndex": 1,
"endColumnIndex": 2
},
"fields": "userEnteredValue"
}
}
]
}
spreadsheet.batch_update(requests)
When this script is run, the value of
0is put to the cellsB2:B50.In this script, the A1Notation of
B2:B50is used as the GridRange.
References:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Tanaike |