16 Years of Sheets! Google Sheets was released over 16 years ago in March 2006 and is one of the core applications comprising Google Workspace today.  While Google Sheets does not offer all the bells-and-whistles available with Microsoft Excel, it is still a very powerful spreadsheet application.  With Google Workspace enjoying greater adoption as a result of the pandemic-fueled work-at-home trend, it is likely that more and more people are using or will be using Google Sheets. Like any spreadsheet application, Google Sheets allows users to organize, edit, analyze, and visualize data.  Given that Google Sheets has been a web-based application from its inception, its collaborative features are arguably its greatest strength where multiple users can view and edit a given spreadsheet simultaneously. Additional strengths and weaknesses (as compared to Microsoft Excel) include: Strengths User-friendly. Integration with Google Forms and other Google Workspace products. Collaboration features, including revision history. Weaknesses Cannot work with very large datasets. Limited graphing/charting features. Limited macro/scripting capabilities. The Google Sheets API provides a programmatic way to interact with a Google Sheets spreadsheet and supports a wide variety of languages including Python, Java, and Node.js.  An application developer can read data from and write data to a Google Sheets spreadsheet via the API, as well as complete more complex interactions such as creating new worksheets, formatting data, and creating charts.  To a certain extent, the Google Sheets API provides developers with a set of methods that can be used to manipulate a given Google Sheets spreadsheet in a nearly unlimited way. In this tutorial you will learn how to set up the underlying dependencies needed to use the Google Sheets API with a Python application.  In particular, you will create a new Google Cloud Platform project, enable the Google Sheets API, create credentials to securely access the API, install the required Python libraries to interact with the API from a Python application, and build a simple test application. Prerequisites To complete this tutorial, you will need: To have a Google Account. Python 2.6 or higher installed on your development system. To have installed. pip Step 1 - Create a New Google Cloud Platform Project Google Cloud Platform projects allow developers to work with Google Workspace APIs, including the Google Sheets API.  Keep in mind that Google sets a quota on the number of projects a given user can create.  If you are using the Google Cloud Platform for the first time, then this obviously shouldn't be a problem.  However, if you need a higher project quota in the future, you can always request one from Google. To begin, .  Enter your Google account credentials to login. open the Google Cloud Console Once you are logged in, you will land at the page where you will be able to view a summary of your existing projects.  Of course, if this is your first time using the Google Cloud Console, you will not have any existing projects. Dashboard Click on the main menu (hamburger) icon in the upper-left corner of the screen and select from the drop-down menu. IAM & Admin > Create a Project Enter a name for the new project .  You will also see a in a small font directly underneath the project input field.  The is created automatically based on the project name that you select.  You can click on to change the .  However, keep in mind that once the new project is created, the cannot be changed. Project ID Name Project ID Edit Project ID Project ID Optionally, enter a location for the project.  The location specifies the organization or folder to which the project will belong.  As a new user, the default value of this field may display and does not need to be changed.  If you used a Google account that is managed, by your employer for example,  when logging in, the field may display a different value. No organization Location Click on to create the project. Create You will be re-directed back to the page.  In a few minutes, you should see the newly created project listed under the window. Dashboard Project Info Step 2 - Enable the Google Sheets API Now that you have created a new Google Cloud Platform project, you need to enable the Google Sheets API for the project. From the page, click on the main menu icon in the upper-left corner again and select . Dashboard APIs & Services > Library From the page, scroll down to the Google Workspace section and click on . Library Google Sheets API Once on the page, click on . Google Sheets API Enable The Google Sheets API is now enabled for the new Google Cloud Platform project created in Step 1. Step 3 - Create a Service Account A Python application that wants to interact with the Google Sheets API must be authenticated and authorized to access the resources that the API supports.  Google provides different options for the authentication and authorization mechanisms depending on the needs of the developer.  In this step, we will choose to configure a new for authentication and authorization purposes. Service Account Step 3a - Create a New Service Account From the page, click on the main menu icon in the upper-left corner again and select . Dashboard APIs & Services > Credentials On the page, click on under . Credentials Manage Service Accounts Service Accounts You will be directed to the page where you need to click on . Service Accounts Create Service Account Enter a new name for the service account.  You can choose any name that you would like.  Notice that as you enter your chosen service account name, an associated will be automatically generated in the field.  Additionally, an e-mail address matching the service account ID will also be automatically generated.  For example, if you entered in the name field, you might see a strange looking e-mail address ending in like as the service account e-mail.  Copy this e-mail address since you will use it later when connecting a Python application to a Google Sheets spreadsheet via the Google Sheets API. Service account ID Service account ID some new service account .iam.gserviceaccount.com some-new-service-account@young-home-460928.iam.gserviceaccount.com Once you have entered a name for the service account, click on . Create And Continue Click on the dropdown menu from the section.  Look for the option on the left side of the menu and hover over it.  Then, click on from the right-side of the menu.  The role will allow a Python application using this service account to read and write to a Google Sheets spreadsheet that it is interacting with via the Google Sheets API. Select a role Grant this service account access to project Basic Editor Editor Click on to create the new service account. Done You will be redirected back to the page. Service Accounts Step 3b - Create a New Key for the Service Account From the page, click on the options menu icon (i.e. the icon with three vertical dots) under the label for the new service account.  When the menu opens, click on . Service Accounts Actions Manage Keys On the page, click on and then select . Keys Add Key Create new key The key format should already be set to JSON when the modal opens.  Click on .  A new private key will be generated and a JSON file download with the new key should automatically start.  Note the location of the JSON file with the private key value as it will be needed later when connecting to the Google Sheets API from a Python application. Create private key Create Step 4 - Install Required Google Libraries for Python A Python application needs to import certain Google libraries for Python to successfully authenticate with and interact with the Google Sheets API. Step 4a - Install the Google API Python Client Library The provide access to Google Cloud APIs for a variety of languages.  In this tutorial, we will install the Google API Python Client Library. Google Client Libraries You can install the Google API Python Client Library using the following command: pip install google-cloud Step 4b - Install the Google Authentication Libraries The Google Authentication libraries are used for authentication with Google Workspace APIs, and in this particular case, the Google Sheets API. First, install the Google Authentication Library for Python using the following command: pip install google-auth Second, install the Google Authentication OAuth Library for Python using the following command: pip install google-auth-oauthlib The required Python libraries have now been installed. Step 5 - Configuring a Test Application in Python With everything setup in Steps 1 - 4, you can now start programmatically interacting with Google Sheets spreadsheets via the Google Sheets API. This steps builds a simple test application to retrieve the title of a Google Sheets spreadsheet. Step 5a - Create a New Google Sheets Spreadsheet In , create a new spreadsheet and give it a title such as . Google Sheets My New Google Sheets Spreadsheet For this simple test, the new spreadsheet does not need to have any data in it. Step 5b - Share the New Google Sheets Spreadsheet With Your Service Account Click on the green button in the upper right corner of the new Google Sheets spreadsheet. Share When the dialog opens, copy the Google Cloud Service Account e-mail address from Step 3a into the field. Add people and groups Uncheck the option and click . Notify people Send Step 5c - Record the URL Identifier for the New Google Sheets Spreadsheet Your new Google Sheets spreadsheet will have a URL like: https://docs.google.com/spreadsheets/d/8VaaiCuZ2q09IVndzU54s1RtxQreAxgFNaUPf9su5hK0/edit#gid=0 Record the portion of the URL string starting after and ending before . d/ /edit In other words, you would write down using this example. 8VaaiCuZ2q09IVndzU54s1RtxQreAxgFNaUPf9su5hK0 Step 5d - Configure the Test Application in Python To keep things simple, put the file generated in Step 3b in the same directory where you will create the test application in Python. key.json Build the test application as follows: from google.oauth2 import service_account
from googleapiclient.discovery import build

spreadsheet_id = ENTER_YOUR_SPREADSHEET_ID FROM_STEP_5c_HERE_WITH_QUOTES
# For example:
# spreadsheet_id = "8VaaiCuZ2q09IVndzU54s1RtxQreAxgFNaUPf9su5hK0"

credentials = service_account.Credentials.from_service_account_file("key.json", scopes=["https://www.googleapis.com/auth/spreadsheets"])
service = build("sheets", "v4", credentials=credentials)

request = service.spreadsheets().get(spreadsheetId=spreadsheet_id, ranges=[], includeGridData=False)
sheet_props = request.execute()

print(sheet_props["properties"]["title"])

# Output:
# My New Google Sheets Spreadsheet Conclusion In this tutorial, you created a new Google Cloud Platform project, enabled the Google Sheets API for that project, created a new service account to authenticate with the Google Sheets API, installed all required Google libraries for Python, and built a simple test application. As mentioned in the introduction, the possibilities are almost endless when programmatically interfacing with Google Sheets. See documentation for more information. Google Sheets for Developers

Discovery

How to Use the Google Sheets API with Python

Too Long; Didn't Read

Companies Mentioned

Picture in the Noise

Share Your Thoughts

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...