Trouble accessing information recently added to a google sheet

[brookezkeene]

As of 2022-02-03, the googlesheets package has been archived on CRAN. I'm closing all issues and pull requests prior to archiving this GitHub repo.

Learn more at:

https://github.com/jennybc/googlesheets#readme

[jennybc]

What is the error? I suspect it is a question of timing. And republishing the app ... takes time. Basically, when you do a write operation, there is no precise guarantee from Google on how quickly that information is available for a read operation. Can you use Sys.sleep() to ensure some bare minimum of a gap exists and see if that helps? FYI I have noticed this sort of performance issue is highly intermittent.

You should definitely keep .httr-oauth files out of version control and off of GitHub. I see at least one in your repo btw.

[brookezkeene]

The problem is that I am consistently not able to access information that I have added to a google sheet on the published shiny app. The only time I am able to access it is after I have republished the app. I do not think that Sys.sleep() will do anything because even waiting for 10 minutes and then running the code that accesses that information does not work.

[jennybc]

What is the error?

[brookezkeene]

It is not throwing an error because I have an if statement that first checks to make sure the data is in the google sheet. It simply does not find the data in the last row.

[brookezkeene]

It appears I do not have to republish anymore, but it does seem to take a long time (~1 hour). You said this might just be inherent to Google Sheets? Is there anyway you can think of to improve this?

I am able to access the information almost immediately when I run the app on my local server. Why is this?

[jennybc]

It's impossible for me to say what's going on but will throw out various ideas based on what you're saying:

When you edit the sheet, do you store the return value (containing up-to-date metadata for the sheet) and use that for subsequent read access? That is the advised workflow and perhaps you are doing something where that actually matters. The fact that re-deploying the app helps suggests that maybe what really matters is re-registering the sheet.

Are you absolutely 100% sure the write is succeeding with the deployed app? If the write is not succeeding there (but is succeeding when you run the app locally), that is another explanation for what you're seeing.

Are there differences in auth/token setup that are worth investigating?

Basically, I think you've got to start inserting debugging logic and checking that each thing you believe is happening, is actually happening. Question things you "know" to be true and try to prove them.

[brookezkeene]

We are sure that the write is succeeding with our deployed app because we are able to confirm that the Google Sheet has been updated. However, it is definitely possible that we should be storing the data in variables accessible by our server for subsequent use.

Additionally, I thought I was reregistering the sheet by calling gs_key again, but I will try using gs_gs().

[jennybc]

All functions like gs_edit_cells() re-register the sheet at the very end and return that object, FYI. That's why a workflow where you keep updating/overwriting the googlesheet object as you go is recommended.

https://github.com/jennybc/googlesheets/blob/12abb0227342fa83c5e23f53421924bae62278b7/R/gs_edit_cells.R#L166-L167

[jennybc]

For example, depending on how you read the data, googlesheets might limit the read to the sheet's known extent, which it takes from the metadata. So if you add a row but don't re-register the sheet (i.e. you don't update the metadata), that could explain why you don't see the new data.

Until you redeploy the app ... but what really mattered was re-registering the sheet.

So there's one hypothesis. I have no idea why you would get different behaviour with exactly the same Shiny app code on a server and locally, though.