Python gspread, how to populate google sheet, dictionaries within list

Question

I tried looking at the answers of similar questions but wasn't able to get the code working. I'm having trouble understanding how to populate gspread of similar dictionaries within a list.

Below is my data structure that I want to export:

    sheet_data = [{
                "timestamp": "09-04-2019",
                "value": "10.0",
                "company_name": "Xbox",
                "product": "Buy"
                },
                {
                "timestamp": "09-03-2019",
                "value": "2.0",
                "company_name": "something",
                "product": "Sell"
        }]

Below is what I've tried and works now. My remaining question is, I am manually inputting the cell_range = worksheet.range('A2:D3'), how can I do it so it updates the sheet to whatever cell is available with the given data. Since the amount of data I have in sheet_data will change in future updates.


header = ['timestamp', 'value', 'company_name', 'product']
        worksheet.add_rows(len(sheet_data))
        cell_range = worksheet.range('A2:D3')
        flat_sheet_data = []
        for row in sheet_data:
            for column in header:
                flat_sheet_data.append(row[column])
        for i, cell in enumerate(cell_range):
            cell.value = flat_sheet_data[i]
        worksheet.update_cells(cell_range)

link to image of what I want to accomplish on spreedsheet using above data structure: https://i.sstatic.net/EQLEI.png

Can I ask you about your question? About my data structure that I want to export:, do you want to retrieve the values from Google Spreadsheet as an object of sheet_data? But in your script, it seems that the range of "A2:C10" is updated. So can I ask you about your goal? By the way, can you provide a sample Spreadsheet and sample result values you expect? I think that it will help users think of the solution. Of course, please remove your personal information. By the way, have you already been able to get and put values for Spreadsheet using gspread? — Tanaike
– Tanaike, Commented Feb 27, 2020 at 0:33
@tanaike yes I can already put values on the spreedsheet, with my above script it populates certain rows with 'x'. I want to update, or push the above data structure into google sheet. I also attached an image of what I want to do. — zstar
– zstar, Commented Feb 27, 2020 at 0:42
Thank you for replying and adding more information. You have already been able to use gspread. And you want to put sheet_data to Spreadsheet. I could understand like this. Is my understanding correct? If my understanding is correct, I have one more question. When I saw your image and sheet_data, it seems that the headers of Spreadsheet and the keys of sheet_data are different. How about this? — Tanaike
– Tanaike, Commented Feb 27, 2020 at 0:51
@tanaike yes, you understand everything correctly. The headers are different, I'd like to keep the headers I already defined in the googlesheet but if need be I can work with the Key's from sheet_data as headers . — zstar
– zstar, Commented Feb 27, 2020 at 0:53
Why would you assign row (i.e. a whole dictionary representing a row) to cell.value? Shouldn't you instead cell.value = row['value'] and just iterate over the row that needs value? Then do the same for the other rows, instead of doing the whole block at once. Or, alternatively, simply have 4 assignment statements, assigning the correct value to the correct cell in order - but that'll be a bit messy. — Grismar
– Grismar, Commented Feb 27, 2020 at 1:12

Tanaike · Accepted Answer · 2020-02-27 01:16:31Z

3

You want to put sheet_data to Spreadsheet.
You have already been able to get and put values using Sheets API with gspread.
From your headers in the image and keyes of sheet_data, "Date", "Company_Name", "Traffic" and "Product" are corresponding to "timestamp", "company_name", "value" and "product", respectively.

I could understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

The flow of this sample script is as follows.

Prepare header_to_key.
Create a list for putting to Spreadsheet.
Put the created list using the method of values_append.

Sample script:

Before you run the script, please set the variables.

spreadsheetId = "###"  # Please set the Spreadsheet ID.
sheetName = "Sheet1"  # Please set the sheet name.

sheet_data = [{
    "timestamp": "09-04-2019",
    "value": "10.0",
    "company_name": "Xbox",
    "product": "Buy"
}, {
    "timestamp": "09-03-2019",
    "value": "2.0",
    "company_name": "something",
    "product": "Sell"
}]

header_to_key = {
    'Date': 'timestamp',
    'Company_Name': 'company_name',
    'Traffic': 'value',
    'Product': 'product'
}

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
worksheet = spreadsheet.worksheet(sheetName)

headers = worksheet.row_values(1)
put_values = []
for v in sheet_data:
    temp = []
    for h in headers:
        temp.append(v[header_to_key[h]])
    put_values.append(temp)
spreadsheet.values_append(sheetName, {'valueInputOption': 'USER_ENTERED'}, {'values': put_values})

Note:

In this sample script, it supposes that the headers of "Date", "Company_Name", "Traffic" and "Product" are set to the Spreadsheet like your image.

Reference:

values_append(range, params, body)

answered Feb 27, 2020 at 1:16

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zstar Over a year ago

I was able to solve it a few seconds before I saw this (updated my question with what works for me) but your answer is much much better and it seems that it allows for me to not know the worksheet.range. Thanks for all your help, I really appreciate your time!

Tanaike Over a year ago

@zstar Thank you for replying. I'm glad your issue was resolved. Thank you, too.

Rajesh Kumar · Accepted Answer · 2021-01-25 07:34:10Z

0

The below approach worked for me

gc = gspread.authorize(credentials)
sh = gc.open_by_url("url of the sheet")
ws = sh.worksheet(ws_title)
records = json.loads(sheet_data)
keys = [str(eachvalue) for eachvalue in records.keys()]
values = [str(eachvalue) for eachvalue in records.values()]
Data =[{'range': 'A1:D1','values': [keys],}, {'range': 'A2:D2','values': [values],}]
ws.batch_update(Data);

answered Jan 25, 2021 at 7:34

Rajesh Kumar

211 silver badge3 bronze badges

Collectives™ on Stack Overflow

Python gspread, how to populate google sheet, dictionaries within list

2 Answers 2

Flow:

Sample script:

Note:

Reference:

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Flow:

Sample script:

Note:

Reference:

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related