Trying to Optimize Speed of R Shiny App Load Time -- Slow googlesheets4 pull

Question

I'm currently working on an R Shiny App that utilizes googlesheets4 to pull in a large dataset from GoogleSheets upon app launch. Loading in this dataset to my app takes ~2 minutes, which stalls my entire app's load time.

The only visual in my app is based on this GoogleSheets data, so it is very dependent on this specific dataset. Once the dataset gets pulled into my app, it is filter and therefore becomes much smaller (85,000 rows ---> 1,000 rows). This GoogleSheet data is updated every day, so I don't have the luxury of pre-downloading it once and storing it as a .csv forever.

There are two different fixes for this that I have tried but have been unsuccessful...curious if anyone has any thoughts.

1. Have a separate app running. My first idea was to create a separate Shiny app entirely, that would have a sole purpose of pulling the GoogleSheets df once a day. Once it pulls it, it would conduct the necessary data cleaning to get it down to ~1,000 rows, and then would push the smaller df to a different GoogleSheet link. Then, my original app with the visual would just always reference that new GoogleSheet (which would take much less time to load in).

The problem I ran into here is that I couldn't figure out how to write a new GoogleSheets doc using googlesheets4. If anyone has any idea how to do that it would be much appreciated.

2. Temporarily delay the load in of the GoogleSheets data, and let visual populate first. My second idea was to have the code that pulls in the GoogleSheets df be delayed upon launch, letting my visual first populate (using old data) and then have the GoogleSheets pull happen. Once the pull is complete, have the visual re-populate with the updated data.

I couldn't figure out the best/right way to make this happen. I tried messing around with sleep.sys() and futures/promises but couldn't get things to work correctly.

Curious if anyone has any thoughts on my 2 different approaches, or if there's a better approach I'm just not considering...

Thanks!

Answer 1

There is a function called write_sheet that allows you to write data to a google sheet. Does that work for you?

googlesheets4::write_sheet(data = your_data,
                           ss = spread_sheet_identifier,
                           sheet = "name_of_sheet_to_write_in")

If you on only want to add something without deleting everything in the sheet, the function is sheet_append

googlesheets4::sheet_append(data = your_data,
                           ss = spread_sheet_identifier,
                           sheet = "name_of_sheet_to_write_in")

Not sure you can store the credentials in a save way, but couldn't you use github actions? Or alternatively a cron job on your local computer?