How to pull data from google sheets into python

Depending on your skillset, you can spend hours every day trying to extract data from multiple sources and then copying and pasting it into Google Sheets before even beginning to analyze the data. It would be nice if you could run a simple script that would automate the process of extracting the data, uploading it to Google Sheets. This will allow you to focus on using the data for decision making, thereby saving time and reducing the risk of introducing errors into your data.

Aside from interacting with Google Sheets via the web and mobile interface, Google provides an API for performing most of the operations that can be done using the web and mobile interfaces. In this post, we have laid out a step-by-step approach of how to use Python with Google sheets.

The motivation for using Python to write to Google Sheets

Python is a general purpose programming language that can be used for developing both desktop and web applications. It is designed with features that support data analysis and visualization, which is the reason why it is often the de facto language for data science and machine learning applications.

If you use Python with Google Sheets, it is easy to integrate your data with data analysis libraries, such as NumPy or Pandas, or with data visualization libraries, such as Matplotlib or Seaborn.

The no-code alternative to using Python for exporting data to Google Sheets

In today’s business world, speed plays a key role in being successful. Speed entails automation of everything including entering data into a spreadsheet. When you automate repetitive tasks, such as reading and writing to Google Sheets, you can reach functional and operational efficiency. If your business uses Google Sheets and you rely on data from various sources, consider using Python to automate your data transfer. However, this will require coding skills.

If you are not tech-savvy enough to use Python, you can go with a no-code solution, such as Coupler.io. It lets you import data into Google Sheets, Excel, or BigQuery from multiple sources including Pipedrive, Jira, BigQuery, Airtable, and many more. Besides, you can use Coupler.io to pull data via REST API, as well as from online published CSV and Excel files, for example, from Google Drive to Excel.

The best part is that you can schedule your data imports whenever your want. 

Check out more about the Google Sheets integrations available with Coupler.io.

Is there a way to upload Python data into Google Sheets?

There are a number of ways to get Python code to output to Google Sheets.

• Using the Python Google API client
• Or using pip packages such as:
  • Gsheets 
  • Pygsheets
  • Ezsheets 
  • Gspread

For the purpose of this post, we will be using the Python Google API client to interact with Google Sheets. Check out the following guide to learn the steps to complete.

In order to read from and write data to Google Sheets in Python, we will have to create a Service Account.

A service account is a special kind of account used by an application or a virtual machine (VM) instance, not a person. Applications use service accounts to make authorized API calls, authorized as either the service account itself or as Google Workspace or Cloud Identity users through domain-wide delegation.

– Google Cloud Docs

Creating a service account

• Head over to Google developer console and click on “Create Project”. 

• Fill in the required fields and click on “Create”. You will be redirected to the project home page once the project is created.

• Click on “Enable API and Services”.

• Search for Google Drive API and click on “Enable”. Do the same for the Google Sheets API.

• Click on “Create Credentials”

• Select “Google Drive API” as the API and “Web server” (e.g. Node.js, Tomcat, etc.) as where you will be calling the API from. Follow the image below to fill in the other options.

• Name the service account, then grant it a “Project” role with “Editor” access and click on “Continue”.

• The credentials will be created and downloaded as a JSON file. If everything is successful, you will see a screen similar to the image below.

• Copy the JSON file to your code directory and rename it to credentials.json

How to enable Python access to Google Sheets

Armed with the credentials from the developer console, you can use it to enable Python access to Google Sheets.

Prerequisite:

This tutorial requires you to have Python 3 and Pip3 installed on your local computer. To install Python, you can follow this excellent guide on the Real Python blog.

Create a new project directory using your system’s terminal or command line application using the command mkdir python-to-google-sheets. Navigate to the new project directory using cd python-to-google-sheets

Create a virtual Python environment for the project using the venv module.

venv is an inbuilt Python module that creates isolated Python environments for each of your Python projects.

Each virtual environment has its own Python binary (which matches the version of the binary that was used to create this environment) and can have its own independent set of installed Python packages. The two commands below will create and activate a new virtual environment in a folder called env.

python -m venv env
source env/bin/activate

Next, install Google client libraries. Create a requirement.txt file and add the following dependencies to it.

google-api-python-client==1.7.9
google-auth-httplib2==0.0.3
google-auth-oauthlib==0.4.0

Run pip install -r requirements.txt to install the packages.

Create an auth.py file and add the code below to the file.

# auth.py

from __future__ import print_function
from googleapiclient.discovery import build 
from google.oauth2 import service_account


SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]

credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES)

spreadsheet_service = build('sheets', 'v4', credentials=credentials)
drive_service = build('drive', 'v3', credentials=credentials)

The code above will handle all authentication to Google Sheets and Google Drive. While the sheets API will be useful for creating and manipulating spreadsheets, the Google Drive API is required for sharing the spreadsheet file with other Google accounts.

How to use Python with Google Sheets

Python to Google Sheets – create a spreadsheet 

To create a new spreadsheet, use the create() method of the Google Sheets API, as shown in the following code sample. It will create a blank spreadsheet with the specified title python-google-sheets-demo.

# sheets.py

from __future__ import print_function
from auth import spreadsheet_service
from auth import drive_service

def create():
    spreadsheet_details = {
    'properties': {
        'title': 'Python-google-sheets-demo'
        }
    }
    sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details,
                                    fields='spreadsheetId').execute()
    sheetId = sheet.get('spreadsheetId')
    print('Spreadsheet ID: {0}'.format(sheetId))
    permission1 = {
    'type': 'user',
    'role': 'writer',
    'emailAddress': 'YOUR EMAIL'
    }
    drive_service.permissions().create(fileId=sheetId, body=permission1).execute()
    return sheetId

create()

You have just created your first Google Sheets file with Python using a service account and shared it with your Google account.

The service account is different from your own Google account, so when a spreadsheet is created by the service account, the file is created in the Google Drive of the service account and cannot be seen in your own Google Drive. The Drive’s permission API has been used to grant access to your Google account or any other account that you want to view the sheet with.

How to write to Google Sheets using Python

You have created a new spreadsheet, but it does not have any data in it yet. The Google Sheets API provides the spreadsheets.values collection to enable the simple reading and writing of values. To write data to a sheet, the data will have to be retrieved from a source, database, existing spreadsheet, etc. For the purpose of this post, you will be reading data from an existing spreadsheet Sample Data for Modeling Google Spreadsheet Budget and then outputting it to the python-google-sheets-demo spreadsheet that we created in the previous step.

How to publish a range of data to Google Sheets with Python

The spreadsheets.values collection has a get() method for reading a single range and an update() method for updating a single range. The get() accepts the spreadsheet ID and a range (A1 Notation) while the update() accepts additional required body and valueInputOption arguments:

• body is the data you wish to write to Google Sheets
• valueInputOption describes how you want the data to be formatted (for example, whether or not a string is converted into a date).

Send Python data to Google Sheets script

from __future__ import print_function
from auth import spreadsheet_service
from auth import drive_service

def create():
    spreadsheet_details = {
    'properties': {
        'title': 'Python-google-sheets-demo'
        }
    }
    sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details,
                                    fields='spreadsheetId').execute()
    sheetId = sheet.get('spreadsheetId')
    print('Spreadsheet ID: {0}'.format(sheetId))
    permission1 = {
    'type': 'user',
    'role': 'writer',
    'emailAddress': ''
    }
    drive_service.permissions().create(fileId=sheetId, body=permission1).execute()
    return sheetId

def read_range():
    range_name = 'Sheet1!A1:H1'
    spreadsheet_id = '1JCEHwIa4ZzwAiKGmAnWGfbjeVCH_tWZF6MkIU0zICwM'
    result = spreadsheet_service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id, range=range_name).execute()
    rows = result.get('values', [])
    print('{0} rows retrieved.'.format(len(rows)))
    print('{0} rows retrieved.'.format(rows))
    return rows

def write_range():
    spreadsheet_id = create()
    range_name = 'Sheet1!A1:H1'
    values = read_range()
    value_input_option = 'USER_ENTERED'
    body = {
        'values': values
    }
    result = spreadsheet_service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id, range=range_name,
        valueInputOption=value_input_option, body=body).execute()
    print('{0} cells updated.'.format(result.get('updatedCells')))

write_range()

This code reads the first row (Sheet1!A1:H1) of the sample spreadsheet and writes it to the python-google-sheets-demo spreadsheet.

Export multiple ranges to Google Sheets with Python

You previously updated only the first row of the demo sheet. To fill in the other cells, the code below will read multiple discontinuous ranges from the sample expense spreadsheet using the spreadsheets.values.batchGet method and then write those ranges to the demo sheet.

from __future__ import print_function
from auth import spreadsheet_service
from auth import drive_service

spreadsheet_id = ''
def create():
    spreadsheet_details = {
    'properties': {
        'title': 'Python-google-sheets-demo'
        }
    }
    sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details,
                                    fields='spreadsheetId').execute()
    global spreadsheet_id
    spreadsheet_id = sheet.get('spreadsheetId')
    print('Spreadsheet ID: {0}'.format(spreadsheet_id))
    permission1 = {
    'type': 'user',
    'role': 'writer',
    'emailAddress': ''
    }
    drive_service.permissions().create(fileId=spreadsheet_id, body=permission1).execute()
    return spreadsheet_id
def read_range():
    range_name = 'Sheet1!A1:H1'
    sheetId = '1JCEHwIa4ZzwAiKGmAnWGfbjeVCH_tWZF6MkIU0zICwM'
    result = spreadsheet_service.spreadsheets().values().get(
    spreadsheetId=sheetId, range=range_name).execute()
    rows = result.get('values', [])
    print('{0} rows retrieved.'.format(len(rows)))
    print('{0} rows retrieved.'.format(rows))
    return rows
def write_range():
    create()
    range_name = 'Sheet1!A1:H1'
    values = read_range()
    value_input_option = 'USER_ENTERED'
    body = {
        'values': values
    }
    result = spreadsheet_service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id, range=range_name,
        valueInputOption=value_input_option, body=body).execute()
    print('{0} cells updated.'.format(result.get('updatedCells')))
def read_ranges():
    write_range()
    sheetId = '1JCEHwIa4ZzwAiKGmAnWGfbjeVCH_tWZF6MkIU0zICwM'
    range_names = ['Sheet1!A2:H21', 'Sheet1!A42:H62']
    result = spreadsheet_service.spreadsheets().values().batchGet(
     spreadsheetId=sheetId, ranges=range_names).execute()
    ranges = result.get('valueRanges', [])
    print('{0} ranges retrieved.'.format(len(ranges)))
    return ranges

def write_ranges():
    values = read_ranges()
    data = [
        {
            'range': 'Sheet1!A2:H21',
            'values': values[0]['values']
        },
       {
            'range': 'Sheet1!A22:H42',
            'values': values[1]['values']
        }
    ]
    body = {
        'valueInputOption': 'USER_ENTERED',
        'data': data
    }
    result = spreadsheet_service.spreadsheets().values().batchUpdate(
        spreadsheetId=spreadsheet_id, body=body).execute()
    print('{0} cells updated.'.format(result.get('totalUpdatedCells')))
write_ranges()

Append list to Google Sheets with Python

You can also append data after a table of data in a sheet using the spreadsheets.values.append method. It does not require specifying a range as the data will be added to the sheet beginning from the first empty row after the row with data.

def append():
    values = read_ranges()
    data = [
         values[0]['values'], values[1]['values']
    ]
    body = {
        'valueInputOption': 'USER_ENTERED',
        'data': data
    }
    result = spreadsheet_service.spreadsheets().values().append(
        spreadsheetId=spreadsheet_id, body=body).execute()
    print('{0} cells updated.'.format(result.get('totalUpdatedCells')))
append()

Python script to export Excel to Google Sheets

Already have an Excel sheet whose data you want to send to Google Sheets? That is also possible with Python. Here is the sample Excel worksheet we have: 

You can read some of the data there and add it to the existing Google Sheets document.

First, add pandas==1.2.3 and openpyxl==3.0.7 as new dependencies in your requirement.txt and re-run pip install -r requirements.txt to install the packages.

Now add the code below into the sheets.py file.

def export_excel_to_sheets():
    spreadsheet_id = '1JCEHwIa4ZzwAiKGmAnWGfbjeVCH_tWZF6MkIU0zICwM'
    excel_data_df = pandas.read_excel('sample-excel.xlsx', sheet_name='Sheet1', skiprows = range(1, 62), engine='openpyxl')
    excel_data_df['Date'] = excel_data_df['Date'].astype(str)
    excel_data_df['Unnamed: 0'] = ''



    body = {
        'values': excel_data_df.values.tolist()
    }
    
  
    spreadsheet_service.spreadsheets().values().append(
        spreadsheetId=spreadsheet_id, body=body, valueInputOption='USER_ENTERED', range='Sheet1').execute()
    print('{0} cells appended.'.format(result \
                                       .get('updates') \
                                       .get('updatedCells')))


export_excel_to_sheets()

This will extract the data from the Excel sheet beginning from row 63 and then add it to the Google Sheets file.

Push Pandas dataframe to Google Sheets with Python

Exporting Pandas dataframe to Google Sheets is as easy as converting the data to a list and then appending it to a sheet. The code below sends a Pandas dataframe to Google Sheets.

def export_pandas_df_to_sheets():
    spreadsheet_id = '1iKZHRN4MnbyUeeSO5EWvTLmSeIOzXfwx1s0PSVw27lc'
    
    df = pd.DataFrame(
        [[21, 72, 67],
        [23, 78, 69],
        [32, 74, 56],
        [52, 54, 76]],
        columns=['a', 'b', 'c'])



    body = {
        'values': df.values.tolist()
    }
    
  
    result = spreadsheet_service.spreadsheets().values().append(
        spreadsheetId=spreadsheet_id, body=body, valueInputOption='USER_ENTERED', range='Sheet1').execute()
    print('{0} cells appended.'.format(result \
                                       .get('updates') \
                                       .get('updatedCells')))

How fast can Python load data to Google Sheets?

With automation, your data can be in Google Sheets in a matter of 2-5 seconds! Of course, you will have to spend time writing the initial code, but after that, everything will be on auto pilot. 

On the other hand, why would you spend time coding when there is a ready-to-use solution? With Coupler.io, you can automate export of data to Google Sheets from BigQuery, Trello, and many other sources with just a few clicks. Make the right choice and use the best approach based on your needs. Good luck with your data!

Back to Blog