以google sheet作為資料庫製作線上查詢網頁

#以google sheet做為資料庫
#install.packages("lubridate")
#install.packages("googlesheets4")
#install.packages("dplyr")
#install.packages("shiny")
#install.packages("DT")
#install.packages("magrittr")
library(lubridate)
library(googlesheets4)
library(dplyr)
library(shiny)
library(DT)
library(magrittr)
gs4_deauth()
#starttime <- 7
#endtime <- 17
google_sheet_URL <- "https://docs.google.com/spreadsheets/d/1cAEG8RqpJoDJukBY83ffK8fvdHSV2pGT3F1L9C8zQlE/edit?usp=sharing"
google_sheet_URL2 <- "https://docs.google.com/spreadsheets/d/1ZtHk0VsMcYMSXDOnUyBWcVJvv4qPJtah1Ylst7139D4/edit?usp=sharing"
gdata <- read_sheet(google_sheet_URL) #%>% as.data.frame() 
gdata$`date-D` %<>% ymd()
gdata$time_H %<>% as.numeric() 
head(gdata)

gdata2 <- read_sheet(google_sheet_URL2) %>% as.data.frame() 
gdata2$`date-D` %<>% ymd()
gdata2$`population-mm` %<>% as.numeric() 
gdata2$Last_Week_Total %<>% as.numeric() 
head(gdata2)


ui <- fluidPage(
    titlePanel("查詢降雨加總"),
    dateRangeInput("daterange", "Date range:",
                   min = min(gdata$`date-D`),
                   max = max(gdata$`date-D`)),
    mainPanel(
        DTOutput("dataoutput")
    ))
server <- function(input, output) {
    output$dataoutput <- renderDT(gdata[,-2] %>%
                                             filter(gdata$`date-D`>= as.Date(input$daterange[1],"%Y-%m-%e") & gdata$`date-D`<= as.Date(input$daterange[2],"%Y-%m-%e")) %>%  
                                             group_by(`date-D`) %>%
                                             summarise_all(sum) %>% 
                                             mutate(Last_Week_Total= gdata2 %>%
                                                        filter(gdata2$`date-D`>= as.Date(input$daterange[1],"%Y-%m-%e") & gdata2$`date-D`<= as.Date(input$daterange[2],"%Y-%m-%e")) %>%
                                                        select(`Last_Week_Total`)) %>% 
                                             DT::datatable(colnames =c("Date","Total rainfall","Last week total rainfall"),filter = 'top',extensions = 'Buttons', options = list(
                                                 dom = 'Bfrtip', buttons = c('copy', 'csv', 'excel', 'pdf', 'print'))) %>% 
                                             formatStyle('population-mm',
                                                         color = styleInterval(0,c('blak','red'))) %>% 
                                             formatStyle('Last_Week_Total',
                                                         color = styleInterval(0,c('blak','red'))) %>% 
                                             formatRound('Last_Week_Total',1)
    ) 
}

# Run the application 
shinyApp(ui = ui, server = server)