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.

../_images/example-spreadsheet.png

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:

../_images/enable-sheets-api.png

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

../_images/create-project.png

Give the project a name:

../_images/project-name.png

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

../_images/download-config.png

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:

../_images/create-folder.png

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:

../_images/upload-py.png

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

../_images/split-py.png

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

../_images/split-ls.png

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…:

../_images/go-to-link.png

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:

../_images/oauth-sign-in.png

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:

../_images/verify.png

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
../_images/second-run.png

Python 3 compatibility

The script provided by Google is compatible with current versions of both Python 2 and Python 3. The default python command in the CoCalc terminal runs Python 2. Use python3 at the command line to run quickstart.py with Python 3 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.