[R] Saving Google worksheets with common prefix
Jennifer Sabatier
plessthanpointohfive at gmail.com
Fri Nov 21 16:19:17 CET 2014
Anyone can help?
On Thu, Nov 20, 2014 at 6:44 PM, Jennifer Sabatier <
plessthanpointohfive at gmail.com> wrote:
> Hi R-Help,
>
> So, I will try to provide a reproducible example...I basically made a
> dummy spreadsheet that contains the same number of tabs as the spreadsheet
> I am really interested in. The data on that spreadsheet is really
> sensitive so I couldn't use it.
>
> Anyway, here are the various sheets in the spreadsheet:
> > names(ts)
> [1] "Operations" "Financing" "Income" "Balance sheet" "Cash
> Flows211"
> [6] "Cash Flows210" "Cash Flows29" "Cash Flows28" "Cash Flows27"
>
> I am only interested in these sheets:
> > names(ts2)
> [1] "Cash Flows211" "Cash Flows210" "Cash Flows29" "Cash Flows28" "Cash
> Flows27"
>
> I want to save them to csv files that contain the same name or similar as
> the sheet name.
>
> Here's the error I'm getting (using traceback i ran it twice to get the
> trace):
> [1] "Cash Flows211"
> [1] "Cash Flows211 - 2014-11-20.csv"
> 6: stop(err)
> 5: stop.if.HTTP.error(http.header)
> 4: getURLContent(uri, .opts = .opts, .encoding = .encoding, binary =
> binary,
> curl = curl)
> 3: getForm("https://www.google.com/accounts/ClientLogin", accountType =
> "HOSTED_OR_GOOGLE",
> Email = login, Passwd = password, service = service, source =
> appID,
> .opts = list(ssl.verifypeer = FALSE))
> 2: getGoogleAuth(usrname, pword, "...", service = "wise")
> 1: getGoogleDocsConnection(getGoogleAuth(usrname, pword, "...",
> service = "wise"))
> Error in getURL(sheet at cellsfeed, curl = getCurlCon(con), followlocation =
> TRUE) :
> trying to get slot "cellsfeed" from an object of a basic class ("NULL")
> with no slots
>
>
> Here's the code:
>
> # install the RGoogleDocs package
> install.packages("RGoogleDocs", repos = "http://www.omegahat.org/R",
> type="source", dep=F)
>
> library(RGoogleDocs)
>
> usrname <- "r.project.user at gmail.com"
>
> pword <- "fakepword"
>
> sheets.con <- getGoogleDocsConnection(getGoogleAuth(usrname, pword, "...",
> service = "wise"))
>
> a <- getDocs(sheets.con)
>
> ts <- getWorksheets('Google spreadsheet example', sheets.con)
>
> ts2 <- ts[grep("^Cash Flow", names(ts))]
> nms <- names(ts2)
> lnth <- length(ts2)
> sheetz <- list("integer" = lnth, "names" = nms)
> sheetz
>
> for (i in sheetz$names) {
> print(i)
> file.name <- paste(i, " - ", Sys.Date(), ".csv", sep="")
> print(file.name)
> traceback()
> tab <- sheetAsMatrix(ts2$i, header = TRUE, as.data.frame = TRUE, trim =
> TRUE)
> writecsv(tab, file.name)
>
> }
>
>
> Now, if I do this as below it works:
>
>
>
> tab <- sheetAsMatrix(ts$"Cash Flows211", header = T, as.data.frame = TRUE,
> trim = TRUE)
>
> head(tab)
>
> > head(tab)
> STATEMENTS OF CASH FLOWS NA NA NA NA
> 1 Year 0.0 1.0 2.0 3.0
> 2 Net income <NA> -43.0 -6.0 32.0
> 3 Plus depreciation <NA> 100.0 100.0 100.0
> 4 Less increase in inventory -10.0 -15.0 -10.0 -8.0
> 5 Less increase in accounts receivable - -60.0 -24.0 -18.0
> 6 Plus increase in accounts payable 8.0 12.0 8.0 6.0
>
>
>
> So, why can't I automate this?
>
> BTW, you should be able to access this spreadsheet. I made a dummy Google
> account and put this dummy spreadsheet on it.
>
[[alternative HTML version deleted]]
More information about the R-help
mailing list