Skip to content

Instantly share code, notes, and snippets.

@mhawksey
Last active August 17, 2016 08:27
Show Gist options
  • Star 13 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save mhawksey/7230553 to your computer and use it in GitHub Desktop.
Save mhawksey/7230553 to your computer and use it in GitHub Desktop.
Version 5.1.1 of TAGS script. Open Tools > Script editor and replace the TAGS.gs code with the one below
// This code excluding Google(c) Code tab:
/*
Copyright 2013 Martin Hawksey
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
var MINSIZE = 1; //minimum node size on autofill
var MAXSIZE = 10; //maximum node size on autofill
var SEARCH_TERM_CELL = "B9";
var SEARCH_PERIOD_CELL = "B13";
var RESULT_TYPE_CELL = "B14";
var FOLLOWER_COUNT_CELL = "B15";
var NUMBEROFTWEETS = "B16";
function authenticateTwitter(){
ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());
authorize();
}
function authenticateGoogleAnalytics(){
ScriptProperties.setProperty('haveGAoAuth',true);
authorizeGA();
}
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [];
// When the user clicks on "addMenuExample" then "Menu Entry 1", the function function1 is executed.
menuEntries.push({name: "Twitter API Authentication", functionName: "configureAPI"});
menuEntries.push({name: "Test Collection", functionName: "testCollection"});
menuEntries.push({name: "Delete duplicates", functionName: "deleteDuplicates"});
menuEntries.push({name: "Run Now!", functionName: "collectTweets"});
menuEntries.push(null); // line separator
menuEntries.push({name: getTriggerStatus(), functionName: "toggleTrigger"});
ss.addMenu("TAGS", menuEntries);
var menuEntries = [];
menuEntries.push({name: "Google Analytics Configuration", functionName: "renderGAConfigurationDialog"});
menuEntries.push(null); // line separator
menuEntries.push({name: "1. Copy users to Vertices sheet", functionName: "fillVerticesSheet"});
menuEntries.push({name: "2. Fill Twitter User Information", functionName: "lookupTwitterID"});
menuEntries.push(null); // line separator
menuEntries.push({name: "Get Follower IDs", functionName: "getFollowerIDs"});
menuEntries.push({name: "Get Friend IDs", functionName: "getFriendIDs"});
menuEntries.push({name: "Display Number of IDs", functionName: "displayIDSinCell"});
menuEntries.push({name: "Test Rate", functionName: "testRate"});
ss.addMenu("TAGS - Advanced", menuEntries);
}
function collectTweets() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sumSheet = ss.getSheetByName("Readme/Settings");
var SEARCH_TERM = sumSheet.getRange(SEARCH_TERM_CELL).getValue();
var RESULT_TYPE = sumSheet.getRange(RESULT_TYPE_CELL).getValue();
// if continuous sheetname = archive else make a name
if (RESULT_TYPE == "continuous"){
var sheetName = "Archive";
} else {
var sheetName = Utilities.formatDate(new Date(), "GMT", "dd-MMM-yy hh:mm"); //make a new sheet name based on todays date
}
// if sheetname doesn't exisit make it
if (!ss.getSheetByName(sheetName)){
var temp = ss.getSheetByName("TMP");
var sheet = ss.insertSheet(sheetName, {template:temp});
} else {
var sheet = ss.getSheetByName(sheetName);
}
var sinceid = false;
var id_strs = sheet.getRange(2, 1, 1501).getValues();
for (r in id_strs){
if (id_strs[r][0] != ""){
sinceid = id_strs[r][0];
break;
}
}
//if no since id grab search results
if (sinceid){
var data = getTweets(SEARCH_TERM, {"sinceid": sinceid}); // get results from twitter sinceid
} else {
var data = getTweets(SEARCH_TERM); // get results from twitter
}
// if some data insert rows
if (data.length>0){
sheet.insertRowsAfter(1, data.length);
setRowsData(sheet, data);
}
}
function getTweets(searchTerm, optAdvParams) {
var advParams = optAdvParams || {};
var data = [];
var idx = 0;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sumSheet = ss.getSheetByName("Readme/Settings");
var MIN_FOLLOWER_COUNT = parseInt(sumSheet.getRange(FOLLOWER_COUNT_CELL).getValue());
if (!isConfigured()){
Browser.msgBox("Twitter API Configuration Required");
return;
}
try {
var max_id = "";
var max_id_url = "";
var page = 1;
var done = false;
var params = {};
//defaults
params.q = searchTerm;
params.count = advParams.count || 100;
params.result_type = advParams.result_type || "recent";
params.include_entities = advParams.include_entities || 1;
if (advParams.sinceid != undefined) {
params.since_id = advParams.sinceid;
}
if (advParams.lang != undefined) {
params.lang= advParams.lang;
}
if (advParams.geocode != undefined) {
params.geocode= advParams.geocode;
}
var SEARCH_DURATION = sumSheet.getRange(SEARCH_PERIOD_CELL).getValue();
// prepare search term
if (SEARCH_DURATION != "default"){
var period = parseInt(SEARCH_DURATION.replace(/\D/g,""))-1;
var until=new Date();
until.setDate(until.getDate()-period);
var since = new Date(until);
since.setDate(since.getDate()-1-period);
params.since = twDate(since);
params.until = twDate(until);
}
var numTweets = sumSheet.getRange(NUMBEROFTWEETS).getValue();
if (numTweets > 18000) numTweets = 18000;
var maxPage = Math.ceil(numTweets/100);
var maxid_str = "";
var queryString = buildUrl("", params); // make url
while(!done){
var responseData = tw_request("search/tweets.json"+queryString);
if (responseData){
var objects = responseData.statuses;
if (objects.length>0){ // if data returned
for (i in objects){ // for the data returned we put in montly bins ready for writting/updating files
if(objects[i].user.followers_count >= MIN_FOLLOWER_COUNT){
if (objects[i].geo != null){
objects[i]["geo_coordinates"] = "loc: "+objects[i].geo.coordinates[0]+","+objects[i].geo.coordinates[1];
}
for (j in objects[i].user){
objects[i]["user_"+j] = objects[i].user[j];
}
objects[i]["from_user"] = objects[i]["user_screen_name"];
objects[i]["from_user_id_str"] = objects[i]["user_id_str"]
objects[i]["profile_image_url"] = objects[i]["user_profile_image_url"];
objects[i]["status_url"] = "http://twitter.com/"+objects[i].user_screen_name+"/statuses/"+objects[i].id_str;
objects[i]["time"] = new Date(objects[i]["created_at"]);
objects[i]["entities_str"] = Utilities.jsonStringify(objects[i]["entities"]);
data[idx]=objects[i];
idx ++;
}
}
if(responseData.search_metadata.max_id_str === objects[objects.length-1]["id_str"]){
done = true;
}
if (responseData.search_metadata.next_results != undefined) {
queryString = responseData.search_metadata.next_results;
params.max_id = responseData.search_metadata.max_id_str;
} else {
params.max_id = objects[objects.length-1]["id_str"];
queryString = buildUrl("", params); // make url
}
} else { // if not data break the loop
done = true;
}
page ++;
if (page > maxPage) done = true; // if collected 16 pages (the max) break the loop
} else {
done = true;
}
} //end of while loop
return data;
} catch (e) {
Browser.msgBox("Line "+e.lineNumber+" "+e.message+e.name);
//Browser.msgBox("Line "+e.lineNumber+" "+e.message+e.name);
ScriptProperties.setProperty("errormsg","Line "+e.lineNumber+" "+e.message+e.name);
return;
}
}
/**
* Build a querystring from a object http://stackoverflow.com/a/5340658/1027723
* @param {String} base url.
* @param {Object} objects to add to string.
* @return {String} url.
*/
function buildUrl(url, parameters){
var qs = "";
for(var key in parameters) {
var value = parameters[key];
qs += encodeURIComponent(key) + "=" + encodeURL(value) + "&";
}
if (qs.length > 0){
qs = qs.substring(0, qs.length-1); //chop off last "&"
url = url + "?" + qs;
}
return url;
}
function filterUnique(tweets){
var output = [];
var temp = {};
tweets.reverse();
for (i in tweets){
if (tweets[i][2] !="text"){
if (i>0){
var tmp = tweets[i][2];
var urlPattern = /(\b(https?|ftp|file):\/\/[-A-Z0-9+&@#\/%?=~_|!:,.;]*[-A-Z0-9+&@#\/%=~_|])/ig;
tmp = tmp.replace(urlPattern,"")
tmp = tmp.substring(0,parseInt(tmp.length*0.9));
var link = "http://twitter.com/"+tweets[i][1]+"/statuses/"+tweets[i][0].trim();
if (temp[tmp] == undefined){
temp[tmp] = [tweets[i][2],0,link];
}
temp[tmp] = [tweets[i][2],temp[tmp][1]+1,link];
//output.push([tmp]);
}
}
}
for (i in temp){
output.push([temp[i][0],temp[i][1],temp[i][2]]);
}
output.sort(function(a,b) {
return b[1]-a[1];
});
return output.slice(0, 12);
}
function returnId(){
return SpreadsheetApp.getActiveSpreadsheet().getId();
}
function testCollection(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sumSheet = ss.getSheetByName("Readme/Settings");
var SEARCH_TERM = sumSheet.getRange(SEARCH_TERM_CELL).getValue();
var data = getTweets(SEARCH_TERM, {"count":5}); // get results from twitter
if (data.length>0){
Browser.msgBox("Found some tweets. Here's an example one from "+data[0]["from_user"]+" which says: "+data[0]["text"]);
} else {
Browser.msgBox("Twitter said: "+ScriptProperties.getProperty("errormsg"));
}
}
function twDate(aDate){
var dateString = Utilities.formatDate(aDate, "GMT", "yyyy-MM-dd");
return dateString;
}
function deleteDuplicates() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Archive");
var dups = {};
var rows = [];
var toDelete = [];
var id_strs = sheet.getRange(1, 1, sheet.getLastRow()).getValues();
var row = id_strs.length;
for (r in id_strs){
var id_str = id_strs[r][0].trim();
if (dups[id_str] == undefined){
dups[id_str] = 1;
} else {
rows.push(parseInt(r));
}
row--;
}
// http://stackoverflow.com/a/3844632/1027723
var count = 0;
var firstItem = 0; // Irrelevant to start with
for (x in rows) {
// First value in the ordered list: start of a sequence
if (count == 0) {
firstItem = rows[x];
count = 1;
}
// Skip duplicate values
else if (rows[x] == firstItem + count - 1) {
// No need to do anything
}
// New value contributes to sequence
else if (rows[x] == firstItem + count) {
count++;
}
// End of one sequence, start of another
else {
if (count >= 3) {
Logger.log("Found sequence of length "+count+" starting at "+firstItem);
toDelete.push([firstItem+1,count]);
}
count = 1;
firstItem = rows[x];
}
}
if (count >= 3) {
Logger.log("Found sequence of length "+count+" starting at "+firstItem);
toDelete.push([firstItem+1,count]);
}
toDelete.reverse();
for (r in toDelete){
var resp = Browser.msgBox("Delete duplicate rows "+toDelete[r][0]+" to "+(parseInt(toDelete[r][0])+parseInt(toDelete[r][1])), Browser.Buttons.OK_CANCEL);
if (resp == "ok") sheet.deleteRows(toDelete[r][0], toDelete[r][1]);
}
}
function testRate(){
var api_request = "application/rate_limit_status.json?resources=users,search,statuses";
var data = tw_request(api_request);
var output = {};
output.search = data.resources.search["/search/tweets"];
output.user_id = data.resources.users["/users/show/:id"];
output.user_lookup = data.resources.users["/users/lookup"];
output.statuses_embeds = data.resources.statuses["/statuses/oembed"];
Browser.msgBox(JSON.stringify(output,"","\t"));
Logger.log(data.toString());
return data;
}
/**
* Toggles the script trigger to refresh archive
*/
function toggleTrigger(){
var test = getTriggerID();
if (getTriggerID() == "none" || getTriggerID() == null){ // add trigger
var dailyTrigger = ScriptApp.newTrigger("collectTweets")
.timeBased()
.everyHours(1)
.create();
setTriggerID(dailyTrigger.getUniqueId());
onOpen();
} else {
var triggers = ScriptApp.getScriptTriggers();
for (var i = 0; i < triggers.length; i++) {
if (triggers[i].getUniqueId() == getTriggerID()){
ScriptApp.deleteTrigger(triggers[i]);
setTriggerID("none");
onOpen();
break;
}
}
}
}
/**
* Gets trigger menu option text
* @return {String} Stats text for menu.
*/
function getTriggerStatus(){
if (getTriggerID() == "none" || getTriggerID() == null) return "Update archive every hour";
return "Stop updating archive every hour"
}
/**
* @param {String} set a trigger id.
*/
function setTriggerID(id){
ScriptProperties.setProperty("triggerID", id);
}
/**
* @return {String} get a trigger id.
*/
function getTriggerID(){
return ScriptProperties.getProperty("triggerID");
}
function tw_request(api_request, optMethod){
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(getConsumerKey());
oauthConfig.setConsumerSecret(getConsumerSecret());
var requestData = {
"method": optMethod || "GET",
"oAuthServiceName": "twitter",
"oAuthUseToken": "always"
};
try {
var result = UrlFetchApp.fetch("https://api.twitter.com/1.1/"+api_request, requestData);
if (result.getResponseCode() === 200){
return Utilities.jsonParse(result.getContentText());
} else {
return false;
}
} catch (e) {
Logger.log(e);
return false;
}
}
// suggested by Sergii Kauk https://plus.google.com/u/0/+AmitAgarwal/posts/FSuCNdh7jJ1
function encodeURL(string){
return encodeURIComponent(string).replace(/!|\*|\(|\)|'/g, function(m){return "%"+m.charCodeAt(0).toString(16)});
}
function alltrim(str) {
return str.replace(/^\s+|\s+$/g, '');
}
function replicate (n, x) {
var xs = [];
for (var i = 0; i < n; ++i) {
xs.push(x);
}
return xs;
}
// http://jsfromhell.com/array/chunk
function chunk(a, s){
for(var x, i = 0, c = -1, l = a.length, n = []; i < l; i++)
(x = i % s) ? n[c][x] = a[i] : n[++c] = [a[i]];
return n;
}
// http://snook.ca/archives/javascript/testing_for_a_v
function oc(a)
{
var o = {};
for(var i=0;i<a.length;i++)
{
o[a[i]]='';
}
return o;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment