google-api-ruby-client: batchupdate spreadsheet values

Question

Following the instructions laid out here to perform a batch update.

First, I built out the required ValueRange object like so:

values = [
  ["first", "title", "second", "url", "long"]
]

range = "WorksheetInParentSpreadsheet!A1:M"

data = Google::Apis::SheetsV4::ValueRange.new
data.values = values
data.major_dimension = 'ROWS'
data.range = range

Next used info in 1 above to built out the request like so:

spreadsheet = `spreadsheet_object`
request = Google::Apis::SheetsV4::BatchUpdateValuesRequest.new
request.data = data
request.value_input_option = value_input_option

Finally, I make the request:

sheet_service.batch_update_values(spreadsheet.id, request)

I expect this to update the sheet but instead I get this error:

NoMethodError Exception: undefined method `each_with_index' for #<Google::Apis::SheetsV4::ValueRange:0x00007ffc7826c2e0>

It appears that ValueRange either:

• does not respond to each_with_index
• or the ValueRange object provided (data) is missing an enumerable object

Usually would inspect the gem I am using but in this case response doesn't provide line or a file to narrow the error down to.

I am stumped. What am I missing?

Complete Code:

values = [
  ["first", "title", "second", "url", "long"]
]

range = "WorksheetInParentSpreadsheet!A1:M"

data = Google::Apis::SheetsV4::ValueRange.new
data.values = values
data.major_dimension = 'ROWS'

spreadsheet = `spreadsheet_object`
request = Google::Apis::SheetsV4::BatchUpdateValuesRequest.new
request.data = data
request.value_input_option = value_input_option
data.range = range

sheet_service.batch_update_values(spreadsheet.id, request)

Answer 1

Modification points:

• At the method of spreadsheets.values.batchUpdate in Sheets API, the value of property of data is required to be an array.
• value_input_option is not declared.

When above points are reflected to your script, it becomes as follows.

Modified script:

value_input_option = "USER_ENTERED" # Please set this for your actual situation.

values = [
  ["first", "title", "second", "url", "long"]
]

range = "WorksheetInParentSpreadsheet!A1:M"

data = Google::Apis::SheetsV4::ValueRange.new
data.values = values
data.major_dimension = 'ROWS'

spreadsheet = `spreadsheet_object`
request = Google::Apis::SheetsV4::BatchUpdateValuesRequest.new

request.data = [data] # <--- Modified

request.value_input_option = value_input_option
data.range = range

sheet_service.batch_update_values(spreadsheet.id, request)

• In this modification, it supposes that the values of sheet_service and spreadsheet.id are the valid values. Please be careful this.

Reference:

• Method: spreadsheets.values.batchUpdate