Keeping Track Of Hashtags

Sometimes, you’ll want to keep track of a search topic on Twitter, maybe because you want to remember an interesting hashtag chat, or there’s an event on that you want to follow the tweets for, or maybe you just want to see who is talking about your blog. We’ve found a free and (reasonably!) easy way to do this, and thought we’d share it with you.

“Twitter Archiving Google Spreadsheet” or TAGS is a Google Docs spreadsheet with lots of macros, etc, built into it that will keep track of any Twitter search term you like. This would be really useful if you’re running a competition on your blog that requires people to tweet about it, or if you want to see who is talking about your blog. In this example, we’re searching for any tweet with “bonjourblogger” in, which will fetch all tweets that link to our blog. It’s a little bit of work to get this working, so we thought we’d go through step by step.

First, click the link in this blog post, and copy the spreadsheet to your Google Drive account. You’ll see the below screen. In cell B9, type in what you want to track.

tagspreadsheet1

Click the “Enable Custom Menu” button, and the following will appear.

tagspreadsheet2

Click OK, and a pop up will come up, asking you to give the spreadsheet permission to run macros. You need those for the spreadsheet to work, so if you’re happy with it, click OK.

tagspreadsheet3

This will put two new items in your taskbar – TAGS, and TAGS – Advanced. Next, you’ll need to head over to the Twitter “Create An Application” page to get a “consumer key” and “consumer secret” which is basically like a username and password for your Twitter account. You only need set up the application once on Twitter, but you’ll need the key and secret for each spreadsheet that you set up.

 
tagspreadsheet11

Once you’ve got the consumer key and consumer secret from Twitter, head back to your spreadsheet, and click the TAGS menu, and Twitter API Authentication. The following screen will pop up. Copy and paste the key and secret into the relevant sections, and click save.

tagspreadsheet6

Next, go to Tools > Script Editor.

tagspreadsheet4

Click Run > authenticateTwitter. You’ll have another pop up telling you “Authentication Required”, then you’ll be asked to log into Twitter to authorise the application for your account.

tagspreadsheet5

tagspreadsheet7That’s it! Just go back to your spreadsheet, and click TAGS > Run Now! and it will fetch all the tweets in the last week with your required search term. If you want the spreadsheet to keep updating, select the “Update archive every hour” option and it will keep updating your spreadsheet (the “Archive” tab) with new lines.

tagspreadsheet8

tagspreadsheet10

You’ll be able to see some statistics on the “Summary” and “Dashboard” tabs if you want them.

What would you track with this?

Hayley Constantine

Hayley has been blogging since before most people knew what a blog was! She started Bonjour, Blogger! in May 2013 as a way to share her knowledge and experiences - if you ever have a question, get in touch!

Leave a Reply

})(jQuery)