Update Tableau Workbook datasource password by R

update_workbook_datasources.R

rm(list=ls())

library(vvtableau)

tableau <- authenticate_server(
  base_url = "https://tableau.server.com/",
  username = "ABC",
  password = "OOXX" 
) 

WBList <- get_server_workbooks(
  tableau,
  page_number = 1,
  page_size = 200,
  include_metadata = FALSE
)

setWorkbookID <- WBList$id[which(WBList$name == 'TS_Workbook')]

get_workbook_datasources <- function(tableau, workbook_id, api_version = 3.4) {
  base_url <- tableau$base_url
  token <- tableau$token
  site_id <- tableau$site_id
  
  url <- paste0(
    base_url,
    "api/",
    '3.4',
    "/sites/",
    site_id,
    "/workbooks/",
    workbook_id,
    "/connections"
  )
  
  api_response <- httr::GET(
    url,
    httr::add_headers("X-Tableau-Auth" = token)
  )
  
  jsonResponseText <- httr::content(api_response, as = "text")
  
  df <- as.data.frame(jsonlite::fromJSON(jsonResponseText), check.names = FALSE) %>%
    tidyr::unnest(names_sep = ".") %>%
    dplyr::rename_with(~ stringr::str_remove(., "connections."), dplyr::everything())
  
  return(df)
}

DBConnection <- get_workbook_datasources(tableau, setWorkbookID)

update_workbook_datasources <- function(tableau, DBConnection, DBUser, DBPassword, api_version = 3.4) {
  base_url <- tableau$base_url
  token <- tableau$token
  site_id <- tableau$site_id
  
  datasource_id <- DBConnection$connection.datasource.id
  connection_id <- DBConnection$connection.id
  setServerAddress <- DBConnection$connection.serverAddress
  setServerPort <- dplyr::case_when(
    DBConnection$connection.type == 'sqlserver' ~ "1433",
    DBConnection$connection.type == 'oracle' ~ "1521",
    .default = "1521"
  )
  
  url <- paste0(
    base_url,
    "api/",
    '3.4',
    "/sites/",
    site_id,
    "/datasources/",
    datasource_id,
    "/connections/",
    connection_id
  )
    
  request_body <- paste0(
    "<tsRequest>",
    "<connection ",
    if (!is.null(setServerAddress)) paste0("serverAddress=\"", setServerAddress, "\" "),
    if (!is.null(setServerPort)) paste0("serverPort=\"", setServerPort, "\" "),
    if (!is.null(DBUser)) paste0("userName=\"", DBUser, "\" "),
    if (!is.null(DBPassword)) paste0("password=\"", DBPassword, "\" "),
    "embedPassword=\"", 'True', "\" ",
    "queryTaggingEnabled=\"", 'False', "\" ",
    "/>",
    "</tsRequest>"
  )
  
  # Make the PUT request to the Tableau REST API
  api_response <- httr::PUT(
    url,
    httr::add_headers("X-Tableau-Auth" = token),
    body = request_body
  )
  
  return(api_response)
}

update_workbook_datasources(tableau, DBConnection, 'TS_USER', 'OOXX')