Skip to content

Instantly share code, notes, and snippets.

@takaheraw

takaheraw/all_tweet.gs

Created October 1, 2016 10:45
Show Gist options
  • Save takaheraw/bbdc95e91174118f7997a71bccd8ae39 to your computer and use it in GitHub Desktop.
Save takaheraw/bbdc95e91174118f7997a71bccd8ae39 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
all_tweet.gs
var SCREEN_NAME = '';
var CONSUMER_KEY = '';
var CONSUMER_SECRET = '';
var PROJECT_KEY = '';
var FOLDER_ID = '';
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 SEARCH_KEY = 'MM-dd-HH-mm';
var IMG_FILE_NAME = 'yyyy-MM-dd-HH-mm-ss';
/*
* tweet
*/
var JST_DATE = 1;
var TEXT = 2;
var RETWEET_COUNT = 3;
var FAVORITE_COUNT = 4;
var ENTITIES_HASHTAGS = 5;
var ENTITIES_USER_MENTIONS = 6;
var ENTITIES_MEDIA = 7;
var URL = 8;
var SOURCE = 9;
var CREATED_AT = 10;
var TWEET_KEY = 11;
var ID_STR = 12;
var GEO_LAT = 13;
var GEO_LON = 14;
var PLACE_ID = 15;
var PLACE_URL = 16;
var PLACE_PLACE_TYPE = 17;
var PLACE_NAME = 18;
var PLACE_FULL_NAME = 19;
var PLACE_COUNTRY_CODE = 20;
var PLACE_COUNTRY = 21;
var LANG = 22;
/*
* reply
*/
var IN_REPLY_TO_STATUS_ID_STR = 23;
var IN_REPLY_TO_USER_ID_STR = 24;
var IN_REPLY_TO_SCREEN_NAME = 25;
/*
* user
*/
var USER_ID_STR = 26;
var USER_NAME = 27;
var USER_SCREEN_NAME = 28;
var USER_LOCATION = 29;
var USER_DESCRIPTION = 30;
var USER_URL = 31;
var USER_FOLLOWERS_COUNT = 32;
var USER_FRIENDS_COUNT = 33;
var USER_LISTED_COUNT = 34;
var USER_CREATED_AT = 35;
var USER_FAVOURITES_COUNT = 36;
var USER_STATUSES_COUNT = 37;
var USER_LANG = 38;
var USER_PROFILE_IMAGE_URL_HTTPS = 39;
var USER_PROFILE_BANNER_URL = 40;
/*
* retweet
*/
var RETWEETED_STATUS_CREATED_AT = 41;
var RETWEETED_STATUS_ID_STR = 42;
var RETWEETED_STATUS_TEXT = 43;
var RETWEETED_STATUS_SOURCE = 44;
var RETWEETED_STATUS_IN_REPLY_TO_STATUS_ID_STR = 45;
var RETWEETED_STATUS_IN_REPLY_TO_USER_ID_STR = 46;
var RETWEETED_STATUS_IN_REPLY_TO_SCREEN_NAME = 47;
var RETWEETED_STATUS_USER_ID_STR = 48;
var RETWEETED_STATUS_USER_NAME = 49;
var RETWEETED_STATUS_USER_SCREEN_NAME = 50;
var RETWEETED_STATUS_USER_LOCATION = 51;
var RETWEETED_STATUS_USER_DESCRIPTION = 52;
var RETWEETED_STATUS_USER_URL = 53;
var RETWEETED_STATUS_USER_FOLLOWERS_COUNT = 54;
var RETWEETED_STATUS_USER_FRIENDS_COUNT = 55;
var RETWEETED_STATUS_USER_LISTED_COUNT = 56;
var RETWEETED_STATUS_USER_CREATED_AT = 57;
var RETWEETED_STATUS_USER_FAVOURITES_COUNT = 58;
var RETWEETED_STATUS_USER_STATUSES_COUNT = 59;
var RETWEETED_STATUS_USER_LANG = 60;
var RETWEETED_STATUS_USER_PROFILE_IMAGE_URL_HTTPS = 61;
var RETWEETED_STATUS_USER_PROFILE_BANNER_URL = 62;
/*
* quote
*/
var QUOTED_STATUS_CREATED_AT = 63;
var QUOTED_STATUS_ID_STR = 64;
var QUOTED_STATUS_TEXT = 65;
var QUOTED_STATUS_SOURCE = 66;
var QUOTED_STATUS_IN_REPLY_TO_STATUS_ID_STR = 67;
var QUOTED_STATUS_IN_REPLY_TO_USER_ID_STR = 68;
var QUOTED_STATUS_IN_REPLY_TO_SCREEN_NAME = 69;
var QUOTED_STATUS_USER_ID_STR = 70;
var QUOTED_STATUS_USER_NAME = 71;
var QUOTED_STATUS_USER_SCREEN_NAME = 72;
var QUOTED_STATUS_USER_LOCATION = 73;
var QUOTED_STATUS_USER_DESCRIPTION = 74;
var QUOTED_STATUS_USER_URL = 75;
var QUOTED_STATUS_USER_FOLLOWERS_COUNT = 76;
var QUOTED_STATUS_USER_FRIENDS_COUNT = 77;
var QUOTED_STATUS_USER_LISTED_COUNT = 78;
var QUOTED_STATUS_USER_CREATED_AT = 79;
var QUOTED_STATUS_USER_FAVOURITES_COUNT = 80;
var QUOTED_STATUS_USER_STATUSES_COUNT = 81;
var QUOTED_STATUS_USER_LANG = 82;
var QUOTED_STATUS_USER_PROFILE_IMAGE_URL_HTTPS = 83;
var QUOTED_STATUS_USER_PROFILE_BANNER_URL = 84;
/*
* 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;
}
/*
* Google Drive のフォルダにファイル保存
*/
function createImage() {
Logger.log("[START] createImage()");
var folder = DriveApp.getFolderById(FOLDER_ID);
var twSs = SpreadsheetApp.getActive().getSheetByName('tweet');
var startRow = 2343;
var headerRow = 2;
var numRows = twSs.getDataRange().getLastRow() - headerRow;
var numColumns = CREATED_AT - ENTITIES_MEDIA + 1;
var targetRange = twSs.getRange(startRow, ENTITIES_MEDIA, numRows, numColumns);
var targetValues = targetRange.getValues();
for (var row in targetValues) {
var media = targetValues[row][0];
if(media) {
var created_at = targetValues[row][numColumns - 1];
var imgFileName = Utilities.formatDate(new Date(created_at), 'JST', IMG_FILE_NAME);
var urls = media.split('\n');
for(var i = 0; i < urls.length; i++) {
imgFileName = imgFileName + "_" + i + ".jpg"
var files = folder.getFilesByName(imgFileName);
if(files.hasNext()) {
continue;
}
Logger.log(imgFileName + " " + urls[i]);
try{
var imageBlob = UrlFetchApp.fetch(urls[i]).getBlob();
var file = folder.createFile(imageBlob);
file.setName(imgFileName);
} catch(e) {
Logger.log("[ERROR] createImage : " + e.message);
}
}
}
}
Logger.log("[END] createImage()");
}
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');
}
}
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);
}
}
function allTweets() {
Logger.log("[START] allTweets()");
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++) {
/*
* tweet
*/
var d = new Date(tweets[ii].created_at);
tws.getRange(iRow, JST_DATE ).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);
// hashtag
var hashtags = tweets[ii].entities.hashtags;
if(hashtags instanceof Array) {
var hashtag = [];
for(var iii = 0; iii < hashtags.length; iii++) {
hashtag.push(hashtags[iii].text);
}
tws.getRange(iRow, ENTITIES_HASHTAGS).setValue(hashtag.join('\n'));
}
// use mention
var user_mentions = tweets[ii].entities.user_mentions;
if(user_mentions instanceof Array) {
var screen_name = [];
for(var iii = 0; iii < user_mentions.length; iii++) {
screen_name.push(user_mentions[iii].screen_name);
}
tws.getRange(iRow, ENTITIES_USER_MENTIONS).setValue(screen_name.join('\n'));
}
// media
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);
tws.getRange(iRow, SOURCE ).setValue(tweets[ii].source.replace(/<("[^"]*"|'[^']*'|[^'">])*>/g, ''));
tws.getRange(iRow, CREATED_AT).setValue(tweets[ii].created_at);
tws.getRange(iRow, TWEET_KEY ).setValue(Utilities.formatDate(d, 'JST', SEARCH_KEY));
tws.getRange(iRow, ID_STR ).setValue(tweets[ii].id_str);
var geo = tweets[ii].geo;
if(geo) {
tws.getRange(iRow, GEO_LAT).setValue(geo.coordinates[0]);
tws.getRange(iRow, GEO_LON).setValue(geo.coordinates[1]);
}
var place = tweets[ii].place;
if(place) {
tws.getRange(iRow, PLACE_ID ).setValue(place.id);
tws.getRange(iRow, PLACE_URL ).setValue(place.url);
tws.getRange(iRow, PLACE_PLACE_TYPE ).setValue(place.place_type);
tws.getRange(iRow, PLACE_NAME ).setValue(place.name);
tws.getRange(iRow, PLACE_FULL_NAME ).setValue(place.full_name);
tws.getRange(iRow, PLACE_COUNTRY_CODE).setValue(place.country_code);
tws.getRange(iRow, PLACE_COUNTRY ).setValue(place.country);
}
tws.getRange(iRow, LANG).setValue(tweets[ii].lang);
/*
* reply
*/
tws.getRange(iRow, IN_REPLY_TO_STATUS_ID_STR).setValue(tweets[ii].in_reply_to_status_id_str);
tws.getRange(iRow, IN_REPLY_TO_USER_ID_STR ).setValue(tweets[ii].in_reply_to_user_id_str);
tws.getRange(iRow, IN_REPLY_TO_SCREEN_NAME ).setValue(tweets[ii].in_reply_to_screen_name);
/*
* user
*/
var user = tweets[ii].user;
if(user) {
tws.getRange(iRow, USER_ID_STR ).setValue(user.id_str);
tws.getRange(iRow, USER_NAME ).setValue(user.name);
tws.getRange(iRow, USER_SCREEN_NAME ).setValue(user.screen_name);
tws.getRange(iRow, USER_LOCATION ).setValue(user.location);
tws.getRange(iRow, USER_DESCRIPTION ).setValue(user.description);
tws.getRange(iRow, USER_URL ).setValue(user.url);
tws.getRange(iRow, USER_FOLLOWERS_COUNT ).setValue(user.followers_count);
tws.getRange(iRow, USER_FRIENDS_COUNT ).setValue(user.friends_count);
tws.getRange(iRow, USER_LISTED_COUNT ).setValue(user.listed_count);
tws.getRange(iRow, USER_CREATED_AT ).setValue(user.created_at);
tws.getRange(iRow, USER_FAVOURITES_COUNT ).setValue(user.favourites_count);
tws.getRange(iRow, USER_STATUSES_COUNT ).setValue(user.statuses_count);
tws.getRange(iRow, USER_LANG ).setValue(user.lang);
tws.getRange(iRow, USER_PROFILE_IMAGE_URL_HTTPS).setValue(user.profile_image_url_https);
tws.getRange(iRow, USER_PROFILE_BANNER_URL ).setValue(user.profile_banner_url);
}
/*
* retweet
*/
var retweeted_status = tweets[ii].retweeted_status;
if(retweeted_status) {
tws.getRange(iRow, RETWEETED_STATUS_CREATED_AT ).setValue(retweeted_status.created_at);
tws.getRange(iRow, RETWEETED_STATUS_ID_STR ).setValue(retweeted_status.id_str);
tws.getRange(iRow, RETWEETED_STATUS_TEXT ).setValue(retweeted_status.text);
tws.getRange(iRow, RETWEETED_STATUS_SOURCE ).setValue(retweeted_status.source.replace(/<("[^"]*"|'[^']*'|[^'">])*>/g, ''));
tws.getRange(iRow, RETWEETED_STATUS_IN_REPLY_TO_STATUS_ID_STR ).setValue(retweeted_status.in_reply_to_status_id_str);
tws.getRange(iRow, RETWEETED_STATUS_IN_REPLY_TO_USER_ID_STR ).setValue(retweeted_status.in_reply_to_user_id_str);
tws.getRange(iRow, RETWEETED_STATUS_IN_REPLY_TO_SCREEN_NAME ).setValue(retweeted_status.in_reply_to_screen_name);
tws.getRange(iRow, RETWEETED_STATUS_USER_ID_STR ).setValue(retweeted_status.user.id_str);
tws.getRange(iRow, RETWEETED_STATUS_USER_NAME ).setValue(retweeted_status.user.name);
tws.getRange(iRow, RETWEETED_STATUS_USER_SCREEN_NAME ).setValue(retweeted_status.user.screen_name);
tws.getRange(iRow, RETWEETED_STATUS_USER_LOCATION ).setValue(retweeted_status.user.location);
tws.getRange(iRow, RETWEETED_STATUS_USER_DESCRIPTION ).setValue(retweeted_status.user.description);
tws.getRange(iRow, RETWEETED_STATUS_USER_URL ).setValue(retweeted_status.user.url);
tws.getRange(iRow, RETWEETED_STATUS_USER_FOLLOWERS_COUNT ).setValue(retweeted_status.user.followers_count);
tws.getRange(iRow, RETWEETED_STATUS_USER_FRIENDS_COUNT ).setValue(retweeted_status.user.friends_count);
tws.getRange(iRow, RETWEETED_STATUS_USER_LISTED_COUNT ).setValue(retweeted_status.user.listed_count);
tws.getRange(iRow, RETWEETED_STATUS_USER_CREATED_AT ).setValue(retweeted_status.user.created_at);
tws.getRange(iRow, RETWEETED_STATUS_USER_FAVOURITES_COUNT ).setValue(retweeted_status.user.favourites_count);
tws.getRange(iRow, RETWEETED_STATUS_USER_STATUSES_COUNT ).setValue(retweeted_status.user.statuses_count);
tws.getRange(iRow, RETWEETED_STATUS_USER_LANG ).setValue(retweeted_status.user.lang);
tws.getRange(iRow, RETWEETED_STATUS_USER_PROFILE_IMAGE_URL_HTTPS).setValue(retweeted_status.user.profile_image_url_https);
tws.getRange(iRow, RETWEETED_STATUS_USER_PROFILE_BANNER_URL ).setValue(retweeted_status.user.profile_banner_url);
}
/*
* quote
*/
var quoted_status = tweets[ii].quoted_status;
if(quoted_status) {
tws.getRange(iRow, QUOTED_STATUS_CREATED_AT ).setValue(quoted_status.created_at);
tws.getRange(iRow, QUOTED_STATUS_ID_STR ).setValue(quoted_status.id_str);
tws.getRange(iRow, QUOTED_STATUS_TEXT ).setValue(quoted_status.text);
tws.getRange(iRow, QUOTED_STATUS_SOURCE ).setValue(quoted_status.source.replace(/<("[^"]*"|'[^']*'|[^'">])*>/g, ''));
tws.getRange(iRow, QUOTED_STATUS_IN_REPLY_TO_STATUS_ID_STR ).setValue(quoted_status.in_reply_to_status_id_str);
tws.getRange(iRow, QUOTED_STATUS_IN_REPLY_TO_USER_ID_STR ).setValue(quoted_status.in_reply_to_user_id_str);
tws.getRange(iRow, QUOTED_STATUS_IN_REPLY_TO_SCREEN_NAME ).setValue(quoted_status.in_reply_to_screen_name);
tws.getRange(iRow, QUOTED_STATUS_USER_ID_STR ).setValue(quoted_status.user.id_str);
tws.getRange(iRow, QUOTED_STATUS_USER_NAME ).setValue(quoted_status.user.name);
tws.getRange(iRow, QUOTED_STATUS_USER_SCREEN_NAME ).setValue(quoted_status.user.screen_name);
tws.getRange(iRow, QUOTED_STATUS_USER_LOCATION ).setValue(quoted_status.user.location);
tws.getRange(iRow, QUOTED_STATUS_USER_DESCRIPTION ).setValue(quoted_status.user.description);
tws.getRange(iRow, QUOTED_STATUS_USER_URL ).setValue(quoted_status.user.url);
tws.getRange(iRow, QUOTED_STATUS_USER_FOLLOWERS_COUNT ).setValue(quoted_status.user.followers_count);
tws.getRange(iRow, QUOTED_STATUS_USER_FRIENDS_COUNT ).setValue(quoted_status.user.friends_count);
tws.getRange(iRow, QUOTED_STATUS_USER_LISTED_COUNT ).setValue(quoted_status.user.listed_count);
tws.getRange(iRow, QUOTED_STATUS_USER_CREATED_AT ).setValue(quoted_status.user.created_at);
tws.getRange(iRow, QUOTED_STATUS_USER_FAVOURITES_COUNT ).setValue(quoted_status.user.favourites_count);
tws.getRange(iRow, QUOTED_STATUS_USER_STATUSES_COUNT ).setValue(quoted_status.user.statuses_count);
tws.getRange(iRow, QUOTED_STATUS_USER_LANG ).setValue(quoted_status.user.lang);
tws.getRange(iRow, QUOTED_STATUS_USER_PROFILE_IMAGE_URL_HTTPS).setValue(quoted_status.user.profile_image_url_https);
tws.getRange(iRow, QUOTED_STATUS_USER_PROFILE_BANNER_URL ).setValue(quoted_status.user.profile_banner_url);
}
iRow++;
}
}
} catch(e) {
Logger.log("[ERROR] allTweets() : " + e.message);
MailApp.sendEmail("[email protected]", "[ERROR] allTweets()", 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 onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [
{name: "Tweetをダウンロード", functionName: "allTweets"}
];
ss.addMenu("スクリプト",menuEntries);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment