Accessing Google Sheets in Python with df2gspread

If you want to save a pandas DataFrame to use later, there are a few options: csv, feathers, pickles, etc. But if a DataFrame is your completed product, and you want to share it, you can’t beat uploading the data as a Google Sheet. It’s free, you can access it anywhere, and you can easily share it with anyone.

Pandas can’t turn a DataFrame straight to a Google Sheet automatically, as you can with actual file formats. Instead, you need to access the Google Drive API. There are lots of libraries to use this access with Python, but for simply saving and retrieving between DataFrames and sheets, df2gspread is simple and yet works great. There are lots of guides out there, but the process changes slightly very often, so many may be a bit out of date. Here is an up-to-date walkthrough of getting this to work.

To start the process of getting API access to your Google Drive, start by going to the Google Cloud Console and changing to the Google account you want to use.

1. Click “Select a Project”.
2. Click on “New Project” in the pop-up box.
3. On the new project screen, give your project a name and click “CREATE”.
4. You should then be forwarded to the dashboard page for the project, where you need to click “Enable APIs and Services”.
5. There are a lot of APIs available, but for this, we just need the one for Google Drive. It should be under the “Google Workspace” section, but you can also search for it if you don’t see it there.
6. Click “Enable” for the Google Drive API.
7. Select the “APIs & Services” to go back to the dashboard.
8. From the API & services dashboard, go to the “OAuth consent screen”.
9. Select “External” for user type then click “Create”.
10. Enter an app name and support email address (the same project name and email address you’ve already used works fine, this is what someone would see when Google asks them for permission to access their Google Drive) and click “Save and Continue”.
11. Now continue to the “Credentials” screen.
12. Click “Create Credentials”, then select “OAuth client ID”.
13. Select “Desktop app” as your application type, give whatever name you want for the client, then click “Create”.
14. Click “OK” to get rid of the pop-up screen, then click the download button for the client you just created. You should receive a .json file named “client_secret” with a bunch of random characters after. Rename the entire thing to “.gdrive_private”, removing the .json extension. This contains the private credentials you just created, so don’t share this file. Move this file into your user directory (C:\Users\<user-name>).

The actual Python code is simple.

from df2gspread import df2gspread as d2g
d2g.upload(df, ‘/example-project/example-spreadsheet’, ‘example-worksheet-1’)

If you have multiple DataFrames, you can also upload multiple as different sheets in the same workbook.

file_name = ‘/example-project/example-spreadsheet
for i, df in enumerate(dfs):
    sheet_name = f’example-sheet-{str(i+1)}’
    d2g.upload(df, file_name, sheet_name)

The first time you access the API like this, you’ll get a tab opened in your browser, asking permission for the API to access your Drive. Accept the access, and run the command again. You should then have your DataFrames uploaded to Google Docs! Share them, link them, do whatever you want with them.

There are a few other options with the df2gspread library, such as loading a sheet into a DataFrame, rather than the other way around. Check out the full documentation here.