Google Sheets API ERROR PERMISSION

I'm trying to create sheets dynamically, but when running the code below I'm getting this error.

Code:

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 = "new_credential.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

spreadsheet = {
  properties: {
    title: 'Sales Report'
  }
}
spreadsheet = service.create_spreadsheet(spreadsheet,
                                         fields: 'spreadsheetId')
puts "Spreadsheet ID: #{spreadsheet.spreadsheet_id}"

Error:

Traceback (most recent call last):
    15: from quickstart.rb:49:in `<main>'
    14: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-api-client-0.53.0/generated/google/apis/sheets_v4/service.rb:121:in `create_spreadsheet'
    13: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/base_service.rb:377:in `execute_or_queue_command'
    12: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:102:in `execute'
    11: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
    10: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
     9: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
     8: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:111:in `block in execute'
     7: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
     6: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
     5: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
     4: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:114:in `block (2 levels) in execute'
     3: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:311:in `execute_once'
     2: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:195:in `process_response'
     1: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/api_command.rb:134:in `check_status'
/home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:229:in `check_status': PERMISSION_DENIED: Request had insufficient authentication scopes. (Google::Apis::ClientError)

For this code I am using OAUTH authentication and generating the JSON

I selected all available scopes, recreated the JSON authentication, but it still doesn't work. What can I do?

CodePudding user response：

Mate, by using this line, you're telling the app to only get READONLY permission:

Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY

But in this lines I think you're trying to create a speadsheet

spreadsheet = {
  properties: {
    title: 'Sales Report'
  }
}
spreadsheet = service.create_spreadsheet(spreadsheet,
                                         fields: 'spreadsheetId')

Solution:

Edit the scope to:

SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS

Hope this help

CodePudding user response：

As per the documentation for this 'create' request and the error you are getting that is an issue directly related to the scopes you are using in your code.

These are the scopes you need to add to your script and your project scopes:

https://www.googleapis.com/auth/drive
https://www.googleapis.com/auth/drive.file
https://www.googleapis.com/auth/spreadsheets

After you have done the modification here:

SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY

Execute the code and issue will be solved.