Google-api-ruby-client: Google Sheets API v4 service_account - struggling with error forbidden: The caller does not have permission (Google::Apis::ClientError)

[jiggneshhgohel]

Ruby: 2.3.1

Rails: 5.0.0

I need to access and update my private Google Sheet through Google Sheets API.
Eventually this shall be done by a recurring background job in my Rails application. Hence I have setup a service account in my Google Cloud Console.

But to start playing with Google Sheets API I have created a script:

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 = "#{sheet_name}!A2:B"

response = service.get_spreadsheet_values(spreadsheet_id, range)

puts 'No data found.' if response.values.empty?

response.values.each do |row|
  # Print columns A and B, which correspond to indices 0 and 1.
  puts "#{row[0]}, #{row[1]}"
end

When I run that script

$ rails runner -e development lib/scripts/google_sheets_api_demo.rb
/home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/google-api-client-0.9.12/lib/google/apis/core/http_command.rb:211:in `check_status': forbidden: The caller does not have permission (Google::Apis::ClientError)
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/google-api-client-0.9.12/lib/google/apis/core/api_command.rb:102:in `check_status'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/google-api-client-0.9.12/lib/google/apis/core/http_command.rb:179:in `process_response'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/google-api-client-0.9.12/lib/google/apis/core/http_command.rb:286:in `execute_once'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/google-api-client-0.9.12/lib/google/apis/core/http_command.rb:107:in `block (2 levels) in execute'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/retriable-2.1.0/lib/retriable.rb:54:in `block in retriable'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/retriable-2.1.0/lib/retriable.rb:48:in `times'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/retriable-2.1.0/lib/retriable.rb:48:in `retriable'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/google-api-client-0.9.12/lib/google/apis/core/http_command.rb:104:in `block in execute'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/retriable-2.1.0/lib/retriable.rb:54:in `block in retriable'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/retriable-2.1.0/lib/retriable.rb:48:in `times'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/retriable-2.1.0/lib/retriable.rb:48:in `retriable'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/google-api-client-0.9.12/lib/google/apis/core/http_command.rb:96:in `execute'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/google-api-client-0.9.12/lib/google/apis/core/base_service.rb:346:in `execute_or_queue_command'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/google-api-client-0.9.12/generated/google/apis/sheets_v4/service.rb:322:in `get_spreadsheet_values'
  from lib/scripts/google_sheets_api_demo.rb:35:in `<top (required)>'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/railties-5.0.0/lib/rails/commands/runner.rb:60:in `load'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/railties-5.0.0/lib/rails/commands/runner.rb:60:in `<top (required)>'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/railties-5.0.0/lib/rails/commands/commands_tasks.rb:138:in `require'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/railties-5.0.0/lib/rails/commands/commands_tasks.rb:138:in `require_command!'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/railties-5.0.0/lib/rails/commands/commands_tasks.rb:104:in `runner'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/railties-5.0.0/lib/rails/commands/commands_tasks.rb:49:in `run_command!'
  from /home/jignesh/.rvm/gems/ruby-2.3.1@my_integrations/gems/railties-5.0.0/lib/rails/commands.rb:18:in `<top (required)>'
  from bin/rails:9:in `require'
  from bin/rails:9:in `<main>'

If I try to access a public sheet it works. I have tried a lot but unable to figure out what is causing this. Initially I used API Key approach for authorization but as per the documentation found at https://developers.google.com/api-client-library/ruby/auth/api-keys it says

When calling APIs that do not access private user data, you can use
simple API keys. These keys are used to authenticate your application
for accounting purposes. The Google Developers Console documentation
also describes API keys.

Note: If you do need to access private user data, you must use OAuth
2.0. See Using OAuth 2.0 for Web Server Applications, Using OAuth 2.0 for Installed Applications, and Using OAuth 2.0 for Server to Server
Applications for more information.

Hence I switched to Service Account approach. But it didn't helped.

Can anybody please let me know what is it I am missing?

Additional Findings:

The following post http://stackoverflow.com/a/38530755/936494 looks helpful but as per my requirement I want to avoid 3-Legged oauth the reason being the sheet is supposed to be accessed and updated through a background-job.

[sqrrrl]

Did you share the sheet with the service account? Service accounts are effectively separate users and need to be on the ACLs the same as any other user.

[jiggneshhgohel]

@sqrrrl You are really a saviour. It worked straightaway.

As per your suggestion I shared the sheet from Google Drive with the email address there against client_email key in the private key JSON I downloaded while creating the service account and ran my script and it returned the data from the sheet.

Presently I have given Can View permission while sharing but I am sure Can Edit should also work and allow me to update the sheet through Sheets API.

On another note do you mind sharing the link to the part of documentation which mentions about such really small but important things? Because I have almost gone through every page on documentation available at https://developers.google.com/sheets/guides/concepts and the nearby tabs but couldn't find anything related to what you suggested.

Thanks a ton.

[sqrrrl]

There's nothing which spells it out exactly. There's an overview of what service accounts are at https://developers.google.com/identity/protocols/OAuth2ServiceAccount. The implication is that service accounts are separate users. They're acting under their own identity. And since sheets have ACLs, the service account must be granted permission (unless it's the owner of the sheet, but it's still on the ACL in that case...) It's no different than if you were using your own account to edit somebody else's sheet. Unless they grant you access, you'd get a permission error.

[gantaa]

@jiggneshhgohel thanks for sharing more info on how you got your permissions from your sheet sorted out. And thanks @sqrrrl this thread sent me down a rabbit hole that fixed my problem. I am working with the Java api. Your link to the Oauth2ServiceAccount was just what I needed to get started unravelling it all. This all started as a basic permission problem, but has exposed a lot more about Credentials with Service Accounts, and the lack of documentation out there for how to accomplish this quickly.

After I had the correct permissions, I still ran into other issues with Service Account requests. Which I was able to resolve from this thread: https://stackoverflow.com/questions/14188239/unable-to-get-token-using-google-apis-google-oauth-java-client-1-12-0-beta-for

Thanks!

[arjamizo]

@gantaa please note that this error can also occur if you did not enable link sharing under share options in google docs

[g4girin]

@arjamizo @sqrrrl I have shared the sheet with my service account and enabled the link sharing.. I am still getting the error. I was using the same code to access my google sheets in my previous machine and the error has started appearing after I changed the system. I have gotten and stored the access token in the new system too.

I seem to have done everything thats required, I am still getting the "Error: The caller does not have permission" error

Thanks in advance

[arjamizo]

@g4girin are you sure you are using OAuth2 access_token for those requests?
Please also check reading the doc by appending key parameter, this has to work without anything else than just GET and key param.

[DaveyJake]

@arjamizo Had I not seen your comment about _link sharing_ I would've never even thought to check who was able to view the sheet. Thank you!