【Sales Hack!!】Google Spreadsheet API v4を使ってテレアポのリストを作る #Ruby

はじめに

(空想)
僕のチームの新卒1年目の営業マン「通称今坂くん」が、メールで企業からアポイントメントを取ろうと頑張っています。問い合わせフォームから素敵な案内メールを送っているようですが、返信はほとんどありません。

リード顧客からメールを送れば、開封やクリックリストは作れるけど、問い合わせフォームからの案内だと、クリックリストは作れません。

悩める坊やは、途方にくれていました。

今回は悩める坊や「今坂くん」の為に、クリックリストをGoogleのスプレッドシートを使って作ります。

まずは諸々設定

Ruby Quickstartを見ればほとんどつまらずに作れます。

developerツールにアクセスし、プロジェクトの作成や、ClientIDを作りましょう。

こんな感じにアカウントを作成し、secret-key等が記載されたjsonをDLできればOK！

jsonの中に記載されたclient_emailをGoogleのspreadSheetの共有設定で、共有しましょう。

これでOK！

プログラムを書きましょう

Gemをインストール。

gem 'google-api-client'

認証→書き込みをまとめたClassを作成。

class GoogleSpreadsheet
  RANGE = 'A1:C4000'
  SCOPES = [Google::Apis::SheetsV4::AUTH_SPREADSHEETS]

  # ここで認証しておく
  def initialize(sheet_id, file_path)
    @sheet_id = sheet_id
    authorizer = Google::Auth::ServiceAccountCredentials.make_creds(
      json_key_io: File.open(file_path),
      scope: SCOPES)
    authorizer.fetch_access_token!
    @service = Google::Apis::SheetsV4::SheetsService.new
    @service.authorization = authorizer
  end

  # スプレッドシートの中身を取ってくる
  def columns
    @service.batch_get_spreadsheet_values(@sheet_id, ranges: RANGE).value_ranges.first.values
  end

  # スプレッドシートに書き込む
  def write(id, url="")
    value_range = Google::Apis::SheetsV4::ValueRange.new
    writed_size = columns.size
    value_range.range = "A#{writed_size+1}:C#{writed_size+1}"
    value_range.major_dimension = 'ROWS'
    value_range.values = [[id, Date.today.strftime('%Y-%m-%d'), url]]
    @service.update_spreadsheet_value(
      @sheet_id, 
      value_range.range,
      value_range,
      value_input_option: 'USER_ENTERED',
    )
  end
end

書き込みは常に最後の行に追加したいので、

writed_size = columns.size
value_range.range = "A#{writed_size+1}:C#{writed_size+1}"

サイズを取ってきて、+1する形で設定しております。

class ApisController < ApplicationController
  def write_to_imasaka_sheet
    gs = GoogleSpreadsheet.new(
      CONFIG['gs']['id'],
      CONFIG['gs']['file_path']
    )
    gs.write(params[:id], params[:url])
  end
end

今後も色々なシートを使う事を踏まえると、書き込み側でシートのIDや、jsonのpathは指定します。

ちなみにシートのIDはこちらです。

フックはjs側で作ります。
例えば、リンクに「imasaka_mail=123」というパラメータが入っているとリストに追加するとかが良いですね。他のものと混ざりませんし。

var params = new Object;
var pair = location.search.substring(1).split('&');
for(var i=0; pair[i]; i++) {
  var kv = pair[i].split('=');
  params[kv[0]]=kv[1];
}

if(params['imasaka_mail'] !== undefined && params['imasaka_mail'] !== null) {
  $.get("/apis/write_to_imasaka_sheet", { 
    mcid: params['imasaka_mail'], url: $(location).attr('pathname') 
  });
}

最近はすっかりReactになっているので、JQueryを書くのは久しぶりですね。

結果

問い合わせフォームから連絡する際も、きちんとメールにIDをつけておけば、自動的にシートに書き込まれるようになりました。これで「今坂くん」の素敵な案内が見られたかどうかわかりますね。

今坂くんもさぞかし喜んでいます。
皆様年末は大変事故が起きやすくなっておりますので、くれぐれも飛び出しはしないように気をつけてくださいね。