Erro to connect Google Sheets API

Question

I'm trying to follow these Ruby quickstart code(https://developers.google.com/sheets/api/quickstart/ruby#step_2_set_up_the_sample),

require "google/apis/sheets_v4"
require "googleauth"
require "googleauth/stores/file_token_store"
require "fileutils"

OOB_URI = "urn:ietf:wg:oauth:2.0:oob".freeze
APPLICATION_NAME = "Google Sheets API Ruby Quickstart".freeze
CREDENTIALS_PATH = "credentials.json".freeze
# The file token.yaml stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
TOKEN_PATH = "token.yaml".freeze
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY

##
# Ensure valid credentials, either by restoring from the saved credentials
# files or intitiating an OAuth2 authorization. If authorization is required,
# the user's default browser will be launched to approve the request.
#
# @return [Google::Auth::UserRefreshCredentials] OAuth2 credentials
def authorize
  client_id = Google::Auth::ClientId.from_file CREDENTIALS_PATH
  token_store = Google::Auth::Stores::FileTokenStore.new file: TOKEN_PATH
  authorizer = Google::Auth::UserAuthorizer.new client_id, SCOPE, token_store
  user_id = "default"
  credentials = authorizer.get_credentials user_id
  if credentials.nil?
    url = authorizer.get_authorization_url base_url: OOB_URI
    puts "Open the following URL in the browser and enter the " \
         "resulting code after authorization:\n" + url
    code = gets
    credentials = authorizer.get_and_store_credentials_from_code(
      user_id: user_id, code: code, base_url: OOB_URI
    )
  end
  credentials
end

# Initialize the API
service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = APPLICATION_NAME
service.authorization = authorize

# Prints the names and majors of students in a sample spreadsheet:
# https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
spreadsheet_id = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
range = "Class Data!A2:E"
response = service.get_spreadsheet_values spreadsheet_id, range
puts "Name, Major:"
puts "No data found." if response.values.empty?
response.values.each do |row|
  # Print columns A and E, which correspond to indices 0 and 4.
  puts "#{row[0]}, #{row[4]}"
end

but I'm getting this error:

Traceback (most recent call last):
    5: from quickstart.rb:42:in `<main>'
    4: from quickstart.rb:22:in `authorize'
    3: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/googleauth-1.1.2/lib/googleauth/client_id.rb:67:in `from_file'
    2: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/googleauth-1.1.2/lib/googleauth/client_id.rb:67:in `open'
    1: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/googleauth-1.1.2/lib/googleauth/client_id.rb:70:in `block in from_file'
/home/vagrant/.rvm/gems/ruby-2.7.2/gems/googleauth-1.1.2/lib/googleauth/client_id.rb:84:in `from_hash': Expected top level property 'installed' or 'web' to be present. (RuntimeError)

I think it's a authentication issue.

I'm using a service accounts authentication https://developers.google.com/workspace/guides/create-credentials#create_credentials_for_a_service_account

Maybe the code doesn't work for service account authentication, but I couldn't find any documentation that talks about how to integrate Google Sheets with my code using service account authentication.

Can you share the part in your code in which you are performing the authorization for the service account? Also, what credentials are you using for this app - are the ones created for the service account? — ale13, Mar 30 '22 at 13:39
I created the json like this: `Open the Google Cloud Console. At the top-left, click Menu menu > IAM & Admin > Service Accounts. Select your service account. Click Keys > Add keys > Create new key. Select JSON, then click Create.` — Felipe Cristiano, Mar 30 '22 at 13:46
And how are you adding these credentials for your code, can you share the snipped for this as well? — ale13, Mar 30 '22 at 13:56

Ricky Nguyen · Answer 1 · 2022-03-30T15:17:50.303

Okay, I'm gonna say that your credentials configuration did not setup properly.

I'm gonna follow these steps if I were you:

Step 1: Install this gem

https://github.com/googleapis/google-auth-library-ruby

Step 2: Get your Google Service Account Credentials, when this step is done, you should get a .json file. I'm gonna call it google_service_account_credentials.json file.

Step 3: Try this snippet:

require "google/apis/sheets_v4"
require "googleauth"

CREDENTIALS_PATH = "google_service_account_credentials_path/google_service_account_credentials.json"
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY

# Author & Authen with Google by Google Service Account Credentials
authorizer = Google::Auth::ServiceAccountCredentials.make_creds(
  json_key_io: File.open(CREDENTIALS_PATH),
  scope: SCOPE)

authorizer.fetch_access_token!

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

# Some demo code to prove it run
# Prints the names and majors of students in a sample spreadsheet:
# https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
spreadsheet_id = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
range = "Class Data!A2:E"
response = service.get_spreadsheet_values spreadsheet_id, range
puts "Name, Major:"
puts "No data found." if response.values.empty?
response.values.each do |row|
  # Print columns A and E, which correspond to indices 0 and 4.
  puts "#{row[0]}, #{row[4]}"
end

Step 4: Enjoy (hope it helps) <3

Further information you can get from here, I just customize the answer so it suit your need :")) https://stackoverflow.com/a/56747012/6542152

You need to explain further for your answer instead of pasting links only — Nick Vu, Mar 30 '22 at 14:41
@NickVu thank you, this is my first answer so far, gonna edit it now :") — Ricky Nguyen, Mar 30 '22 at 14:58
@RickyNguyen I think that there is a typo on `service.authorization = authorize`. It should be `service.authorization = authorizer`. Otherwise, thank you very much for your answer. It helped a lot! — pedrosfdcarneiro, May 26 '22 at 10:33

Linda Lawton - DaImTo · Answer 2 · 2022-03-30T14:00:17.080

0

The code you are using from the Ruby quickstart Is designed to for use with a Authorization credentials for a desktop application

This code can be used with either an installed client or a web client created on google cloud console. Which is what the error message is telling you

Expected top level property 'installed' or 'web' to be present.

The code for use with a service account is not the same

Links: Oauth service

edited Mar 30 '22 at 14:00

answered Mar 30 '22 at 13:34

Linda Lawton - DaImTo

106,405
32
180
449

this code returned me: `Can't convert Google::Auth::ServiceAccountCredentials into String or Array. (TypeError)` using the service account authentication json. I don't think Oauth is the best choice as I just need to dynamically create sheets. – Felipe Cristiano Mar 30 '22 at 13:40
I think you should check the documentation on the page i linked. I have been unable to find any better source for a sample with service accounts. – Linda Lawton - DaImTo Mar 30 '22 at 14:01
theres a few more examples here you may want to look around https://stackoverflow.com/q/50376820/1841839 – Linda Lawton - DaImTo Mar 30 '22 at 14:04

Erro to connect Google Sheets API

2 Answers2