R and Google Sheets API: "ChartSourceRange must have at least one source" error

47 Views Asked by At

I am working with the R package googlesheets4, trying to make an automated workflow for a group that does not use R or APIs. Part of this automation is creating plots out of data submitted through a Google Form.

To make the most automation, I am making my own API calls (outside of the base gs4 functions), something that I am not familiar with. I have understood quite a bit from reading googlesheets4's and Google's documentation but I'm getting an error I don't know what to do with: "ChartSourceRange must have at least one source."

My task right now is figuring out how to change the title of a chart. All charts originally start with a template title and, depending on the data, may change and need to be updated.

I have tried to create an R function that changes a chart's title. The function finds the existing chart data for a given chart, updates the title in that spec, and sends it back to Google.

update_title <- function(ssid, chart_num, new_title) {
  
  # external function I made to pull out all the chart ids for a given spreadsheet id
  chart_ids <- find_chart_ids(ssid)

  # external function I made to pull out all the raw chart data from a given spreadsheet id
  chart_data <- find_chart_data(ssid)

  # pull out original spec for the specified chart
  chart_spec <- chart_data[[chart_num]]$spec
  
  # replace old title with new title (built outside of function)
  chart_spec$title <- new_title
  
  # build full request to change title
  title_req <- list(
    updateChartSpec =
      list(chartId = chart_ids[chart_num],
           spec = chart_spec)
  )

  req <- request_generate(
    "sheets.spreadsheets.batchUpdate",
    params = list(
      spreadsheetId = ssid,
      requests = title_req
    )
  )

  # compile and execute the request
  resp_raw <- request_make(req)
  response <- gargle::response_process(resp_raw)

}

update_title(<ssid>, 8, "Test Title")

The result I get is

Error in update_title(): ! Client error: (400) INVALID_ARGUMENT • Client specified an invalid argument. Check error message and error details for more information. • Invalid requests[0].updateChartSpec: ChartSourceRange must have at least one source.

I'm not sure where I am going wrong since I'm just changing the title (I think). Nothing else about the chart has changed, as far as I know. I would have expected this to operate so clearly there is something I'm missing and I don't know if it's on the R side, the Google side, or both.

0

There are 0 best solutions below