読者です 読者をやめる 読者になる 読者になる

笑いと統計学をつまみに日本酒と献血を楽しむ人が書くブログ

お笑いについて思った事を書きます,統計学・プログラミングについて勉強した事を書きます.美味しいお酒を見つけたらメモがてら書きます.献血ルームに行ったらメモがてら書きます.

営業リストから対象の「会社のURL」が存在するかGoogle apps scriptで瞬殺判断するというお話

BtoBの営業の方はよく営業をかける会社のリスト,所謂「営業リスト」を作ることが多いと思います.

例えば,人材紹介会社のリスト・ECサイトのリスト・Web制作会社のリストなどなど.

きっとリストに掲載されてあるURLから,問い合わせフォームから怒涛の営業連絡をすることでしょう.

 

自分で作らなくても,会社の先輩が作っていたものを使う場合や,今は営業リストを販売している業者があるのでそれを使うこともあるでしょう.

 

ただそれを実際に使おうとすると,もうそんな会社存在してなかったりHPがもうなかったりすることがあります.ECサイトなんか特に.

そんなとき,一個一個確認するのはダルいので,Google Apps scriptを使って瞬殺してしまいましょう.

 

イメージはこんな感じのスプレッドシート

f:id:chan_ume:20160820132427p:plain

4列目にURLが入っていて,その判定結果を5列目に入れていくプログラムを書きたいです.

プログラム実行前は,5行目には何も入力されていない,つまり空白ということをイメージしてください.

まあこれだと5行しかデータがないですが,実際のは1000行とか10000行のURLに対して実行します.

サンプルコード

では,早速URLが存在するか判定するGoogle Apps Scriptのサンプルコードを書いてみました.

function url_exit() {
  var spreadsheet = SpreadsheetApp.openByUrl("スプレッドシートのURL");
  var sheet = spreadsheet.getSheetByName("シートの名前");
  
  var url_number = sheet.getLastRow();
  for (i=2; i <= url_number; i = i + 1){
    var url = sheet.getRange(i, 4).getValue();
    try {
      var response = UrlFetchApp.fetch(url);
      sheet.getRange(i, 5).setValue('OK');
    } catch(e) {
      sheet.getRange(i, 6).setValue('NG');
    }    
  }
}

url という変数で企業のURLを取得し, UrlFetchApp.fetch(url)でそのURLの情報を取得しようとします.
このときにエラーが出たら"NG",エラーが出なければ"OK"を返すというプログラムです.

ちなみに,この UrlFetchApp.fetch を使うとURLの情報をパースしてくれるので,やろうと思えばスクレイピングが簡単にできます.うーん便利やGASさん.

コードの中の「スプレッドシートのURL」と「シートの名前」となっているとことは,会社情報が書かれているスプレッドシートのURLとシート名をコピペしてください.

URL判定プログラムの問題点

これ,やっていただくとわかると思うんですが,結構時間かかります……

Google Apps Scriptは5分以上かかると自動でタイムアウトしてしまうのですが,僕が一度実行した感じだと5分で200セルぐらいしかURL判定が終わりません.
そうすると10000行とかあると,5分じゃ奇跡が起こらない限り終わんないのでどうにか工夫するしかないですね.

トリガーと組み合わせ改良する

Google Apps Scriptにはトリガー機能があって,めちゃくちゃ簡単にプログラムを「1分おきとか1日おきとか月に1度とか回す」ということができます.
これを5分置きに回すよう設定すれば良いのでは?と一瞬思いますが,現状のプログラムだとまた2行目から読みにいってしまい,5分後に再実行されても全く進まず終わります.

 

では,前回実行が終わったところからスタートすれば良いので,実行時に現在の「5列目の最終行が何行目か?」が分かればOKですね.しかし,これがなかなか大変です.
なぜならGoogle Apps script には「シート全体の最終行は何行目か?」を判断する getLastRow()という関数はあるのですが,
「この列の最終行は何行目か?」を判断する関数はないからです.

 

そこで(無理やりですが),下記のように改良してみました.

function url_exit() {
  var spreadsheet = SpreadsheetApp.openByUrl("スプレッドシートのURL");
  var sheet = spreadsheet.getSheetByName("シートの名前");
  
  var url_number = sheet.getLastRow();
  var j = 2;
  var fifth_row_length = 2;
  
  for(var i = 2; i <= url_number; i = i + 20){
    if(sheet.getRange(i, 5).getValue() == ''){
      fifth_row_length = j;
      break;
    }
    else {
    j = i 
    }
  }
  
  for (i = fifth_row_length; i <= url_number; i = i + 1){
    var url = sheet.getRange(i, 4).getValue();
    try {
      var response = UrlFetchApp.fetch(url);
      sheet.getRange(i, 5).setValue('OK');
    } catch(e) {
      sheet.getRange(i, 5).setValue('NG');
    }
  }
}

これでかつ関数 url_exit を5分置きに実行するようにトリガーを設定すればOKです.

 

簡単に説明すると,20行置きに5列目を確認していって,何も入力されていなければ,「このあたりで前回実行が終わったんだな?」ということで,
その空白だった20行前に戻ってURLの判定をし始める,というプログラムにしました.

URLの判定はとても時間がかかるのですが,空白かどうかのチェックはとても速いのでこれを利用しています.
また「20」という数字はかなり適当です.あまり大きすぎるとURLチェックに時間がかかりますし,小さすぎたら空白かどうかのチェックが完了するまで時間がかかってしまいます.
僕が何度か試した感じで20ぐらいが良いんじゃないかなー?というかなり適当な経験則です.