My goal is to upload a data.frame to Google Sheets, have it do the iterative calculations, then bring the results back into R all using googlesheets4. I'm attempting to replace a step in my workflow where I do the same thing with Excel.
Even with the hitch I write about here, it's notably smoother than using Excel.
Those wondering about a reproducible example/reprex
It would take me some time to figure out how to recreate the circular references causing the need for this workflow. I'm also working with confidential data and so can't share the spreadsheet itself unfortunately.
I'm hoping someone might help with the info I provide in this post. I'll work on a reprex as time allows.
Background
I have a series of equations that involve circular references. The equations involve five categories across which the proportions should sum to one. The iterative calculation is needed to complete the calculation.
What I have been doing is using R to create a series of spreadsheet formulas and using the spreadsheet programs for the iterative calculation step (I have been able to do something similar to the iterative calculation within R, yet not fully).
The issue
The process works in Excel and in Google Sheets if I load it in manually. The issue has to do with creating the spreadsheet through googlesheets4.
If I use googlesheets4's gs4_formula() function on the columns holding formulas, I get errors when loaded into Google Sheets. It looks like this:
You can see that the "check sum" column sums to 1 in the first row. It's summing the columns labeled A-E. The case in the first row works because it has no circular references. Those with circular references are getting the #ERROR message. The ERROR.TYPE function in Google Sheets returns type 8, which corresponds to "for all other errors". So not much info there.
If I skip the conversion with gs4_formula(), Google Sheets won't evaluate the formulas off the bat. It places a leading ' mark to denote the column as text/character format. If go in and manually delete the leading ' marks with Find and replace (using searching within the formulas option), I then get the expected results:
As the screenshot shows, the check_sum column values sum to 1 as expected. This makes me think the issue has to do with the class created by the gs4_formula().
Here are some screenshots from my .Rmd notebook of column "A" as printed to the notebook. The first is converted with gs4_formula():
This is column A without the conversion:
In how they print, I don't see a difference other than the right vs left justification.
Running class() on that or any of the formula columns returns "googlesheets4_formula" "vctrs_vctr. So I've tagged the post with the vctrs tag in addition to googlesheets4.
Enabling Iterative Calculation
I had a couple of stumbles, but with the googlesheets4's documentation I was able to figure out how to create a new Google Sheet and enable iterative calculation pretty quickly. Here's how it worked.
library(googlesheets4)
gs4_create(name = "do iterative calculation",
sheets = to_gs_setup,
iterativeCalculationSettings = list("maxIterations" = 10000,
"convergenceThreshold" = 0.00001))
The "to_gs_setup" is the data.frame containing the formulas. I actually used data.table to create the formulas. Converting it to a tibble or a data.frame first didn't seem make a difference.
In sum
Again, apologies for not having a reprex available. I'm thinking I may file an issue with googlesheets4 on Github. Any insight into what may be happening here would be appreciated. Thanks!