0

I tried three different ways to update values of a google sheets, but none of them works, I went through the pygsheet docs but I still can't find a solution to it.

import pandas as pd

import pygsheets as pyg

gc=pyg.authorize(service_account_file=r'C:\Users\mingyu\Downloads\heroic-icon-311506-93b9b87f2fed.json')

sht=gc.open_by_url('https://docs.google.com/spreadsheets/d/1sX7Rb6Z9E1_JdG-Uf4HdcjxawgC3RLoRt1m7Akn40DQ/edit?hl=zh-TW#gid=0')
wk_list=sht.worksheets()
wks=sht[0]

cell_list=wks.range('A3:A4')
wks.update_values(cell_list,values=[[0,2]])

and it goes

InvalidArgumentValue: crange

then I tried

wks.update_values(cell_list=cell_list,values=[[0,2]])

it shows

TypeError: list indices must be integers or slices, not list

after that I tried crange

wks.update_values(crange='A3:A4',values=[[0,2]])

the error message is

HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1sX7Rb6Z9E1_JdG-Uf4HdcjxawgC3RLoRt1m7Akn40DQ/values/%E5%B7%A5%E4%BD%9C%E8%A1%A81%21A1%3AA2?valueInputOption=USER_ENTERED&alt=json returned "Requested writing within range ['工作表1'!A1:A2], but tried writing to column [B]". Details: "Requested writing within range ['工作表1'!A1:A2], but tried writing to column [B]">

wks.update_values('A3:A4',values=[[0,2]])

get me the same thing

I found the docs, it said the 'cell_list: List of a :class:Cell objects to update with their values.', then why can't I execute the codes above?

Tanaike
  • 181,128
  • 11
  • 97
  • 165
drew
  • 3
  • 1

1 Answers1

0

Modification points:

  • From your script, I thought that you might want to put the value of [[0,2]] to the range of A3:A4.
    • In this case, when you want to put the values of 0 and 2 to A3:A4, [[0,2]] is required to be [[0],[2]].
    • When you want to put the values of 0 and 2 to A3:B3, A3:A4 is required to be A3:B3 with [[0,2]].
    • I thought that this might be the reason for your issue.

When these points are reflected in your script, it becomes as follows.

From:

wks.update_values(cell_list,values=[[0,2]])

To:

If you want to put the values of 0 and 2 to the cells A3 and A4, respectively, please modify as follows.

wks.update_values(crange="A3:A4", values=[[0], [2]])

or, you can also put the start cell range like below.

wks.update_values(crange="A3", values=[[0], [2]])

or, you can use majordim="COLUMNS".

wks.update_values(crange="A3:A4", values=[[0, 2]], majordim="COLUMNS") # or crange="A3"

If you want to put the values of 0 and 2 to the cells A3 and B3, respectively, please modify as follows.

wks.update_values(crange="A3:B3", values=[[0, 2]])

or, you can also put the start cell range like below.

wks.update_values(crange="A3", values=[[0, 2]])

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165