CODEWITHSUNDEEP
pythonpython-3.xgoogle-sheets-api
JoshJohnson
JoshJohnson

Reputation: 113

How to update row in one line using Google Sheets API?

Problem description: I want to create a program that can update one whole row (or cells in this row within given range) in one single line (i.e. one single API request).

This is what've seen in the documentation, that was related to my problem:

# Updates A2 and A3 with values 42 and 43
# Note that update range can be bigger than values array
worksheet.update('A2:B4', [[42], [43]])

This is how I tried to implement it into my program:

sheet.update(f'A{rowNumber + 1}:H{rowNumber + 1}', [[str(el)] for el in list_of_auctions[rowNumber]])

This is what printing [[str(el)] for el in list_of_auctions[rowNumber]] looks like:

[['068222bb-c251-47ad-8c2a-e7ad7bad2f60'], ['urlLink'], ['100'], ['250'], ['20'], [''], [' ,'], ['0']]

As far as I can tell, everything here is according to the docs, however I get this output:

error from callback <bound method SocketHandler.handle_message of <amino.socket.SocketHandler object at 0x00000196F8C46070>>: {'code': 400, 'message': "Requested writing within range ['Sheet1'!A1:H1], but tried writing to row [2]", 'status': 'INVALID_ARGUMENT'}
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\websocket\_app.py", line 344, in _callback
    callback(*args)
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\amino\socket.py", line 80, in handle_message
    self.client.handle_socket_message(data)
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\amino\client.py", line 345, in handle_socket_message
    return self.callbacks.resolve(data)
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\amino\socket.py", line 204, in resolve
    return self.methods.get(data["t"], self.default)(data)
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\amino\socket.py", line 192, in _resolve_chat_message
    return self.chat_methods.get(key, self.default)(data)
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\amino\socket.py", line 221, in on_text_message
    def on_text_message(self, data): self.call(getframe(0).f_code.co_name, objects.Event(data["o"]).Event)
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\amino\socket.py", line 209, in call
    handler(data)
  File "C:\Users\1\Desktop\python bots\auction-bot\bot.py", line 315, in on_text_message
    bid_rub(link, data.message.author.nickname, data.message.author.userId, id, linkto)
  File "C:\Users\1\Desktop\python bots\auction-bot\bot.py", line 162, in bid_rub
    sheet.update(f'A{ifExisting + 1}:H{ifExisting + 1}', [[str(el)] for el in list_of_auctions[ifExisting]])
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\gspread\utils.py", line 592, in wrapper
    return f(*args, **kwargs)
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\gspread\models.py", line 1096, in update
    response = self.spreadsheet.values_update(
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\gspread\models.py", line 235, in values_update
    r = self.client.request('put', url, params=params, json=body)
  File "C:\Users\1\Desktop\ИНФА\pycharm\venv\lib\site-packages\gspread\client.py", line 73, in request
    raise APIError(response)

Question: I cannot figure out what can be the issue here, trying to tackle this for quite a while.

Upvotes: 1

Views: 3636

Answers (2)

Nikko J.
Nikko J.

Reputation: 5533

The error indicates that the data you are trying to write has more rows than the rows in the range. Each list inside your list represent single row of data in the spreadsheet.

In your example:

[['068222bb-c251-47ad-8c2a-e7ad7bad2f60'], ['urlLink'], ['100'], ['250'], ['20'], [''], [' ,'], ['0']]

It represents 8 rows of data.

row 1 = ['068222bb-c251-47ad-8c2a-e7ad7bad2f60']
row 2 = ['urlLink']
row 3 = ['100']
row 4 = ['250']
row 5 = ['20']
row 6 = ['']
row 7 = [' ,']
row 8 = ['0']

If you want to write into a single row, the format should be:

[['068222bb-c251-47ad-8c2a-e7ad7bad2f60', 'urlLink', '100', '250', '20', '', ' ,', '0']]

To solve this, remove the [ and ] in [str(el)] and add another [ and ] to str(el) for el in list_of_auctions[rowNumber] or use itertools.chain() to make single list within a list

Your code should look like this:

sheet.update(f'A{rowNumber + 1}:H{rowNumber + 1}', [[str(el) for el in list_of_auctions[rowNumber]]])

or 

import itertools
sheet.update(f'A{rowNumber + 1}:H{rowNumber + 1}', [list(itertools.chain(str(el) for el in list_of_auctions[rowNumber]))]

Reference:

Writing to a single range

Python itertools

Upvotes: 1

Prateek Jain
Prateek Jain

Reputation: 196

Hi you may try the following:

def gs_writer(sheet_name,dataframe,sheet_url,boolean,row,col):

    import gspread
    from gspread_dataframe import get_as_dataframe, set_with_dataframe
    import google.oauth2
    from oauth2client.service_account import ServiceAccountCredentials
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name('gsheet_credentials.json', scope)
    gc = gspread.authorize(credentials)
    sht2 = gc.open_by_url(sheet_url)
    sht = sht2.worksheet(sheet_name)
    set_with_dataframe(sht,dataframe,resize = boolean,row=row,col=col)

Now after defining this function, you may simply get your required data in a dataframe and input your row and col number where you want this printed, like this:

gs_writer("sheet_name",df,sheet_url,False,1,1)

This should write your data starting from A1

Upvotes: 0

Related Questions