Google Sheets API Demo

This example illustrates:

What the program does

This example runs a short program to read two columns, “Student Name” and “Major”, from a spreadsheet in Google Sheets and writes them to the user’s terminal.

You don’t need to open the sample data in Google Sheets to run the program, but if you’re curious, you can view it at Example Spreadsheet.

example spreadsheet

The python program is provided by Google and runs under Python 2 and Python 3.

Get credentials file

To get started, you will need a Google account. You will have one already if you use Gmail or Google Drive. If not, browse to drive.google.com and follow the steps to create an account.

Now that you have a Google account, browse to the Python Quickstart page at Google and click ENABLE THE GOOGLE SHEETS API:

enable sheets api

In the pop-up dialog, select + Create a new project:

create project

Give the project a name:

name project

Click DOWNLOAD CLIENT CONFIGURATION to copy file credentials.json to your computer.

download config

Upload Python script and credentials to CoCalc

Log into your CoCalc account. Make a new folder called Sheets API or such.

You can create a folder by typing its name in the Files search box, appending a slash, and pressing Enter:

create folder

Click (+) New (upper left) or Upload (upper right) to upload the credentials.json file from your computer into the new folder.

Also upload python source from the GitHub repository for the Google sample program to the same CoCalc folder. The quickstart page has a link to the repo. We want the raw link to file quickstart.py: https://raw.githubusercontent.com/gsuitedevs/python-samples/master/sheets/quickstart/quickstart.py

Paste the above link into the (+) New dialog in the CoCalc Files screen:

upload script

Open the .py file. Split the view vertically. Note that the two panes can be scrolled separately.

split the view

Change the second pane to a command line terminal and do ls to list files in the folder:

ls comment in the terminal pane

First run: OAuth2 authentication

In the terminal file, run the following command. Note the command line option so that OAuth2 does not open a browser inside CoCalc.

python quickstart.py --noauth_local_webserver

The program will print a message saying Go to the following link…:

go to link

Copy that link from the terminal output. Open a new browser tab and paste it in, and visit that link. If you have more than one Google account, choose the same account you used when creating your credentials.json file. Log into your Google account when you are prompted to do so. You will see a Sign in confirmation message with a code to be copied:

sign in

Click the icon to copy the code to your clipboard. Then paste it into the CoCalc terminal session that is waiting for it and hit Enter. After a few seconds, you will see the selected columns of sample data:

verify

Second and later runs

In the terminal pane, again type ls and see that there is now a token.json file in your folder. That token saves your authorization. Subsequent runs of the program won’t need the OAuth2 detour through an external browser window. Run the Python script again. This time, you don’t need the added command line switch:

python quickstart.py
second run

Python 2 compatibility

The script provided is compatible with current versions of both Python 2 and Python 3. The default python command in the CoCalc terminal runs Python 3. Use python2 at the command line to run quickstart.py with Python 2 compatible versions of the imported modules.

For further study

The Google Sheets API can do much more than download selected columns of a spreadsheet. To learn more, see the documentation for Google Sheets API v4.