Export Pandas Data Frame to Google Sheets Using Python

Export Pandas Data Frame to Google Sheets Using Python

Google Sheets is an online web-based spreadsheet program offered by Google. It is a real-time application that lets users create and modify spreadsheets & share data online.

Most organizations ask multiple users to work simultaneously on a single spreadsheet. This collaboration keeps track of every update made by the specific user in the document.

We can take Pandas data frame as the two-dimensional data structure, i.e., a table having rows & columns, similar to a two-dimensional array but with labeled axes.

If you have Pandas data frames to be exported to Google Sheets, you can do it using Python, as this tutorial explains.

Export Pandas Data Frame to Google Sheets Using Python

To export the Pandas data frame to Google sheet, we have to follow the three steps that are listed below:

  1. Sync project with Google API console.
  2. Access Google Sheet API.
  3. Export Pandas data frame to the Google Sheet.
  • Sync project with Google API console.

    To sync the Project with the Google API console, first, we need to create a project via Google Cloud Console.

    export pandas data frame to google sheets using python - create project

    To create credentials, we need to enable the below two APIs by searching in the search bar:

    • Google Drive API
    • Google Sheet API

    Now how to create credentials? To do that, we are required to follow the below steps:

    1. Click on Create Credentials.
    2. Select Service Account, enter Service Account name, and then press Done (rest of the information is optional)
    3. Once it is created, click on it, go to the Keys tab, and download the key in JSON format.

    export pandas data frame to google sheets using python - create credentials

    The downloaded JSON file content (saved in the service_account.json file) may look like this:

    {
      "type": "service_account",
      "project_id": "some-project-id",
      "private_key_id": "eb...25",
      "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw...jINQh/9\n-----END PRIVATE KEY-----\n",
      "client_email": "123...999-yourclientemail@projectid.iam.gserviceaccount.com",
      "client_id": "473...hd.apps.googleusercontent.com",
      ...
    }
    
  • Access the Google Sheet API.

    Install the below Python libraries to connect and interact with Google Sheets.

    • gspread
    PS C:\> pip install gspread
    
    • oauth2client
    PS C:\> pip install oauth2client
    

    Let’s create a client through the code using the downloaded API to connect the Google Sheets.

    Example Code (saved in demo.py):

    import gspread
    from oauth2client.service_account import ServiceAccountCredentials
    
    def create_connection(service_file):
      client = None
      scope = [
    	  'https://www.googleapis.com/auth/drive',
    	  'https://www.googleapis.com/auth/drive.file'
      ]
      try:
    		credentials = ServiceAccountCredentials
    					 .from_json_keyfile_name(service_file, scope)
    	  client = gspread.authorize(credentials)
    	  print('Connection established successfully...')
      except Exception as e:
    	  print(e)
      return client
    
    service = 'service_account.json'  # downloaded Credential file in JSON format
    client = create_connection(service)
    

    First, we imported gspread, and from Python Package outh2client, we imported a class ServiceAccountCredentials stored in the service_account.py file.

    The function create_connection() connects client to the Google Sheets. It takes the service_file as an argument where the credential key is stored.

    Don’t forget to keep the service_account.json in the same directory as demo.py or store its path in the service variable. Inside this function, we create the credentials using ServiceAccountCredentials.from_json_keyfile_name().

    Then we provide the credentials to gspread.authorize() that checks its authenticity and creates a client if the key stored in service_file is valid.

    In case of any error, this function will print the Exception without breaking the program.

  • Export Pandas data frame to the created Google Sheet.

    To export Pandas data frame to a Google sheet, first, we have to create it. Let’s write a code that creates a Google sheet.

    Example Code (saved in demo.py):

    def create_google_sheet(client, sheet_name):
      google_sheet = None
      try:
    	  google_sheet = client.create(sheet_name)
    	  google_sheet.share(
    			'@gmail.com',  # enter the email you want to create a google sheet on
    		  perm_type='user',
    		  role='writer'
    	  )
    	  print('Google Sheet created successfully...')
      except Exception as e:
    	  print(e)
      return google_sheet
    
    
    service = 'service_account.json'  # downloaded Credential file in JSON format
    client = create_connection(service)
    
    sheet_name = '50_Startups'  # google sheet name of your choice
    google_sheet = create_google_sheet(client, sheet_name)
    

    The function create_google_sheet takes the already created client and sheet_name as arguments and uses the client.create() function to create a Google sheet.

    Note that this google sheet is created on the client built using the downloaded API. So we must share it with the email we used to create a service account, giving the writing permissions using the google_sheet.share() function.

    Here, the final step is exporting data to Google Sheets. To do this, we must install the Python library pandas using the following:

    PS C:\> pip install pandas
    

    Let’s run that code that finally exports the Pandas data frame to Google sheets.

    Example Code (saved in demo.py):

    import pandas as pd
    
    def export_dataframe_to_google_sheet(worksheet, dataframe):
      try:
    		worksheet.update([dataframe.columns.values.tolist()] +
    						 dataframe.values.tolist())
    	  print('DataFrame exported successfully...')
      except Exception as e:
    	  print(e)
    
    df_file = '50_Startups.csv'
    dataframe = pd.read_csv(df_file)
    worksheet = google_sheet.get_worksheet(0)
    export_dataframe_to_google_sheet(worksheet, dataframe)
    

By default, there is one worksheet already built inside Google sheets. We can access it from google_sheet using index as google_sheet.get_worksheet(0).

We then export the data frame to it using the function export_dataframe_to_google_sheet(). It simply lists the data frame and exports it to Google sheets.

Now, we run the Python file demo.py as:

PS C:\> python demo.py

Output (printed on console):

export pandas data frame to google sheets using python - output

Whereas the data frame exported to Google sheets is as follows:

export pandas data frame to google sheets using python - google sheets output