Created
March 14, 2018 23:19
-
-
Save jimtalksdata/906fce620938e163b73b8d5a2cf1aada to your computer and use it in GitHub Desktop.
Google Sheets - Twitter Export 3.2.3
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Copyright 2014 Martin Hawksey. All Rights Reserved. | |
// | |
// 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. | |
// @OnlyCurrentDoc | |
var msg =""; | |
function onOpen() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var menuEntries = [ {name: "Configure", functionName: "setup"}, | |
{name: "Disconnect Twitter Access", functionName: "disconnectTwitter"}, | |
{name: "Test Connection", functionName: "aTest"}, | |
{name: "Clear Current Sheet", functionName: "clearSheet"}, | |
{name: "Get Friends", functionName: "getFriends"}, | |
{name: "Get Followers", functionName: "getFollowers"}, | |
{name: "Get other persons followers", functionName: "getAnotherFollowers"}, | |
{name: "Get other persons friends", functionName: "getAnotherFriends"} ]; | |
ss.addMenu("Twitter", menuEntries); | |
} | |
/** | |
* Removes all user data from TwtrService | |
*/ | |
function disconnectTwitter() { | |
var result = Browser.msgBox("Warning!", | |
"Disconnecting Twitter will remove the connection for all of your TAGS sheets.\\n\\nDo you still want to continue?", | |
Browser.Buttons.YES_NO); | |
// Process the user's response. | |
if (result == 'yes') { | |
TwtrService.disconnectTwitter(); | |
} | |
} | |
function getFriends(){ | |
getFriendAndFo("friends", "friends"); // using part of the Twitter API call also as sheet name | |
} | |
function getFollowers(){ | |
getFriendAndFo("followers", "followers"); // using part of the Twitter API call also as sheet name | |
} | |
function getAnotherFollowers(){ | |
var screenName = Browser.inputBox("Enter screen name of person you'd like to get followers for:"); | |
if (screenName){ | |
getAnother(screenName, "followers"); | |
} | |
} | |
function getAnotherFriends(){ | |
var screenName = Browser.inputBox("Enter screen name of person you'd like to get friends for:"); | |
if (screenName){ | |
getAnother(screenName, "friends"); | |
} | |
} | |
function getAnother(screenName, type){ | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var pro = isProtected(screenName); | |
if (isProtected(screenName)){ | |
Browser.msgBox("This users account is protected. Data is not available"); | |
return; | |
} | |
if (!ss.getSheetByName(type + " - " + screenName)){ | |
var temp = ss.getSheetByName("TMP"); | |
// fix for current bug on insertSheet using template | |
/*ss.setActiveSheet(temp); | |
var sheet = ss.duplicateActiveSheet(); | |
ss.setActiveSheet(sheet); | |
ss.renameActiveSheet(type + " - " + screenName); | |
*/ | |
ss.insertSheet(type + " - " + screenName, {template:temp}); | |
} | |
getFriendAndFo(type + " - " + screenName, type, screenName); | |
} | |
function isProtected(screenName){ | |
var url = "users/show.json?screen_name="+screenName; | |
var o = TwtrService.get("users/show", {"screen_name": screenName}).protected; | |
//var o = tw_request("GET", url).protected; | |
return o; | |
} | |
function getFriendAndFo(sheetName, friendOrFo, optScreenName){ | |
var params = {cursor: -1}; | |
var statusString = ""; | |
var your_screen_name = TwtrService.get('account/verify_credentials', {}).screen_name; | |
//var your_screen_name = tw_request("GET", "account/verify_credentials.json"); | |
if (typeof optScreenName != "undefined") { | |
params.screen_name= optScreenName; | |
//statusString = "&screen_name="+optScreenName; | |
your_screen_name = optScreenName; | |
} | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
ss.toast("Looking for data ..."); | |
var sheet = ss.getSheetByName(sheetName); | |
if (sheet.getLastRow()>1) { | |
var existing_ids = sheet.getRange(2, 1, sheet.getLastRow(), 1).getValues(); | |
var existing_ids_str = existing_ids.join(); | |
} else { | |
var existing_ids_str = ""; | |
} | |
//var cursor = "-1"; | |
var count = 0; | |
var users = []; | |
while(params.cursor != "none"){ // while twitter returns data loop | |
try { | |
//var url = friendOrFo+"/ids.json?cursor=" + cursor + statusString; | |
var o = TwtrService.get(friendOrFo+"/ids", params); | |
//var o = tw_request("GET", url); | |
for (j in o.ids){ | |
// get new ids delete missing ones | |
var searchKey = new RegExp(o.ids[j].toString(),"gi"); | |
var test = existing_ids_str.search(searchKey); | |
if (existing_ids_str.search(searchKey) === -1){ | |
users.push(o.ids[j]); | |
} | |
} | |
if (o.next_cursor!="0" || count < 6){ | |
params.cursor = o.next_cursor; // get next cursor | |
count++; | |
} else { | |
params.cursor = "none"; // break | |
} | |
} catch (e) { | |
Logger.log(e); | |
} | |
} | |
// lookup ids from twitter | |
var data = []; | |
var count = 0; | |
var chunks = chunk(users,100); | |
for (i in chunks){ | |
count++; | |
if (count > 50){ | |
msg = "Reaching run-time limit. Run again to get additional data. "; | |
break; | |
} | |
ss.toast("Getting "+i*100+" to "+(parseInt(i)+1)*100+" of "+chunks.length*100); | |
try { | |
var o = TwtrService.get("users/lookup", {"user_id": chunks[i].join()}); | |
//var o = tw_request("GET", "users/lookup.json?user_id=" + chunks[i].join()); // note using sheetname to build api request; | |
for (j in o){ | |
if (o[j]['entities']['url'] !== undefined){ | |
o[j].profile_link = o[j]['entities']['url']['urls'][0]['expanded_url'] || ""; | |
} | |
data.push(o[j]); | |
} | |
} catch(e) { | |
ss.toast("Oops something has gone wrong. Inserting existing data"); | |
insertData(sheet, data); | |
Logger.log(e); | |
} | |
} | |
insertData(sheet, data); | |
} | |
function insertData(sheet, data){ | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
if (data.length>0){ | |
ss.toast(msg+"Inserting "+data.length+" new users"); | |
sheet.insertRowsAfter(1, data.length); | |
setRowsData(sheet, data); | |
} else { | |
ss.toast("All done - no new users"); | |
} | |
} | |
function resetGetLotsOfFriendAndFo(){ | |
PropertiesService.getScriptProperties().setProperty("cursor", "-1"); | |
} | |
function getLotsOfFriendAndFo(){ | |
// NOTE: before using running this script clear any existing data (apart from the header row) from the target sheet | |
var sheetName = "Followers"; // enter the sheet name to update | |
var friendOrFo = "followers"; // options 'friends' or 'followers | |
var optScreenName = "__GAS"; // if you are getting someone elses friends or followers enter their screen name here | |
var params = {}; | |
//var your_screen_name = tw_request("GET", "account/verify_credentials").screen_name; | |
var your_screen_name = TwtrService.get('account/verify_credentials', {}).screen_name; | |
if (typeof optScreenName != "undefined") { | |
params.screen_name = optScreenName; | |
your_screen_name = optScreenName; | |
} | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getSheetByName(sheetName); | |
if (!PropertiesService.getScriptProperties().getProperty("cursor")){ | |
PropertiesService.getScriptProperties().setProperty("cursor", "-1"); | |
} | |
if (PropertiesService.getScriptProperties().getProperty("cursor") != "none"){ // while twitter returns data loop | |
var users = []; | |
try { | |
//var url = friendOrFo+"/ids?cursor=" + PropertiesService.getScriptProperties().getProperty("cursor") + statusString; | |
params.cursor = PropertiesService.getScriptProperties().getProperty("cursor"); | |
//var o = tw_request("GET", url); | |
var o = TwtrService.get(friendOrFo+"/ids", params) | |
Logger.log(o); | |
for (j in o.ids){ | |
users.push(o.ids[j]); | |
} | |
var data = []; | |
var count = 0; | |
var chunks = chunk(users,100); | |
for (i in chunks){ | |
ss.toast("Getting "+i*100+" to "+(parseInt(i)+1)*100+" of "+chunks.length*100); | |
//var d = tw_request("GET", "users/lookup?user_id=" + chunks[i].join()); // note using sheetname to build api request; | |
var userParams = {user_id: chunks[i].join()}; | |
var d = TwtrService.get("users/lookup", userParams); | |
for (j in d){ | |
data.push(d[j]); | |
} | |
} | |
insertData(sheet, data); | |
if (o.next_cursor!="0"){ | |
PropertiesService.getScriptProperties().setProperty("cursor", o.next_cursor.toString()); | |
ss.toast("Run this function again to get more data"); // get next cursor | |
} else { | |
ss.toast("All data collected"); | |
} | |
// lookup ids from twitter | |
} catch (e) { | |
Logger.log(e); | |
} | |
} | |
} | |
function aTest(){ // quick test to see if recieving data from twitter API | |
var api_request = "account/verify_credentials.json"; | |
var method = "GET"; | |
var data = TwtrService.get("account/verify_credentials"); | |
//var data = tw_request(method, api_request); | |
Logger.log(data); | |
if(data.screen_name){ | |
Browser.msgBox("Connected to Twitter sucessfully with "+data.screen_name); | |
} else { | |
Browser.msgBox("OOPS - it didn't work"); | |
} | |
} | |
function clearSheet(){ // quick function to clear active sheet leaving headers | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getActiveSheet(); | |
sheet.getRange(2, 1, sheet.getLastRow(), sheet.getMaxColumns()).clear({contentsOnly:true}); | |
} | |
function authenticate(){ | |
authorize(); | |
} | |
function configureAPI() { | |
//getID(); | |
renderAPIConfigurationDialog(); | |
} | |
/** | |
* Launches key/secret and auth flow | |
*/ | |
function setup() { | |
if (TwtrService.isUserConnectedToTwitter()){ | |
var result = Browser.msgBox("Twitter Authorisation", | |
"You appear to already be connected to Twitter.\\n\\nWould you like to run the setup again?", | |
Browser.Buttons.YES_NO); | |
// Process the user's response. | |
if (result == 'yes') { | |
// User clicked "Yes". | |
TwtrService.showTwitterKeySecret(SpreadsheetApp); | |
} | |
} else { | |
TwtrService.showTwitterKeySecret(SpreadsheetApp); | |
} | |
} | |
/** | |
* Used as part of setup() to process form data | |
*/ | |
function processForm(formObject) { | |
TwtrService.setUserKeySecret(formObject); | |
return TwtrService.showTwitterLogin(SpreadsheetApp); | |
} | |
/** | |
* Get the url for GA-Beacon tracking | |
*/ | |
function getGABeacon(tid){ | |
var id = SpreadsheetApp.getActiveSpreadsheet().getId(); | |
var locale = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetLocale(); | |
return 'https://ga-beacon.appspot.com/'+tid+'/FriendFo/3.2/'+id+'/'+locale+'/'; | |
} | |
function tw_request(method, api_request){ | |
return TwtrService.get(api_request); | |
} | |
// Back to the stuff from Google --> | |
// setRowsData fills in one row of data per object defined in the objects Array. | |
// For every Column, it checks if data objects define a value for it. | |
// Arguments: | |
// - sheet: the Sheet Object where the data will be written | |
// - objects: an Array of Objects, each of which contains data for a row | |
// - optHeadersRange: a Range of cells where the column headers are defined. This | |
// defaults to the entire first row in sheet. | |
// - optFirstDataRowIndex: index of the first row where data should be written. This | |
// defaults to the row immediately below the headers. | |
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) { | |
var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns()); | |
var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1; | |
var headers = normalizeHeaders(headersRange.getValues()[0]); | |
var data = []; | |
for (var i = 0; i < objects.length; ++i) { | |
var values = []; | |
var row = objectDot (objects[i]).reduce(function(p,c){ | |
p[c.key] = c.value; | |
return p; | |
},{}); | |
for (j = 0; j < headers.length; ++j) { | |
var header = headers[j]; | |
values.push(header.length > 0 && row[header] ? row[header] : ""); | |
} | |
data.push(values); | |
} | |
var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), | |
objects.length, headers.length); | |
destinationRange.setValues(data); | |
} | |
// getRowsData iterates row by row in the input range and returns an array of objects. | |
// Each object contains all the data for a given row, indexed by its normalized column name. | |
// Arguments: | |
// - sheet: the sheet object that contains the data to be processed | |
// - range: the exact range of cells where the data is stored | |
// - columnHeadersRowIndex: specifies the row number where the column names are stored. | |
// This argument is optional and it defaults to the row immediately above range; | |
// Returns an Array of objects. | |
function getRowsData(sheet, range, columnHeadersRowIndex) { | |
columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1; | |
var numColumns = range.getEndColumn() - range.getColumn() + 1; | |
var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns); | |
var headers = headersRange.getValues()[0]; | |
return getObjects(range.getValues(), normalizeHeaders(headers)); | |
} | |
// For every row of data in data, generates an object that contains the data. Names of | |
// object fields are defined in keys. | |
// Arguments: | |
// - data: JavaScript 2d array | |
// - keys: Array of Strings that define the property names for the objects to create | |
function getObjects(data, keys) { | |
var objects = []; | |
for (var i = 0; i < data.length; ++i) { | |
var object = {}; | |
var hasData = false; | |
for (var j = 0; j < data[i].length; ++j) { | |
var cellData = data[i][j]; | |
if (isCellEmpty(cellData)) { | |
continue; | |
} | |
object[keys[j]] = cellData; | |
hasData = true; | |
} | |
if (hasData) { | |
objects.push(object); | |
} | |
} | |
return objects; | |
} | |
// Returns an Array of normalized Strings. | |
// Arguments: | |
// - headers: Array of Strings to normalize | |
function normalizeHeaders(headers) { | |
var keys = []; | |
for (var i = 0; i < headers.length; ++i) { | |
var key = normalizeHeader(headers[i]); | |
if (key.length > 0) { | |
keys.push(key); | |
} | |
} | |
return keys; | |
} | |
// Normalizes a string, by removing all alphanumeric characters and using mixed case | |
// to separate words. The output will always start with a lower case letter. | |
// This function is designed to produce JavaScript object property names. | |
// Arguments: | |
// - header: string to normalize | |
// Examples: | |
// "First Name" -> "firstName" | |
// "Market Cap (millions) -> "marketCapMillions | |
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored" | |
function normalizeHeader(header) { | |
var key = ""; | |
var upperCase = false; | |
for (var i = 0; i < header.length; ++i) { | |
var letter = header[i]; | |
if (letter == " " && key.length > 0) { | |
upperCase = true; | |
continue; | |
} | |
//if (!isAlnum(letter)) { | |
// continue; | |
//} | |
if (key.length == 0 && isDigit(letter)) { | |
continue; // first character must be a letter | |
} | |
if (upperCase) { | |
upperCase = false; | |
key += letter.toUpperCase(); | |
} else { | |
key += letter.toLowerCase(); | |
} | |
} | |
return key; | |
} | |
// Returns true if the cell where cellData was read from is empty. | |
// Arguments: | |
// - cellData: string | |
function isCellEmpty(cellData) { | |
return typeof(cellData) == "string" && cellData == ""; | |
} | |
// Returns true if the character char is alphabetical, false otherwise. | |
function isAlnum(char) { | |
return char >= 'A' && char <= 'Z' || | |
char >= 'a' && char <= 'z' || | |
isDigit(char); | |
} | |
// Returns true if the character char is a digit, false otherwise. | |
function isDigit(char) { | |
return char >= '0' && char <= '9'; | |
} | |
// 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; | |
} | |
// next bit modified from Bruce Macpherson's | |
// https://sites.google.com/a/mcpher.com/share/Home/excelquirks/gassnips/dotsyntax | |
function objectSplitKeys (ob,obArray,keyArray) { | |
obArray = obArray || []; | |
//turns this {a:1,b:2,c:{d:3,e:{f:25}}} | |
// into this, so that the keys can be joined to make dot syntax | |
//[{key:[a], value:1},{key:[b], value:2} , {key:[c,d], value:3}, {key:[c,e,f], value:25}] | |
if (isObject(ob)) { | |
Object.keys(ob).forEach ( function (k) { | |
var ka = keyArray ? keyArray.slice(0) : []; | |
ka.push(k); | |
if(isObject(ob[k])) { | |
objectSplitKeys (ob[k],obArray,ka); | |
} | |
else { | |
obArray.push ( {key:ka, value:ob[k]} ); | |
} | |
}); | |
} | |
else { | |
obArray.push(ob); | |
} | |
return obArray; | |
} | |
function isObject (obj) { | |
return obj === Object(obj); | |
} | |
function objectDot(ob) { | |
return objectSplitKeys (ob).map ( function (o) { | |
return {key:o.key.join("_"), value:o.value}; | |
}); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment