【Ruby on Rails】Googleスプレッドシートの値を取得してデータベースに取り込む

やりたい事

API経由でGoogleスプレッドシートの値を取得し、DB（データベース）に取り込みたい。

仕様

• Docker
  • Ruby 3
  • Rails 6（APIモード）
  • MySQL 5.7
• Google API（認証周りはサービスアカウントを使用）

実装

ぼちぼち実装を始めていきましょう。

環境構築

まず、Railsの環境構築から。

作業ディレクトリ & 各種ファイルを作成

$ mkdir google-spreadsheets-importer && cd google-spreadsheets-importer
$ touch Dockerfile docker-compose.yml entrypoint.sh Gemfile Gemfile.lock

./Dockerfile

FROM ruby:3.0

RUN apt-get update -qq && apt-get install -y build-essential libpq-dev nodejs

ENV APP_PATH /myapp

RUN mkdir $APP_PATH
WORKDIR $APP_PATH

COPY Gemfile $APP_PATH/Gemfile
COPY Gemfile.lock $APP_PATH/Gemfile.lock
RUN bundle install

COPY . $APP_PATH

COPY entrypoint.sh /usr/bin/
RUN chmod +x /usr/bin/entrypoint.sh
ENTRYPOINT ["entrypoint.sh"]
EXPOSE 3000

CMD ["rails", "server", "-b", "0.0.0.0"]

./docker-compose.yml

version: "3"
services:
  db:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: password
    volumes:
      - mysql-data:/var/lib/mysql
      - /tmp/dockerdir:/etc/mysql/conf.d/
    ports:
      - 4306:3306
  api:
    build:
      context: .
      dockerfile: Dockerfile
    command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
    volumes:
      - .:/myapp
      - ./vendor/bundle:/myapp/vendor/bundle
    environment:
      TZ: Asia/Tokyo
      RAILS_ENV: development
    ports:
      - "3000:3000"
    depends_on:
      - db
volumes:
  mysql-data:

./backend/entrypoint.sh

# !/bin/bash
set -e

# Remove a potentially pre-existing server.pid for Rails.
rm -f /myapp/tmp/pids/server.pid

# Then exec the container's main process (what's set as CMD in the Dockerfile).
exec "$@"

./backend/Gemfile

# frozen_string_literal: true

source "https://rubygems.org"

git_source(:github) {|repo_name| "https://github.com/#{repo_name}" }

gem "rails", "~> 6"

./backend/Gemfile.lock

# 空欄でOK

rails new

今回は特にビュー部分は要らないため、APIモードで作成します。

$ docker-compose run api rails new . --force --no-deps -d mysql --api

database.ymlを編集

デフォルトの状態だとデータベースとの接続ができないので「database.yml」の一部を書き換えます。

./backend/config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password: password # デフォルトだと空欄になっているはずなので変更
  host: db # デフォルトだとlocalhostになっているはずなので変更

development:
  <<: *default
  database: myapp_development

test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  database: <%= ENV["DATABASE_NAME"] %>
  username: <%= ENV["DATABASE_USERNAME"] %>
  password: <%= ENV["DATABASE_PASSWORD"] %>

コンテナを起動 & データベースを作成

$ docker-compose build
$ docker-compose up -d
$ docker-compose run api bundle exec rails db:create

動作確認（localhost:3000 にアクセス）

localhost:3000 にアクセスして初期状態の画面が表示されればOKです。

Google API連携

次にGoogle API連携を行います。

サービスアカウントを作成

冒頭で触れましたが、今回はサービスアカウントで認証を行うのであらかじめ準備しておきましょう。

https://console.cloud.google.com/

Google Cloud Platformのアカウントをまだ持っていない方は下記記事などを参考に作成しておいてください。

参照記事: これから始めるGCP（GCE） 安全に無料枠を使い倒せ

左サイドバーから「APIとサービス→ライブラリ」を選択。各種Google APIが出てくるので、その中から「Google Sheets API」を見つけて有効化します。

次に左サイドバーから「APIとサービス→認証情報」を選択し、「サービスアカウント」へと進みます。

参照記事: GCP Service Accountを理解する

• サービスアカウント名
  • 適当
• サービスアカウントの説明
  • わかりやすければ何でもOK

サービスアカウント名以外は基本的に任意入力項目なので、特にこだわりが無ければ空欄で大丈夫です。

作成完了後、サービスアカウント詳細ページの下部にいくと「鍵を追加」という項目があるのでクリック。

すると手元のデバイスに認証情報を含んだJSONファイル（ファイル名: atlantean-axon-*******-*****.json）がダウンロードされるので、大事に保管しておいてください。（後ほど使用します。）

google-api-clientをインストール

./Gemfile

gem "google-api-client"

$ docker-compose build

認証ロジックなどを作成

$ mkdir lib/google && touch lib/google/spreadsheets.rb

./lib/google/spreadsheets.rb

require "google/apis/sheets_v4"

class Google::Spreadsheets
  def initialize
    @service = Google::Apis::SheetsV4::SheetsService.new
    @service.authorization = authorize
  end

  # 認証
  def authorize
    json_key = JSON.generate(
      private_key: "-----BEGIN PRIVATE KEY-----\n ... \n-----END PRIVATE KEY-----\n",
      client_email: "************@atlantean-axon-*******.iam.gserviceaccount.com"
    )

    json_key_io = StringIO.new(json_key)

    authorizer = Google::Auth::ServiceAccountCredentials.make_creds(
      json_key_io: json_key_io,
      scope: ["https://www.googleapis.com/auth/spreadsheets"]
    )
    authorizer.fetch_access_token!
    authorizer
  end

  # 指定されたスプレッドシートIDとレンジ（範囲）から値を取得
  def get_values(spreadsheet_id, ragne)
    @service.get_spreadsheet_values(spreadsheet_id, ragne)
  end
end

• private_key
• client_email

には先ほどサービスアカウント作成時にダウンロードしたJSONファイルに含まれる情報を入力。

※ 今回はサンプルなので直書きしてますが、実際に運用する場合はdotenvなどを使い環境変数で管理してください。

./app/config/application.rb

module Myapp
  class Application < Rails::Application
    # ----
    config.paths.add "lib", eager_load: true # 適当な場所に追記
    # ----
  end
end

lib/配下のファイルを読み込むため「./app/config/application.rb」内に↑の1行を追加しましょう。

動作確認

ここで一旦動作確認をしておきます。

$ docker-compose run api rails c

irb(main):001:0> Google::Spreadsheets.new.get_values("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", ["Class Data!A:F"])

=> 
# <Google::Apis::SheetsV4::ValueRange:0x000055ea3c82c388
 @major_dimension="ROWS",
 @range="'Class Data'!A1:F101",
 @values=
  [["Student Name", "Gender", "Class Level", "Home State", "Major", "Extracurricular Activity"],
   ["Alexandra", "Female", "4. Senior", "CA", "English", "Drama Club"],
   ["Andrew", "Male", "1. Freshman", "SD", "Math", "Lacrosse"],
   ["Anna", "Female", "1. Freshman", "NC", "English", "Basketball"],
   ["Becky", "Female", "2. Sophomore", "SD", "Art", "Baseball"],
   ["Benjamin", "Male", "4. Senior", "WI", "English", "Basketball"],
   ["Carl", "Male", "3. Junior", "MD", "Art", "Debate"],
   ["Carrie", "Female", "3. Junior", "NE", "English", "Track & Field"],
   ["Dorothy", "Female", "4. Senior", "MD", "Math", "Lacrosse"],
   ["Dylan", "Male", "1. Freshman", "MA", "Math", "Baseball"],
   ["Edward", "Male", "3. Junior", "FL", "English", "Drama Club"],
   ["Ellen", "Female", "1. Freshman", "WI", "Physics", "Drama Club"],
   ["Fiona", "Female", "1. Freshman", "MA", "Art", "Debate"],
   ["John", "Male", "3. Junior", "CA", "Physics", "Basketball"],
   ["Jonathan", "Male", "2. Sophomore", "SC", "Math", "Debate"],
   ["Joseph", "Male", "1. Freshman", "AK", "English", "Drama Club"],
   ["Josephine", "Female", "1. Freshman", "NY", "Math", "Debate"],
   ["Karen", "Female", "2. Sophomore", "NH", "English", "Basketball"],
   ["Kevin", "Male", "2. Sophomore", "NE", "Physics", "Drama Club"],
   ["Lisa", "Female", "3. Junior", "SC", "Art", "Lacrosse"],
   ["Mary", "Female", "2. Sophomore", "AK", "Physics", "Track & Field"],
   ["Maureen", "Female", "1. Freshman", "CA", "Physics", "Basketball"],
   ["Nick", "Male", "4. Senior", "NY", "Art", "Baseball"],
   ["Olivia", "Female", "4. Senior", "NC", "Physics", "Track & Field"],
   ["Pamela", "Female", "3. Junior", "RI", "Math", "Baseball"],
   ["Patrick", "Male", "1. Freshman", "NY", "Art", "Lacrosse"],
   ["Robert", "Male", "1. Freshman", "CA", "English", "Track & Field"],
   ["Sean", "Male", "1. Freshman", "NH", "Physics", "Track & Field"],
   ["Stacy", "Female", "1. Freshman", "NY", "Math", "Baseball"],
   ["Thomas", "Male", "2. Sophomore", "RI", "Art", "Lacrosse"],
   ["Will", "Male", "4. Senior", "FL", "Math", "Debate"]]>

無事取得できていますね。

今回はサンプルとして全世界に公開されているスプレッドシート（https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms）を使いました。

get_valuesの引数は

• spreadsheet_id
  • URLに含まれるスプレッドシートID
    • 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms
• ragne
  • [ワークシート名!列:列]
    • ["Class Data!A:F"]

といった感じで渡してください。

データベースへの取り込み

Google API連携が上手くいったら、いよいよスプレッドシートの値をデータベースに取り込んでいきます。

Userモデルの作成

$ docker-compose run api rails g model User student_name:string gender:string class_level:string home_state:string major:string extracurricular_activity:string
$ docker-compose run api rails db:migrate

ジョブの作成

今回はサンプルなので1回だけの作業になりますが、もしかすると将来的に定期実行などしたくなる可能性もあるので、Active Jobとして作成しておきます。

$ docker-compose run api rails g job spreadsheets_import_job  

./app/jobs/spreadsheets_import_job.rb

class SpreadsheetsImportJob < ApplicationJob
  queue_as :default

  # 行の構造を定義
  Row = Struct.new(
    :student_name,            # 生徒名
    :gender,                  # 性別
    :class_level,             # 学年
    :home_state,              # 出身州
    :major,                   # 専攻
    :extracurricular_activity # 課外活動
  )

  def perform(spreadsheet_id, range)    
    res = google_spreadsheet_service.get_values(spreadsheet_id, range)
    return if res.values.empty? # 値が空だった場合はここで終了

    res.values.drop(1).each do |row_data| # 1行目はヘッダーなので削除
      row = Row.new(*row_data)
      attributes = row.to_h.slice(
        :student_name,
        :gender,
        :class_level,
        :home_state,
        :major,
        :extracurricular_activity
      )
      
      # 重複するデータを作成したくないのでfind_or_initialize_byを使用
      user = User.find_or_initialize_by(attributes)
      user.save
    end
  end

  private

    def google_spreadsheet_service
      @google_spreadsheet_service ||= Google::Spreadsheets.new
    end
end

動作確認

ここまでで大体の部分は完成したので、コンソールから動作確認してみましょう。

$ docker-compose run api rails c

irb(main):001:0> SpreadsheetsImportJob.perform_now("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", ["Class Data!A:F"])

User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`student_name` = 'Alexandra' AND `users`.`gender` = 'Female' AND `users`.`class_level` = '4. Senior' AND `users`.`home_state` = 'CA' AND `users`.`major` = 'English' AND `users`.`extracurricular_activity` = 'Drama Club' LIMIT 1
  TRANSACTION (0.4ms)  BEGIN
  User Create (0.5ms)  INSERT INTO `users` (`student_name`, `gender`, `class_level`, `home_state`, `major`, `extracurricular_activity`, `created_at`, `updated_at`) VALUES ('Alexandra', 'Female', '4. Senior', 'CA', 'English', 'Drama Club', '2021-10-30 11:13:32.144234', '2021-10-30 11:13:32.144234')
  TRANSACTION (2.0ms)  COMMIT
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`student_name` = 'Andrew' AND `users`.`gender` = 'Male' AND `users`.`class_level` = '1. Freshman' AND `users`.`home_state` = 'SD' AND `users`.`major` = 'Math' AND `users`.`extracurricular_activity` = 'Lacrosse' LIMIT 1
  TRANSACTION (0.3ms)  BEGIN
  User Create (0.4ms)  INSERT INTO `users` (`student_name`, `gender`, `class_level`, `home_state`, `major`, `extracurricular_activity`, `created_at`, `updated_at`) VALUES ('Andrew', 'Male', '1. Freshman', 'SD', 'Math', 'Lacrosse', '2021-10-30 11:13:32.152480', '2021-10-30 11:13:32.152480')
  TRANSACTION (2.1ms)  COMMIT
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`student_name` = 'Anna' AND `users`.`gender` = 'Female' AND `users`.`class_level` = '1. Freshman' AND `users`.`home_state` = 'NC' AND `users`.`major` = 'English' AND `users`.`extracurricular_activity` = 'Basketball' LIMIT 1
  TRANSACTION (0.3ms)  BEGIN
  User Create (0.4ms)  INSERT INTO `users` (`student_name`, `gender`, `class_level`, `home_state`, `major`, `extracurricular_activity`, `created_at`, `updated_at`) VALUES ('Anna', 'Female', '1. Freshman', 'NC', 'English', 'Basketball', '2021-10-30 11:13:32.159338', '2021-10-30 11:13:32.159338')
  TRANSACTION (1.6ms)  COMMIT

...

Performed SpreadsheetsImportJob (Job ID: *********-*********-*********-*********-*********) from Async(default) in 3265.17ms

こんな感じでデータベース内に取り込めれていれば成功です。

あとがき

以上、Googleスプレッドシートの値を取得してデータベースに取り込んでみました。

少しでも参考になれば幸いです。