Google Sheets API Demo
This example illustrates:
writing Python code with frame editor and terminal in split view
run-through of Google Sheets API Python Quickstart
OAuth2 authentication from a CoCalc project
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.
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:
In the pop-up dialog, select
+ Create a new project:
Give the project a name:
DOWNLOAD CLIENT CONFIGURATION to copy file credentials.json to your computer.
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:
(+) 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:
Open the .py file. Split the view vertically. Note that the two panes can be scrolled separately.
Change the second pane to a command line terminal and do
ls to list files in the folder:
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…:
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:
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:
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 3 compatibility
The script provided by Google is compatible with current versions of both Python 2 and Python 3.
python command in the CoCalc terminal runs Python 2.
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.