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

Answer 1

• 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.

1. Prepare header_to_key.
2. Create a list for putting to Spreadsheet.
3. 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)