CODEWITHSUNDEEP
pythonpython-3.xgoogle-sheetspygsheets
E_L
E_L

Reputation: 3

Issues / question with batch update with pygsheets / google sheets

Ok I'm new to python but...I really like it. I have been trying to figure this out for awhile and thought someone could help that knows a lot more than I.

So what I would like to do is use pygsheets and combine batch the updates with one api call vs several. I have been searching for examples or ideas and found if you unlink and link it will do this? I tried and it speed it up only a little bit, then I looked and you could use update.values vs update.value. I have got it to work with the something like this wk1.update_values('A2:C4',[[1,2,3],[4,5,6],[7,8,9]]) but what if you want the updates to be in specific cell locations vs a range like a2:c4? I appreciate any advice in advance.

https://pygsheets.readthedocs.io/en/latest/worksheet.html#pygsheets.Worksheet.update_values https://pygsheets.readthedocs.io/en/latest/sheet_api.html?highlight=batch_updates#pygsheets.sheet.SheetAPIWrapper.values_batch_update

import pygsheets

gc = pygsheets.authorize() # This will create a link to authorize 

#  Open spreadsheet  

GS_ID = ''
File_Tab_Name = 'File1'
Main_Topic = 'Main Topic'
Actual_Company_Name = 'Company Name'
Street = 'Street Address'
City_State_Zip = 'City State Zip'
Phone_Number = 'Phone Number'


# 2. Open spreadsheet by key
sh = gc.open_by_key(GS_ID)

sh.title = File_Tab_Name
wk1 = sh[0]
wk1.title = File_Tab_Name
#wk1.update_values('A2:C4',[[1,2,3],[4,5,6],[7,8,9]])  
wk1.update_values([['a1'],['h1'],['i3']],[[Main_Topic],[Actual_Company_Name],[Street]])   ### is this possible
#wk1.unlink()
#wk1.title = File_Tab_Name
#wk1.update_value("a1",Main_Topic)  ###Topic
#wk1.update_value("h1",Actual_Company_Name)  ###Company Name
#wk1.update_value("i3",Street)  ###Street Address
#wk1.update_value("i4",City_State_Zip)  ###City State Zip
#wk1.update_value("i5",Phone_Number)   ### Phone Number
#wk1.link() # will do all the updates

Upvotes: 0

Views: 2169

Answers (2)

Nithin
Nithin

Reputation: 5840

From what I could undersand you want to batch update values. you can use the update_values_batch function.

wks.update_values_batch(['A1:A2', 'B1:B2'], [[[1],[2]], [[3],[4]]])
# or
wks.update_values_batch([((1,1), (2,1)), 'B1:B2'], [[[1,2]], [[3,4]]], 'COLUMNS')
# or
wks.update_values_batch(['A1:A2', 'B1:B2'], [[[1,2]], [[3,4]]], 'COLUMNS')

see doc here.

NB: update pygsheets to latest version or install from gitub

pip install --upgrade https://github.com/nithinmurali/pygsheets/archive/staging.zip

Upvotes: 1

Nikko J.
Nikko J.

Reputation: 5533

Unfortunately, pygsheets has no method for updating multiple ranges in batch. Instead, you can use gspread.

gspread has batch_update method where you can update multiple cell or range at once.

Example:

Code:

import gspread

gc = gspread.service_account()
sh = gc.open_by_key("insert spreadsheet key here").sheet1
sh.batch_update([{
    'range': 'A1:B1',
    'values': [['42', '43']],
}, {
    'range': 'A2:B2',
    'values': [['44', '45']],
}])

Output:

enter image description here

References:

Upvotes: 0

Related Questions