I am attempting to run a function on a Google Sheet using the Apps Script API in a Ruby on Rails application. The purpose is to execute a function on a Google Sheet, but I'm encountering an error during this process.
Upon reviewing the Google Apps Script API documentation, it is stated that using a Service Account for the Apps Script APIs is not supported. In response, I attempted to authenticate using OAuth 2.0. However, even after using OAuth 2.0 for authentication, I encountered a 401 Unauthorized error when attempting to run the script.
I need assistance with authorizing the Apps Script API and running the script. Can you please review the code and provide guidance on how to achieve this?
Code:
require 'google/apis/drive_v3'
require 'sinatra'
require 'googleauth'
require 'googleauth/stores/redis_token_store'
client_id = Google::Auth::ClientId.from_file('lib/get_property_data/client_secret.json')
scope = ['https://www.googleapis.com/auth/spreadsheets']
token_store= Google::Auth::Stores::RedisTokenStore.new(redis: Redis.new)
authorizer = Google::Auth::WebUserAuthorizer.new(client_id, scope, token_store, '/oauth2callback')
service = Google::Apis::ScriptV1::ScriptService.new
service.client_options.application_name = 'Application name'
service.authorization = authorizer
request = Google::Apis::ScriptV1::ExecutionRequest.new(function: 'testFunction', parameters: ['sheet_id'])
script_id = 'xxxxxxx'
response = service.run_script(script_id, request)
Error:
Caught error Unauthorized
Error - #<Google::Apis::AuthorizationError: Unauthorized status_code: 401 header: #<HTTP::Message::Headers:0x00007f806c517978 @http_version="1.1", @body_size=0, @chunked=false, @request_method="POST", @request_uri=#<Addressable::URI:0x3fc03926f794 URI:https://script.googleapis.com/v1/scripts/1BrWLyFp4L0Q-00sZekntFTsZzed_4TLesPQYUSDWx-AbRe4EVEGd5-DW:run?>, @request_query=nil, @request_absolute_uri=nil, @status_code=401, @reason_phrase="Unauthorized", @body_type=nil, @body_charset=nil, @body_date=nil, @body_encoding=#<Encoding:UTF-8>, @is_request=false, @header_item=[["WWW-Authenticate", "Bearer realm=\"https://accounts.google.com/\""], ["Vary", "Origin"], ["Vary", "X-Origin"], ["Vary", "Referer"], ["Content-Type", "application/json; charset=UTF-8"], ["Content-Encoding", "gzip"], ["Date", "Wed, 20 Sep 2023 12:27:41 GMT"], ["Server", "ESF"], ["Cache-Control", "private"], ["X-XSS-Protection", "0"], ["X-Frame-Options", "SAMEORIGIN"], ["X-Content-Type-Options", "nosniff"], ["Alt-Svc", "h3=\":443\"; ma=2592000,h3-29=\":443\"; ma=2592000"], ["Transfer-Encoding", "chunked"]], @dumped=false> body: "{\n \"error\": {\n \"code\": 401,\n \"message\": \"Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.\",\n \"errors\": [\n {\n \"message\": \"Login Required.\",\n \"domain\": \"global\",\n \"reason\": \"required\",\n \"location\": \"Authorization\",\n \"locationType\": \"header\"\n }\n ],\n \"status\": \"UNAUTHENTICATED\",\n \"details\": [\n {\n \"@type\": \"type.googleapis.com/google.rpc.ErrorInfo\",\n \"reason\": \"CREDENTIALS_MISSING\",\n \"domain\": \"googleapis.com\",\n \"metadata\": {\n \"service\": \"script.googleapis.com\",\n \"method\": \"google.apps.script.v1.ScriptExecution.Execute\"\n }\n }\n ]\n }\n}\n">
Retrying after authentication failure
Traceback (most recent call last):
1: from (irb):37
Google::Apis::AuthorizationError (Unauthorized)
I would greatly appreciate any insights or suggestions on how to effectively authenticate using OAuth 2.0 and resolve the issue with the run_script method.