Appending google sheets with python DataFrame

Question

I try to append the google sheet with python df. After google sheet authorization, I tried this:

sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A1:E100").execute()
values = result.get('values', [])
request = sheet.values().append(spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A1", valueInputOption="USER_ENTERED", insertDataOption="INSERT_ROWS", body={"values":df}).execute()

but it gives me TypeError: Object of type DataFrame is not JSON serializable

So I tried this function, which allows to load and overwrite data into google sheet but not to append data:

def iter_pd(df):
    for val in df.columns:
        yield val
    for row in df.to_numpy():
        for val in row:
            if pd.isna(val):
                yield ""
            else:
                yield val
    
def pandas_to_sheets(pandas_df, sheet, clear = True):
    # Updates all values in a workbook to match a pandas dataframe
    if clear:
        sheet.clear()
    (row, col) = pandas_df.shape
    cells = sheet.range("A1:{}".format(gspread.utils.rowcol_to_a1(row + 1, col)))
    for cell, val in zip(cells, iter_pd(pandas_df)):
        cell.value = val
    sheet.update_cells(cells)
   

pandas_to_sheets(df, workbook.worksheet("Sheet1"))

I will be grateful for any suggestions.

Thanks, I ve already tried this, but it gives me this error: ibb.co/6sxQ1JS — Jana Stará
– Jana Stará, Commented Dec 16, 2020 at 13:48

PnkFlffyUncrn · Accepted Answer · 2020-12-16 15:27:08Z

2

After some investigating I found a method using df.to_json() that works for me:

to_json returns a string thats json formatted and the sheets api expects a list instead. So i used this call:

sheet.values().append(
    spreadsheetId=SAMPLE_SPREADSHEET_ID,
    range="Sheet1!A1",
    valueInputOption="USER_ENTERED",
    insertDataOption="INSERT_ROWS",
    body={
        "values": json.loads(df.to_json(orient='values'))
    },
).execute()

with import json beforehand.

orient='values' formats the data as just a values array as required by the sheets api, json.loads() parses the generated json-string into a python object (list)

answered Dec 16, 2020 at 15:27

PnkFlffyUncrn

1332 silver badges6 bronze badges

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

Comments

Jason E. · Accepted Answer · 2020-12-16 15:17:46Z

0

Issue

The body parameter in the request variable of your code should be in dict type and your dataframe should be converted to json.

Solution

Your code in your request variable should look like this(I replicated this on my end and it is working as expected):

request = sheet.values().append(spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A1", valueInputOption="USER_ENTERED", insertDataOption="INSERT_ROWS", body={'values':df.to_json()}).execute()

answered Dec 16, 2020 at 15:17

Jason E.

1,2111 gold badge6 silver badges10 bronze badges

Collectives™ on Stack Overflow

Appending google sheets with python DataFrame

2 Answers 2

Comments

Issue

Solution

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Issue

Solution

Comments

Your Answer

Sign up or log in

Post as a guest

Related