Import Google Spreadsheet with formula into R

Question

I am trying to import a Google Spreadsheet with formulas (specifically with =HYPERLINK formulas) in addition to or instead of the values for a column.

There are many helpful posts on how to import Google Spreadsheers into R, but, at least using a CSV file, these import the values but not the formulas of cells.

The "View" -> "All formulas" option in Google Spreadsheets shows all of the formulas but even with this approach the values of the cells but not the formulas are returned.

Any there any relatively straightforward ways to import a Google Spreadsheets with Formulas into R? And if not, are there any other approaches that may work?

score 3 · Answer 1 · answered Jul 08 '15 at 12:57

3

I strongly suspect you only get values I would file an issue ticket (after checking the documentation first, of course) at the googlesheets project which just last week had its first CRAN release.

answered Jul 08 '15 at 12:57

Dirk Eddelbuettel

360,940
56
644
725

Thanks, I'll do that. – Joshua Rosenberg Jul 08 '15 at 13:23

MarkeD · Accepted Answer · 2015-12-30T21:54:54.637

2

All the GoogleSheets -> R packages export a csv from GoogleSheets, so it will only show the values.

The best way sounds like to copy-paste the forumlas of your Google Sheet into text page:

first do a find-replace of (equals)

to (single quote-equals)

'=

This will get rid of the formulas and change them back into strings, then copy-paste back into Google sheets or R.

Hacky :)

edited Dec 30 '15 at 21:54

answered Jul 08 '15 at 13:01

MarkeD

2,500
2
21
35

Sorry to come back to this so much later, but how can you copy-paste the formulas in G-Sheets? – Joshua Rosenberg Dec 30 '15 at 18:09
I edited it a bit, the find-replace you do turns the formulae into normal strings. – MarkeD Dec 30 '15 at 21:55

score 1 · Answer 3 · answered Mar 22 '19 at 09:11

1

gs_read_cellfeed() function from googlesheets library reads googlesheet data in dataframe where each cell as 1 row. value is cell value, input_value - formula

answered Mar 22 '19 at 09:11

Yuriy Barvinchenko

1,465
1
12
17

Import Google Spreadsheet with formula into R

3 Answers3