How to update/insert rows and columns of google sheets dynamically in gspread?

Question

I have a spreadsheet like below and i have an API which will give data as a list by date like below

[05/06/2019', 10000, 14003, 140, $576.1786404]
[05/07/2019', 11000, 14003, 140, $576.1786404]
[05/08/2019', 50641, 14067, 357, $578.8120356]

All I have to do is update the data for a date if it already exists or insert a new row into the sheet of a new date.

From the above list I have to update data of 05/06/2019 and I have to insert two rows of date 05/07/2019 and 05/08/2019

I have below code and don't know to do next steps to implement

gc = gspread.authorize(creds)

worksheet = gc.open("chethan testing").sheet1

cell_list = worksheet.range('A3:E3')
cell_values = [1,2,3,4,5]

for i, val in enumerate(cell_values):
    cell_list[i].value = val 

 worksheet.update_cells(cell_list)

Can somebody guide me on how to proceed with next steps.?

Thanks in advance

Can I ask you about your question? 1. In your Spreadsheet, is the date of the column "A" put as the date object? If you cannot check it, can you provide a sample Spreadsheet? 2. About ``insert a new row into the sheet of a new date``, do you want to put the value for other sheet? If you use other sheet, can you provide the information? — Tanaike, May 14 '19 at 22:01
@Tanaike 1)the date column is not an object, it is just a string, in some cases date columns will not come in client sheets 2)we will get the complete url of the sheet with id of worksheet that needs to be update or insert — chethi, May 15 '19 at 12:04

score 3 · Accepted Answer · edited Nov 23 '20 at 00:42

For the above requirement i did like below to get solved.

my_list = [['2018-11-10', '1000000000', '14003', '140', '576.1786404'], ['2018-11-11', '506541', '14067', '357', '578.8120356'], ['2018-11-12', '423175', '15250', '330', '627.4887'], ['2018-11-13', '274503', '11337', '240', '466.4812716'], ['2018-11-14', '285468', '11521', '194', '474.0522828']]

worksheet.values_update(
    'Sheet1!A3',
    params={
        'valueInputOption': 'RAW'
    },
    body={
        'values':my_list
    }
)

This will update the existing values and add the other values to new rows.

More you can find here https://gspread.readthedocs.io/en/latest/api.html

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update

How to update/insert rows and columns of google sheets dynamically in gspread?

1 Answers1