Trying to display a table from live poll results using R Shiny

[kmera]

Hi, I'm trying to read every second a file which is a google sheet from a poll performed through google forms, and then present the table in a shiny dashboard. I'm using the reactiveFileReader() function but it doesn't work because the table doesn't update, it only happens when I refresh the web page. I also include invalidateLater(1000, session) inside renderDT({ }), and the table never updates, so I conclude that the problem is in the reactiveFileReader().

The code is the following:
library(shiny)
library(DT)
library(googlesheets4)

ui <- fluidPage(
titlePanel("Real Time NPS"),

sidebarLayout(
    sidebarPanel(
    ),
    
    mainPanel(
        DTOutput("tabla1")
    )
)

)

server <- function(input, output, session) {

 rt_data <- reactiveFileReader(
              intervalMillis = 1000,
              session = session,
              filePath = "https://docs.google.com/spreadsheets/d/17SZnF6mo0dNe8vlRjKmOZglzlpoNoYyZQaESkM-kkDU/edit#gid=1323543702",
              readFunc = read_sheet
          )
 
 output$tabla1 <- renderDT({
     rt_data()
     })

}

shinyApp(ui = ui, server = server)

I really appreciate if you could help with this.

Thanks in advance.

Regards,

KM

[benkates]

Hi KM, based on the reactiveFileReader() documentation found here it appears that the function is meant for files on disk (ie: files that have a readable last modified time which I don't believe you'd get with a Google Sheet)

I believe you'd want to use the googlesheets4 package, which you've already included, to create a reactive() that invalidates every second:

  rt_data <- reactive({
    invalidateLater(1000)
    googlesheets4::read_sheet("https://docs.google.com/spreadsheets/d/17SZnF6mo0dNe8vlRjKmOZglzlpoNoYyZQaESkM-kkDU/edit#gid=1323543702")
  })

[kmera]

Hi benkates, thank you for answering me. I did what you suggested but now the problem is the output table is constantly flashing (every second of course), so there is no way to interact with the table because is like every second you press the update button of the web page, so you can just see the first part of the table. Something to stop the invalidateLater function inside the output$table <- renderDT({}) is needed, any suggestion about this new issue?

Regards,

KM

[benkates]

Hi KM, ah - of course - didn't think about that. You'll want to use server-side mode and a proxy to interact with the table, I believe the code below is a nice solution.

You'll want to be aware of the note around data shape for the replaceData() function.

server <- function(input, output, session) {
  
  #read data, invalidate every second
  rt_data <- reactive({
    invalidateLater(1000)
    googlesheets4::read_sheet("https://docs.google.com/spreadsheets/d/17SZnF6mo0dNe8vlRjKmOZglzlpoNoYyZQaESkM-kkDU/edit#gid=1323543702")
  })
  
  #render dataTable, use server mode
  #isolate the reactive data so the table doesn't re-render
  output$tabla1 <- renderDT(server = T,expr = {
    isolate(rt_data())
  })
  
  #declare proxy to control dataTable
  proxy <- dataTableProxy("tabla1")
  
  #when data changes, replace data using server-side proxy
  #set resetPaging/clearSelection to FALSE to avoid loosing interactivity mechanics
  #https://rdrr.io/cran/DT/man/replaceData.html
  observe({
    replaceData(
      proxy,
      rt_data(),
      resetPaging=F,
      clearSelection=F
    )
  })
}

[kmera]

hi benkates, I really appreciate your support, now it works!
The only thing is that sometimes 'Processing' message appears, and a little flashing. I uploaded to shiny server but the app needs to validate google credentials, so I'm going to include code to do that.
Thank you again.

Regards,

KM

[benkates]

Excellent! You can remove the "Processing" message with datatable() options (specifically processing = FALSE), see this reference for more info on options in DT. You'll need to supply a datatable() in your renderDT() as opposed to the reactive you have now.

For google creds - take a look at the authentication options that the package provides. I'm actually getting a quota limit quickly for querying every second so I'd look into setting up your own GCP project with a higher quota there as well. If you're still having trouble I'd recommend opening a new community question.

If you think my last response resolved your issue please mark it as the solution!

[kmera]

Thank you again Ben, I really appreciate your support.

Regards,

KM