Google Sheets v4 API Ruby - Trying to update values in cells in a column but the updates are not happening

Question

Ruby: 2.3.1

Rails: 5.0.0

I have a sheet containing data like below: A, B , C, D represents the columns name in the sheet and 1, 2, 3 represents the row numbers.

I have written a script mentioned about here and from it I am trying to update the values in cells B2 and B3 and for updating I am using the following code:

spreadsheet_id = "<My Private Spreadsheet ID>"

range = 'Sheet1!B2:B3'

value_range_object = { 
  "majorDimension"=>"COLUMNS", 
  "values"=>[
     [9, 18] # B2 should be updated with value 9 and B3 should be updated with value 18
   ]
}

service = Google::Apis::SheetsV4::SheetsService.new
service.authorization = authorization
update_res = service.update_spreadsheet_value(spreadsheet_id, range, value_range_object, value_input_option: 'USER_ENTERED')

puts ">>>>>>>>>> update_res: #{update_res.inspect}"

But that puts is printing

>>>>>>>>>> update_res: #<Google::Apis::SheetsV4::UpdateValuesResponse:0x000000068acc70 @updated_range="Sheet1!B2", @spreadsheet_id="<My Private Spreadsheet ID>">

and there are no updates made to the sheet.

As per the documentation when an update is successful following kind of response should be received

{
  "spreadsheetId": spreadsheetId,
  "updatedRange": "Sheet1!B1:D5",
  "updatedRows": 3,
  "updatedColumns": 2,
  "updatedCells": 6,
}

Comparing that with the response I am getting there is definitely something which is missing or incorrect in my code and which is hampering the updates to be applied.

Can I please get some guidance to get past my problem and hence achieve my desired goal?

Update

I tried out this with a slightly different approach following the example given at https://developers.google.com/sheets/samples/writing#write_a_single_range

As part of this I created a fresh new blank sheet on Google Drive, shared it with my Service Account and tried to update it with the following code:

require 'google/apis/sheets_v4'

ENV["GOOGLE_ACCOUNT_TYPE"] = 'service_account'
ENV["GOOGLE_CLIENT_EMAIL"] = '<client_email_from_downloaded_json_here>'
ENV["GOOGLE_PRIVATE_KEY"] = "<private_key_from_downloaded_json_here>"

SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS

authorization = Google::Auth.get_application_default(SCOPE)

# Initialize the API
service = Google::Apis::SheetsV4::SheetsService.new
service.authorization = authorization

spreadsheet_id = '<MY SPREADSHEET ID HERE>'

sheet_name = 'Sheet1'

range = "Sheet1!A1:D5"

value_range_object = {
  "majorDimension" => "ROWS",
  "values" => [
     ["Item", "Cost", "Stocked", "Ship Date"],
     ["Wheel", "$20.50", "4", "3/1/2016"],
     ["Door", "$15", "2", "3/15/2016"],
     ["Engine", "$100", "1", "30/20/2016"],
     ["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
   ]
}


update_res = service.update_spreadsheet_value(spreadsheet_id, range, value_range_object, value_input_option: 'USER_ENTERED')

puts ">>>>>>>>>> update_res: #{update_res.inspect}"

But strangely that is also not working. I am getting the following response

>>>>>>>>>> update_res: #<Google::Apis::SheetsV4::UpdateValuesResponse:0x00000006540348 @updated_range="Sheet1!A1", @spreadsheet_id="MY SPREADSHEET ID HERE">

and the sheet is also blank after executing the code.

Can anybody please take a look into this and comment on what is going on?

Answer 1

This is solved.

The solution is to use Symbol-keys, instead of String-keys in ValueRangeObject. That is I had to change my value_range_object from following

{
  "major_dimension" => "ROWS",
  "values" => [
     ["Item", "Cost", "Stocked", "Ship Date"],
     ["Wheel", "$20.50", "4", "3/1/2016"],
     ["Door", "$15", "2", "3/15/2016"],
     ["Engine", "$100", "1", "30/20/2016"],
     ["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
   ]
}

TO

value_range_object = {
  major_dimension: "ROWS",
  values: [
     ["Item", "Cost", "Stocked", "Ship Date"],
     ["Wheel", "$20.50", "4", "3/1/2016"],
     ["Door", "$15", "2", "3/15/2016"],
     ["Engine", "$100", "1", "30/20/2016"],
     ["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
   ]
}

Take a note that hash keys are Symbols and not Strings.

Looks like a simple thing but took me few hours to figure out this.

More details can be found at https://github.com/google/google-api-ruby-client/issues/462#issuecomment-243927914

Thanks.