Google Apps Script で全ツイートをスプレッドシートに保存

VPSをBotの為だけに利用しているが、Google Apps Script に置き換えれそうなので、まずは全ツイートをスプレッドシートに保存するまでやってみた。

スプレッドシート

保存するツイートの項目を用意

Google Apps Script と関連付け

Google Apps Script

OAuth1 のGoogle Apps Script ライブラリ

• 「リソース」⇒「ライブラリ」
  • https://developers.google.com/apps-script/migration/oauth-config

authorize

• Twitterの「Application Management」の「Callback URL」にGoogle Apps Scriptのプロジェクトキー(「ファイル」⇒「プロジェクトのプロパティ」)を設定する

• authorize処理

function getTwitterService() {
  var service = OAuth1.createService("twitter");
  service.setAccessTokenUrl("https://api.twitter.com/oauth/access_token")
  service.setRequestTokenUrl("https://api.twitter.com/oauth/request_token")
  service.setAuthorizationUrl("https://api.twitter.com/oauth/authorize")
  service.setConsumerKey(CONSUMER_KEY);
  service.setConsumerSecret(CONSUMER_SECRET);
  service.setProjectKey(PROJECT_KEY);
  service.setCallbackFunction("authCallback");
  service.setPropertyStore(PropertiesService.getScriptProperties());
  return service;
}

function authCallback(request) {
  var service      = getTwitterService();
  var isAuthorized = service.handleCallback(request);
  if(isAuthorized) {
    return HtmlService.createHtmlOutput("Success! You can close this page.");
  } else {
    return HtmlService.createHtmlOutput("Denied. You can close this page");
  }
}

全件ツイートをスプレッドシートに保存

1. /users/lookup.jsonで全ツイート件数取得
2. 全ツイート件数/200の回数分/statuses/user_timeline.jsonを叩く
3. スプレッドシートに書き込む

var SCREEN_NAME       = "babymetal_japan";
var CONSUMER_KEY      = "";
var CONSUMER_SECRET   = "";
var PROJECT_KEY       = "";
var API_USER_TIMELINE = "https://api.twitter.com/1.1/statuses/user_timeline.json";
var API_USER_LOOKUP   = "https://api.twitter.com/1.1/users/lookup.json";
var LIMIT_COUNT       = 200;
var CREATED_AT        = 1;
var TEXT              = 2;
var RETWEET_COUNT     = 3;
var FAVORITE_COUNT    = 4;
var ENTITIES_MEDIA    = 5;
var URL               = 6;

function getAllTweets() {
  Logger.log("[START] getAllTweets()");

  var service = getTwitterService();
  if(service.hasAccess()) {
    try {
      var as              = SpreadsheetApp.getActiveSpreadsheet();
      var tws             = as.getSheetByName("tweet");              // tweetシート
      var iRow            = 3;                                       // 書き込む行数の開始位置
      var lookup          = usersLookup(SCREEN_NAME);                // /users/lookup.json
      var statusesCount   = Math.floor(lookup.statuses_count);       // 全ツイート数
      var division        = Math.floor(statusesCount / LIMIT_COUNT); // 全ツイートを200で割る除算
      var remain          = Math.floor(statusesCount % LIMIT_COUNT); // 全ツイートを200で割る剰余算
      var apiRequestCount = remain == 0 ? division : division + 1;   // APIのリクエスト数
      var max_id          = "";
      var apiUrl          = API_USER_TIMELINE + "?screen_name=" + SCREEN_NAME + "&count=" + LIMIT_COUNT;
    
      Logger.log("statusesCount = " + statusesCount);
      Logger.log("apiRequestCount = " + apiRequestCount);
    
      for(var i = 0; i < apiRequestCount; i++) {
        var url      = max_id != "" ? apiUrl + "&max_id=" + max_id : apiUrl;
        var response = service.fetch(url);
        var tweets   = JSON.parse(response.getContentText());
        if (tweets.length < 1) {
          break;
        }
        max_id = decStrNum(tweets[tweets.length -1].id_str);

        Logger.log("tweets = " + tweets.length);
        Logger.log("max_id = " + max_id);

        for(var ii = 0; ii < tweets.length; ii++) {

          var d = new Date(tweets[ii].created_at);
          tws.getRange(iRow, CREATED_AT    ).setValue(Utilities.formatDate(d, "JST", "yyyy-MM-dd HH:mm:ss"));
          tws.getRange(iRow, TEXT          ).setValue(tweets[ii].text);
          tws.getRange(iRow, RETWEET_COUNT ).setValue(tweets[ii].retweet_count);
          tws.getRange(iRow, FAVORITE_COUNT).setValue(tweets[ii].favorite_count);
          var media = tweets[ii].entities.media;
          if(media instanceof Array) {
            var media_url = [];
            for(var iii = 0; iii < media.length; iii++) {
              media_url.push(media[iii].media_url_https);
            }
            tws.getRange(iRow, ENTITIES_MEDIA).setValue(media_url.join('\n'));
          }
          tws.getRange(iRow, URL).setValue("https://twitter.com/" + SCREEN_NAME + "/status/" + tweets[ii].id_str);
          iRow++;
        }
      }      

    } catch(e) {
      Logger.log("[ERROR] getAllTweets() : " + e.message);
      MailApp.sendEmail("[e-mail]", "[ERROR] getAllTweets()", e.message);
    }
  } else {
    var authorizationUrl = service.authorize();
    Logger.log("Please visit the following URL and then re-run the script: " + authorizationUrl);
  }
  
  Logger.log("[END] allTweets()");
}

function usersLookup(screen_name) {
  var service = getTwitterService();
  if(service.hasAccess()) {
    var url      = API_USER_LOOKUP + "?screen_name=" + screen_name;
    var response = service.fetch(url);
    var tweets   = JSON.parse(response.getContentText());
    return tweets[0]
  } else {
    var authorizationUrl = service.authorize();
    Logger.log("Please visit the following URL and then re-run the script: " + authorizationUrl);
  }
}

/* 
 * Decreasing 64-bit Tweet ID
 */
function decStrNum(n) {
  n          = n.toString(); 
  var result = n; 
  var i      = n.length - 1; 
  while(i > -1) { 
    if(n[i] === "0") {
      result = result.substring(0, i) + "9" + result.substring(i + 1); 
      i --; 
    } else { 
      result = result.substring(0, i) + (parseInt(n[i], 10) -1).toString() + result.substring(i + 1); 
      return result; 
    }
  } 
  return result; 
}

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    {name: "Tweetをダウンロード", functionName: "getAllTweets"}
  ];
  ss.addMenu("スクリプト",menuEntries);
}

アウトプット