スプレッドシートで、定期的にツイッターのフォロワー数を取得して書き込むプログラムを作成しました。

完成のイメージはこんな感じ。 定期的にフォロワー数を集計して書き込んでいます。 スプレッドシートで定期的にツイッターのフォロワー数を書き込む - 結果画面

下記に実装手順を記載しました。 試してみたい!という方は、手順に沿ってやってみてください。

実装に入る前に

Twitter APIについて

本機能はTwitter APIを使うので、事前に登録をお願いいたします。 登録の仕方は、今回の記事では割愛させていただきます。 Twitter Application Management TwitterAPIの値や取得したいツイッターのスクリーン名はシート内に設定値として書けるようにしました。

定期実行について

定期的に取得する部分は、シート内にcrontabの記法で書いて制御できるようにしました。 crontabの書き方 スプレッドシートにトリガーの機能があるのですが、もっと細かく設定できるようにしたいということで、 機能として追加しました。 ※本機能は一部のcrontabの記載方法のみサポートしているので、詳しくは下記「クーロンの書き方」を参照ください。

では、早速手順と説明をして行きたいと思います。

スプレッドシートを開く

Google スプレッドシート スプレッドシートを開きます。

シート名は「集計シート」にしてください。 ※後に出てきますが、スクリプトの「コード.gs」のexecGetTwitterUserInfo()という関数で、 シート名を変更することができます。

スクリプトの登録

「ツール>スクリプトエディタ」を開いて、下記スクリプトをコピペしてください。 一応機能・役割などでスクリプトファイルを分けてます。

新規作成は、「ファイル>新規作成>スクリプトファイル」を選択し、 ファイル名を入力することで作成することができます。

スプレッドシートで定期的にツイッターのフォロワー数を書き込む - コード.js

common.gs

//************************************************************
// common
//************************************************************
/**
 * データが記載されているワークシートを取得する
 * @param {string} sheet_name - シート名
 * pparam {string} open_id - スプレッドシートのID. ActiveSheetから取得する場合は空文字かnullを指定。
 *                 URL https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxx/edit の xxxxxxxxxxxxxxxx の部分
 * @return sheetオブジェクト
 */
function getSheetObject_(sheet_name, open_id) {
  if (open_id == null || open_id == "") {
    // 現在のスプレッドシートを取得
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  } else {
    var spreadsheet = SpreadsheetApp.openById(open_id);
  }
  // 現在のシートを取得
  return spreadsheet.getSheetByName(sheet_name);
}

/**
 * 指定された範囲から、特定の文字の座標を返す
 * @param {Sheet}
 * @param {Number} startRow - 開始行
 * @param {Number} startCol - 開始列
 * @param {Number} rowNum - 何行分検索対象とするか
 * @param {Number} colNum - 何列分検索対象とするか
 * @return {Range} - Rangeオブジェクト。取得できなかった場合はnull
 *
 * example : A1~B3 = searchValue_(sheet, 1, 1, 3, 2, "keyword");
 */
function searchValue_(sheet, startRow, startCol, rowNum, colNum, keyword) {
  var dat = sheet.getRange(startRow, startCol, rowNum, colNum).getValues();

  for(var i = 0; i < dat.length; i++){
    for (var j = 0; j < dat[i].length; j++) {
      if(dat[i][j] === keyword){
        return sheet.getRange(startRow + i, startCol + j);
      }
    }
  }
  return null;
}

/**
 * 指定された範囲をクリアする
 * @param {Sheet} sheet - シート
 * @param {Number} row - 開始行
 * @param {Number} col - 開始列
 * @param {Number} height - 行範囲
 * @param {Number} width - 列範囲
 */
function clearRange_(sheet, row, col, height, width) {
  sheet.getRange(row, col, height, width).clear();
}

/**
 * 日付をフォーマットする
 * @param {Date} date - 日付。現在日の場合はnull
 * @param {string} format - フォーマット。 nullなら 'yyyy-MM-dd HH:mm'
 * https://qiita.com/yasuyuki_tsuda/items/11e882076e2dc9fdee16
 */
function dateFormat_(date, format) {
  // defaults
  if (date == null) {
    var date = new Date();
  }

  if (format == null) {
    var format = 'yyyy-MM-dd HH:mm';
  }
  // format
  return Utilities.formatDate( date, 'Asia/Tokyo', format);
}

/**
 * ソートを行う
 * @param {Sheet} sheet - シートオブジェクト
 * @param {String} start - 開始位置("B4"など)
 */
function sortSheetValues_(sheet, start) {
  var range = sheet.getRange(start);

  var row = range.getRow();
  var col = range.getColumn();
  var last_row = sheet.getLastRow();
  var last_col = sheet.getLastColumn();

  sheet.getRange(row, col, last_row, last_col)
    .sort([{column: col, ascending: false}, {column: (col + 1), ascending: false}]);
}

/**
 * 指定した日付がクーロンの設定とマッチするか判断する
 * @param {String} crontab - クーロン設定文字列 (*\/10 *) = 10分起き (0 *) = 毎時0分
 * @param {Date} date - 日付
 * @return {Boolean} - true = マッチする、 false = マッチしない
 */
function isMatchDateCrontab_(crontab, date) {
  // validate
  if ((crontab == null || crontab == "") || date == null) {
    return false;
  }
  var split = crontab.split(" ");
  if (split.length < 2) {
    return false;
  }

  // check minute
  var minute = date.getMinutes();
  var c_minute = split[0];
  var minute_result = isMatchDateCrontabOne_(c_minute, minute);
  if (minute_result == false) {
    return false;
  }

  // check hour
  var hour = date.getHours();
  var c_hour = split[1];
  var hour_result = isMatchDateCrontabOne_(c_hour, hour);
  if (hour_result == false) {
    return false;
  }

  Logger.log(split);
  return true;
}

/**
 * クーロンの一つの設定についてのチェックを行う
 * @param {String} one_setting - クーロン設定(*, *\/10, 5 など)
 * @param {Number} value - 値 (月、日、時、分などの一つ一つの値)
 * @return {Boolean} - true = マッチする, false = マッチしない
 */
function isMatchDateCrontabOne_(one_setting, value) {
  // * だったらどの値でもOKなのでtrue
  if (one_setting == "*") {
    return true;
  }

  // 値が同じだった場合はtrue
  if (one_setting == value) {
    return true;
  }

  // /で指定されていた場合
  var split = one_setting.split("/");

  if (split.length != 2) {
    return false;
  }
  var one_value = split[1];
  var rem = value % one_value;

  if (rem == 0) {
    return true;
  }
  return false;
}

twitter_api.gs

OAuthの連携については、以下のサイトを参考にさせていただきました。 と言いますか、コードほぼコピーです。 GoogleAppsScript + Twitterで、OAuthConfigからOAuth2のApplication-only authenticationへと移行した 大変助かりました、ありがとうございます。

var TWITTER_CONFIG = {
  API_BASE_URL : "https://api.twitter.com/1.1"
};

function twitter_getUserTimeline_(inScreenName, config) {
  var apiUrl = TWITTER_CONFIG.API_BASE_URL + "/statuses/user_timeline.json?screen_name=" + inScreenName + "&count=" + config.count;
  return twitter_exec_api_(apiUrl, config);
}

function twitter_getUsersShow_(inScreenName, config) {
  var apiUrl = TWITTER_CONFIG.API_BASE_URL + "/users/show.json?screen_name=" + inScreenName;
  return twitter_exec_api_(apiUrl, config);
}

/**
 * ツイッターのAPIを実行し、結果を返す
 * @param {string} apiUrl - 実行するUrl
 * @param {object} config - 設定
 */
function twitter_exec_api_(apiUrl, config) {
 // アクセストークンの取得
  var tokenUrl = "https://api.twitter.com/oauth2/token";
  var tokenCredential = Utilities.base64EncodeWebSafe(config["TWITTER_CONSUMER_KEY"] + ":" + config["TWITTER_CONSUMER_SECRET"]);
  var tokenOptions = {
    headers : {
      Authorization: "Basic " + tokenCredential,
      "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8"
    },
    method: "post",
    payload: "grant_type=client_credentials"
  };
  var responseToken = UrlFetchApp.fetch(tokenUrl, tokenOptions);
  var parsedToken = JSON.parse(responseToken);
  var token = parsedToken.access_token;

  // Application-only authenticationでTwitter APIの利用
  var apiOptions = {
    headers : {
      Authorization: 'Bearer ' + token
    },
    "method" : "get"
  };
  var responseApi = UrlFetchApp.fetch(apiUrl, apiOptions);

  // バリデーション
  if (responseApi.getResponseCode() !== 200) return "";
  var tweets = JSON.parse(responseApi.getContentText());
  if (!tweets) return "";

  return tweets;
}

コード.gs

//************************************************************
// main
//************************************************************
// execGetTwitterUserInfo
function execGetTwitterUserInfo() {
  var product_name = "sample";
  var sheets = {
    config : {
      sheet_name : "集計シート",
      start_range : "C3"
    },
    result : {
      sheet_name : "集計シート",
      start_range : "C10"
    }
  };
  var g_config = getGeneralConfig_();
  controller_(product_name, sheets, g_config);
}

//************************************************************
// controller
//************************************************************
/**
 * コントローラー
 * @param {string} product_name - プロダクト名
 * @param {Object} sheets - 操作対象のシート名一覧
 * @param {Object} g_config - 全体設定
 */
function controller_(product_name, sheets, g_config) {
  Logger.log("controller exec");
  Logger.log(product_name);
  Logger.log(g_config);

  // 設定が書かれているシートを取得
  var config_sheet = getSheetObject_(sheets.config.sheet_name, "");

  // 設定シートのプロダクト設定位置を取得
  var config_start_range = config_sheet.getRange(sheets.config.start_range);

  var config = getConfig_(config_sheet, config_start_range, "row");
  Logger.log("設定");
  Logger.log(config);

  // クーロン設定を調べる
  var cron_result = isMatchDateCrontab_(config.crontab, g_config.now_date);
  Logger.log("crontab check");
  Logger.log(cron_result);
  if (cron_result == false) {
    return false;
  }

  // モジュールを実行
  var results = {
    m_twitter_getUsersShow_ : twitter_getUsersShow_(config.TWITTER_SCREEN_NAME, config)
  }

  // 結果を表示するシートと表示開始Rangeを取得
  var target_sheet = getSheetObject_(sheets.result.sheet_name, "");
  var result_start_range = target_sheet.getRange(sheets.result.start_range);

  // 描画用のデータを作成
  Logger.log("実行結果");
  Logger.log(results);
  var result_data = createDisplayData_(results);

  // 表示
  var result_range = getOutputRange_(result_start_range, config);
  printResult_(target_sheet, result_range, result_data, config);

  // sort
  sortSheetValues_(target_sheet, sheets.result.start_range);
}

//************************************************************
// init, config
//************************************************************
/**
 * 全体に関わる設定を定義する
 * @return {Object} - 設定
 */
function getGeneralConfig_() {
  return {
    // 実行した時の日時
    now_date : new Date()
  };
}

/**
 * 設定を取得する
 * @param {Sheet} sheet - 取得対象となるシート
 * @param {Range} config_start_range - 原点となる位置
 * @param {string} direction - 設定を読み取っていく方向を指定 row | col.
 * @return {Object} - 設定値
 */
function getConfig_(sheet, config_start_range, direction) {
  // default
  if (direction == null || direction == "") direction = "row";

  var config_result = {};

  var start_column = config_start_range.getColumn();
  var start_row = config_start_range.getRow();
  var i = start_column;
  do {
    if (direction == "col") {
      var values = sheet.getRange(start_row, i, 2, 1).getValues();
      var key = values[0][0];
      var value = values[1][0];
    } else if (direction == "row") {
      var values = sheet.getRange(i, start_column, 1, 2).getValues();
      var key = values[0][0];
      var value = values[0][1];
    } else {
      return {};
    }

    config_result[key] = value;
    i += 1;
  } while(value != "");

  return config_result;
}

//************************************************************
// logic
//************************************************************
/**
 * 表示用データを作成する
 * @param {Object} results - データ取得結果
 * @return {Array[[],[]...]} - データ取得結果を二次元配列化したもの
 */
function createDisplayData_(results) {
  var user_info = results.m_twitter_getUsersShow_;
  var result_arr = [];

  result_arr.push([
    dateFormat_(null, 'yyyy-MM-dd'),
    dateFormat_(null, 'HH:mm'),
    user_info.followers_count
  ]);
  return result_arr;
}

/**
 * データの表示開始位置を取得する
 * @param {Range} result_start_range - 原点
 * @param {Object} config - 設定
 * @return {Range} - データの表示位置。取得できなかった場合はnull
 */
function getOutputRange_(result_start_range, config) {
  var i = 0;

  do {
    var tmp_range = result_start_range.offset(i, 0);
    if (tmp_range.getValue() == "") {
      return tmp_range;
    }
    i += 1;
  } while(true);
  return null;
}

//************************************************************
// print
//************************************************************
/**
 * 結果を表示する
 * @param {Sheet} target_sheet - 表示対象のシート
 * @param {Range} result_range - 表示開始位置
 * @param {Array} result_data - 表示データ
 * @param {Object} config - 設定
 */
function printResult_(target_sheet, result_range, result_data, config) {
  var print_range = target_sheet.getRange(result_range.getRow(), result_range.getColumn(), result_data.length, result_data[0].length);
  print_range.setValues(result_data);
}

シートに設定値を入力

シートのC2から、以下のように記載をお願いします。

キー 説明
crontab * * 集計のタイミングをクーロンタブの書き方で指定。
TWITTER_SCREEN_NAME m_hirasaki1985 対象となるツイッターアカウントのスクリーン名
TWITTER_CONSUMER_KEY TWITTER_CONSUMER_KEYを入力 ツイッター認証用のKEY
TWITTER_CONSUMER_SECRET TWITTER_CONSUMER_SECRETを入力 ツイッター認証用のSECRET

TWITTER_SCREEN_NAMEは取得したいツイッターのスクリーンネーム。 TWITTER_CONSUMER_KEYとTWITTER_CONSUMER_SECRETの値は環境に合わせて設定して下さい。 ※crontabの設定はひとまず「* *」で設定してください。

コードを読める人は見て欲しいのですが、 「コード.gs」のexecGetTwitterUserInfo()関数内の、sheets.config.start_rangeで、設定と集計結果の開始位置を変更できます。 →実際の設定値は、C3から1行ずつ読み込むように設定しています。

集計結果

集計結果はC10から始まります。 日付と時間の降順で表示されるようになってます。

こちらも、「コード.gs」のexecGetTwitterUserInfo()関数内の、sheets.result.start_rangeで開始位置を変更できます。

手動実行で動作確認

設定が終わりましたら、まずは手動で動かしてみて動作確認を行います。

スクリプトエディタで「コード.gs」のファイルを開いた後、「実行」ボタンを押してみてください。 うまく設定できていれば、集計結果に書き込まれるはずです。

うまく集計されない場合

考えられる原因としては、

  1. 設定が間違っている。
  2. シート名が「集計シート」になっていない。
  3. コードのコピーミス。

などです。 すいませんが、もう一度ご確認をお願いします。

クーロンの設定を変更

手動で実行して問題なければ、後は定期実行の設定をするだけです。 クーロンの設定は前述の通り「設定」のcrontabで変更します。

クーロンの設定について

改めて、下記に書き方が乗っています。 crontabの書き方

このうち、本機能では「* (アスタリスク)」と[/ スラッシュ]のみサポートしており、時間指定は、分時までとなります。 そのため、

cron設定 集計タイミング
* * 一分毎に集計。設定してもいいですが、やりすぎて怒られないようにしましょう。
*/5 * 5分毎に集計。
* 2 2時に集計。
15 */3 3時間毎、15分に集計。

のように設定していただければと思います。 ※クーロンの設定を変えると、手動で実行した時もそのクーロン設定を現在日時がマッチしていないと集計されなくなりますので、注意してください。

プロジェクトのトリガーを設定

最後に、プロジェクトトリガーを設定して終わりです。 設定は以下のようにしてください。

実行 execGetTwitterUserInfo
イベント 時間主導型
分タイマー
1分ごと

スプレッドシートで定期的にツイッターのフォロワー数を書き込む - trigger

これで、 プロジェクトトリガーで集計プログラムが分毎に動作し、 crontabの設定と現在日時がマッチしたら集計を行い、 シートに集計結果を書き込む流れが完成しました。

その他参考にしたサイト