GASを使用して社内システムとスプレッドシートとの連携を行った話

GAS Google-API Rails

はじめに

エンジニアとしての実務未経験からGAに入社し、来月で丸1年が経ちます、櫻井と申します。

入社以来、不動産仕入れ用の社内システム「SUPPLIER by RENOSY（以下SUPPLIER）」の開発を行っています。

www.ga-tech.co.jp （ここに私は載っていません。「もう一人」の方です。）

初投稿の今回は「GASを使ってSUPPLIERとスプレッドシートの連携を行なった」話を共有させて下さい。

社内システムあるある

上の記事にもあるように、SUPPLIERによる仕入れの自動化は着々と進んでいるのですが、一方でアナログな部分もいくつか存在します。

その１つが「SUPPLIERとスプレッドシートの併用」です。

これは様々な社内システムにおいて当てはまることだと思いますが、事業部が

「社内システムを利用する一方で、そのデータを別のスプレッドシートに入れて操作・管理している」

というケースがあります。

SUPPLIERに関わる業務でも、SUPPLIERに入ったデータをスプレッドシートに転記したり、逆にスプレッドシートのデータをSUPPLIERに入力することなどがありました。

こういった作業では、同じデータを重複して入力するという無駄な作業や、目視やコピペによる入力ミスが発生します。

これらのスプレッドシートを全てSUPPLIERに統合し、一元管理できれば良いのですが、

  • 部署を跨いでシートを共有しているため、担当部署用のシステムに移行できない（関連システム全ての変更が必要）
  • 複雑な情報や関数があり、さらにそれが色々な部署で変更されるため、システム反映が難しい

などの課題があり、すぐに対応ができないケースもあります。

このようなケースにおいて

  • シート自体には影響を与えず（中身を変更しない）

  • システム・スプレッドシート間の情報入力における作業重複・入力ミスを無くす

方法として挙げられるのが、「GASによるシステムとスプレッドシートの連携」です。

GASとは？

Google Apps Script（通称＆以下GAS）とは、 Google関連サービス（Gmail, Google Drive, Google Spread Sheet）などと連携するアプリケーション開発のプラットフォームです。

https://developers.google.com/apps-script/overview

Google関連サービスと連携して、WebサービスやAPIとして公開することができます。 このGASを使用することで、システムからAPIを利用し、スプレッドシートとの連携を行うことができるのです。

実行方法

実際の手順は大きく分けると以下の３ステップです。

1. スプレッドシートからApps Scriptを作成する

2. Apps ScriptをAPIとして公開する

3. システム内からAPIを実行する

今回は

「システムから特定のデータを入力して、スプレッドシート内でその情報をもとに関数を実行し、その結果をシステムに返す。」

という簡単な作業を例に、GASを使ってシステムとスプレッドシートを連携する方法を説明します。

なお、SUPPLIERではRailsを使用している為、Railsで行います。

事前準備

GASを利用する前提として、Google Cloud Platform（以下GCP）に登録し、プロジェクトを作成しておく必要があります。

https://cloud.google.com/resource-manager/docs/creating-managing-projects

プロジェクトが作成できたら、以下の手順通り進めていきます。

1. スプレッドシートからApps Scriptを作成する

スプレッドシートの内容は「B2,B3に文字を入力すると、B1-B3を結合したものがB4に表示される」という簡単なものです。

今回は、システムからB2,B3に文字を流し込み、結果のB4を取得するという作業を行います。

f:id:sobameshi_boy:20191027113231p:plain

ここから実際に使用するスプレッドシートに紐付いたApps Scriptを作成します。

Apps Scriptはスプレッドシート内で、「ツール」→「スクリプトエディタ」をクリックすると簡単に作成できます（以下のような画面が出てきます）。 f:id:sobameshi_boy:20191027113236p:plain ここで「パラメータを受け取って、それをセルに入力し、式が入っているセルの結果を受け取る」関数を作成しました。

2. Apps ScriptをAPIとして公開する

Apps Scriptの設定

Apps Scriptを公開するには、まずGCPのプロジェクトに紐付けを行う必要があります。

「リソース」→「プロジェクト」から以下の画面で f:id:sobameshi_boy:20191025220229p:plain ここにGCPのプロジェクト番号を入力します。プロジェクト番号はGCPのプロジェクト上のダッシュボードから確認が可能です。

f:id:sobameshi_boy:20191027114542p:plain

紐付けが完了したら、ここで「公開」→「実行可能APIとして導入する」を押して、公開しましょう。 f:id:sobameshi_boy:20191028211510p:plain

ここでの「現在の API ID」は後ほどAPIの実行時に使用するので、手元にとっておきましょう。

※ここで関数に変更があった場合、現在のバージョンを「更新」するのではなく、「新しいバージョンを作成」として「更新」する必要があるため、注意してください。。

また、「ファイル」→「プロジェクトのプロパティ」→「スコープ」から「このスクリプトで必要な OAuth スコープ」についても使用するため、とっておきましょう。 f:id:sobameshi_boy:20191027135502p:plain

これでApps Script側での設定は完了です。

GCPでの設定

ここからはGCPプロジェクト側での設定です。

Apps Scriptを使用するためのAPIを有効にする必要があります。

GCPプロジェクトのコンソール、「APIとサービス」から、

f:id:sobameshi_boy:20191026183113p:plain

Apps Script APIを検索し、「有効にする」を選択しましょう。 f:id:sobameshi_boy:20191026183051p:plain

次にシステムからAPIを使用する為の、認証情報の設定を行います。 今回はOauthクライアントIDを使用します。

OauthクライアントIDを使用する際に、同意画面の登録が必要です。

この認証方法では、システム内でユーザー（今回であればスプレッドシートの使用者）に同意を求め、そのユーザーの認証情報でスプレッドシートにアクセスを行うApps Scriptを実行することができます。その際の同意画面の設定です。 f:id:sobameshi_boy:20191026190359p:plain

ここでApps Scriptのスプレッドシートを追加しておきましょう。 f:id:sobameshi_boy:20191028211847p:plain

完了したら、最後にトークンの登録です。このトークンをシステムから使用することで、認証情報の作成を行うことができます。 f:id:sobameshi_boy:20191027115028p:plain

今回は認証の実行を画面からは行わないので、「その他」を選択します。

アプリケーションの画面から認証を行い、リダイレクトなどが必要な場合はウェブアプリケーションを使用しましょう。 f:id:sobameshi_boy:20191027204910p:plain

最後にこのトークンをダウンロードして、GCPの設定は完了です。 f:id:sobameshi_boy:20191027125511p:plain

これでこのScriptをAPIとして使用する準備は全てOKです。

3. システム内でAPIを実行する

いよいよシステム側からAPIを実行していきます。

今回はRailsのコンソールから実行することにします。 Railsは5.0.6、Rubyは2.4.3を使用しています。

まずはGemをインストールします。今回は以下の２つです。

  • google-api-client

  • redis

redisに関しては、認証情報の保存先をRedisにしている為で、Fileとした場合には必要ありません。後ほど説明します。

ちなみにRedisにしている理由は、Supplierを複数コンテナで運用している為です。

実行ファイルについては、以下にテンプレートが用意されているので、これを参考にします。 https://developers.google.com/apps-script/api/quickstart/ruby

実行コード

今回のテストで実行したコードは以下の通りです。

# frozen_string_literal: true

require 'google/apis/script_v1'
require 'googleauth'
require 'googleauth/stores/redis_token_store'

class GasTest
  attr_accessor :service

  OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'
  APPLICATION_NAME = 'GAS TEST'
  CREDENTIALS_PATH = File.join(Rails.root, '.credentials','credentials.json')
  FUNCTION_NAME_COMBINE_STRING = 'combineString'
  APP_ID = 'Apps ScriptでコピーしたAPP_ID'
  SCOPE = 'https://www.googleapis.com/auth/spreadsheets'

  def authorize
    client_id = Google::Auth::ClientId.from_file(CREDENTIALS_PATH)
    token_store = Google::Auth::Stores::RedisTokenStore.new
    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

  def initialize
    self.service = Google::Apis::ScriptV1::ScriptService.new
    service.client_options.application_name = APPLICATION_NAME
    service.authorization = authorize
  end

  def combine_string(string_1, string_2)
    request_object = Google::Apis::ScriptV1::ExecutionRequest.new
    request_object.parameters = [string_1: string_1, string_2: string_2 ]
    request_object.function = FUNCTION_NAME_COMBINE_STRING
    response = service.run_script(APP_ID, request_object).response
    response['result']['combined_string']
  end
end

小分けにして説明していきます。 まずはGemの読み込みです。

require 'google/apis/script_v1'
require 'googleauth'
require 'googleauth/stores/redis_token_store'
  • ここで認証情報の保存先をRedisではなく、Fileにする場合は以下のようになります。
require "googleauth/stores/file_token_store"

次に、認証の部分です。

  def authorize
    # 先ほどダウンロードした、jsonファイルの読み込み
    client_id = Google::Auth::ClientId.from_file(CREDENTIALS_PATH)
    # token_storeにはRedisを使用
    token_store = Google::Auth::Stores::RedisTokenStore.new
    authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPE, token_store)
    # userを複数登録したい場合は、一意にする
    user_id = 'default'
    credentials = authorizer.get_credentials user_id
    # credentialsが存在しない場合のみ、認証を行う
    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

  • client_idには、先ほどGCPでダウンロードしたトークンのjsonファイルを指定します。 任意の場所にjsonファイルを置き、そのパスを指定します。

  • token_storeにはRedisを使用していますが、Fileの場合は以下のようにします。（TOKEN_PATHには認証情報のパスを指定します。）

token_store = Google::Auth::Stores::FileTokenStore.new file: TOKEN_PATH

  • SCOPEには、Apps Scriptで確認した「このスクリプトで必要な OAuth スコープ」を指定します。

  • user_idは、複数人が認証を行う場合は、ユーザーが識別できるものを使用しましょう。今回はスプレッドシートの管理者一人のみなので、固定で問題ありません。

  • Redisにはオプションを指定することで、認証情報のキーにprefixを設定することができます。

  • 初めて認証する場合は、画面からの同意が必要ですが、それが完了すると、保存先（今回はRedis）に認証情報が保存され、 次回以降はそちらを参照するため、再認証は必要ありません。

最後にAPIの実行部分です

  def initialize
    self.service = Google::Apis::ScriptV1::ScriptService.new
    # 同意画面でのアプリ名
    service.client_options.application_name = APPLICATION_NAME
    # authorizeを実行
    service.authorization = authorize
  end

  def combine_string(string_1, string_2)
    request_object = Google::Apis::ScriptV1::ExecutionRequest.new
    request_object.parameters = [string_1: string_1, string_2: string_2 ]
    request_object.function = FUNCTION_NAME_COMBINE_STRING
    response = service.run_script(APP_ID, request_object).response
    response['result']['combined_string']
  end

  • Google::Apis::ScriptV1::ScriptServiceのインスタンス(service)を作成、initializeの中で認証を行ないます。

  • service.client_options.application_nameは認証画面で表示する名前（「"アプリ名"が」）を指定します。

  • Google::Apis::ScriptV1::ExecutionRequestのインスタンス(request_object)を作成し、関数名と実行に必要なパラメータを設定します。

  • 最後にAPP_IDとrequest_objectを引数に、service.run_scriptを実行すれば、APIが実行され、responseが返ってきます。

実行しよう！

では、実際にこのコードをコンソールから実行してみます。

# インスタンスを作成
irb(main):001:0> gt = GasTest.new
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&client_id=[client_id].apps.googleusercontent.com&include_granted_scopes=true&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets

ここで上のURLをブラウザで開きます。 f:id:sobameshi_boy:20191027142012p:plain

あとは画面に従って同意を行うと、コードが表示されるので、 f:id:sobameshi_boy:20191027212359p:plain

これを先ほどのコンソールで打ち込むと認証が完了し、この情報がRedisに保存されます。

認証が完了したので、最後にcombine_stringを実行します。

# メソッドの実行
gt.combine_string('テスト','太郎')

# Apps Scriptの実行結果
Success - #<Google::Apis::ScriptV1::Operation:0x00007f9300a91740
 @done=true,
 @response=
  {"@type"=>"type.googleapis.com/google.apps.script.v1.ExecutionResponse",
   "result"=>{"combined_string"=>"GA technologies テスト太郎"}}>

=> "GA technologies テスト太郎"

無事に取得できました！

今回は簡単な例でしたが、このようにしてシステム内のデータを与えて、スプレッドシート内のセルに入力、別の式の結果を取得するということは、実際に多く人の手で行われている作業だと思うので、これを使えば様々な入力作業を省くことができるようになるのではないでしょうか。

GCPの最初の設定は少し手順が多いですが、慣れればスムーズに行えるようになりますし、 スプレッドシートだけでなくGmailやGoogle カレンダーなどとも連携して様々な機能が使えるので、ぜひお試しください！

sobameshi_boy 読者になる

関連記事

エンジニア積極採用中

不動産業界のITイノベーション
最前線に立ちませんか?

ITANDI