0

I'm trying to update a Google Workspace Sheet using a client in Ruby on macOS. I can't find a sample of this that currently works. I am currently trying the following, which is obviously incorrect! When it executes:

user_credentials.fetch_access_token!

I get the error:

ERROR Signet::AuthorizationError: Authorization failed.  Server message:\n{\n  "error": "unsupported_grant_type",\n  "error_description": "Invalid grant_type: "\n}
    /Users/jtosey/.rbenv/versions/3.1.3/lib/ruby/gems/3.1.0/gems/signet-0.17.0/lib/signet/oauth_2/client.rb:1028:in `fetch_access_token'

This is my current authentication code:

require 'googleauth'
require 'googleauth/stores/file_token_store'
require 'launchy'
require 'webrick'

# Configuration
secrets = ENV['HOME'] + '/.secrets'
credentials_file = secrets + '/google_workspace_client_secret.json'
token_store_file = secrets + '/google_workspace_token.yaml'

# Authorization
scopes = ['https://www.googleapis.com/auth/spreadsheets']
client_id = Google::Auth::ClientId.from_file(credentials_file)
token_store = Google::Auth::Stores::FileTokenStore.new(file: token_store_file)
authorizer = Google::Auth::UserAuthorizer.new(client_id, scopes, token_store)

user_credentials = Google::Auth::UserRefreshCredentials.new(client_id: client_id.id, scope: scopes, token_store: token_store)

if user_credentials.refresh_token.nil?
  auth_uri = user_credentials.authorization_uri(
    access_type: 'offline',
    prompt: 'select_account consent',
    redirect_uri: 'http://localhost:8080'
  )

  Launchy.open(auth_uri)

  root = File.expand_path '~/public_html'
  server = WEBrick::HTTPServer.new Port: 8080, DocumentRoot: root
  server.mount_proc '/' do |req, res|
    code = req.query['code']

    if code.nil?
      [200, {'Content-Type' => 'text/html'}, [%(<html><body><h2>Authorization required</h2><p>Click <a href="#{auth_uri}">here</a> to authorize the application.</p></body></html>)]]
    else
      user_credentials.code = code
      user_credentials.fetch_access_token!
      user_credentials.store(token_store)
      [200, {'Content-Type' => 'text/html'}, ['<html><body><h2>Authorization successful!</h2></body></html>']]
      # TODO: stop server
    end
  end
  server.start
end

Suggestions?

Joseph
  • 1,354
  • 12
  • 15

1 Answers1

0

This works for me, as of 2023-04-05:

require 'googleauth'
require 'googleauth/stores/file_token_store'
require 'google/apis/sheets_v4'
require 'launchy'
require 'webrick'

# Configuration
secrets = ENV['HOME'] + '/.secrets'
CREDENTIALS_FILE = secrets + '/google_workspace_client_secret.json'
TOKEN_STORE_FILE = secrets + '/google_workspace_token.yaml'

BASE_URL = 'http://localhost:8080'.freeze
CREDENTIALS_PATH = "credentials.json".freeze

SCOPES = [
  Google::Apis::SheetsV4::AUTH_DRIVE,
  Google::Apis::SheetsV4::AUTH_DRIVE_FILE,
  Google::Apis::SheetsV4::AUTH_DRIVE_READONLY,
  Google::Apis::SheetsV4::AUTH_SPREADSHEETS,
  Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY
]

def authorize
  client_id = Google::Auth::ClientId.from_file(CREDENTIALS_FILE)
  token_store = Google::Auth::Stores::FileTokenStore.new(file: TOKEN_STORE_FILE)
  authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPES, token_store)
  user_id = "default"
  credentials = authorizer.get_credentials user_id

  if credentials.nil?
    auth_uri = authorizer.get_authorization_url(base_url: BASE_URL)
    Launchy.open(auth_uri)

    root = File.expand_path(ENV['HOME'] + '/public_html')
    server = WEBrick::HTTPServer.new(Port: 8080, DocumentRoot: root)

    server.mount_proc '/oauth2callback' do |request, response|
      code = request.query['code']

      response.status = 200
      response['Content-Type'] = 'text/html'
      response.body = if code.nil?
        %(<html><body><h2>Authorization required</h2><p>Click <a href="#{auth_uri}">here</a> to authorize the application.</p></body></html>)
      else
        credentials = authorizer.get_and_store_credentials_from_code(user_id: user_id, code: code, base_url: BASE_URL)
        server.shutdown
        '<html><body><h2>Authorization successful!</h2></body></html>'
      end
    end

    server.start
  end
  credentials
end

spreadsheet_id = 'your spreadsheet ID'
range = 'Sheet1!A1' # The range of the cell to write to

# Google Sheets API
service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = 'Your Application Name'
service.authorization = authorize

# Write the value to the cell
value_range = Google::Apis::SheetsV4::ValueRange.new
value_range.range = range
value_range.values = [['Hello, World!']]
result = service.update_spreadsheet_value(spreadsheet_id, range, value_range, value_input_option: 'USER_ENTERED')

puts "#{result.updated_cells} cells updated."
Joseph
  • 1,354
  • 12
  • 15