Skip to content

Instantly share code, notes, and snippets.

@katahirado

katahirado/TsubunomiScript.gs

Created July 12, 2012 03:20
Show Gist options
  • Save katahirado/3095464 to your computer and use it in GitHub Desktop.
Save katahirado/3095464 to your computer and use it in GitHub Desktop.
Download ZIP
つぶのみ Google Apps Script版
Raw
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);
}
@katahirado
Copy link
Author

使う場合は↓あたりを参考にしてください
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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment