In R shiny - How do I take inputs and stores them into a google sheet?

Question

I am relatively new to shiny package and any help is great!

I wanted to learn the basics of how to take shiny inputs and store them in a google sheet as the user submits them. I was hoping for a option to link a personal google sheet that will update and save to the specific user (not sure if this is possible). I would love for whoever answers this to provide some example code based on this shiny snippit example and also a short explanation of why the code is there and what the code is ultimately doing.

---

For Example:

library(shiny)

ui <- fluidPage(
  
  selectInput(
    inputId = "transaction_type",
    label = "Select a Transaction Type",
    choices = c("Expense", "Income")
  ),
  numericInput(
    inputId = "transaction_amount",
    label = "How much did you spend or save?",
    value = 0
  ),
  actionButton(
    inputId = submit_button,
    label = "Submit")
)

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

  })
}

shinyApp(ui, server)

---

So my question is: How do I take the selectInput and numericInput and stores them into a google sheet? (using library(googlesheets4))

The data table format I was thinking (simple):

col1. col2.
Expense. $100 Income. $240 Expense. $50 Expense. $12

Further Ideas:

• Maybe a way for the user to enter the link to there google sheet so it saves their personal info for each separate individual (maybe using textInput near the top of app). If this is possible!
• When you click Submit button it will reset the selectInput and numericInput ready for the next transaction
• Display the data table from google sheets to the UI for the user to view all transaction history

Answer 1

Looks good so far. For your server function, you would need to add a call to observeEvent and call maybe googlesheets4::sheet_append. Here is an example (untested):

server <- function(input, output, session) {
    observeEvent(input$submit_button, {
        googlesheets4::sheet_append(
            gsheet_id="your_id", # needs adapt
            data.frame(
                type=input$transaction_type,
                amount=input$transaction_amout
            ),
            sheet=1 # which sheet in the doc
        )
    })
  })
}

The observeEvent function gets called when the button is pressed.

The input$transaction_type and input$transaction_amount hold the current values.

You would need to figure out the gsheet_id of the document where you wish to write to. See here for documentation on sheet_append.

You would also want to validate the input to prevent from writing wrong data.