I am trying to write a shiny app that allows the following workflow:
- User selects a spreadsheet from Google Sheets.
- The selected sheet is then added to a list of pre-loaded spreadsheets for subsequent manipulation.
However, I can't get the user-selected spreadsheet to carry across into the relevant server code in step 2 above.
The ui.R and server.R are copied below. The publicly available sheet that can be used to load a GoogleSheet has URL: https://docs.google.com/spreadsheets/d/1SPstfa4ykLv5rXeos5wcmGNKTch438ZYy4lOLQcKYlo/edit?usp=sharing
To replicate the error (which says Warning: Error in reactive:dataset_CLInput: object 'newdata_tri' not found) proceed as follows:
- Run the shiny app, making sure all relevant packages are installed.
- Select "New data" option from the top menu.
- Type the above URL into the box asking for a Google Sheets URL. This might then ask you for your google account. The sheet is publicly available to anyone with a link.
- Click action button "Load from URL"
- The data should then show on the right hand side of the screen.
- Click the "Chain Ladder" option from the top menu.
- Select a few options in turn (except the last one) and the data should change on the right hand side.
- Select the last option ("User-defined").
- The error should then occur.
Any ideas how I carry across the GoogleSheets data to the list of options for the user to select from, via the reactive statement at "dataset_CLInput <- reactive({......" ?
Many Thanks
David.
####UI.R
library("shiny")
library("devtools")
library("ChainLadder")
library("markdown")
library("shinydashboard")
library("shinythemes")
library("shinycssloaders")
library("shinyLP")
library("shinyBS")
library("shinyjs")
library(googlesheets4)
library(googledrive)
library(tidyverse)
library("copula")
library("matrixcalc")
library("shinycssloaders")
#library("scales") # used for adding commas as separators for numbers
#library("formattable")
library("DT") # for fancy datatables
library("ggplot2") # for good graphs
Triangle_options<-c("Reserving book", "RAA", "UKMotor","MW2008","MW2014","GenIns","User-selected")
#options(shiny.sanitize.errors = TRUE)
#Special CSS for loading page.
appCSS <- "
loading-content {
position: absolute;
background: #000000;
opacity: 0.9;
z-index: 100;
left: 0;
right: 0;
height: 100%;
text-align: center;
color: #FFFFFF;
}
"
header <- dashboardHeader(disable=TRUE)
sidebar <- dashboardSidebar(disable=TRUE)
jsResetCode <- "shinyjs.resetapp = function() {history.go(0);}" # Define the js method that resets the page
jscloseWindowCode<-"shinyjs.closeWindow = function() { window.close(); }" # define the js method that closes window (doesn't seem to work in Chrome or Safari though)
body<-dashboardBody(fluidPage
(theme=shinytheme("cerulean"),
useShinyjs(),
inlineCSS(appCSS),
# Loading message
div(
id = "loading-content",
h2("Loading...")
),
tags$style(type="text/css", "body {padding-top: 50px;}"),
tags$head(HTML('<link rel="icon", href="favicon-line-chart.ico"/>')),
div(style="padding: 1px 0px; width: '100%'",
titlePanel(title="", windowTitle="Claims reserving")
),
navbarPage("",position="fixed-top",collapsible = TRUE,
##############Menu_item Intro##################
tabPanel("",icon=icon("home"),
jumbotron("GIRA - General Insurance Reserving Application", "An app' to accompany the book.",
buttonLabel = "Introduction")
),
################################Chain Ladder menu##############
tabPanel("Chain Ladder",icon=icon("menu-hamburger",lib="glyphicon"),
fluidPage(
fluidRow(
column(3,
wellPanel(id="CL_inputpanel", #main wellPanel for assumptions
wellPanel(
selectInput("datasetCL", label="Select data triangle",
choices =Triangle_options),
selectInput("unitselect","Unit for display purposes:",c("Units"=1,"Hundreds"=100,"Thousands"=1000,"Millions"=1000000,"Billions"=1000000000),selected=1000)
)
) # close overall assumption wellPanel
),# close first three columns
column(9,
tabBox(width=NULL,
tabPanel("Data and Link Ratios",
h5("Data triangle"),
withSpinner(DT::dataTableOutput("Datatri1"))
)
) # end of tabsetPanel for results and graphs
) # end of column 9
) # end of fluidRow
)# end of fluidPage
), # end of TabPanel for main CL menu
#########################tabPanel for Gsheets stuff
tabPanel("New data",icon=icon("plus",lib="glyphicon"),
fluidPage(
fluidRow(
column(3,
wellPanel(id="GS_inputpanel", #main wellPanel for assumptions
wellPanel(
textInput("GS_URL", "Google Sheets URL", ""),
actionButton("LoadGS","Load from URL",icon = icon("download"))
)
), # close wellPanel for GS data input type
wellPanel(
selectInput("GSunitselect","Unit for display purposes:",c("Units"=1,"Hundreds"=100,"Thousands"=1000,"Millions"=1000000,"Billions"=1000000000),selected=1000)
)
),# close first three columns
column(9,
tabBox(width=NULL,
tabPanel("Data triangle from GoogleSheets",
DT::dataTableOutput("GSDatatri1"),
)
) # end of tabBox
)# end of column 9
) # end of fluidRow
)# end of fluidPage
) # end of TabPanel for main CL menu
#####################################################
) # overall navbarPage closes
) # fluidPage closes
) # overall dashboard body, ending definition of UI
ui <- dashboardPage(header, sidebar, body)
###########SERVER.R###################
server <- function(input, output, session) {
##########Things that apply generally go here
Percent_values<-c(0.5,0.75,0.9,0.95,0.995,0.999)
#note that CL Bootstrap percentile results table column headings will need changing if the above values change
#Change GenIns cohort and development periods of GenIns so that they are as per reserving book
GenIns2<-GenIns
row.names(GenIns2)<-c(0,1,2,3,4,5,6,7,8,9)
colnames(GenIns2)<-c(0,1,2,3,4,5,6,7,8,9)
##############################################
toggleModal(session, "Licensemodal", toggle = "open")
##################################################################
################Chain Ladder######################################
##################################################################
dataset_CLInput <- reactive({
switch(input$datasetCL,
"Reserving book" = GenIns2,
"RAA" = RAA,
"UKMotor" = UKMotor,
"MW2008"=MW2008,
"MW2014"=MW2014,
"GenIns"=GenIns,
"User-selected"=newdata_tri
)
})
#Info button
output$Datatri1 <- DT::renderDataTable({
datasetshow <- dataset_CLInput()
#show the dataset in raw form as in simple R script.
datasetshow<-as.data.frame.array(datasetshow)
datasetshow<-format(round(datasetshow/as.numeric(input$unitselect),digits=0),big.mark=",")
datasetshow
},
extensions=c('Buttons'),selection='none',class = 'stripe compact',
options=list(dom='Bfrtip',buttons=list('copy','print',list(extend='collection',buttons=c('csv','excel','pdf'),text='Download'))
,scrollY=TRUE,scrollX=TRUE,ordering=FALSE,paging=FALSE,searching=FALSE,info=FALSE,
columnDefs=list(list(className='dt-right',targets='_all'))))
##################################################################
hide(id = "loading-content", anim = TRUE, animType = "fade")
################################Gsheets server content
#########all code below only runs when Load data is clicked
observeEvent(input$LoadGS,
{
##########now display the data triangle
output$GSDatatri1 <- DT::renderDataTable({
GS_url<-input$GS_URL # #the G sheets URL that the user has input
xx<-read_sheet(GS_url)
#convert to triangle for ChainLadder package
datasetshow <- as.triangle(as.matrix(xx))
newdata_tri<-datasetshow # save for later use
#show the dataset in raw form as in simple R script.
datasetshow<-as.data.frame.array(datasetshow)
datasetshow<-format(round(datasetshow/as.numeric(input$GSunitselect),digits=0),big.mark=",")
datasetshow
},
extensions=c('Buttons'),selection='none',class = 'stripe compact',
options=list(dom='Bfrtip',buttons=list('copy','print',list(extend='collection',buttons=c('csv','excel','pdf'),text='Download'))
,scrollY=TRUE,scrollX=TRUE,ordering=FALSE,paging=FALSE,searching=FALSE,info=FALSE,
columnDefs=list(list(className='dt-right',targets='_all'))))
}) #ends code after load data action button
#############################Ghseets server stuff ends
} #end the server stuff