Google Sheets APIでワークシートのコピーにハマった

Googleスプレッドシートをプログラムで操作する際

事前にテンプレートをつくっておき、
そのテンプレートをコピーして値を入力するといった
使い方をしたいケースがよくあると思います。

Google Sheets APIでワークシートのコピーをしたところ微妙にハマりました

参考

実装は公式のAPIリファレンスを参考にしました。

ワークシートコピーの実装サンプル
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.sheets/copyTo

認証部分の実装サンプル
https://developers.google.com/sheets/api/quickstart/ruby

第1のハマりポイント

Step1で次のボタンをクリックすると新しいプロジェクトが作成されてしまいます。

私は既存のプロジェクトを使いたかったので、
GCPウェブコンソール上の「APIとサービス」から「Google Sheets API」の有効化と
OAuthのクライアントIDを作成しました。

作成したクライアントIDのJSONファイルをダウンロードして「credentials.json」というファイル名で作業ディレクトリに配置します。

実装

サンプルを元にrails runnerで起動できるような実装に微調整しました。
スプレッドシートのIDは対象のものに変更してください。

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 = 'TEST'.freeze
CREDENTIALS_PATH = 'credentials.json'.freeze
TOKEN_PATH = 'token.yaml'.freeze
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY

class Tasks::Test

  def self.execute
    service = Google::Apis::SheetsV4::SheetsService.new
    service.client_options.application_name = APPLICATION_NAME
    service.authorization = authorize

    spreadsheet_id = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
    sheet_id = 0  # TODO: Update placeholder value.

    request_body = Google::Apis::SheetsV4::CopySheetToAnotherSpreadsheetRequest.new
    response = service.copy_spreadsheet(spreadsheet_id, sheet_id, request_body)
    puts response.to_json
  end

  def self.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

end

認証

最初に実行すると次のように認証を求められます。

bundle exec rails runner "Tasks::Test.execute"
Running via Spring preloader in process 17212
Open the following URL in the browser and enter the resulting code after authorization:
https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force&*****************************************************
[コードをコピーしてエンター]

第2のハマりポイント

認証後にエラーとなってしまいました。

/home/ec2-user/test_project/vendor/bundle/ruby/2.4.0/gems/google-api-client-0.30.2/lib/google/apis/core/http_command.rb:228:in `check_status': forbidden: Request had insufficient authentication scopes. (Google::As::ClientError)
        from /home/ec2-user/test_project/vendor/bundle/ruby/2.4.0/gems/google-api-client-0.30.2/lib/google/apis/core/api_command.rb:116:in `check_status'
        from /home/ec2-user/test_project/vendor/bundle/ruby/2.4.0/gems/google-api-client-0.30.2/lib/google/apis/core/http_command.rb:193:in `process_response'
        from /home/ec2-user/test_project/vendor/bundle/ruby/2.4.0/gems/google-api-client-0.30.2/lib/google/apis/core/http_command.rb:310:in `execute_once'
        from /home/ec2-user/test_project/vendor/bundle/ruby/2.4.0/gems/google-api-client-0.30.2/lib/google/apis/core/http_command.rb:113:in `block (2 levels) in execute'

これはスプレッドシートの読み込み権限しか与えていなかったことが原因でした。

スコープに与える権限を AUTH_SPREADSHEETS に変更し編集権限も与えます。

#SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS

第3のハマりポイント

スコープを編集し実行するとまた認証手続きが必要になります。
再度認証手続きを済ませると、またエラーになってしましました。

/home/ec2-user/test_project/vendor/bundle/ruby/2.4.0/gems/google-api-client-0.30.2/lib/google/apis/core/http_command.rb:228:in `check_status': badRequest: Invalid destinationSpreadsheetId [] (Google::Apis::ClientError)
        from /home/ec2-user/test_project/vendor/bundle/ruby/2.4.0/gems/google-api-client-0.30.2/lib/google/apis/core/api_command.rb:116:in `check_status'
        from /home/ec2-user/test_project/vendor/bundle/ruby/2.4.0/gems/google-api-client-0.30.2/lib/google/apis/core/http_command.rb:193:in `process_response'
        from /home/ec2-user/test_project/vendor/bundle/ruby/2.4.0/gems/google-api-client-0.30.2/lib/google/apis/core/http_command.rb:310:in `execute_once'

これはよくわからず解決するのに手間取りましたが、
次のページが参考になりました。

https://github.com/googleapis/google-api-ruby-client/issues/620

要は「request_body.destination_spreadsheet_id」をちゃんとセットしないとダメってことですね。

いやー 公式APIのサンプルは不親切です

    request_body = Google::Apis::SheetsV4::CopySheetToAnotherSpreadsheetRequest.new
    request_body.destination_spreadsheet_id = spreadsheet_id ←追加

解決

destination_spreadsheet_id をセットしてあげて実行すると無事シートコピーができました。

$ bundle exec rails runner "Tasks::Test.execute"
Running via Spring preloader in process 18365
{"gridProperties":{"columnCount":5,"rowCount":1000},"index":2,"sheetId":1028228210,"sheetType":"GRID","title":"template のコピー 1"}