gspread Python Module and More Push-Ups

Yesterday I posted about my new push-up strategy here at work. The new PowerShell script is working great. I am really starting to hate that purple Blink(1) beacon....but its good for me. My arms and chest are thrashed from all the pushups.

Anyway, as mentioned yesterday I needed a way to track my push-up progress (or lack there of). Having already posted about the awesomeness of the gspread Python Module, I figured now would be a good time to revisit the module and find a good use for it.

First things First I'm the Realest

First we need to authenticate. You can go the route of just storing your email account and password in your Python script...but what fun is that when you can go OAuth. Browse on over to the Google Developers Console and make sure you have the Google Drive API enabled.

After you have enabled the API go to the Credentials section under APIs and Auth. Click Create new Client ID under OAuth, Choose the Service Account option and make sure JSON key is selected. Once complete you should see a service account on the right side of your screen with a Client ID, Email address, Certificate Fingerprints.

Click the Generate new JSON key button below the Service Account and you will download a JSON file that has your private key and such. Rename this key file to something easy (I just called it json.key), and drop this file in the folder where your Python script is located.

Open up the json.key file you downloaded and take note of the client_email address. You will need that later.

Client Time

Now its time to get the oauth client. I had to go through a few rounds of pip installs to get my client working. Here are the modules I had to install for sure:

• pip install oauth2client
• pip install PyOpenSSL

You may need a few other libraries here. Just google your errors when you start trying to authenticate. Ima bet there is a StackOverflow article on it to help you out.

Authenticate don't Playa Hate

Reference the gspread API documentation for some help but its pretty simple once you get the pieces above in place.

That is about it. This will crack open that sweet key.json file you downloaded earlier from Google. This file contains all the API goodies you need to make the Authentication happen Cap'n.

Sharing is Caring

Grab that Client Email you noted from the earlier step and you will now want to share the spreadsheet you will be modifying with this email. The email should be a big ol hairy email with the @developer.gserviceaccount.com address on the end.

Make sure this email account can edit your spreadsheet.

All Together Now

So putting it all together here. I wanted a quick script that would take in a parameter for the number of pushups and grab the current time, then write these values to a Google Spreadsheet.

I encountered a small issue with the gspread Module. It's more of a Drive API issue than a gspread issue. When you use the append_row function it appends a row to the bottom of the worksheet. By default the bottom row is row 1000 so append_row will put your data at 1001 regardless of where your data is at.  As a quick work around I just use the col_values function to get the number of rows and then use the resize function to set that as the spreadsheet size. Then you run append_row and you are good to go.



So now I am tracking push-ups in a more automatic fashion.

Example call:

python.exe c:\Python27\Projects\PushUpTracker\PushUpTracker.py 15


This call will record 15 push ups in my Google Spreadsheet with the current time.

Blink, PowerShell, and Push-Ups

I have been at my new gig for a little over a month now. Some fellas at the new gig like to do push-ups throughout the day in an attempt to stay active during a primarily sedentary job. Me being a data guy and a fella that likes to try and stay active this activity falls right in my wheelhouse.

The first few weeks I did pretty good remembeing to stop and do push-ups throughout the day, however soon I kept forgetting. I would get to the end of the day and only have like 20 or 40 pushups. I recently brought the Blink(1) to work to keep an eye on traffic, news and stuff so I said "Self!" why not write a quick script to remind you to do pushups every hour.

Blink(1) makes this so simple. All you have to do is write a HEX color to a text file periodically. You then setup a rule in the blink control software to monitor the file. When the modified date on the file changes the blink(1) will show whatever color or pattern you send it.

I wrote a quick PowerShell script to write to the file.


So we loop 8 times and sleep for 3600 seconds each time (1 hour) giving us an all day pushup reminder script. I setup the Blink control software to scan the file every 5 minutes.

I also setup a Google Spreadsheet so I could track my push-up progress. I will make a chart with my progress (if it shows improvement ha) over the next few weeks.