twitter-search-data.gs

function myFunction() {
  ss = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
    
  as = SpreadsheetApp.getActiveSheet();
  searchWord = as.getRange("A1").getValue();
  
  search(searchWord, 0);
}


/**
 * TwitterのOAuth設定
 */
function setOAuth() {
  var oAuthConfig = UrlFetchApp.addOAuthService("twitter");
  oAuthConfig.setAccessTokenUrl("https://api.twitter.com/oauth/access_token");
  oAuthConfig.setRequestTokenUrl("https://api.twitter.com/oauth/request_token");
  oAuthConfig.setAuthorizationUrl("https://api.twitter.com/oauth/authorize");
  oAuthConfig.setConsumerKey(UserProperties.getProperty("TWITTER_CONSUMER_KEY"));
  oAuthConfig.setConsumerSecret(UserProperties.getProperty("TWITTER_CONSUMER_SECRET"));
}

/**
 * Twitterでキーワード検索したツイートをスプレッドシートに展開する
 *
 * @param query 検索キーワード
 * @param sheetNum シート番号
 */
function search(query, sheetNum) {

  if (query == "") {
    return;
  }
  
  query = encodeURIComponent(query);
  
  var url, options, response, jsonString, json, tweets = [],
      ss, as, cell, firstId, lastRow, row = 0, id, result, col, dd;

  // Twitter Search APIのURL
  var API_URL = "https://api.twitter.com/1.1/search/tweets.json?";
  
  try {
    
    setOAuth();
    
    url = API_URL + "count=100"
                  + "&q=" + query;
    options = {
      "oAuthServiceName": "twitter",
      "oAuthUseToken":    "always"
    };
      
    try {
      response = UrlFetchApp.fetch(url, options);
    } catch(e) {
      Logger.log(e);
      return;
    }
      
    if (!response) {
      Logger.log("no response");
      return;;
    }
      
    jsonString = response.getContentText();
    json       = Utilities.jsonParse(jsonString);    
    tweets     = json.statuses;
        
    if (tweets.length === 0) {
      Logger.log("no tweets");
      return;
    }
        
    // 保存するシートを指定する
    ss = SpreadsheetApp.getActiveSpreadsheet();
    SpreadsheetApp.setActiveSheet(ss.getSheets()[sheetNum]);
    
    as = SpreadsheetApp.getActiveSheet();
    cell = as.getRange("A4");    
    firstId = tweets[tweets.length - 1].id_str;
    
    row = 0;
    
    lastRow = as.getLastRow();
    
    // 取得済みのツイートを上書きしないように出力開始行を設定する
    for (var i = 0; i < lastRow; i++) {
      
      id = cell.offset(i, 0).getValue();
      Logger.log(id + ":" + firstId);
      
      if(firstId == id || !id) {
        break;
      }
      row++;
    }
    
    //重複確認
    if (lastRow == 3) {
      lastRowDate = null;
      lastRowText = null;
    } else {
      lastRowDate = cell.offset(lastRow-4, 1).getValue();
      lastRowText = cell.offset(lastRow-4, 2).getValue();
      lastRowUser = cell.offset(lastRow-4, 4).getValue();
    }
    
    // D1セルに最終行の日時を表示
    as.getRange("D1").setValue(lastRowDate);
    
    // スプレッドシートに出力
    for (var i = tweets.length - 1; i >= 0; i--) {
        
        result = tweets[i];
        col = 0;
        dd = new Date(result.created_at);
      
        // E1セルに取得中のtweetの日付を入れる
        as.getRange("E1").setValue(dd);
      
        if (lastRowDate <= dd) {
          if (lastRowText == null || (lastRowText != result.text && lastRowUser != 'https://twitter.com/' + result.user.screen_name)) {
            cell.offset(row, col++).setValue(result.id_str);
            cell.offset(row, col++).setValue(dd);
            cell.offset(row, col++).setValue(result.text);
            cell.offset(row, col++).setValue('https://twitter.com/' 
                                             + result.user.screen_name
                                             + "/status/"
                                             + result.id_str);
            cell.offset(row, col++).setValue('https://twitter.com/' 
                                             + result.user.screen_name);
            if (result.entities.urls.length > 0) {
              cell.offset(row, col++).setValue(result.entities.urls[0].expanded_url);
            }
            row++;
          }
        }
    }
        
  } catch (e) {
    Logger.log(e);
  }
}