能否将Google Sheets数据流式传输至R？如何实现Google Sheets数据实时同步到ShinyApp？

Great questions! Let’s break this down with clear, actionable steps and code you can use right away.

1. Can you stream Google Sheets data into R? #

Short answer: Yes—while Google Sheets doesn’t offer a native "streaming" API that pushes updates directly to R, you can simulate real-time sync by regularly polling the sheet for changes. This approach works seamlessly for most use cases, including keeping a Shiny App in lockstep with sheet updates.

2. Real-Time Sync for Shiny Apps (Core Requirement) #

Here’s a step-by-step solution to import your Google Sheets data into a Shiny App and ensure it auto-updates whenever the sheet changes:

Step 1: Install Required Packages #

First, get the libraries you’ll need to connect Google Sheets and build the Shiny App:

install.packages(c("googlesheets4", "shiny"))
library(googlesheets4)
library(shiny)

Step 2: Authenticate with Google Sheets #

Run this once to grant R access to your Google Sheets. You’ll be redirected to a browser to log in and confirm permissions:

gs4_auth()

For deployed Shiny Apps (like on shinyapps.io), use a service account instead of manual authentication. Create one in the Google Cloud Console, download the JSON key file, and authenticate with gs4_auth(path = "path/to/your/service-account-key.json") to avoid needing user input.

Step 3: Build the Shiny App with Real-Time Sync #

We’ll use reactivePoll()—this function checks if the sheet has been updated at a set interval, and only reloads data when changes are detected (saving unnecessary API calls).

Here’s a complete working example:

ui <- fluidPage(
  titlePanel("Real-Time Google Sheets Data in Shiny"),
  mainPanel(
    dataTableOutput("live_sheet_data")
  )
)

server <- function(input, output, session) {
  # Replace with your Google Sheet URL or unique ID
  SHEET_LOCATION <- "your-google-sheet-url-or-id"

  # Function to check when the sheet was last modified
  check_update_timestamp <- function() {
    sheet_meta <- gs4_get(SHEET_LOCATION)
    sheet_meta$updated # Returns the last modified timestamp
  }

  # Function to load fresh data from the sheet
  load_latest_data <- function() {
    read_sheet(SHEET_LOCATION)
  }

  # Sync data in real-time using reactivePoll
  live_data <- reactivePoll(
    intervalMillis = 5000, # Check for updates every 5 seconds (adjust as needed)
    session = session,
    checkFunc = check_update_timestamp,
    valueFunc = load_latest_data
  )

  # Display the synced data as an interactive table
  output$live_sheet_data <- renderDataTable({
    live_data()
  })
}

shinyApp(ui, server)

Key Notes: #

• intervalMillis: Tweak this number to balance responsiveness and API usage. For example, 1000 = check every 1 second, 10000 = check every 10 seconds.
• Sheet Location: Use either the full sheet URL (e.g., https://docs.google.com/spreadsheets/d/123abc/edit) or just the unique ID from the URL (123abc).
• Customization: Replace dataTableOutput with tableOutput for a simpler view, or add ggplot2 visualizations using the synced live_data() object.

Test the Sync: #

1. Run the Shiny App.
2. Open your Google Sheet and edit a cell or add a row.
3. Wait for the interval you set—your app will automatically refresh to show the updated data.

内容的提问来源于stack exchange，提问作者Mayur Shende