Google-api-ruby-client: Sheets4 API - Trying to update values in cells in a column but the updates are not happening

[jiggneshhgohel]

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.

| | A | B | C | D |
| --- | --- | --- | --- | --- |
| 1 | UID | POS | NEU | NEG |
| 2 | 39f626f5-d9bc-43d4-89e1-d2cd987cd736 | 22 | 0 | 5 |
| 3 | 01b7575d-1c6d-4398-a996-ca1cc024c45e | 105 | 6 | 4 |

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?

[jiggneshhgohel]

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?

[sqrrrl]

The client uses snake_case for properties (unlike the underlying JSON API). Try changing majorDimension to major_dimension.

I added a note to the readme to make this clearer.

[jiggneshhgohel]

Try changing majorDimension to major_dimension.

@sqrrrl Thanks. That didn't helped. I changed it as per your suggestion and ran the script again and got the same response:

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

[jiggneshhgohel]

@sqrrrl Cracked this. Had to spend time to find the root cause by digging the source code on what was happening internally.

Summary:

Instead of using String-keys, Symbol-keys were to be used in ValueRangeObject. Looks like a simple thing but took me few hours to figure out this just because it is not mentioned about anywhere in the documentation.

Details:

I took the starting point as following in method Google::Apis::SheetsV4::SheetsService#update_spreadsheet_value

command.request_representation = Google::Apis::SheetsV4::ValueRange::Representation
command.request_object = value_range_object

where command is an instance of Google::Apis::Core::ApiCommand which is instantiated by
following line in Google::Apis::SheetsV4::SheetsService#update_spreadsheet_value method.

command = make_simple_command(:put, 'v4/spreadsheets/{spreadsheetId}/values/{range}', options)

Then I moved to Google::Apis::Core::ApiCommand#prepare! method and inspected following line

self.body = request_representation.new(request_object).to_json(skip_undefined: true)

in that method.

request_representation in that method held the class name Google::Apis::SheetsV4::ValueRange::Representation

request_object in that method held my following 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)"]
   ]
}

Then I moved to finding the definition for class Google::Apis::SheetsV4::ValueRange::Representation which I found at https://github.com/google/google-api-ruby-client/blob/0.9.12/generated/google/apis/sheets_v4/representations.rb#L307

Moving deeper into the class hierarchy I proceeded to find out the definition of class Google::Apis::Core::JsonRepresentation which is extended by Google::Apis::SheetsV4::ValueRange::Representation. I found its definition at https://github.com/google/google-api-ruby-client/blob/0.9.12/lib/google/apis/core/json_representation.rb#L121

Then I checked https://github.com/apotonick/representable documentation on how it works but couldn't get it for sometime. Then I experimented by putting debug statements in each method defined in module Google::Apis::Core::JsonRepresentationSupport::JsonSupport and ran my script and while the script ran my debug statements were printed but there were a lot of them. I searched through them and found that major_dimension name was printed by method property(name, options = {})/json_representation.rb#L90

Next I tried inspecting each argument value passed to the method i.e. name, options etc and I found this

>>>>>>>>> DEBUG property: major_dimension; Symbol; :major_dimension; {:as=>"majorDimension"}

corresponding to the following debug statement I added in that method:

>>>>>>>>> DEBUG property: #{name}; #{name.class}; #{name.class.inspect}; #{options}

Now I was wondering from where the option {:as=>"majorDimension"} is being passed so I deliberately put a method call nil.abc so that an exception gets raised and I can trace the stack from where the method was being called. And strangely I ended up at another Google::Apis::SheetsV4::ValueRange::Representation class defined in the same file https://github.com/google/google-api-ruby-client/blob/0.9.12/generated/google/apis/sheets_v4/representations.rb but at line 1244

So I came to be known about a fact there are two classes defined in the same file having same name Google::Apis::SheetsV4::ValueRange::Representation and the one at line 1244 is what is being used at runtime and it did make sense now on how the option {:as=>"majorDimension"} was being received in property method. property method is a declarative method provided by Representable gem and in Google::Apis::Core::JsonRepresentationSupport::JsonSupport module it is being overridden to intercept the execution by hooking in invocation to method set_default_options(name, options).

The set_default_options(name, options) method adds customized options to the options hash received by it one of which is an :if option. Representable gem allows excluding properties from being written to a JSON by passing an :if lambda if the lambda evaluates to false.

The set_default_options(name, options) method was setting a lambda by invoking the if_fn(name)

I put a debug statement in if/else statement in lambda defined in if_fn method in following manner:

if respond_to?(:key?)
   puts ">>>>>>>>>>>>> self: #{self.inspect}"
   puts ">>>>>>>>>>>>> self.key?(name): #{self.key?(name)}"
   puts ">>>>>>>>>>>>> instance_variable_defined?(ivar_name): #{instance_variable_defined?(ivar_name)}"

   self.key?(name) || instance_variable_defined?(ivar_name)
else
  instance_variable_defined?(ivar_name)
end

and ended up seeing following output for major_dimension property:

>>>>>>>>>>>>> self: {"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)"]]}
>>>>>>>>>>>>> self.key?(name): false
>>>>>>>>>>>>> instance_variable_defined?(ivar_name): false

Which looked strange at first because why self was a Hash then why self.key?(name) returned false and then it clicked that in the Hash a String-type key is present "major_dimension", however the property name being received Symbol-type :major_dimension.

Finally I changed 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 and guess what the script worked and I got the output

>>>>>>>>>> update_res: #<Google::Apis::SheetsV4::UpdateValuesResponse:0x00000006ead318 @updated_range="Sheet1!A1:D5", @updated_columns=4, @spreadsheet_id="MY SPREADSHEET ID HERE", @updated_rows=5, @updated_cells=20>

and on Drive my sheet was updated with the data I sent via API.

[sqrrrl]

Yeah, there's a section in the readme about that. Sorry I didn't notice that earlier and could have saved you a little bit of trouble :)

"While the API will always return instances of schema classes, plain hashes are accepted in method calls for convenience. Hash keys must be symbols matching the attribute names on the corresponding object the hash is meant to replace."

[jiggneshhgohel]

A rectification in bold text in following statement:

Looks like a simple thing but took me few hours to figure out this just because it is not mentioned about anywhere in the documentation.

After adding above comment I again gone through the documentation on https://github.com/google/google-api-ruby-client and ended up at section Hashes mentioning

Hash keys must be symbols matching the attribute names on the corresponding object the hash is meant to replace. For example:

I think due to lack of specific-API context in that statement or I guess it applies to each API but the example demonstrating Drive API usage is what mislead from focusing on the must part in that statement. If something is must why not emphasize it clearly and at the top.

One more reason I can think of I had to struggle getting around a basic use-case like writing to a spreadhseet is lack of any Write example available in the sample https://github.com/google/google-api-ruby-client/blob/master/samples/cli/lib/samples/sheets.rb which forced me to go by the examples given in documentation at https://developers.google.com/sheets/samples/writing and use the structure documented in there.

Anyway any effort made is never wasted. With this experience I came to know about few internals of how Google Sheets API Ruby Client work.

Thanks.