Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function myFunction() {
- ss = SpreadsheetApp.getActiveSpreadsheet();
- SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
- as = SpreadsheetApp.getActiveSheet();
- kesu = null;
- as.getRange("A4:H800").setValue(kesu);
- searchWord = as.getRange("F1").getValue();
- if (searchWord != "") {
- Logger.log("F1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("G1").getValue();
- if (searchWord != "") {
- Logger.log("G1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("H1").getValue();
- if (searchWord != "") {
- Logger.log("H1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("I1").getValue();
- if (searchWord != "") {
- Logger.log("I1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("J1").getValue();
- if (searchWord != "") {
- Logger.log("J1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("K1").getValue();
- if (searchWord != "") {
- Logger.log("K1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("L1").getValue();
- if (searchWord != "") {
- Logger.log("L1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("M1").getValue();
- if (searchWord != "") {
- Logger.log("M1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("N1").getValue();
- if (searchWord != "") {
- Logger.log("N1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("O1").getValue();
- if (searchWord != "") {
- Logger.log("O1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("P1").getValue();
- if (searchWord != "") {
- Logger.log("O1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("Q1").getValue();
- if (searchWord != "") {
- Logger.log("O1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("R1").getValue();
- if (searchWord != "") {
- Logger.log("O1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("S1").getValue();
- if (searchWord != "") {
- Logger.log("O1");
- search(searchWord, 0);
- }
- searchWord = as.getRange("T1").getValue();
- if (searchWord != "") {
- Logger.log("O1");
- 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 ,ff;
- // Twitter Search APIのURL
- var API_URL = "https://api.twitter.com/1.1/search/tweets.json?";
- try {
- setOAuth();
- url = API_URL + "count=50"
- + "&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++;
- }
- Logger.log("row");
- Logger.log(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)) {
- ff = "";
- cell.offset(row, col++).setValue(result.id_str);
- ff = result.id_str + "%%x%%";
- cell.offset(row, col++).setValue(dd);
- ff = ff + dd + "%%x%%";
- cell.offset(row, col++).setValue(result.user.name);
- ff = ff + result.user.name + "%%x%%";
- cell.offset(row, col++).setValue(result.text);
- ff = ff + result.text + "%%x%%";
- cell.offset(row, col++).setValue('https://twitter.com/'
- + result.user.screen_name
- + "/status/"
- + result.id_str);
- ff = ff + "https://twitter.com/" + result.user.screen_name + "/status/" + result.id_str + "%%x%%";
- cell.offset(row, col++).setValue('https://twitter.com/'
- + result.user.screen_name);
- ff = ff + "https://twitter.com/" + result.user.screen_name + "%%x%%";
- if (result.entities.urls.length > 0) {
- cell.offset(row, col++).setValue(result.entities.urls[0].expanded_url);
- ff = ff + result.entities.urls[0].expanded_url + "%%x%%";
- }
- else {
- cell.offset(row, col++).setValue("");
- ff = ff + "%%x%%";
- }
- cell.offset(row, col++).setValue(ff);
- row++;
- }
- // }
- }
- } catch (e) {
- Logger.log(e);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement