Integrate data frame from R with Smartsheet

Question

I want to add data frame row by row to a specific sheet in Smartsheet. My data frame fields design is: "Name", "Score", and "Email". and the sheet name from Smartsheet is "Student Scores" with the same field names from R.

How to integrate the information from the data frame to "Student Scores" in smartsheet using API and Sheet ID.

I tried the following, but doesn't work:

# Install and Load Necessary R Packages
if (!require("httr")) install.packages("httr")
if (!require("jsonlite")) install.packages("jsonlite")
library(httr)
library(jsonlite)

# Set Up API Credentials
api_key <- Sys.getenv("SMARTSHEET_API_KEY")  # Replace with your API key or use an environment variable

# Function to Fetch Column IDs
get_column_ids <- function(sheet_id, api_key) {
  base_url <- "https://api.smartsheet.com/2.0/sheets/"
  response <- GET(
    url = paste0(base_url, sheet_id),
    add_headers(Authorization = paste0("Bearer ", api_key))
  )
  
  if (status_code(response) != 200) {
    stop("Failed to fetch sheet details. Status Code: ", status_code(response))
  }
  
  sheet_data <- fromJSON(rawToChar(response$content))
  columns <- sheet_data$columns
  column_ids <- setNames(sapply(columns, function(col) col$id), sapply(columns, function(col) col$title))
  
  return(column_ids)
}

# Function to Add Rows to Smartsheet
add_rows_to_smartsheet <- function(df, sheet_id, column_ids, api_key) {
  base_url <- "https://api.smartsheet.com/2.0/sheets/"
  rows_to_add <- lapply(1:nrow(df), function(i) {
    list(cells = lapply(names(df), function(column_name) {
      list(columnId = column_ids[column_name], value = df[i, column_name])
    }))
  })
  
  body <- list(rows = rows_to_add)
  response <- POST(
    url = paste0(base_url, sheet_id, "/rows"),
    add_headers(Authorization = paste0("Bearer ", api_key), "Content-Type" = "application/json"),
    body = toJSON(body),
    encode = "json"
  )
  
  return(content(response))
}

# Specify Sheet ID
sheet_id <- "YOUR_SHEET_ID"  # Replace with your actual Sheet ID

# Fetch Column IDs
column_ids <- get_column_ids(sheet_id, api_key)

# Prepare Data Frame
df <- data.frame(
  Name = c("Alice", "Bob", "Charlie"),
  Scores = c(85, 92, 78),
  Email = c("[email protected]", "[email protected]", "[email protected]")
)

# Add Rows to Smartsheet
result <- add_rows_to_smartsheet(df, sheet_id, column_ids, api_key)
print(result)

When I run the code, the following error show:

Error in get_column_ids(sheet_id, api_key) : 
  Failed to fetch sheet details. Status Code: 403

Any hint or solution?

Thanks