[R] Uploading Google Spreadsheet data into R

[David Carlson]

Stripping down to the bare essentials seems to get it. In
particular making the query just "select *" instead of "select *
where B!=''" works. You don't need the processing that the more
complicated Guardian web page requires. After loading the RCurl
package and creating the gsqAPI function:

>
tmp=gsqAPI("0AkvLBhzbLcz5dHljNGhUdmNJZ0dOdGJLTVRjTkRhTkE","selec
t *", 0)
> str(tmp)
'data.frame':   9 obs. of  3 variables:
 $ COL1: chr  "25/10/2013" "25/10/2013" "31/10/2013"
"31/10/2013" ...
 $ COL2: int  50 10 16 18 25 34 56 47 50
 $ COL3: chr  "TEXT" "TEXT TEXT" "TEXT" "TEXT" ...
> tmp
        COL1 COL2      COL3
1 25/10/2013   50      TEXT
2 25/10/2013   10 TEXT TEXT
3 31/10/2013   16      TEXT
4 31/10/2013   18      TEXT
5 31/10/2013   25 TEXT TEXT
6 31/10/2013   34      TEXT
7 31/10/2013   56      TEXT
8 31/10/2013   47      TEXT
9 31/10/2013   50      TEXT

-------------------------------------
David L Carlson
Department of Anthropology
Texas A&M University
College Station, TX 77840-4352

-----Original Message-----
From: r-help-bounces at r-project.org
[mailto:r-help-bounces at r-project.org] On Behalf Of Luca Meyer
Sent: Friday, November 8, 2013 1:33 AM
To: r-help at r-project.org
Subject: [R] Uploading Google Spreadsheet data into R

Hello,

I am trying to upload data I have on a Google Spreadsheet within
R to
perform some analysis. I regularly update such data and need to
perform
data analysis in the quickiest possible way - i.e. without need
to publish
the data, so I was wondering how to make work this piece of code
(source
http://www.r-bloggers.com/datagrabbing-commonly-formatted-sheets
-from-a-google-spreadsheet-guardian-2014-university-guide-data/)
with my dataset (see
https://docs.google.com/spreadsheet/ccc?key=0AkvLBhzbLcz5dHljNGh
UdmNJZ0dOdGJLTVRjTkRhTkE#gid=0
):

library(RCurl)
gsqAPI = function(key,query,gid=0){
  tmp=getURL( paste(
sep="",'https://spreadsheets.google.com/tq?',
'tqx=out:csv','&tq=', curlEscape(query), '&key=', key, '&gid=',
gid),
ssl.verifypeer = FALSE )
  return( read.csv( textConnection( tmp ),  stringsAsFactors=F )
)
}
handler=function(key,i){
  tmp=gsqAPI(key,"select * where B!=''", i)
  subject=sub(".Rank",'',colnames(tmp)[1])
  colnames(tmp)[1]="Subject.Rank"
  tmp$subject=subject
  tmp
}
key='0AkvLBhzbLcz5dHljNGhUdmNJZ0dOdGJLTVRjTkRhTkE'
gdata=handler(key,0)

The code is currently returning  the following:

Error in `$<-.data.frame`(`*tmp*`, "subject", value = "COL1") :
  replacement has 1 row, data has 0

Thank you in advance,
Luca

	[[alternative HTML version deleted]]

______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible
code.