A few sites seem to be posting links to Google spreadsheets on data.gov.uk, and it also seems to be previewing spreadsheets using Zoho. Is it possible to use the spreadsheet like a database in some way, or do I have to download the whole spreadsheet in order to get out data from a single row, column or even cell?

asked 11 Dec '10, 18:08

psychemedia's gravatar image

psychemedia ♦♦
1.1k323961
accept rate: 11%


Google provide an API for interacting with Google Docs spreadheets. It's a RESTful service which isn't hard to work with once you've worked out which mode of spreadsheet access you need. The documentation lives here: http://code.google.com/apis/spreadsheets/

The two access modes are (as I understand them):

  1. Lists/Cells In this mode you can search the spreadsheet for values in a column and receive a list of rows which match, or you can address individual cells directly.
  2. Tables and Records This mode provides a more database-like api. Or so I'm told. I've not used it...

I've been using it with the python wrapper gdata-python-client which you can find at http://code.google.com/apis/gdata/docs/client-libraries.html along with libraries for some other languages (including javascript).

link

answered 15 Jan '11, 12:40

marxian's gravatar image

marxian
121128
accept rate: 0%

edited 15 Jan '11, 12:48

The Google Chart Tools Query Language provides a query language for interrogating data in a Google spreadsheet via a RESTful URL of the form: http://spreadsheets.google.com/tq?tq=QUERY&key=SPREADSHEET_ID

Responses are returned in a format that plays nicely with the Google Visualisation widgets.

Select queries take a range of arguments, such as: - - Fetch everything: select (in a lot of computer languages, ‘’ often refers to ‘everything and anything’) - Fetch just columns B, C and I: select B,C,I - Fetch and order rows from selected columns: select B,C,I where (I!=23083 and I>=0) order by I - Search by string: select B,C,D,E where (C contains ‘Joan’ or C matches ‘John’) - Fetch rows where the amount in a particular column exceeds some limit : select * where F<100000 - Fetch the summed total of entries in column I: select sum(I) - Count the number of rows in an arbitrary column: select count(I) - Find an average: select sum(I)/count(I) - Grouped results (total values in a group): select D,sum(I) where I>=0 group by D - Grouped results (average values in a group): select D,sum(I)/count(I) where I=0 group by D

An example of a single web page app that interrogates a Google spreasheet via the chart tools query language is described in this post on a Government Spending Data Explorer; the code can be found here, although it needs tidying up!

link

answered 15 Jan '11, 13:18

psychemedia's gravatar image

psychemedia ♦♦
1.1k323961
accept rate: 11%

There's also some handy wrappers for python in the python datautil library, specifically in datautil.tabular.gdocs. You can use it like this:

 # import the library
 import datautil.tabular.gdocs as gdocs

# set up a reader (datautil uses the Google provided TextDb object by default)
reader = gdocs.GDocsReaderTextDb(
    'tQSJ9dxTh8AKl-ON4Qqja8Q', # Google spreadsheet key.
    config['gdocs_username'],
    config['gdocs_password']
    )

# Read the data - you get an object with a header (list) and data (list of lists) attribute
tabulardata = reader.read()

# the field names
print tabulardata.header

# read the first 10 rows of data and print it out
for row in tabulardata.data[:10]:
    foo(row)
link

answered 17 Jan '11, 22:34

pudo's gravatar image

pudo ♦♦
31238
accept rate: 33%

edited 18 Jan '11, 08:52

rgrp's gravatar image

rgrp ♦♦
501122027

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×3
×1
×1

Asked: 11 Dec '10, 18:08

Seen: 929 times

Last updated: 18 Jan '11, 08:52

powered by OSQA