つぶのみ Google Apps Script版

TsubunomiScript.gs

var Tsubunomi = {
  userSheetName:"user",
  mentionsSheetName:"mentions",
  searchSheetName:"search",
  dmSheetName:"directMessage",
  userDataKey:"currentUser",
  apiConfigKey:"apiConfiguration",
  apiUrl:"https://api.twitter.com/",
  defaultCount:140,
  getOptions:{
    "oAuthServiceName" : "twitter",
    "oAuthUseToken" : "always"
  },
  postOptions:{
    "oAuthServiceName" : "twitter",
    "oAuthUseToken" : "always",
    "method" : "POST"
  },
  postPayloadOptions : function(payload) {
    return {
    "oAuthServiceName" : "twitter",
    "oAuthUseToken" : "always",
    "method" : "POST",
    "payload" : payload
  };
  },
  status:{
    OK:200
  },
  oAuthConfigInitialize:function() {
    // Setup OAuthServiceConfig
    var oAuthConfig = UrlFetchApp.addOAuthService("twitter");
    oAuthConfig.setAccessTokenUrl(Tsubunomi.apiUrl+"oauth/access_token");
    oAuthConfig.setRequestTokenUrl(Tsubunomi.apiUrl+"oauth/request_token");
    oAuthConfig.setAuthorizationUrl(Tsubunomi.apiUrl+"oauth/authorize");
    oAuthConfig.setConsumerKey(ScriptProperties.getProperty("twitterConsumerKey"));
    oAuthConfig.setConsumerSecret(ScriptProperties.getProperty("twitterConsumerSecret"));
  },
  buildMentionsString : function(screenName,userMentions) {
    var screenNames="";
    for(var i in userMentions){
      var mentionName=userMentions[i].screen_name;
      if(Utilities.jsonParse(ScriptProperties.getProperty(Tsubunomi.userDataKey)).screen_name==mentionName){
      }else if(screenName==mentionName){
      }else{
        screenNames+="@"+mentionName+" ";
      }
    }
    return screenNames;
  },
  buildQueryString : function(searchWord){
    var queryString;
    var wordArray=searchWord.split("&");
    if(wordArray.length>1&&wordArray[1].indexOf("lang=")>-1){
      queryString = encodeURIComponent(wordArray[0])+"&"+wordArray[1];
    }else{
      queryString = encodeURIComponent(searchWord);
    }
    return queryString;
  },
  getTimelineWord : function(word,sheetName){
    if(word==null){
      word=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange("b1").getValue();
    }
    return word;
  },
  getSheet : function(sheetName){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetName);
    if(sheet==null){
      sheet = ss.insertSheet(sheetName);
    }
    sheet.clear();
    ss.setActiveSheet(sheet);
    return sheet;
  },
  isDmSheet : function(){
    var bool=false;
    var sheetName = SpreadsheetApp.getActiveSheet().getSheetName();
    if(sheetName == Tsubunomi.dmSheetName){
      bool = true;
    }
    return bool;
  },
  getTweetScreenName : function(status){
    var tweetScreenName;
    if(status.hasOwnProperty('retweeted_status')){
      tweetScreenName=status.retweeted_status.user.screen_name;
    }else{
      tweetScreenName=status.user.screen_name;
    }
    return tweetScreenName;
  },
  getTweetText : function(status){
    var tweetText;
    if(status.hasOwnProperty('retweeted_status')){
      tweetText=status.retweeted_status.text;
    }else{
      tweetText = status.text;
    }
    return tweetText;
  },
  getTweetId : function(status){
    var tweetId;
    if(status.hasOwnProperty('retweeted_status')){
      tweetId=status.retweeted_status.id_str;
    }else{
      tweetId=status.id_str;
    }
    return tweetId;
  },
  getTweetDate : function(status){
    var tweetDate;
    if(status.hasOwnProperty('retweeted_status')){
      tweetDate = status.retweeted_status.created_at;
    }else{
      tweetDate = status.created_at;
    }
    return tweetDate;
  },
  setTimelineData : function(response,sheetName,timelineName){
    var o = Utilities.jsonParse(response.getContentText());
    var sheet = Tsubunomi.getSheet(sheetName);
    sheet.getRange('a1').getCell(1,1).setValue("タイムライン : ");
    sheet.getRange('b1').getCell(1,1).setValue(timelineName);    
    var cell = sheet.getRange('a2');
    var index = 0;
    for (var i in o){
      var row = o[i];
      cell.offset(index,0).setValue(Tsubunomi.getTweetId(row));
      cell.offset(index,1).setValue(Tsubunomi.getTweetScreenName(row));
      cell.offset(index,2).setValue(Tsubunomi.getTweetText(row));
      cell.offset(index,3).setValue(Utilities.formatDate(new Date(Tsubunomi.getTweetDate(row)),"JST","yyyy-MM-dd HH:mm"));
      cell.offset(index,4).setValue(Tsubunomi.buildMentionsString(Tsubunomi.getTweetScreenName(row),row.entities.user_mentions));
      Tsubunomi.setEntities(cell,index,row);
      index++;
    }
  },
  setSearchData : function(response,searchWord){
    var o = Utilities.jsonParse(response.getContentText());
    var sheet = Tsubunomi.getSheet(Tsubunomi.searchSheetName);
    sheet.getRange('a1').getCell(1,1).setValue("検索ワード ⇒ ");
    sheet.getRange('b1').getCell(1,1).setValue(searchWord);
    var cell = sheet.getRange('a2');
    var index = 0;
    for (var i in o.results){
      var row = o.results[i];
      cell.offset(index,0).setValue(row.id_str);
      cell.offset(index,1).setValue(row.from_user);
      cell.offset(index,2).setValue(row.text);
      cell.offset(index,3).setValue(Utilities.formatDate(new Date(row.created_at),"JST","yyyy-MM-dd HH:mm"));
      cell.offset(index,4).setValue(Tsubunomi.buildMentionsString(row.from_user,row.entities.user_mentions));
      Tsubunomi.setEntities(cell,index,row);
      index++;
    }
  },
  setDMData : function(sortedArray){
    var sheet = Tsubunomi.getSheet(Tsubunomi.dmSheetName);
    sheet.getRange('a1').getCell(1,1).setValue("タイムライン : ");
    sheet.getRange('b1').getCell(1,1).setValue(Tsubunomi.dmSheetName);
    var cell = sheet.getRange('a2');
    var index = 0;
    for(var i=0,l=sortedArray.length;i<l;i++){
      var row = sortedArray[i];
      cell.offset(index,0).setValue(row.id_str);
      cell.offset(index,1).setValue(row.sender.screen_name);
      cell.offset(index,2).setValue(row.text);
      cell.offset(index,3).setValue(Utilities.formatDate(new Date(row.created_at),"JST","yyyy-MM-dd HH:mm"));
      cell.offset(index,4).setValue(row.recipient.screen_name);
      Tsubunomi.setEntities(cell,index,row);
      index++;
    }
  },
  setEntities : function(cell,idx,status){
    var entitiesArray=[];
    if(status.hasOwnProperty("retweeted_status")){
      entitiesArray.push(status.user.screen_name);
    }
    for(var i in status.entities.media){
      entitiesArray.push(status.entities.media[i].url);
    }
    for(var i in status.entities.urls){
      entitiesArray.push(status.entities.urls[i].url);
    }
    for(var i in status.entities.hashtags){
      entitiesArray.push("#"+status.entities.hashtags[i].text);
    }
    for(var i in status.entities.user_mentions){
      entitiesArray.push(status.entities.user_mentions[i].screen_name);
    }
    for(var i=0,l=entitiesArray.length;i<l;i++){
      cell.offset(idx,5+i).setValue(entitiesArray[i]);
    }
  }
};

function tweetPost(in_reply_to_status_id,text) {
  Tsubunomi.oAuthConfigInitialize();
  var payload = {
    "in_reply_to_status_id" :in_reply_to_status_id,
    "status" : text
  };
  return UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/statuses/update.json", Tsubunomi.postPayloadOptions(payload));
}

function publicRetweetPost(statusId){
  Tsubunomi.oAuthConfigInitialize();
  return UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/statuses/retweet/"+statusId+".json", Tsubunomi.postOptions);
}

function favoriteCreate(statusId){
  Tsubunomi.oAuthConfigInitialize();
  return UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/favorites/create/"+statusId+".json",Tsubunomi.postOptions);
}

function directMessagesNew(screenName,text){
  Tsubunomi.oAuthConfigInitialize();
  var payload = {
    "screen_name" : screenName,
    "text" : text
  };
  return UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/direct_messages/new.json",  Tsubunomi.postPayloadOptions(payload));
}

function friendshipsCreate(screenName){
  Tsubunomi.oAuthConfigInitialize();
  var payload = {
    "screen_name" : screenName
  };
  return UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/friendships/create.json", Tsubunomi.postPayloadOptions(payload));
}

function getVerifyCredentials(){
  Tsubunomi.oAuthConfigInitialize();
  var response = UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/account/verify_credentials.json",Tsubunomi.getOptions);
  if(response.getResponseCode()==Tsubunomi.status.OK){ 
    ScriptProperties.setProperty(Tsubunomi.userDataKey,response.getContentText());
  }
}

function getHelpConfiguration(){
  Tsubunomi.oAuthConfigInitialize();
  var response = UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/help/configuration.json", Tsubunomi.getOptions);
  if(response.getResponseCode()==Tsubunomi.status.OK){ 
    ScriptProperties.setProperty(Tsubunomi.apiConfigKey, response.getContentText());
  }
}

//mentionsを取得する
function getMentions(){
  Tsubunomi.oAuthConfigInitialize();
  return UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/statuses/mentions.json?include_entities=true&include_rts=true", Tsubunomi.getOptions);
}

function getUserTimeline(screenName){
  Tsubunomi.oAuthConfigInitialize();
  return UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/statuses/user_timeline.json?include_entities=true&include_rts=true&screen_name="+screenName,
                       Tsubunomi.getOptions);
}

function getSearchTimeline(searchWord){
  return UrlFetchApp.fetch("http://search.twitter.com/search.json?include_entities=true&q="+searchWord);
}

function getDirectMessages(){
  Tsubunomi.oAuthConfigInitialize();
  return UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/direct_messages.json?include_entities=true",Tsubunomi.getOptions);
}

function getDirectMessagesSent(){
  Tsubunomi.oAuthConfigInitialize();
  return UrlFetchApp.fetch(Tsubunomi.apiUrl+"1/direct_messages/sent.json?include_entities=true",Tsubunomi.getOptions);
}

function tweetDialog(){
  var repId="";
  var screenName="";
  var statusText="";
  var screenNames="";
  var range = SpreadsheetApp.getActiveRange();
  var colIdx = range.getColumnIndex();
  var lastCol = range.getLastColumn();
  if(Tsubunomi.isDmSheet()){
    if(colIdx==1){
      var row = range.getValues()[0];
      if(lastCol>=2){
        screenName=row[1];
      }
      if(lastCol>=3){
        statusText=row[2];
      }
    }else{
      screenName = range.getCell(1,1).getValue();
    }
  }else{
    if(colIdx==1){
      var row=range.getValues()[0];
      if(row[0].match(/^[0-9]+$/)){
        repId =row[0];
      }
      if(lastCol>=2){
        screenName=row[1];
      }
      if(lastCol>=3){ 
        statusText=row[2];
      }
      if(lastCol>=5){
        screenNames=row[4];
      }
    }
  }
  var app = UiApp.createApplication().setTitle("Tweet Post");
  var panel = app.createVerticalPanel();
  var label = app.createLabel("label");
  if(statusText!=""){
    label.setText("@"+screenName+" : "+statusText+"\n");
  }else{
    label.setText("");
  }
  panel.add(label);
  var l1 = app.createLabel("label1");
  l1.setId("label1").setText("in_reply_to_status_id : "+repId);
  panel.add(l1);
  var hidden = app.createHidden();
  hidden.setName("rep_id").setId("rep_id").setValue(repId);
  panel.add(hidden);
  var tArea = app.createTextArea();
  tArea.setName("status_text").setId("status_text").setSize("400px", "100px");
  if(screenName!=""){
    tArea.setValue("@"+screenName+" "+screenNames);
  }
  var keyDownHandler = app.createServerHandler("onTextAreaKeyDown").addCallbackElement(tArea);
  tArea.addKeyDownHandler(keyDownHandler);
  panel.add(tArea);
  var button = app.createButton("Tweet");
  var clickhander = app.createServerHandler("onTweetButtonClick").addCallbackElement(panel);
  button.addClickHandler(clickhander);
  panel.add(button);
  var tweetCount = app.createLabel("tweetCount");
  tweetCount.setId("tweetCount").setText(Tsubunomi.defaultCount);
  panel.add(tweetCount);
  app.add(panel);
  SpreadsheetApp.getActiveSpreadsheet().show(app);
}
  
function dmDialog(){
  var statusText="";
  var range = SpreadsheetApp.getActiveRange();
  var colIdx = range.getColumnIndex();
  var lastCol = range.getLastColumn();
  var row=range.getValues()[0];
  var screenName = row[0];
  if(colIdx==2){
    if(lastCol>=3){
      statusText=row[1];
    }
  }
  var app = UiApp.createApplication().setTitle("Send DM");
  var panel = app.createVerticalPanel();
  var label = app.createLabel("label");
  label.setText("@"+screenName+" : "+statusText+"\n");
  panel.add(label);
  var hidden = app.createHidden();
  hidden.setName("screen_name").setId("screen_name").setValue(screenName);
  panel.add(hidden);
  var tArea = app.createTextArea();
  tArea.setName("status_text").setId("status_text").setSize("400px", "100px");
  var keyDownHandler = app.createServerHandler("onTextAreaKeyDown").addCallbackElement(tArea);
  tArea.addKeyDownHandler(keyDownHandler);
  panel.add(tArea);
  var button = app.createButton("Send DM");
  var clickhander = app.createServerHandler("onDmButtonClick").addCallbackElement(panel);
  button.addClickHandler(clickhander);
  panel.add(button);
  var tweetCount = app.createLabel("tweetCount");
  tweetCount.setId("tweetCount").setText(Tsubunomi.defaultCount);
  panel.add(tweetCount);
  app.add(panel);
  SpreadsheetApp.getActiveSpreadsheet().show(app);
}

function onTextAreaKeyDown(e){
  var app = UiApp.createApplication();
  //var urls=twttr.txt.extractUrls(e.parameter.status_text);
  app.getElementById("tweetCount").setText(Tsubunomi.defaultCount-e.parameter.status_text.length);
  return app;
}

function onTweetButtonClick(e){
  var repId = e.parameter.rep_id;
  var statusTxt = e.parameter.status_text;
  if(statusTxt!=""){    
    var response = tweetPost(repId,statusTxt);
    if(response.getResponseCode()==Tsubunomi.status.OK){
      Browser.msgBox("ツイートしました");
    }
  }
  return UiApp.getActiveApplication().close();
}

function onDmButtonClick(e){
  var screenName = e.parameter.screen_name;
  var text = e.parameter.status_text;
  if(text!="" && screenName.match(/^[a-zA-Z0-9_]+$/)!=null){
    var response =directMessagesNew(screenName,text);
    if(response.getResponseCode()==Tsubunomi.status.OK){
      Browser.msgBox("DM送信しました");
    }
  }else{
    Browser.msgBox("screenNameが範囲選択されていないか、本文が入力されていません");
  }
  return UiApp.getActiveApplication().close();
}

function buildSingleTextBoxDialog(actionName) {
  var title,clickHandlerName,buttonLabel;
  if(actionName==Tsubunomi.userSheetName){
    title="screenNameを入力";
    clickHandlerName="onUserButtonClick";
    buttonLabel="ユーザータイムライン表示";
  }else if(actionName==Tsubunomi.searchSheetName){
    title="検索ワードを入力";
    clickHandlerName="onSearchButtonClick";
    buttonLabel="検索タイムライン表示";
  }
  var app = UiApp.createApplication().setTitle(title);
  var panel = app.createVerticalPanel();
  var textBox = app.createTextBox();
  textBox.setId("textBox").setName("textBox").setValue(SpreadsheetApp.getActiveRange().getCell(1,1).getValue());
  panel.add(textBox);
  var button = app.createButton(buttonLabel);
  var clickhander = app.createServerHandler(clickHandlerName).addCallbackElement(panel);
  button.addClickHandler(clickhander);
  panel.add(button);
  app.add(panel);
  SpreadsheetApp.getActiveSpreadsheet().show(app);
}

function onUserButtonClick(e){
  var screenName = e.parameter.textBox;
  if( screenName!="" && screenName.match(/^[a-zA-Z0-9_]+$/)!=null ){
    userTimeline(screenName);
  }else{
    Browser.msgBox("screenNameを入力してください");
  }
  return UiApp.getActiveApplication().close();
}

function onSearchButtonClick(e){
  var searchWord = e.parameter.textBox;
  if(searchWord!=""){
    searchTimeline(searchWord);
  }
  return UiApp.getActiveApplication().close();
}

function retweetDialog(){
  var range = SpreadsheetApp.getActiveRange();
  var colIdx = range.getColumnIndex();
  if(Tsubunomi.isDmSheet()){
    return;
  }
  if(colIdx!=1){
    return;
  }
  var statusId = range.getCell(1,1).getValue();
  if(statusId==""||!statusId.match(/^[0-9]+$/)){
    Browser.msgBox("リツイートするツイートのidを範囲選択してください");
    return;
  }
  var result = Browser.msgBox("リツイートしますか？",Browser.Buttons.YES_NO);
  if(result=="yes"){
    var response = publicRetweetPost(statusId);
    if(response.getResponseCode()==Tsubunomi.status.OK){
      Browser.msgBox("リツイートしました");
    }
  }
}

function favoriteDialog(){
  var range = SpreadsheetApp.getActiveRange();
  var colIdx = range.getColumnIndex();
  if(Tsubunomi.isDmSheet()){
    return;
  }
  if(colIdx!=1){
    return;
  }
  var statusId = range.getCell(1,1).getValue();
  if(statusId=="" || !statusId.match(/^[0-9]+$/)){
    Browser.msgBox("お気に入り登録するツイートのidを範囲選択してください");
    return;
  }
  var result = Browser.msgBox("お気に入りに登録しますか？",Browser.Buttons.YES_NO);
  if(result=="yes"){
    var response = favoriteCreate(statusId);
    if(response.getResponseCode()==Tsubunomi.status.OK){
      Browser.msgBox("お気に入りに登録しました");
    }
  }
}

function followDialog(){
  var screenName = SpreadsheetApp.getActiveRange().getCell(1, 1).getValue();
  if(screenName.match(/^[a-zA-Z0-9_]+$/)==null){
    Browser.msgBox("フォローするscreenNameを範囲選択してください");
    return;
  }
  var result = Browser.msgBox("フォローしますか？",Browser.Buttons.YES_NO);
  if(result=="yes"){
    var response = friendshipsCreate(screenName);
    if(response.getResponseCode()==Tsubunomi.status.OK){
      Browser.msgBox("フォローしました");
    }
  }
}

function screenNameDialog() {
  buildSingleTextBoxDialog(Tsubunomi.userSheetName);
}

function searchDialog() {
  buildSingleTextBoxDialog(Tsubunomi.searchSheetName);
}

function mentionsTimeline(){
  var response = getMentions();
  Tsubunomi.setTimelineData(response,Tsubunomi.mentionsSheetName,Tsubunomi.mentionsSheetName);
}

function userTimeline(screenName){
  var name = Tsubunomi.getTimelineWord(screenName,Tsubunomi.userSheetName);  
  var response = getUserTimeline(name);
  Tsubunomi.setTimelineData(response,Tsubunomi.userSheetName,name);
}

function searchTimeline(searchWord){
  var word = Tsubunomi.getTimelineWord(searchWord,Tsubunomi.searchSheetName);
  var encodeSearchWord=Tsubunomi.buildQueryString(word);
  var response=getSearchTimeline(encodeSearchWord);
  Tsubunomi.setSearchData(response,word);
}

function directMessagesTimeline() {
  var dmResponse = getDirectMessages();
  var dmSentResponse = getDirectMessagesSent();
  var dmObjects= Utilities.jsonParse(dmResponse.getContentText());
  var dmSentObjects = Utilities.jsonParse(dmSentResponse.getContentText());
  var array = [];
  for(var i in dmObjects){
    array.push(dmObjects[i]);
  }
  for(var i in dmSentObjects){
    array.push(dmSentObjects[i]);
  }
  Tsubunomi.setDMData(array.sort(function(a,b){return b.id_str-a.id_str; }));
}

function reloadSheet(){
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetName();
  if(sheetName==Tsubunomi.userSheetName){
    userTimeline(null);
  }else if(sheetName==Tsubunomi.mentionsSheetName){
    mentionsTimeline();
  }else if(sheetName==Tsubunomi.searchSheetName){
    searchTimeline(null);
  }else if(sheetName==Tsubunomi.dmSheetName){
    directMessagesTimeline();
  }
}

//スプレッドシートを開いた時に自動的に呼ばれる
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {
      name : "tweet",
      functionName : "tweetDialog"
    },
    {
      name : "send DM",
      functionName : "dmDialog"
    },
    {
      name : "public retweet",
      functionName : "retweetDialog"
    },
    {
      name : "favorite",
      functionName : "favoriteDialog"
    },
    {
      name : "follow",
      functionName : "followDialog"
    },
    {
      name : "mentions timeline",
      functionName : "mentionsTimeline"
    },
    {
      name : "user timeline",
      functionName : "screenNameDialog"
    },
    {
      name : "DM timeline",
      functionName : "directMessagesTimeline"
    },
      {
      name : "search timeline",
      functionName : "searchDialog"
    },
    {
      name : "reload",
      functionName : "reloadSheet"
    }
  ];
  sheet.addMenu("Tsubunomi Menu", entries);
}

使う場合は↓あたりを参考にしてください
http://d.hatena.ne.jp/yuichi_katahira/20120717/1342491857
http://d.hatena.ne.jp/yuichi_katahira/20120715/1342365253
http://d.hatena.ne.jp/yuichi_katahira/20120712/1342068696