Coupler.io Blog

How to Connect Python to Google Sheets

  • Zakhar Yung

    • Python is designed with features that support data analysis and visualization. This is why it is often the de facto language for data science and machine learning applications. You can integrate your data with data analysis libraries, such as NumPy or Pandas, or data visualization libraries, such as Matplotlib or Seaborn. 

    And of course, it’s possible to read and write to Google Sheets using Python. 

    What are the ways to upload Python data into Google Sheets?

    Here are the ways to load data to Google Sheets using Python:

    You need Python to create a connection between a source application and Google Sheets and automate data load. 

    At the same time, you can go without coding at all with Coupler.io. It’s a reporting automation solution that provides 50+ preset data sources. Moreover, it allows you to extract data from external APIs to Google Sheets without coding!

    Automate data import to Google Sheets without coding 

    All you need to do is select your source application in the form below and click Proceed.

    You’ll be prompted to create a Coupler.io account for free. You can sign up with your Google account without any credit card required. 

    Once signed in, specify the data you want to extract from your source and transform it. YES, you can rename and hide columns, change data types, filter data, and even add custom fields right in the UI. 

    Not to speak of data blending – you can add multiple sources and combine data from them. 

    After that, select the spreadsheet where you want to load the data and that’s it. Oh no, you can also schedule data refresh like every day or every 15 minutes with a few clicks!
    Check out more about the Google Sheets integrations available with Coupler.io.

    Connect Python to Google Sheets

    For this post, we will be using the Python Google API client to interact with Google Sheets. 

    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

    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:

    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': 'godwinekuma@gmail.com'
    }
    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': 'godwinekuma@gmail.com'
    }
    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.

    However, you can do the same with Coupler.io much more easily. Just click Proceed in the form below and specify which data to extract from Excel and where to load it in Google Sheets. 

    Push Pandas dataframe to Google Sheets

    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 autopilot. 

    Do you actually need Python to automate data exports 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.

    You can rely on Python to automate data flows from your source to Google Sheets. However, it’s not an easy job to do since you need to learn APIs, write a script, and so on. Of course, this will require coding skills.

    If you are not tech-savvy enough to use Python or you just want to automate routing tasks faster, check out Coupler.io. It lets you import data into Google Sheets 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, into Google Sheets.

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

    Automate data export with Coupler.io

    Get started for free
    Exit mobile version