Share All Of The Content – Automation around sharing community blogs on Twitter

sharememeAttending an X Field Day event for me has been awesome – there are a ton of perks, you get to hear deep-dives directly from vendors, usually involving CTO/CEO/Founder type people.  You get to meet an amazing panel of fellow delegates and develop friendships.  But aside from all this there is one huge benefit that usually goes un-blogged; and that is you get to hear stories and experiences from Stephen Foskett himself – and he has a lot of them.  One in particularly caught my attention – as he explained all of the behind the scenes automation that occurs in terms of building the TFD webpages and sharing all this information out on his respective social media channels.  So, as soon as we, as a delegate click ‘Publish’ there is a ton of IFTTT recipes, Zapier functionality and custom scripts that takes our posts, tags relevant vendors/authors and kicks off the craziness that is Foskett’s brain in code.  It’s really quite amazing.  So amazing that I thought I might try my hand at my own.  Now I am by no means at the level that Mr. Foskett is at – but it’s a start – and here’s how it goes…

My Brain

So what I set out to accomplish was simple.  I wanted to be able to flag, Digg, or star (chose whatever terminology for the RSS reader of your choice) blog posts as I read them – ones that I thought were awesome – From there, the posts would be transformed into a tweet, mentioning the author and sent out on my twitter account at a later time, keeping in mind I would need to buffer these as I could be “digging” 20 posts at a time.

My Brain on Code

So here is how I accomplished that task of taking those random ideas from my brain and transforming them into code.  There is a few pre-reqs and different technologies used, so ensure you have these if you plan on duplicating this.

  • Twitter – yeah, you need a Twitter account.
  • Twitter App – you need to setup an app within that Twitter account – this is what will allow the rest of this craziness to actually connect into your account and send the tweet – we will go over the how to on this
  • Google Drive – The core scripting behind all of this is done in Google Script in behind a Google Spreadsheet – so, you will need a Google account.
  • Digg – I use this as my RSS reader so if you are following along step by step you might want to set this up.  If you use another, I’m sure you can figure out how to get your favorite posts from the reader into Delicious
  • Delicious – I implemented this as a middle man between my RSS reader and the Google Spreadsheet simply due to the fact that I may want to share out content that isn’t inside of my RSS reader.  I can easily add any content into Delicious.
  • IFTTT – You will also need an If This Then That account setup as we will be using recipes to move content from Digg into Delicious, and further more from Delicious into the Google Spreadsheet.  I use IFTTT for a ton of other “stuff” to make my life easier.  You should already have an account here setup Smile

So, the concept is as follows

  1. I “digg” a post within digg – IFTTT then takes that post and creates a Delicous bookmark with a specific tag “ShareOut”  I could also just use the Delicious chrome plug-in to tag any current site that I’m on as a bookmark as well.
  2. IFTTT then takes any new public bookmarks with the “ShareOut” tag that I have created and adds them into a Google Spreadsheet, containing the blog Title, URL, and a “0” indicating that I haven’t processed this yet.
  3. The spreadsheet contains a “CheckNew” trigger/function, which runs every x minutes/hours.  This takes any rows with a “0” (new ones from Delicious) and transforms them into a tweet, shortening the URL, ensuring we are under the 140 characters and adding the authors Twitter handle.  It then places this tweet into the next blank row on the “Tweets” sheet and updates the processed column to “1”
  4. The spreadsheet contains a “SendTweet” trigger/function, which runs once an hour – this simply takes the first tweet on the “Tweets” sheet and tweets it out, then deletes it from the spreadsheet, allowing the next Tweet to move up in the queue and be processed in the next hour, repeat, repeat, repeat.  Every hour until the sheet is empty.

So let’s set this stuff up.

First up we need the spreadsheet created within Google Drive – you can copy mine if you like as it already has a sheet which maps the top 100 vBloggers (and some more) blogs to Twitter (this is the only way I could figure out how to map Twitter handles to blogs).  Either way it will need to be setup the same if you create it new.

Next, setup the recipes in IFTTT to copy Diggs to Delicious and subsequently Delicious to Google Spreadsheet –  IFTTT is pretty easy to use so  I’ll leave  it up to you to figure out how to get the data to Delicious and the spreadsheet.  Just ensure if you are using “Add Row to Spreadsheet” as a that – that you populate all three columns in the spreadsheet in the following order (Title, URL, 0) – The “0” needs to added in order for the Google Script to function properly.  Let me know if you need a hand.

Now we need to setup an app to allow Google script to send the Tweets for us.  Log into your Twitter account and head to http://dev.twitter.com/apps/ .  Once there click the “Create New App” button in the top right hand corner.  Most of the information you put here doesn’t matter, with the exception of the Callback URL – this needs to be “https://script.google.com/macros/”

twitterapp

Once created, click on the Permissions tab and ensure that the access is set to “Read and Write”

permissions

Now we need to get the Consumer Key and Consumer Secret – This is on the “Keys and Access Tokens” tab and we will need to copy to use within our Google Script later, so shove it over to notepad or vi or something Smile

tokens

Now we are done with Twitter and it’s time to get into the good stuff!  Open up your Google Spreadsheet and make sure you have the following sheets within it.

  • Sheet1 – named exactly this and it will be a place holder for the information coming from IFTTT
  • BlogTwitter – This sheet contains all of the Blog->Twitter handle mappings.
  • Tweets – This sheet will be a place holder for our Tweets

Again, feel free to simply copy my spreadsheet – it may make it easier and already has the BlogTwitter sheet populated.

As far as just setting up the sheets with the above names in the same order there is nothing we really need to do on the Spreadsheet – it’s the code behind we really need.  To get here, select Tools->Script Editor.  When the dialog appears select “Blank Project” under “Create New Script For”.  If you copied my sheet you will simply be brought into an already existing blank project.

Before we can get started with the code there are a couple of things we need to do.  Since I use the Google URL shortening service you will need to enable this in the project resources.  This is done under Resources->Advanced Google Services”  Find the URL Shortener API and switch it to On.  You will also need to turn this service on within the Google Developers Console – the link to do so is right within that same dialog – go do that.

shortener enable

So, as far as the code goes I’m just going to dump it all right here so you can just copy/paste all of it – I’ll explain a few things about it underneath.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
function sendTweet(){
 
var TWITTER_CONSUMER_KEY = 'CONSUMERKEYHERE';
var TWITTER_CONSUMER_SECRET = 'CONSUMERSECRETHERE';
var oauth = false;
 
function authTwitter(){
 
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(TWITTER_CONSUMER_KEY);
oauthConfig.setConsumerSecret(TWITTER_CONSUMER_SECRET);
};
 
var requestData = {
'method': 'POST',
'oAuthServiceName': 'twitter',
'oAuthUseToken': 'always'
};
 
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheets()[2]);
 
var sheet = ss.getSheetByName("Tweets");
var tweet = sheet.getActiveCell().getValue();
var encodedTweet = encodeURIComponent(tweet);
 
if (tweet!='') {
 
if (!oauth) {
authTwitter();
oauth = true;
};
 
UrlFetchApp.fetch('https://api.twitter.com/1.1/statuses/update.json?status=' + encodedTweet, requestData);
 
sheet.deleteRow(1);
 
}
 
};
 
function checkNew()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var iftsheet = ss.getSheetByName("Sheet1");
var values = iftsheet.getDataRange().getValues();
for(var i=0, iLen=values.length; i<iLen; i++) {
if(values[i][2] != "1") {
 
// get Twitter Hangle
var urlPath = values[i][1].split("/");
var baseURL = urlPath[2];
var twitterHandle = findTwitterHandle(baseURL);
 
// get other data
var postTitle = values[i][0];
var postURLLong = values[i][1];
 
var URLShort = getShortenedUrl(postURLLong);
// build tweet string
var myTweet = buildTweet(postTitle, URLShort, twitterHandle);
 
// place variable in next available row on Tweets spreadsheet
var targetSheet = ss.getSheetByName("Tweets");
var lastRow = targetSheet.getLastRow() + 1;
var targetCol = "A" + lastRow.toString();
targetSheet.getRange(targetCol).setValue(myTweet);
values[i][2] = "1"
}
} 
iftsheet.getDataRange().setValues(values);
}
 
function getShortenedUrl(url){
 
var longUrl = UrlShortener.newUrl();
longUrl.setLongUrl(url);
 
var shortUrl = UrlShortener.Url.insert(longUrl);
 
return shortUrl.getId();
}
 
 
function buildTweet(postTitle, postURL, twitterHandle)
{
var tweet = "[Shared] " + postTitle + " - " + postURL;
if (typeof twitterHandle != "undefined")
{
tweet += " via " + twitterHandle;
}
 
var tweetlength = tweet.length;
 
if (tweetlength > 140)
{
var charsToTrim = tweetlength - 135;
 
postTitle = postTitle.substr(0, postTitle.length-charsToTrim);
tweet = "[Shared] " + postTitle + " - " + postURL;
if (typeof twitterHandle != "undefined")
{
tweet += " via " + twitterHandle;
}
}
return tweet;
}
 
function findTwitterHandle(blogurl) {
var twitterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BlogTwitter");
var values = twitterSheet.getDataRange().getValues();
 
for(var i=0, iLen=values.length; i<iLen; i++) {
if(values[i][0] == blogurl) {
return values[i][1];
}
} 
 
}

Copy and paste all of the above code into the blank code.gs file that was created.  First up, remember that consumer key and secret from Twitter – yeah, they will need to go in their respective spots in lines 3/4.  That is really all of the edits to code you need to make.  If you look at the script there are a few different functions.  checkNew() (Line 45) – this is what takes the data from IFTTT on Sheet1 and transforms it into tweet-format, then places it on the “Tweets” sheet.  You can see it calls out some other functions which shorten the URL and ensure the tweet is under 140 characters, as well as flag a 0 to 1 on Sheet1 (ensures that we don’t’ tweet the same thing twice).   The sendTweet() function (Line 1) – this takes whatever is in A1 on the Tweets sheet and, you guessed it, tweets it.  When its done, row 1 is deleted, allowing the next tweet to move into A1 and be processed next time the function runs,

To test, put some data in the first sheet, Column A – the blog post title, Column B, the blog post URL, and a “0” in column C.  You can do this manually for now if you want or if you have the IFTTT recipes setup let them take charge.

Then, at the top of the script editor change the function drop-down to “checkNew” and click “Run”.  If you don’t see the functions listed within the drop-down you may need to “File-Save” and completely close out both the script editor and the spreadsheet.

test1

Once this has complete its’ run you should be able to flip back to the spreadsheet and see a Tweet sitting inside A1 of the “Tweets” sheet.  The data from “Sheet1” should also be updated with 1 flag.

From here it’s a matter of flipping back to the Script Editor and running the “sendTweet” function the same way you did the “checkNew”.  You will most likely be prompted to authorize Google as well as authorize Twitter during this process.  Go ahead and do that.  Sometimes I have found that the first time you run it you will need to authorize Google, then you will need to run it again to authorize Twitter.  Once both applications are authorized your tweet should have been sent out!

So this is all great, everything is working.  Now to setup triggers for these functions as running them manually doesn’t make much sense.  To get into your triggers select Resources->All your triggers from the script editor.

triggers

As you can see I have set up two.  One that runs my checkNew function every 30 minutes – if it finds a newly inserted row from IFTTT it will build the tweet for me.  The other, sendTweet runs once an hour – this will simply take one of the tweets and send it out!  This way, if there are tweets available it will tweet just one per hour, so I don’t flood everyone with tweets!

And that’s that!  I know this was a long winded post but I wanted to convey what I was trying to accomplish and how I did it!  Hopefully some of you find it useful if you are looking to deploy a similar type solution.  There is a ton of great content out there and this is just my part on helping to spread the good word within the virtualization community.

If you are trying to get through this and need a little help don’t hesitate to reach out – I’m more than willing to help!  If you have any ideas on how to better this process I”d love to hear them as well.  The part currently nagging at me is that manual BlogTwitter sheet – not sure how this part could be automated or bettered but if you have ideas let me know…  Thanks for reading!  And sharing Smile

  • Sheryl Koenigsberg

    Clever solution. You kinda rewrote BufferApp.

    I save in Feedly for my own benefit, then use the Chrome plug-in for Buffer to queue up tweets. I like it because I can immediately add a comment or change the title of the tweet – although your auto-lookup of the twitter handle of the blog author is pretty sweet.