How can I Export Pandas DataFrame to Google Sheets using Python?

Question

I managed to read data from a Google Sheet file using this method:

# ACCES GOOGLE SHEET
googleSheetId = 'myGoogleSheetId'
workSheetName = 'mySheetName'
URL = 'https://docs.google.com/spreadsheets/d/{0}/gviz/tq?tqx=out:csv&sheet={1}'.format(
    googleSheetId,
    workSheetName
)
df = pd.read_csv(URL)

However, after generating a pd.DataFrame that fetches info from the web using selenium, I need to append that data to the Google Sheet.

Question: Do you know a way to export that DataFrame to Google Sheets?

you could also do a `worksheet.clear()` to clear the contents then apply this `worksheet.update([dataframe.columns.values.tolist()] + dataframe.values.tolist())` or the one you posted as a solution to fully update whatever is your `dataframe` **value** but may I ask is there a way to not to a `.clear()`? for like update as it what is the value of the prefered dataframe to be uploaded? — Ice Bear, Jan 16 '21 at 14:36
Here it is, Hamza: `# Initialize Google Sheets google_sheet_id = Sheet_ID google_sheet_name = 'Sheet_Name' URL = 'https://docs.google.com/spreadsheets/d/{0}/gviz/tq?tqx=out:csv&sheet={1}'.format( google_sheet_id, google_sheet_name ) df = pd.read_csv(URL)` — Mihnea-Octavian Manolache, Jul 08 '21 at 03:53

score 16 · Accepted Answer · edited Jan 07 '21 at 05:49

16

Yes, there is a module called "gspread". Just install it with pip and import it into your script.

Here you can find the documentation: https://gspread.readthedocs.io/en/latest/

In particular their section on Examples of gspread with pandas.

worksheet.update([dataframe.columns.values.tolist()] + dataframe.values.tolist())

edited Jan 07 '21 at 05:49

Josh Peak

5,898
4
40
52

answered Jul 15 '20 at 15:07

Daniel

391
1
12

1

In the documentation it links to another package [`gspread-dataframe`](https://github.com/robin900/gspread-dataframe) and it's as simple as `set_with_dataframe(worksheet, df)` – Alaa M. Jan 17 '23 at 19:23

score 10 · Answer 2 · answered Aug 27 '21 at 10:02

This might be a little late answer to the original author but will be of a help to others. Following is a utility function which can help write any python pandas dataframe to gsheet.

import pygsheets
def write_to_gsheet(service_file_path, spreadsheet_id, sheet_name, data_df):
    """
    this function takes data_df and writes it under spreadsheet_id
    and sheet_name using your credentials under service_file_path
    """
    gc = pygsheets.authorize(service_file=service_file_path)
    sh = gc.open_by_key(spreadsheet_id)
    try:
        sh.add_worksheet(sheet_name)
    except:
        pass
    wks_write = sh.worksheet_by_title(sheet_name)
    wks_write.clear('A1',None,'*')
    wks_write.set_dataframe(data_df, (1,1), encoding='utf-8', fit=True)
    wks_write.frozen_rows = 1

Steps to get service_file_path, spreadsheet_id, sheet_name:

Click Sheets API | Google Developers
Create new project under Dashboard (provide relevant project name and other required information)
Go to Credentials
Click on “Create Credentials” and Choose “Service Account”. Fill in all required information viz. Service account name, id, description et. al.
Go to Step 2 and 3 and Click on “Done”
Click on your service account and Go to “Keys”
Click on “Add Key”, Choose “Create New Key” and Select “Json”. Your Service Json File will be downloaded. Put this under your repo folder and path to this file is your service_file_path.
In that Json, “client_email” key can be found.
Create a new google spreadsheet. Note the url of the spreadsheet.
Provide an Editor access to the spreadsheet to "client_email" (step 8) and Keep this service json file while running your python code.
Note: add json file to .gitignore without fail.
From url (e.g. https://docs.google.com/spreadsheets/d/1E5gTTkuLTs4rhkZAB8vvGMx7MH008HjW7YOjIOvKYJ1/) extract part between /d/ and / (e.g. 1E5gTTkuLTs4rhkZAB8vvGMx7MH008HjW7YOjIOvKYJ1 in this case) which is your spreadsheet_id.
sheet_name is the name of the tab in google spreadsheet. By default it is "Sheet1" (unless you have modified it.

score 0 · Answer 3 · answered Jul 15 '20 at 15:06

Google Sheets has a nice api you can use from python (see the docs here), which allows you to append single rows or entire batch updates to a Sheet.

Another way of doing it without that API would be to export the data to a csv file using the python csv library, and then you can easily import that csv file into a Google Sheet.

How can I Export Pandas DataFrame to Google Sheets using Python?

3 Answers3

Linked