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.