CODEWITHSUNDEEP
pythondataframegoogle-sheetsappend
Jana Stará
Jana Stará

Reputation: 73

Appending google sheets with python DataFrame

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.

Upvotes: 0

Views: 453

Answers (2)

PnkFlffyUncrn
PnkFlffyUncrn

Reputation: 133

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)

Upvotes: 2

Jason E.
Jason E.

Reputation: 1221

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()

Upvotes: 0

Related Questions