FANDOM


This IDL code will wget the main table and read all the values into a structure.

Other users please free to add code in other languages or to perform other functions.

; NAME:
;   READGOOGLESPREADSHEET
;
; PURPOSE:
;   Reads a google spreadsheet into a structure, using the first line
;   as the tag names. Default behavior reads the exoplanet table.
;
; INPUTS:
;   ID   The google id of the spreadsheet
;
; CALLING SEQUENCE:
;   result = readgooglespreadsheet()
;
; EXAMPLE:
;   planetdata = readgooglespreadsheet()
;   ;show structure tags
;   help, planetdata, /str
;
; DEPENDENCIES:
;   wget
;
; LIMITATIONS:
;   All data types are strings. This is for several reasons:
;     1) Makes reading/writing code more robust and general
;     2) Saves endless formatting statements
;     3) Ensures no loss of precision in the values
;     4) Prevents padding with unnecessary zeros
;     5) Converting to numeric values for calculations is trivial in IDL
;
; REVISION HISTORY
;   Created: 2008/07/15 Jason Eastman

function readgooglespreadsheet, id, output

;; id of the exoplanet table
if n_elements(id) eq 0 then id = 'puXuABkNAV8D8AFV7PQsWJQ'
if n_elements(output) eq 0 then output = 'exoplanets.csv'

;; retrieve the google document
spawn, 'wget "http://spreadsheets.google.com/pub?key=' + $
  id + '&output=csv" -O ' + output

;; read the header line
openr, table, output, /get_lun
line = ''
readf, table, line

;; server problems create the following first line:
if line eq '<html><head><meta http-equiv="content-type" content="text/html; charset=UTF-8">' then message, 'SERVER ERROR: Unable to reach google documents'

;; parse the headers into tag names
tagnames = strsplit(line,',',/extract,/preserve_null)
ntags = n_elements(tagnames)
nentries = file_lines(output) - 1

;; trim the units from the tag names
for i=0, ntags-1 do begin
    tagnames[i] = strtrim((strsplit(tagnames[i],'()',/extract))(0),2)
endfor

;; read the table into a string array
entry=0
array = strarr(ntags,nentries)
while not eof(table) do begin
    readf, table, line
    entries = strsplit(line,',',/extract,/preserve_null)
    array[0:n_elements(entries)-1,entry] = entries
    entry++
endwhile

;; create a structure with the tag names
structure = create_struct(tagnames[ntags-1],transpose(array[ntags-1,*]))
for i=ntags-2,0,-1 do begin
    structure = create_struct(tagnames[i],transpose(array[i,*]),structure)
endfor

;; return the structure
return, structure

end