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.

Answer 1

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

Answer 2

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

Answer 3

Apart from all the information which has been shared, it's also important to note the fact that creating a service account is not enough. You also have to perform domain wide delegation and impersonate another user in your domain.

The main purpose of granting domain-wide authority to a service account is for these accounts to be able to access data on behalf of a user in your domain as otherwise the service account acts like just another account and it is trying to access its own data from Sheets.

After creating the service account and retrieving its credentials, you will have to impersonate another user in your domain by using a snippet similar to this

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

creds = ENV['GOOGLE_SERVICE_ACCOUNT'] 
creds_json = JSON.parse(creds)
creds_json_io = StringIO.new(creds_json.to_json)
auth = Google::Auth::ServiceAccountCredentials.make_creds(
  json_key_io: creds_json_io,
  scope: [Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY]
)
auth.sub = 'ACCOUNT_TO_IMPERSONATE'
auth.fetch_access_token

Then, you can use Sheets API as you would normally.

However, domain wide delegation can only be used within a Workspace domain, meaning you will have to be the owner of a Workspace account:

In Google Workspace domains, the domain administrator can grant third-party applications with domain-wide access to its users' data — this is known as domain-wide delegation of authority. To delegate authority this way, domain administrators can use service accounts with OAuth 2.0.

Reference

• Google Workspace Domain Wide Delegation.