Erro to connect Google Sheets API-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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