Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
2
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

posted at

updated at

Organization

GoogleSpreadSheetに日次でデータ保存してチャット通知(Railsバッチ、KPI)

やりたいこと

  • 日次でKPI情報をDBから取得して、GoogleSpreadSheetに保存する
  • 保存後にチャットで通知
  • SpreadSheetでグラフ化すればダッシュボードになる

Google側の設定

Google API Manager

  • Google Sheets API の有効化
    プロジェクトが無ければ、作成
  • 認証 -> クライアントIDの作成
    OAuth同意画面で、適当に値をいれて、アプリケーションに「その他」を設定
    OAuth クライアント ID : aaa
    OAuth クライアントシークレット : bbb

トークンなどの取得

ジョブ作成

  • ジョブファイルの場所
    lib/tasks配下が良いみたい
  • gemインストール
    gem 'google-api-client'
    gem 'google_drive'
    gem 'oauth2'
    $ rbenv exec bundle install --path vendor/bundle
  • コマンド実行例
    $ bundle exec rails runner lib/tasks/DailyReport.rb -e production
  • Wheneverでcron管理
    gemインストール
    gem 'whenever', :require => false
    $ rbenv exec bundle install --path vendor/bundle
    $ bundle exec wheneverize
     [add] writing `./config/schedule.rb'
     [done] wheneverized!
    $ vi ./config/schedule.rb
     例)
      every :day, at: "0:30 am" do
       runner "lib/tasks/DailyReport.rb"
      end
    $ bundle exec whenever --update-crontab
    $ bundle exec whenever --clear-crontab

サンプルプログラム

  • controllers/concerns/common.rb (GoogleSpreadSheetとChatwork通知の共通モジュール)
module GoogleSpreadSheet
  extend ActiveSupport::Concern
    @session
  @ws

  def GS_Connect
    require 'google_drive'
    require 'oauth2'

    client_id     = "aaa"
    client_secret = "bbb"
    refresh_token = "eee"
    client = OAuth2::Client.new(
      client_id,
      client_secret,
      site: "https://accounts.google.com",
      token_url: "/o/oauth2/token",
      authorize_url: "/o/oauth2/auth")
    auth_token = OAuth2::AccessToken.from_hash(client,{:refresh_token => refresh_token, :expires_at => 3600})
    auth_token = auth_token.refresh!
    @session = GoogleDrive.login_with_oauth(auth_token.token)
    end

  def GS_Select( ss_key, title )
    @ws = @session.spreadsheet_by_key("#{ss_key}").worksheet_by_title("#{title}")
  end

  def GS_Select_Gid( ss_key, gid )
    @ws = @session.spreadsheet_by_key("#{ss_key}").worksheet_by_gid("#{gid}")
  end

  def GS_Create( ss_key, title )
    @ws = @session.spreadsheet_by_key("#{ss_key}").add_worksheet("#{title}")
    end
end

module Chatwork
  extend ActiveSupport::Concern

    def CW_Send( room_id, msg )
        require 'net/https'
        require 'json'

        uri = URI.parse('https://api.chatwork.com')
        client = Net::HTTP.new(uri.host, 443)
        client.use_ssl = true

        # 投稿
        res = client.post("/v1/rooms/#{room_id}/messages", "body=#{msg}", {'X-ChatWorkToken' => 'チャットワークAPIのトークン'})
        # 戻り値
        # puts JSON.parse(res.body)
    end
end
  • lib/tasks/DailyReport.rb (バッチ本体)
class DailyReport < ApplicationController
  include GoogleSpreadSheet
  include Chatwork
    attr_reader :time_kinou, :time_kinou_disp
  @data

  def initialize
    t = Time.now
    @time_kinou = t.prev_day.strftime("%Y%m%d")
    @time_kinou_disp = t.prev_day.strftime("%Y/%m/%d")

    @data = DashboardController.new
    @data.yesterday  # ← DBから取得したデータ(Model or Controllerを指定して取得)
  end

  def PutGS
    pos_num = @ws.num_rows + 1

    @data.yesterday_data['all'].each do |key,val|
      @ws[pos_num, 2] = key['xxx']
      @ws[pos_num, 3] = key['yyy'].to_s
      pos_num = pos_num + 1
    end

    @ws.save
    @ws.reload
  end

  def SendCW
    msg = ''
    msg += "[info][title]#{@time_kinou_disp}の利用状況 (DailyReport)[/title]"
    msg += "<<サマリ>>\n[code]"
    msg += "ログイン率:#{@data.yesterday_data['login_rate'].round(2)}(%)\n"
    msg += '[/code][hr]https://docs.google.com/spreadsheets/d/スプレッドシートのID/[/info]'

    # Send Message
    self.CW_Send( "ルームID", msg ) # Prod
  end

end

dr = DailyReport.new
dr.GS_Connect
dr.GS_Select( "スプレッドシートのID", "シート名" )
dr.PutGS
dr.SendCW
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
2
Help us understand the problem. What are the problem?