SharePoint resource listing with R

Problem

You (a publisher) want to let your colleagues obtain resource identifiers within various SharePoint Sites owned by your organization for use in programmatically modifying data within SharePoint.

Solution

You are able to create a simple Shiny application that takes in a SharePoint Site URL and queries its Lists endpoint on submit using the logged-in user’s SharePoint access token retrieved by Posit Connect. The application parses the Lists data and outputs a plain text table only if the request is successful, and otherwise an error is displayed.

Shiny

app.R

library(shiny)
library(httr2)
library(bslib)
library(connectapi)

ui <- page_sidebar(
  title = "SharePoint Site Lists",
  sidebar = sidebar(
    title = "SharePoint site URL",
    textInput("sp_site", "SharePoint site", placeholder = "https://my-sharepoint-site.sharepoint.com", value = Sys.getenv("SHAREPOINT_SITE")),
    actionButton("submit", "Submit")
    ),
  layout_columns(
    card(
      card_header("Results"),
      verbatimTextOutput("results")
    )
  )
)


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

  # check if running on Posit Connect
  if (Sys.getenv("RSTUDIO_PRODUCT") == "CONNECT") {
    # initialize Connect API client
    client <- connect()
    # read the user-session-token header
    user_session_token <- session$request$HTTP_POSIT_CONNECT_USER_SESSION_TOKEN
    # grab the OAuth Integration access token using the session token
    credentials <- get_oauth_credentials(client, user_session_token)
    token <- credentials$access_token
  } else {
    # grab the access token from the SHAREPOINT_TOKEN env var if running locally
    token <- Sys.getenv("SHAREPOINT_TOKEN")
  }
  
  # only request sharepoint site lists when the submit button is selected
  observeEvent(input$submit, {
    # do not allow an empty value for the sharepoint site URL
    req(input$sp_site)
    # form the lists endpoint
    path <- paste0(input$sp_site, "/_api/web/lists")
    # request the lists endpoint
    resp <- httr2::request(path) |>
      httr2::req_headers("Accept" = "application/json;odata=verbose") |>
      httr2::req_auth_bearer_token(token) |>
      # to avoid HTTP response error codes being surfaced as R errors
      httr2::req_error(is_error = ~FALSE) |>
      httr2::req_perform() 
    # check the HTTP response code
    http_code <- httr2::resp_status(resp)
    if (http_code == 200) {
      # format response body now that we have the HTTP status code
      resp <- httr2::resp_body_json(resp)
      # save lists request results
      lists <- resp$d$results
      # create a dataframe using the list title and GUID
      df <- tibble::tibble(
        Title = sapply(lists, function(x) x$Title),
        Id = sapply(lists, function(x) x$Id)
      )
      # create and format a simple table of lists data
      rows <- apply(df, 1, function(row) paste(row, collapse = " | "))
      table <- paste(c("Title | Id", rows), collapse = "\n")
      # output the table
      output$results <- renderText({
        table
      })}
    # output the unexpected HTTP code
    if (http_code != 200) {
    output$results <- renderText({
      error <- paste0("Received non-200 HTTP response code: ", http_code)
      error
    })}
  })
}


shinyApp(ui, server)

Running the app locally

Terminal

Sys.setenv(CONNECT_SERVER = "<connect-host>")
Sys.setenv(CONNECT_API_KEY = "<connect-api-key>")
# SHAREPOINT_TOKEN is only required when running the example locally
Sys.setenv(SHAREPOINT_TOKEN = "<sharepoint-token>")
# SHAREPOINT_SITE is optional if you would like to have the
# SharePoint site field populated on load 
# SHAREPOINT_SITE must not contain a trailing slash if provided
Sys.setenv(SHAREPOINT_SITE = "<sharepoint-site>")
shiny::runApp()