0
import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)

sh=client.open("kite")
s0=sh.get_worksheet(0)
s1=sh.get_worksheet(1)

s0.batch_update([
    {'range': 'A3', 'values':fin_5min},
    {'range': 'D3', 'values': fin_15min}
])

I want to update s1 sheet with same data as s0. How can I do this in single request using gspread?

Tushar KZ
  • 5
  • 3

1 Answers1

1

Issue and workaround:

At Sheets API, I thought that your goal can be achieved by the method of spreadsheets.values.batchUpdate. But unfortunately, it seems that in the current stage, this method is not included in gspread. So as a workaround, I would like to propose to use the method using requests module.

When your script is modified, it becomes as follows.

Modified script:

client = gspread.authorize(creds)
sh = client.open("kite")

sheetTitles = [sh.get_worksheet(0).title, sh.get_worksheet(1).title]
values = [{'range': 'A3', 'values': fin_5min}, {'range': 'D3', 'values': fin_15min}]
reqs = [[{'range': "'" + t + "'!" + v['range'], 'values': v['values']} for v in values] for t in sheetTitles]
res = requests.post(
    'https://sheets.googleapis.com/v4/spreadsheets/' + sh.id + '/values:batchUpdate',
    headers={"Authorization": "Bearer " + creds.get_access_token().access_token, "Content-Type": "application/json"}, # modified
    data=json.dumps({"data": sum(reqs, []), "valueInputOption": "USER_ENTERED"})
)
  • The access token is retrieved from creds of client = gspread.authorize(creds).
  • In this case, the values of fin_5min and fin_15min are required to be 2 dimensional array. Please be careful this.
  • By the above modification, the values of values are put to both the 1st and 2nd sheets.
  • And, in this modified script, please add import requests and import json.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you, just one small change was needed, instead of ```creds.access_token``` I had to use ```creds.get_access_token().access_token```. – Tushar KZ Nov 03 '21 at 10:57
  • @Tushar KZ Thank you for replying. I'm glad your issue was resolved. From your replying, I modified it because that might be also useful for other users who have the same issue. – Tanaike Nov 03 '21 at 12:05