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

## はじめに

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

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

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

![0930tobidashi5.png](https://qiita-image-store.s3.amazonaws.com/0/5365/82580ade-0885-3811-b673-51adcf59f370.png)

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

## まずは諸々設定

[Ruby Quickstart](https://developers.google.com/sheets/api/quickstart/ruby)を見ればほとんどつまらずに作れます。

[developerツール](https://console.developers.google.com/start/api?id=sheets.googleapis.com)にアクセスし、プロジェクトの作成や、ClientIDを作りましょう。

<img width="1328" alt="googleconsole.png" src="https://qiita-image-store.s3.amazonaws.com/0/5365/88590ca5-b600-e382-8a55-227c8cde67e6.png">

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

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

<img width="658" alt="スプレッドシート.png" src="https://qiita-image-store.s3.amazonaws.com/0/5365/4be95d38-f61a-3867-37c9-33ff123e196c.png">

これでOK！

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

Gemをインストール。

```Gemfile
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はこちらです。
<img width="851" alt="ID.png" src="https://qiita-image-store.s3.amazonaws.com/0/5365/7e88fe19-0487-009c-9531-3c57ada3aa94.png">

フックは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を書くのは久しぶりですね。

##結果

<img width="534" alt="スクリーンショット 2017-12-24 23.42.07.png" src="https://qiita-image-store.s3.amazonaws.com/0/5365/3058b95b-9b1b-d527-6c3c-a922f04f856e.png">

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

<img width="551" alt="スクリーンショット 2017-12-24 23.43.53.png" src="https://qiita-image-store.s3.amazonaws.com/0/5365/78e0f986-3ebb-7509-e929-f18285161644.png">

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