Skip to content

Instantly share code, notes, and snippets.

@mhawksey
Last active May 3, 2016 06:28
Show Gist options
  • Save mhawksey/7770536 to your computer and use it in GitHub Desktop.
Save mhawksey/7770536 to your computer and use it in GitHub Desktop.
Google Apps Script code used in 'Keep your Twitter Archive fresh on Google Drive' http://mashe.hawksey.info/2013/01/sync-twitter-archive-with-google-drive/. To fix getContentAsString error open Tools > Script editor in your copy and replace the code with version below and test run the function updateArchive()
/*
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.
*/
/**
* Authorize against Twitter. This method must be run prior to
* clicking any link in a script email. If you click a link in an
* email, you will get a message stating:
* "Authorization is required to perform that action."
*/
function authorize() {
if (!isConfigured()){
Browser.msgBox("Twitter API not configured");
return;
}
try {
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": "GET",
"oAuthServiceName": "twitter",
"oAuthUseToken": "always"
};
var result = UrlFetchApp.fetch("https://api.twitter.com/1.1/account/verify_credentials.json", requestData);
if (result.getResponseCode() == 200){
var resp = Utilities.jsonParse(result.getContentText());
if (resp.screen_name != ""){
ScriptProperties.setProperty("authenticated", "true");
}
}
} catch (e) {
Browser.msgBox(e.message);
}
}
/**
* function to update data files used in Twitter interface
*/
function updateArchive(){
if (!isConfigured()){
Browser.msgBox("Twitter API not configured");
return;
}
if (!isAuthenticated()){
Browser.msgBox("Twitter access has not been authenticated");
return;
}
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName("Log");
var js = getJSFolder(getArchiveFolder()); // get the data/js folder
var extraTweets = 0; // payload includes overall count which we'll update
var result = "";
if (js){
// search the js folder for user/payload details and index file
// (as they are javascript we can fetch and eval)
// ADDED fix for find title issue found by @cryptecks http://mashe.hawksey.info/2013/01/sync-twitter-archive-with-google-drive/comment-page-2/#comment-158100
var user_details_file = js.getFilesByName("user_details.js").next();
eval(user_details_file.getBlob().getDataAsString());
var payload_details_file = js.getFilesByName("payload_details.js").next();
eval(payload_details_file.getBlob().getDataAsString());
var tweet_index_file = js.getFilesByName("tweet_index.js").next();
eval(tweet_index_file.getBlob().getDataAsString());
// get deatils of last file in archive
var lastFileMeta = tweet_index[0];
var file_name = lastFileMeta.file_name.replace("data/js/tweets/","");
var var_name = lastFileMeta.var_name;
// prepare Grailbird which will hold last set of tweets archived
var Grailbird = {};
Grailbird.data = {};
Grailbird.data[var_name] = [];
// navigate to data/js/tweets/ and eval last archive file
var tweetsFolder = getSubFolder(js, "tweets");
var tweet_file = tweetsFolder.getFilesByName(file_name).next();
eval(tweet_file.getBlob().getDataAsString());
// get id ofthe last tweet in archive
var sinceid = Grailbird.data[var_name][0]['id_str'];
// based on this get new data from Twitter API
var newData = getNewStatusUpdates(sinceid, user_details.screen_name);
// new data is returned in bins for each monthnewest first, we want to process oldest 1st so flip the keys
var keys = [];
for (var k in newData) {
keys.unshift(k);
}
// for each date bin we need to either add to existing file or if new months add new data files
for (var c = keys.length, n = 0; n < c; n++) {
var i = keys[n];
if (i == var_name){ // handling existing months
Grailbird.data[var_name] = newData[i].concat(Grailbird.data[var_name]); // add new data
tweet_file.setContent("Grailbird.data."+var_name+" = \n"+ JSON.stringify(Grailbird.data[var_name], null, '\t')); // replace old file
extraTweets += newData[i].length; // running total of inserts
tweet_index[0].tweet_count = Grailbird.data[var_name].length; // update tweet_index count for month
} else { // creating files for new months
var new_tweet_filename = i.toString().replace("tweets_","")+".js"; // recycle date bin name for filename maintaining tweets_yyyy_dd.js convention
var new_tweet_file = tweet_file.makeCopy(new_tweet_filename, tweetsFolder); // make a copy of an exsting file (could have blobbed a new file and set mime)
new_tweet_file.setContent("Grailbird.data."+i+" = \n"+ JSON.stringify(newData[i], null, '\t')); // replace content with new data
extraTweets += newData[i].length; // running total of inserts
var new_tweet_index_meta = {}; // build a new tweet_index record
var tweetdate = new Date(newData[i][0].created_at);
new_tweet_index_meta = { file_name: "data/js/tweets/"+new_tweet_filename,
year: tweetdate.getYear(),
var_name: i,
tweet_count: newData[i].length,
month: +Utilities.formatDate(tweetdate, "GMT", "MM") };
tweet_index.unshift(new_tweet_index_meta); // insert at the beginning
}
}
tweet_index_file.setContent("var tweet_index = "+JSON.stringify(tweet_index, null, '\t')); // rewrite the tweet_index
payload_details.tweets = payload_details.tweets + extraTweets; // update payload meta
payload_details.created_at = Utilities.formatDate(new Date(), "GMT", "E MMM dd HH:mm:ss Z yyyy"); // new date stamp
payload_details_file.setContent("var payload_details = "+JSON.stringify(payload_details, null, '\t')); // update payload file
result = extraTweets;
doc.toast("Added "+extraTweets+" tweets to your archive");
} else {
result = "Couldn't find JS folder";
}
sheet.insertRowAfter(1);
sheet.getRange(2, 1, 1, 2).setValues([[new Date(), extraTweets]]);
}
/**
* Get new status updates from Twitter
* @param {String} sinceid of last tweet in archive.
* @param {String} screen_name of archived account.
* @return {Object} json object of Twitter updates binned in month objects yyyy_mm.
*/
function getNewStatusUpdates(sinceid, screen_name){
// some parameters used in the query
var params = { screen_name: screen_name,
count: 200,
since_id: sinceid,
include_rts: true };
var api_request = buildUrl("statuses/user_timeline.json", params); // make url
// some variables
var page = 1;
var done = false;
var output = {};
var max_id = "";
var max_id_url = "";
while(!done){
var data = tw_request("GET", api_request+max_id_url); // get the data from twitter
if (data.length>0){ // if data returned
if (data.length == 1 && data[0].id_str == max_id){ // catching if we've reached last new tweet
done = true;
break;
}
for (i in data){ // for the data returned we put in montly bins ready for writting/updating files
if(data[i].id_str != max_id){
var timestamp = new Date(data[i].created_at);
var bin = "tweets_"+Utilities.formatDate(timestamp, "GMT", "yyyy_MM");
if (output[bin] == undefined) output[bin] = []; // if bin hasn't been used yet make it
output[bin].push(data[i]); //push data to date bin
}
}
if (data[data.length-1].id_str != max_id) { // more bad code trying to work out if next call with a max_id
max_id = data[data.length-1].id_str;
max_id_url = "&max_id="+max_id;
}
} else { // if not data break the loop
done = true;
}
page ++
if (page > 16) done = true; // if collected 16 pages (the max) break the loop
}
return output;
}
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: "Set Archive Path", functionName: "renderSetArchivePath"});
menuEntries.push({name: "API Authentication", functionName: "renderAPIConfigurationDialog"});
menuEntries.push(null); // line separator
menuEntries.push({name: "Update Archive Now", functionName: "updateArchive"});
menuEntries.push({name: getTriggerStatus(), functionName: "toggleTrigger"});
ss.addMenu("Sync Twitter Setup", menuEntries);
}
/**
* 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) + "=" + encodeURIComponent(value) + "&";
}
if (qs.length > 0){
qs = qs.substring(0, qs.length-1); //chop off last "&"
url = url + "?" + qs;
}
return url;
}
/**
* Toggles the script trigger to refresh archive
*/
function toggleTrigger(){
var test = getTriggerID();
if (getTriggerID() == "none" || getTriggerID() == null){ // add trigger
var dailyTrigger = ScriptApp.newTrigger("updateArchive")
.timeBased()
.everyDays(1)
.atHour(0)
.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 "Turn Auto-Refresh On";
return "Turn Auto-Refresh Off"
}
/**
* @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");
}
/**
* oAuth request to Twitter API.
* @param {String} method get, post, put.
* @param {String} api request to twitter.
* @return {Object} json response from twitter.
*/
function tw_request(method, api_request){
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": method,
"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());
}
} catch (e) {
Browser.msgBox(e.message);
return false;
}
}
/**
* Get the subfolder with passed name.
* @param {String} get the js folder with path data/js/.
* @return {Object} folder.
*/
function getJSFolder(folderID){
try {
var folder = DriveApp.getFolderById(folderID);
var data = getSubFolder(folder, "data");
return getSubFolder(data, "js");
} catch (e){
Browser.msgBox(e.message);
return false;
}
}
/**
* Get the subfolder with passed name.
* @param {Object} folder.
* @param {String} sub folder name to return.
* @return {Object} folder.
*/
function getSubFolder(folder, name){
var sub = folder.getFolders();
while (sub.hasNext()) {
var folder = sub.next();
if (folder.getName() == name)
return folder;
}
return false;
}
/**
* Key of ScriptProperty for Twitter consumer key.
* @type {String}
* @const
*/
var CONSUMER_KEY_PROPERTY_NAME = "twitterConsumerKey";
/**
* Key of ScriptProperty for Twitter consumer secret.
* @type {String}
* @const
*/
var CONSUMER_SECRET_PROPERTY_NAME = "twitterConsumerSecret";
/**
* Key of ScriptProperty for Twitter consumer secret.
* @type {String}
* @const
*/
var ARCHIVE_FOLDER_PROPERTY_NAME = "twitterFolderID";
/**
* @return {string} OAuth consumer key to use when tweeting.
*/
function getConsumerKey() {
var key = ScriptProperties.getProperty(CONSUMER_KEY_PROPERTY_NAME);
if (key == null) {
key = "";
}
return key;
}
/**
* @param {String} OAuth consumer key to use when tweeting.
*/
function setConsumerKey(key) {
ScriptProperties.setProperty(CONSUMER_KEY_PROPERTY_NAME, key.trim());
}
/**
* @return {string} OAuth consumer secret to use when tweeting.
*/
function getConsumerSecret() {
var secret = ScriptProperties.getProperty(CONSUMER_SECRET_PROPERTY_NAME);
if (secret == null) {
secret = "";
}
return secret;
}
/**
* @param {string} OAuth consumer secret to use when tweeting.
*/
function setConsumerSecret(secret) {
ScriptProperties.setProperty(CONSUMER_SECRET_PROPERTY_NAME, secret.trim());
}
/**
* @return {string} Folder ID for archive location.
*/
function getArchiveFolder() {
var folderID = ScriptProperties.getProperty(ARCHIVE_FOLDER_PROPERTY_NAME);
if (folderID == null) {
folderID = "";
}
return folderID;
}
/**
* @param {string} Set Folder ID for archive location.
*/
function setArchiveFolder(id) {
ScriptProperties.setProperty(ARCHIVE_FOLDER_PROPERTY_NAME, id.trim());
if (id != ""){
setArchiveLocationCell("https://googledrive.com/host/"+id);
} else {
setArchiveLocationCell("Folder ID not entered yet");
}
}
/**
* @return {boolean} if authenticated.
*/
function isAuthenticated() {
var auth = ScriptProperties.getProperty("authenticated");
if (auth == "true") {
return true;
}
return false;
}
function setArchiveLocationCell(text){
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName("Readme");
sheet.getRange("A14").setValue(text);
}
/**
* Twitter archive path config
*
*/
function renderSetArchivePath() {
// modified from Twitter Approval Manager
// http://code.google.com/googleapps/appsscript/articles/twitter_tutorial.html
var doc = SpreadsheetApp.getActiveSpreadsheet();
var app = UiApp.createApplication().setTitle(
"Twitter Archive Path Configuration").setHeight(250).setWidth(420);
app.setStyleAttribute("padding", "10px");
//var dialog = app.loadComponent("GUIComponent");
var dialogPanel = app.createFlowPanel().setWidth("400px");
var label1 = app.createLabel("1. Extract and upload the .zip archive to a folder in Google Drive").setStyleAttribute("paddingBottom", "10px");
var label2 = app.createLabel("2. Open the folder in your web browser and copy the folder id from the url in the address bar (see setup video for help)");
//("<strong>hello</strong><ul><li>one</li></ul>");
dialogPanel.add(label1);
dialogPanel.add(label2);
var archivePathLabel = app.createLabel("Folder ID of Twitter Archive:");
var archivePathID = app.createTextBox();
archivePathID.setName("archivePathID");
archivePathID.setWidth("90%");
archivePathID.setText(getArchiveFolder());
var saveHandler = app.createServerClickHandler("saveFolderID");
var saveButton = app.createButton("Save Configuration", saveHandler);
var listPanel = app.createGrid(1, 2);
listPanel.setStyleAttribute("margin-top", "10px")
listPanel.setWidth("100%");
listPanel.setWidget(0, 0, archivePathLabel);
listPanel.setWidget(0, 1, archivePathID);
// Ensure that all form fields get sent along to the handler
saveHandler.addCallbackElement(listPanel);
dialogPanel.add(listPanel);
dialogPanel.add(saveButton);
app.add(dialogPanel);
doc.show(app);
}
/**
* Twitter configuration window
*
*/
function renderAPIConfigurationDialog() {
// modified from Twitter Approval Manager
// http://code.google.com/googleapps/appsscript/articles/twitter_tutorial.html
var doc = SpreadsheetApp.getActiveSpreadsheet();
var app = UiApp.createApplication().setTitle(
"Twitter API Authentication Configuration").setHeight(400).setWidth(420);
app.setStyleAttribute("padding", "10px");
//var dialog = app.loadComponent("GUIComponent");
var dialogPanel = app.createFlowPanel().setWidth("400px");
var label1 = app.createLabel("1. Register for an API key with Twitter at http://dev.twitter.com/apps/new (if you've already registered a Google Spreadsheet/Twitter mashup you can reuse your existing Consumer Key/Consumer Secret). In the form these are the important bits: ").setStyleAttribute("paddingBottom", "10px");
var label2 = app.createLabel(" - Application Website = anything you like").setStyleAttribute("textIndent", "30px");
var label3 = app.createLabel(" - Application Type = Browser").setStyleAttribute("textIndent", "30px");
var label4 = app.createLabel(" - Callback URL = https://spreadsheets.google.com/macros").setStyleAttribute("textIndent", "30px");
var label5 = app.createLabel(" - Default Access type = Read-only ").setStyleAttribute("textIndent", "30px").setStyleAttribute("paddingBottom", "10px");
var label6 = app.createLabel("2. Once finished filling in the form and accepting Twitter's terms and conditions you'll see a summary page which includes a Consumer Key and Consumer Secret which you need to enter below").setStyleAttribute("paddingBottom", "10px");
var label7 = app.createLabel("3. When your Key and Secret are saved you need to open Tools > Script Editor ... and run the 'authorize' function").setStyleAttribute("paddingBottom", "10px");
//("<strong>hello</strong><ul><li>one</li></ul>");
dialogPanel.add(label1);
dialogPanel.add(label2);
dialogPanel.add(label3);
dialogPanel.add(label4);
dialogPanel.add(label5);
dialogPanel.add(label6);
dialogPanel.add(label7);
var consumerKeyLabel = app.createLabel("Twitter OAuth Consumer Key:");
var consumerKey = app.createTextBox();
consumerKey.setName("consumerKey");
consumerKey.setWidth("90%");
consumerKey.setText(getConsumerKey());
var consumerSecretLabel = app.createLabel("Twitter OAuth Consumer Secret:");
var consumerSecret = app.createTextBox();
consumerSecret.setName("consumerSecret");
consumerSecret.setWidth("90%");
consumerSecret.setText(getConsumerSecret());
var saveHandler = app.createServerClickHandler("saveConfiguration");
var saveButton = app.createButton("Save Configuration", saveHandler);
var listPanel = app.createGrid(2, 2);
listPanel.setStyleAttribute("margin-top", "10px")
listPanel.setWidth("100%");
listPanel.setWidget(0, 0, consumerKeyLabel);
listPanel.setWidget(0, 1, consumerKey);
listPanel.setWidget(1, 0, consumerSecretLabel);
listPanel.setWidget(1, 1, consumerSecret);
// Ensure that all form fields get sent along to the handler
saveHandler.addCallbackElement(listPanel);
//var dialogPanel = app.createFlowPanel();
//dialogPanel.add(helpLabel);
dialogPanel.add(listPanel);
dialogPanel.add(saveButton);
app.add(dialogPanel);
doc.show(app);
}
/**
* @return {boolean} True if all of the configuration properties are set,
* false if otherwise.
*/
function isConfigured() {
return getConsumerKey() != "" && getConsumerSecret() != "" ;
}
/** Retrieve config params from the UI and store them. */
function saveFolderID(e) {
setArchiveFolder(e.parameter.archivePathID);
var app = UiApp.getActiveApplication();
app.close();
return app;
}
/** Retrieve config params from the UI and store them. */
function saveConfiguration(e) {
setConsumerKey(e.parameter.consumerKey);
setConsumerSecret(e.parameter.consumerSecret);
var app = UiApp.getActiveApplication();
app.close();
return app;
}
@drikkes
Copy link

drikkes commented Jan 24, 2014

Ah, found it in the index of the tweets-folder. But how to change it? There's no script editor? Do I have to download the file, change it, and upload it again? Sorry for bothering you, but I'm a real coding idiot and don't want to break anything.

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