sheetAsMatrix returning formulas instead of values

[kyeoh]

Hi Duncan,

Really loving the package so far. The only issue I'm having with it is that I can't seem to figure out how to pull values instead of formulas.

The same issue I found here: http://stackoverflow.com/questions/1395391/getting-value-rather-than-formula-in-rgoogledocs

worksheet_as_df = sheetAsMatrix(worksheets[['Worksheet]], header = TRUE, as.data.frame = TRUE, trim = TRUE, stringsAsFactors = FALSE)

Returns something like =if(vlookup(R[0]C2,Current!C2:C26,R1C[0],FALSE)=0,"VALUE", vlookup(R[0]C2,Current!C2:C26,R1C[0],FALSE))

And not the value.

Any help would be greatly appreciated!

[duncantl]

Hi Ken

I can't reproduce it with a simple formula in a cell, e.g., =A2+B2

sheetAsMatrix(sheets[[1]], header = FALSE)
  V1 V2 V3
1  1 10 11
2  2 20 22
3  3 30 33

Can you share a sample document with me.

Thanks, D.

[kyeoh]

Thanks for the quick reply!

Here is an example: https://docs.google.com/spreadsheets/d/1sOlUDZ-HC2pytPaJB0kANezlGEGdYrlp0_9hhAvWQJI/edit?usp=sharing

Let me know if this loads for you.

sheets.con = getGoogleDocsConnection(getGoogleAuth("username", "password", service = "wise")) spreadsheets = getDocs(sheets.con,ssl.verifypeer = FALSE)

worksheets = getWorksheets(spreadsheets[['googlesheets_test']], sheets.con) worksheet_as_df = sheetAsMatrix(worksheets[['MachineReadable']], header = TRUE, as.data.frame = TRUE, trim = TRUE, stringsAsFactors = FALSE)

=if(vlookup(R[0]C2,Current!C2:C26,R1C[0],FALSE)=0,"NOT WORKING", vlookup(R[0]C2,Current!C2:C26,R1C[0],FALSE))

[duncantl]

Thanks. Yep, I can see the spreadsheet and I can retrieve it with your commands above. So, in other words, it works for me. I've committed some changes that were pending in my devel version. So these are on github. You can install the package from these (either using the devtools package, or cloning the repository on your machine and then R CMD INSTALL RGoogeDocs). Hopefully that will make it work for you. If not, we'll have to figure out what the difference is.

[kyeoh]

Hmm. Still doesn't work for me. I removed the packages both on my Win7 Machine and CentOS, and installed again using install_github('duncantl/RGoogleDocs') from the devtools package.

While the simple example of =A2+B2 works for me (I get the number), when I do more complex formulas (multiple sheets and IF/vlookups) it seems to revert to the R1C1 notation.

In your testing, were you pulling from the 'MachineReadable' sheet?

[duncantl]

Bummer. Yep, I pulled both the MachineReadable and also Current sheet. Both worked.

[duncantl]

Can you show me the output of traceback() when the error occurs, please? Thanks

[kyeoh]

There is no error, just I don't get the results I'm looking for.

But on the Unix box I get

traceback() No traceback available

On Windows machines

traceback() 2: stop(txt, domain = NA) 1: library(RGoogleDocs

Edit: Just tried again on my windows machine, no traceback error

[duncantl]

Here's what I suggest Send me email to [email protected] (don't post here) with the output of printing

worksheets[['MachineReadable']]

Then

debug(RGoogleDocs:::processCells2)
sheetAsMatrix(worksheets[['MachineReadable']], header = TRUE, as.data.frame = TRUE, trim = TRUE, stringsAsFactors = FALSE)
saveXML(doc, "/tmp/duncan.xml")

and send me the file /tmp/duncan.xml

[kyeoh]

Sounds good! Thanks for all the help.

[duncantl]

Thanks for sending me those. The key difference is that you get https://...../private/full for the cellfeeds, but for your shared document, I get https://.../private/values

So that is why I am seeing the values and not the formulae.

I suspect if I had write permissions for that doc., I would see the different formulae. I'll try to look into whether we can switch to /private/values to get the cell values.

I'll be busy tomorrow and the day after. So if you haven't heard from me by Friday, please ping me and remind me.

[kyeoh]

Yup, that is the problem.

As a quick hack/workaround, I tried: worksheets$MachineReadable@url<-gsub("full","values",worksheets$MachineReadable@url) worksheets$MachineReadable@listfeed<-gsub("full","values",worksheets$MachineReadable@listfeed) worksheets$MachineReadable@cellsfeed<-gsub("full","values",worksheets$MachineReadable@cellsfeed) worksheets$MachineReadable@edit<-gsub("full","values",worksheets$MachineReadable@edit)

By changing values to full, sheetAsMatrix returns the values!

[duncantl]

Great. Thanks for checking what happened if you changed it. I'll look at where to change this in RGoogleDocs and put a new version up. Again, please remind me if I don't do this by Friday.

[kyeoh]

Hey Duncan, Just shooting you a reminder.

Best, Ken