Copying a pandas Dataframe to Google Sheets with pygsheets

Disclaimer: This endeavor was before I discovered AppScript, which may be an alternative solution to using pygsheets or other python libraries. pygsheets is interesting, but it could be a stretch to justify using it for something that could be done with AppScript. Both are ways to solve a problem by automating Google Sheet operations.

This was done on the Windows 7 OS. First, install libraries with pip. Enter in command prompt or terminal:

  1. python -m pip install pandas
  2. python -m pip install numpy
  3. python -m pip install pygsheets

Then, following the steps documented by pygsheets:

  1. Create a Google Developer Account at console.developers.google.com
  2. Enable Sheets API to account
  3. Enable Drive API to account. Same as last step, but search for Drive.
  4. Create a Client Secret json file. Select “Credentials” tab, and “Create Credentials”. Select Client Secret from options. Export from console and place in same directory as your .py file.
  5. Create a Service Account json file by selecting it instead of “Client Secret”.
  6. Authorize pygsheets with your json files. (See below.)
  7. Copy spreadsheet to Google Sheet with pandas and pygsheets. (See below.)

After completing the first 5 steps, import pygsheets and authorize your account with the client secret json file:

import pygsheets
gc = pygsheets.authorize(client_secret='path/to/client_secret[...].json') 

You will be prompted by the terminal to go to a hyperlink in a browser, get your authorization code, and enter that authorization code into the terminal.

Now, import both libraries needed and switch to authorize with your service json file. Then, load the csv to a dataframe with pandas. Finally, copy it to an existing Google Sheet with pygsheets:

import pygsheets
import pandas as pd

"""Select worksheets by id, index, or title."""
gc = pygsheets.authorize(service_file='path/to/service_account_credentials.json') 
sh = gc.open('add_google_sheet_name_here')
wks = sh.worksheet_by_title('add_sheet_tab_name_here') 

"""Set a pandas dataframe to google sheet, starting at 1st row, 1st column"""
df = pd.read_csv('Example_Data.csv') 
wks.set_dataframe(df,(1,1))

[Example] Split and upload a sheet with 40 columns

Google Sheets limits importing to 26 columns and 1,000 rows at a time. So you’ll have to load the sheets in chunks if you have more than that. This approach uses numpy’s array_split:

import pygsheets 
import pandas as pd
import numpy as np

"""
pandas df to Google Sheet with pygsheets - Python Marketer
Copying a pandas Dataframe to Google Sheets with pygsheets
""" gc = pygsheets.authorize(client_secret='path/to/client_secret[...].json') sh = gc.open('add_google_sheet_name_here') wks = sh.worksheet_by_title('add_sheet_tab_name_here') df = pd.read_csv('Data_to_GSheets.csv') # split columns into two dataframes with numpy and pandas first_half_cols, second_half_cols = np.array_split(df.columns, 2) first_half = df[first_half_cols] second_half = df[second_half_cols] # set both dataframes side-by-side in Google sheet wks.set_dataframe(first_half,(1,1)) start_column = first_half.shape[1] wks.set_dataframe(second_half,(1, start_column))

Conclusion

I found the terminal error messages from pygsheets to be very helpful while debugging the above. This module offers many other nifty spreadsheet operations. Solid library. You can now create and edit Google Sheets with Python.

AppsScript should probably be the default tool when working with Google Sheets because it is built in, but Python does have tools available to work with Google Sheets.

Resources

pygsheets Github

pygsheets Documentation

Google Sheets Documentation

pandas Documentation

3 thoughts on “Copying a pandas Dataframe to Google Sheets with pygsheets

    1. Ok on second thought, in this error you have a ‘Spreadsheet’ object when it should be ‘Worksheet’ object:
      AttributeError: ‘Spreadsheet’ object has no attribute ‘set_dataframe’

      When you run:
      wks.set_dataframe(cols, (1,1))

      It sounds like you don’t have a ‘Worksheet’ object in the wks variable. The ‘Worksheet’ object has the set_dataframe function:
      https://pygsheets.readthedocs.io/en/stable/worksheet.html#pygsheets.Worksheet.set_dataframe

      Good luck! You can read more on the pygsheets ‘Spreadsheet’ vs ‘Worksheet’ objects here in the docs:
      https://pygsheets.readthedocs.io/en/stable/spreadsheet.html
      https://pygsheets.readthedocs.io/en/stable/worksheet.html

  1. Hmmm… maybe check your Google Sheets sheet name is properly spelled in the script in this line: wks = sh.worksheet_by_title(‘add_sheet_tab_name_here’)

Leave a Reply