Inserting rows into a Google Spreadsheet with Python

Below are the steps to get started with the Google Spreadsheets Data API via the GDATA Python Client and perform your first row insertion into a blank spreadsheet. The reason for writing this is to provide insight into a few gotcha’s along the way.

1. Go to https://code.google.com/p/gdata-python-client/downloads/list and unzip the client and install

python setup.py install

2. Create your python script and import the python module

import gdata.spreadsheet.service

3. You will need your account information in order to integrate with the SpreadsheetService

  • email - the account your spreadsheet will be connected to
  • password 

4. Initialize a link to the client in your script

client = gdata.spreadsheet.service.SpreadsheetsService() 
client.debug = True # feel free to toggle this 
client.email = email 
client.password = password 
client.source = 'some description' 
client.ProgrammaticLogin()

5. Construct a dictionary object for each row. The keys are tied to the column names in your spreadsheet and the values are the associated values must be of string type. NOTE: column names must be lower case and are can’t have spaces. [a-z].

row = { "id": "0", "title": "First" }

6. Create a new Spreadsheet at http://docs.google.com. Note down the key query param as you will need it. 

  • spreadsheet_key - this is the key param of the spreadsheet you’ve created
  • worksheet_id - the default sheet is ‘od6’

7. [important] Insert a row into your blank spreadsheet with the values that represent your dictionary keys. 

image

If you don’t do this and try to insert data into your spreadsheet, you will most likely get the following error

gdata.service.RequestError: {'status': 400, 'body': 'We're sorry, a server error occurred. Please wait a bit and try reloading your spreadsheet.', 'reason': 'Bad Request'}

8. Insert your rows into the spreadsheet by performing the following command

client.InsertRow(row, spreadsheet_key, worksheet_id)

And there you have it. You should see the data in your spreadsheet.

The main gotcha’s are:

  • If you’re inserting into a blank spreadsheet, make sure to create a row of column headers, which are the values of the keys in the rows that you insert
  • Make sure column names that you use, don’t have spaces and are lowercase
  • Represent your row values as strings

For those interested in something to copy/paste, here you go:

#!/usr/bin/env python

import gdata.spreadsheet.service

email = ''
password = ''
spreadsheet_key = '' # key param
worksheet_id = 'od6' # default

def main():
    client = gdata.spreadsheet.service.SpreadsheetsService()
    client.debug = True
    client.email = email
    client.password = password
    client.source = 'test client'
    client.ProgrammaticLogin()
    
    rows = []
    rows.append({'id':'0','title':'First'})
    rows.append({'id':'1','title':'Second'})
    
    for row in rows:
        try:
            client.InsertRow(row, spreadsheet_key, worksheet_id)
        except Exception as e:
            print e
    
    return
    
if __name__ == '__main__':
    main()
"Stand on the shoulders of the StackExchange Giants" 
Excited to have my first published my first iPhone app: Stack-Ed, and it’s awesome to see it in iTunes! The app review process took 6 days, which is inline with http://reviewtimes.shinydevelopment.com/ and it got approved on a Saturday! Thanks, Apple!
While building the iPhone app, I decided to add the ability to specify which Stack Exchange site Stack-Ed is powered by. I’ve also made this change to the website http://stack-ed.com. 
If you have an iPhone, check the app out! It’s great when you’re looking for some casual reading to do or to take a break and learn something new/different. Learn as much as you can!

"Stand on the shoulders of the StackExchange Giants" 

Excited to have my first published my first iPhone app: Stack-Ed, and it’s awesome to see it in iTunes! The app review process took 6 days, which is inline with http://reviewtimes.shinydevelopment.com/ and it got approved on a Saturday! Thanks, Apple!

While building the iPhone app, I decided to add the ability to specify which Stack Exchange site Stack-Ed is powered by. I’ve also made this change to the website http://stack-ed.com

If you have an iPhone, check the app out! It’s great when you’re looking for some casual reading to do or to take a break and learn something new/different. Learn as much as you can!

Foodler Delivery Reminder Google Apps Script

If you order food online using Foodler.com, you know that you need to check your email for the delivery estimate of when your food is expected to arrive (sent shortly after placing an order).

If you use Foodler frequently enough, you’ll realize that the predictions aren’t that great; they usually compensate to ensure that my expectations are set to 15 minute longer than it would usually take. Regardless of the estimations, though, wouldn’t it be nice to have a calendar reminder for when your order is supposed to arrive (so that you effectively don’t have to go fish into your email for the order info)?

I decided to whip together a little Apps Script to help with this. The script itself looks at your Gmail emails, look for those that are from orders@foodler.com, parses out the relevant information for the order, i.e. total price, tip paid, restaurant, phone number, date the order was placed, to whom it was placed, and of course, when the delivery should have arrived, and creates reminders in your Google Calendar.

The script performs two cron operations (via ‘triggers’). The first checks gmail to see if a new email has come through from foodler. If orders have come in, then we must flag them as needing ‘notifications to be created’ for that order. The second, looks to see if any orders have been flagged, and if so, two calendar events are created: 

  1. The order from when it was placed to when it will arrive, spanning the entire duration, without an alert
  2. An alert for the time that Foodler estimated arrival

The script as it is today, is essentially the first part of what I think could be a valuable tool. Ideally, I’d be able to follow up with the script and provide information about when the food actually arrived, allowing me to perform some math to predict the arrival of future deliveries. If everyone was using said script, imagine the analysis that one can perform to more accurately determine when your food will arrive. Moreover, you can tie in traffic information based on your location vs. the restaurant, making the system pretty interesting.

Anyhow, Apps Script is an extremely interesting platform, and I came away with my 8 hours hacking on it with some tips/feedback:

  1. One can parse HTML as XML and essentially extract particular nodes using a pseudo-xpath expression and performing an eval on the XMLDoc. i had to visually traverse the tree to ensure i was extracting the right nodes.
  2. Triggers are constantly running, so make sure that while you’re in dev mode you don’t have them set too frequently (otherwise your logs may get clobbered eventually leaving you confused with why there’s hardly anything in your logs)
  3. HTML output is not standard html output - the HTML is preprocessed by Google Caja, so don’t expect to be able to import just any library.. so i had to go to a more rudimentary solution for building the html for the webapp view
  4. When performing a query on ScriptDb, you can perform a sort and pass in the sortStrategy, which if you’re dealing with numeric data in string form, allows you to specify db.NUMERIC as the strategy. awesome.
  5. If you’re in the process of executing a script, and you perform any operation like saving the document, the notification allowing you to cancel or dismiss a running function is lost
  6. Debugging large objects in the AppsScript console is a bit painful for the time being. Use the logger or write out to a google doc.
  7. if you’re debugging triggers, set the notifications to be sent immediately. this allows you to sort of debug any issues that may be occurring in those cron jobs

I gave a lightening talk presentation at the Boston Google Developers Group after +Arun Nagarajan gave an awesome presentation on the platform. Here’s a link to the presentation: http://ntikku.appspot.com/p/GAS_Foodler_Delivery_Reminder/

I also posted the project on GitHub, so feel free to check it out. https://github.com/nirvanatikku/foodler_delivery_reminder

Moreover, if you use Foodler, install the script! Available in the Chrome Web Store.

If you’re a developer, there’s no doubt that you utilize StackOverflow’s vast amount of questions and answers in your technical endeavors. Whether you use it for reference, or use it to learn, the resource is phenomenal and the platform rocks.
Recently I have been addicted to SO and feel great engaging in the very act of giving back. I love sharing knowledge and being able to pass along anything I’ve learned wins.
I started perusing the top answerers for tags, and their most viewed questions. I found it tremendously useful (there are some *brilliant* people answering questions extremely thoroughly) but found the experience sub-par for learning purposes. 
That weekend I decided to throw a website together (scratching an itch from going from zero to published project in a weekend), which ended up becoming Stack-Ed. In short, Stack-Ed helps one learn about any topic (on the stack-exchange platform, but particularly StackOverflow in this case). As succinctly put in a tweet, 

Stack-Ed seems great for learning programming things you didn’t know you didn’t know: stack-ed.com
— Jeremy Singer-Vine (@jsvine) January 28, 2013
I plan on writing a follow-up blog post on the rise to #1 on Hacker News but as can be seen in the photos uploaded with this post, after launching and posting to HN 30 minutes went by without any activity, then the following happened:
2.54am - #42.59am - #33.42am - #24.10am - #1
I figured such a hack would resonate with the HN community and it sure did! I managed to get just shy of 19,000 visitors that day alone, with several spikes as a result of tweets from SO engineer Ben Dumke-von der Ehe and a retweet from Jeff Atwood!

This is pretty neat: stack-ed.com
January 28, 2013

The comments were extremely helpful, and I was happy to post the project on GitHub. That week, I also implemented a trending questions/answers TwitterBot - http://www.twitter.com/stack_ed, via the github.com/nirvanatikku/ga_twitterbot project.
The reception from the StackExchange folks was absolutely amazing. Several folks said really kind words and sent me a t-shirt and stickers! Pretty sick. I plan on creating mobile versions soon (and most likely open sourcing those, too), so stay tuned!
ZoomInfo
If you’re a developer, there’s no doubt that you utilize StackOverflow’s vast amount of questions and answers in your technical endeavors. Whether you use it for reference, or use it to learn, the resource is phenomenal and the platform rocks.
Recently I have been addicted to SO and feel great engaging in the very act of giving back. I love sharing knowledge and being able to pass along anything I’ve learned wins.
I started perusing the top answerers for tags, and their most viewed questions. I found it tremendously useful (there are some *brilliant* people answering questions extremely thoroughly) but found the experience sub-par for learning purposes. 
That weekend I decided to throw a website together (scratching an itch from going from zero to published project in a weekend), which ended up becoming Stack-Ed. In short, Stack-Ed helps one learn about any topic (on the stack-exchange platform, but particularly StackOverflow in this case). As succinctly put in a tweet, 

Stack-Ed seems great for learning programming things you didn’t know you didn’t know: stack-ed.com
— Jeremy Singer-Vine (@jsvine) January 28, 2013
I plan on writing a follow-up blog post on the rise to #1 on Hacker News but as can be seen in the photos uploaded with this post, after launching and posting to HN 30 minutes went by without any activity, then the following happened:
2.54am - #42.59am - #33.42am - #24.10am - #1
I figured such a hack would resonate with the HN community and it sure did! I managed to get just shy of 19,000 visitors that day alone, with several spikes as a result of tweets from SO engineer Ben Dumke-von der Ehe and a retweet from Jeff Atwood!

This is pretty neat: stack-ed.com
January 28, 2013

The comments were extremely helpful, and I was happy to post the project on GitHub. That week, I also implemented a trending questions/answers TwitterBot - http://www.twitter.com/stack_ed, via the github.com/nirvanatikku/ga_twitterbot project.
The reception from the StackExchange folks was absolutely amazing. Several folks said really kind words and sent me a t-shirt and stickers! Pretty sick. I plan on creating mobile versions soon (and most likely open sourcing those, too), so stay tuned!
ZoomInfo
If you’re a developer, there’s no doubt that you utilize StackOverflow’s vast amount of questions and answers in your technical endeavors. Whether you use it for reference, or use it to learn, the resource is phenomenal and the platform rocks.
Recently I have been addicted to SO and feel great engaging in the very act of giving back. I love sharing knowledge and being able to pass along anything I’ve learned wins.
I started perusing the top answerers for tags, and their most viewed questions. I found it tremendously useful (there are some *brilliant* people answering questions extremely thoroughly) but found the experience sub-par for learning purposes. 
That weekend I decided to throw a website together (scratching an itch from going from zero to published project in a weekend), which ended up becoming Stack-Ed. In short, Stack-Ed helps one learn about any topic (on the stack-exchange platform, but particularly StackOverflow in this case). As succinctly put in a tweet, 

Stack-Ed seems great for learning programming things you didn’t know you didn’t know: stack-ed.com
— Jeremy Singer-Vine (@jsvine) January 28, 2013
I plan on writing a follow-up blog post on the rise to #1 on Hacker News but as can be seen in the photos uploaded with this post, after launching and posting to HN 30 minutes went by without any activity, then the following happened:
2.54am - #42.59am - #33.42am - #24.10am - #1
I figured such a hack would resonate with the HN community and it sure did! I managed to get just shy of 19,000 visitors that day alone, with several spikes as a result of tweets from SO engineer Ben Dumke-von der Ehe and a retweet from Jeff Atwood!

This is pretty neat: stack-ed.com
January 28, 2013

The comments were extremely helpful, and I was happy to post the project on GitHub. That week, I also implemented a trending questions/answers TwitterBot - http://www.twitter.com/stack_ed, via the github.com/nirvanatikku/ga_twitterbot project.
The reception from the StackExchange folks was absolutely amazing. Several folks said really kind words and sent me a t-shirt and stickers! Pretty sick. I plan on creating mobile versions soon (and most likely open sourcing those, too), so stay tuned!
ZoomInfo
If you’re a developer, there’s no doubt that you utilize StackOverflow’s vast amount of questions and answers in your technical endeavors. Whether you use it for reference, or use it to learn, the resource is phenomenal and the platform rocks.
Recently I have been addicted to SO and feel great engaging in the very act of giving back. I love sharing knowledge and being able to pass along anything I’ve learned wins.
I started perusing the top answerers for tags, and their most viewed questions. I found it tremendously useful (there are some *brilliant* people answering questions extremely thoroughly) but found the experience sub-par for learning purposes. 
That weekend I decided to throw a website together (scratching an itch from going from zero to published project in a weekend), which ended up becoming Stack-Ed. In short, Stack-Ed helps one learn about any topic (on the stack-exchange platform, but particularly StackOverflow in this case). As succinctly put in a tweet, 

Stack-Ed seems great for learning programming things you didn’t know you didn’t know: stack-ed.com
— Jeremy Singer-Vine (@jsvine) January 28, 2013
I plan on writing a follow-up blog post on the rise to #1 on Hacker News but as can be seen in the photos uploaded with this post, after launching and posting to HN 30 minutes went by without any activity, then the following happened:
2.54am - #42.59am - #33.42am - #24.10am - #1
I figured such a hack would resonate with the HN community and it sure did! I managed to get just shy of 19,000 visitors that day alone, with several spikes as a result of tweets from SO engineer Ben Dumke-von der Ehe and a retweet from Jeff Atwood!

This is pretty neat: stack-ed.com
January 28, 2013

The comments were extremely helpful, and I was happy to post the project on GitHub. That week, I also implemented a trending questions/answers TwitterBot - http://www.twitter.com/stack_ed, via the github.com/nirvanatikku/ga_twitterbot project.
The reception from the StackExchange folks was absolutely amazing. Several folks said really kind words and sent me a t-shirt and stickers! Pretty sick. I plan on creating mobile versions soon (and most likely open sourcing those, too), so stay tuned!
ZoomInfo

If you’re a developer, there’s no doubt that you utilize StackOverflow’s vast amount of questions and answers in your technical endeavors. Whether you use it for reference, or use it to learn, the resource is phenomenal and the platform rocks.

Recently I have been addicted to SO and feel great engaging in the very act of giving back. I love sharing knowledge and being able to pass along anything I’ve learned wins.

I started perusing the top answerers for tags, and their most viewed questions. I found it tremendously useful (there are some *brilliant* people answering questions extremely thoroughly) but found the experience sub-par for learning purposes

That weekend I decided to throw a website together (scratching an itch from going from zero to published project in a weekend), which ended up becoming Stack-Ed. In short, Stack-Ed helps one learn about any topic (on the stack-exchange platform, but particularly StackOverflow in this case). As succinctly put in a tweet, 

I plan on writing a follow-up blog post on the rise to #1 on Hacker News but as can be seen in the photos uploaded with this post, after launching and posting to HN 30 minutes went by without any activity, then the following happened:

2.54am - #4
2.59am - #3
3.42am - #2
4.10am - #1

I figured such a hack would resonate with the HN community and it sure did! I managed to get just shy of 19,000 visitors that day alone, with several spikes as a result of tweets from SO engineer Ben Dumke-von der Ehe and a retweet from Jeff Atwood!

The comments were extremely helpful, and I was happy to post the project on GitHub. That week, I also implemented a trending questions/answers TwitterBot - http://www.twitter.com/stack_ed, via the github.com/nirvanatikku/ga_twitterbot project.

The reception from the StackExchange folks was absolutely amazing. Several folks said really kind words and sent me a t-shirt and stickers! Pretty sick. I plan on creating mobile versions soon (and most likely open sourcing those, too), so stay tuned!

Previous pageNext page
Back to top