TAGS gets a timeline widget to display your latest archived tweets (replacement for the Twitter search timeline widget)

This is a cross post from my personal blog

On 23 March 2018 Twitter announced that it was retiring search timeline widgets, suggesting people moved to a Curate a Collection of Tweets. For a lot of people, myself included, this is far from an ideal solution. Given the number of hashtag communities I’m part of I’d much prefer to set something up and let it run in the background.

For TAGS users the good news is I’ve created a custom widget to display the last 10 results from a TAGS Archive. This means you can display a search timeline which will automatically display new results when these are collected in TAGS. If you are reading this from my site rather than via a RSS reader an example is embedded below:

Creating your own Twitter search timeline widget

To create your own widget either setup a TAGS archive or use an existing one. When the Google Sheet is shared so that ‘anyone with a link can view’ use the TAGS Widget Setup Page to configure the widget appearance before grabbing the embed code:

TAGS Widget Setup

Note: Given TAGS defaults to updating every hour the widget content currently only updates when the page is refreshed.

Under the hood

The widget itself runs off a basic HTML/JavaScript page. Twitter provide a JavaScript API that is able to scan a page for blockquote.twitter-tweet elements and turn them into fully-rendered embedded Tweets. To get the blockquote.twitter-tweet elements into a page I’m using the Google Visualisation API to query a Google Sheet and pull content into a Table Chart. There are actually a number of libraries that can read a Google Sheet into a webpage. The reason I went with this particular combination is there is a handy PatternFormat method that makes it easy to combine values from different columns required to create a blockquote.twitter-tweet the Twitter JavaScript API recognises:

   // format the text column into a format that Twitter will convert into a tweet
   var formatter = new google.visualization.PatternFormat('');
   formatter.format(data, [c['from_user'], c['text'], c['time'], c['id_str']], c['text']); // Apply formatter and set the formatted value of the first column.

The Google Visualisation API also has an addListener() method which allows us to wait until the table is ready before asking the Twitter widget to render each tweet:

google.visualization.events.addListener(table, 'ready', function () {

The last trick is to tweak the css of the embedded tweet to reduce the font size. To do this we need to jump into the Shadow DOM the Twitter widget JavaScript generates and add some custom styling:

      twttr.events.bind('rendered', function (event) {
           var hosts = document.getElementsByTagName('twitterwidget');
           var hostList = [].slice.call(hosts);
               var style = document.createElement('style');
               style.innerHTML = '.SandboxRoot.env-bp-min, .SandboxRoot.env-bp-min .TweetAction-stat, .SandboxRoot.env-bp-min .TweetAuthor-screenName, .SandboxRoot.env-bp-min .Tweet-alert, .SandboxRoot.env-bp-min .Tweet-authorScreenName, .SandboxRoot.env-bp-min .Tweet-card, .SandboxRoot.env-bp-min .Tweet-inReplyTo, .SandboxRoot.env-bp-min .Tweet-metadata { font-size: 0.9em; }';
           // when tweets have rendered remove loader

If you are interested in exploring the source code I’ve posted it on Github and created a TAGS Widget forum to capture questions and discussion. Enjoy!

Social Media Analytics: Using Data to Understand Public Conversations (course feat. TAGS) #FLsocmed

This is a cross post from my main blog


Queensland University of Technology have just started a free FutureLearn course on ‘Social Media Analytics: Using Data to Understand Public Conversations’. The course is described as:

From the personal to the political, social media conversations are at the heart of cultural and social change. In this course you will be introduced to digital methods to analyse social media data on Twitter. You will learn from leading researchers, engage in hands-on activities and confront issues in social media data.

The course is broken into 3 weeks, each week address each of these in turn:

  • week 1 – understanding and gathering
  • week 2 – analysing
  • week 3 – visualising 

The tools used in each week are TAGS, Tableau and Gephi. Needless to say I’m thrilled that TAGS is being promoted in this way, I’m also pleased with the way it’s been pitched:

We uncover the anatomy of a tweet and the cultural politics of Twitter. You will decide what you want to analyse: a company hashtag? A campaign hashtag? A cultural reference? You will learn to use TAGS, a free and open framework for accessing the data you need, and discuss the implications of accessing these data. What are the restrictions? What about user privacy and ethics?

The course is using #FLsocmed as a tag and it would be rude for me not to have a look at the conversation.

I’ve enrolled on the course myself and hopefully you can join me

TAGS Tricks: Making a searchable location map of your top Twitter contributors with Geocode by Awesome Table

This is a repost from my main blog.

In this post I’ll show you how to make a searchable Twitter contributor map like the one below in a couple of clicks, however I feel it’s my duty to educate you first…

Click here for interactive version

I’m not sure everyone is aware just how much metadata is available to developers in a tweet. Below is the data (JSON) you get back from a single tweet from the Twitter Search API.

Data in a single tweet

Not all of this is particularly useful, including things like profile colour preferences (there’s a research project for you ;), but as well as the tweet text you get information back about the tweet (like who it is in response to), and the user (like their profile description). In TAGS (myTwitter Archiving Google Sheets project) you get a default set of columns which collect the metadata associated with each tweet. Most of these are there to work with the TAGSExplorer visualisation but  a lot of people don’t know you can add more column names listed here to your Archive sheet to start collecting other data. A question I often get asked is when the geo_location column is added why is the data is often blank.  The answer is that the majority of people don’t geotag their tweets so there is no data to include (<1.0%). Don’t be allude however that by not geo-tagging your tweet that you are not giving away your location. A 2013 study revealed that approximately 20% of tweets could be reveal the user’s location down to street level (Weidemann & Swift, 2013). One of the authors of that work,  Chris Weidemann, has created geosocialfootprint.com “an educational demonstration of how public Twitter.com data can be harvested and analyzed to uncover location intelligence”  to illustrate the point.

One way a location can be revealed is from the Twitter profile location field. This is an optional text field within your Twitter settings which can be collected in a TAGS sheet by adding a column with user_location.


Converting a text description of a location to co-ordinates isn’t that hard and you’ve probably experienced geocoding when using services like Google Maps. Google Maps also has a geocoder service developers can use to get co-ordinates for location descriptions. In Google Sheets there are a couple of ways you can convert location descriptions into co-ordinates.  One way is Google Apps Script which hooks you straight into the Google Maps geocoder, however in this post I’ll save you the code and introduce you to Geocode by Awesome Table. This is a Google Sheets Add-on, a piece of Google Sheets customisation produced by third party developers coded using Google Apps Script. There are actually a couple of free geocoders in the Add-on store so you might want to check those out as well. For Geocode by Awesome Table to work we need to get our data in the right shape and the steps below will help you do that:

Setting up a TAGS Archive for geocoding

  1. First you need some tweets to geocode which you can do by getting a copy of TAGS – you can use either version 6.0 or 6.1 (existing users might want to stick with 6.0, while new users will find the setup easier in 6.1)
  2. In your copy of TAGS make sure there is a user_location column at Column P the Archive sheet (in TAGS v6.1 user_locationis already included)
  3. Enter your search term in the Readme/Settings sheet and from the drop down menu select TAGS > Run Now!
  4. Open this ‘Location’ template and from the sheet tab at the bottom select ‘Copy to…’ and select your new TAGS archive as the destination
    Copy to..

At this point you ‘Copy of Location’ sheet is populated with the screen names that have tweeted the most in your archive and also have a user_location entry:

Example of Copy of Location sheet

Unleashing the awesome with Geocode by Awesome Table

  1. In your Google Sheet select Add-ons > Get add-ons… and search for ‘geocoder’ clicking the ‘Free’ button for Geocode by Awesome Table
    Search for geocoder in Add-ons
  2. You should be prompted with a permissions window and if you are happy click on ‘Allow’
  3. Give it a couple of seconds to let the add-on install and then you can click Add-ons > Geocode by Awesome Table > Start Geocoding
    Add-ons > Geocode by Awesome Table > Start Geocoding
  4. A sidebar should popup which should let you pick your sheet and the user_locationcolumn
  5. Hit ‘Geocode’ at the bottom of the sidebar and watch has the latitude and longitude columns start to fill.
  6. When finished click ‘Create Map’

At this point you can open the map you’ve created which should include your geocoded points. If you’d like to change the popup when you click on the map makers go back to your sheet and from the sidebar click the ‘Customize Tooltip’ button. I use the ‘Edit as HTML’ option with the following markup:

<img src="${"profile_image_url"}" style="float:right;margin-left:10px">
<b>Screen name</b>: <a href="https://twitter.com/${" from_user"}"="">${"from_user"}</a><br>
<b>Location</b>: ${"user_location"}<br>
<b>Tweets</b>: ${"tweets."}<br>
<b>Followers</b>: ${"followers"}<br>

At this point your Awesome Table map is only visible to you and anyone else you have shared your Google Sheet with. To make public you need to share your Google Sheet so that ‘anyone can view’ from the share button.


A couple of things to keep in mind:

  • A couple of things to be aware of. If you set your TAGS sheet you automatically update every hour your map will start putting pointers in the wrong place (this is because the Locations sheet is dynamically populated). You can do a manual update using TAGS > Run Now! followed by steps 3-5 above.
  • Geocoding is also not 100% accurate and co-ordinates returned are the most likely hit. The geocoder also occasionally misses obvious hits … what can I say.
  • Geocode by Awesome Table will also only geocode up to 100 places at a time. I’ll let you figure ways around this.
  • This is only locating the profile location not where the tweet was actually sent


So there you go … I know where you live(ish). And yes companies are using this technique but on a much bigger scale all of the time … Don’t have nightmares, do sleep well

Weidemann, C., & Swift, J. (2013). Social media location intelligence: The next privacy battle-an arcgis add-in and analysis of geospatial data collected from twitter. com. International Journal of Geoinformatics, 9(2).

TAGS Tricks: Find your website’s top Twitter influencers by combining TAGS with Google Analytics referral data

This is a repost from my main blog

This is actually a revisit of a post I made in 2012 on ‘Using Google Spreadsheets to combine Twitter and Google Analytics data to find your top content distributors’. This idea is worth a revisit as things have got easier to mash Google Analytics data in Google Sheets and my Twitter Archive Google Sheets (TAGS) project has got easier for users to setup. The basic concept is that if you are using Google Analytics to track your website usage you can see the volume of traffic from particular sources including Twitter as a referral source. As referral links from Twitter are unique we can identify who originally tweeted the link to your site and measure its impact (number of visits, purchase, etc.). The actual mechanic of this are outlined in the ‘Referral Traffic’ section of the original post if you’d like more explanation so instead I’m going to jump straight into the new method.

Setting up a TAGS Archive for Google Analytics referral analysis

Before going into the setup I should say that this solution requires that the Google Account you use to setup TAGS also has at least view access to the Google Analytics account you use for your website. With this in mind lets begin:

  1. First you need some tweets to use with our Google Analytics data which you can do by getting a copy of TAGS – you can use either version 6.0 or 6.1
  2. In the Readme/Settings in the search term enter the website domain you wish to track (for my website I use hawksey.info  – the Twitter Search API isn’t 100% reliable but surprisingly good at resolving urls e.g. you’ll spot a couple of shortened urls in this search for my site)
  3. From the drop down menu select TAGS > Run Now! (at this point you might want to also use TAGS > ‘Update archive every hour’ to start a continuous collection of data)

Getting all GA with the Google Analytics Add-on

The Google Analytics Add-on is a Google Sheets customisation produced by the Google Analytics team to easily get GA data in Google Sheets. Add-ons are not limited to just customisation written by Google and they can be created by any third party developers coded using Google Apps Script. There are actually a couple of analytics add-ons in the store so you might want to check those out as well. For this solution I’m going to focus on using the official Google Analytics add-on.  The steps below guide you through setting it up:

  1. In your Google Sheet select Add-ons > Get add-ons… and search for ‘analytics’ clicking the ‘Free’ button for Google Analytics
    Add-ons > Get add-ons… and search for ‘analytics’
  2. You should be prompted with a permissions window and if you are happy click on ‘Allow’
  3. Give it a couple of seconds to let the add-on install and then you can click Add-ons > Google Analytics > Create new report
  4. This should open a sidebar where you can give the report a name ‘Twitter Referral’  and select your GA account, property and view
    give the report a name ‘Twitter Referral’  and select your GA account, property and view
  5. At this point you can skip Metrics and Dimensions and click ‘Create Report’
  6. To help you get the Metrics and Dimensions used in the rest of this example open this existing report configuration sheet and copy/paste the values from B8:B15 into your own ‘Report Configuration’ sheet in your TAGS Sheet.
  7. In your TAGS Sheet you can now use the drop down menus to select Add-ons > Google Analytics > Run reports which should pull back some Twitter Referral data into a new ‘Twitter Referral’ sheet similar to the screenshot below
    Twitter Referral data
  8. To finally combine our Google Analytics data to archived tweets open this existing ‘TopTweeters’  and from the sheet tab at the bottom select ‘Copy to…’ and select your new TAGS Sheet as the destination
    select ‘Copy to…’ and select your new TAGS Sheet

This should give you an example sheet with the top 10 tweets that have generated the most new visits in the last 7 days. I’m not saying this a perfect example of displaying the data but hopefully it gives you some ideas:

example sheet with the top 10 tweets that have generated the most new visits in the last 7 days

One final thing you might want to do is select Add-ons > Google Analytics > Schedule reports so that your GA data will automatically be refreshed in this sheet.


So there you go linking your Google Analytics data to individual tweets to monitor impact. There is so much more you can do with this basic idea and it would be interesting to start incorporating ecommerce data, hey you might even want to reward people generating income for you. If you’ve got aspirations for a big website it’s worth remembering Google Sheets isn’t a big data solution so you might want to run this on a 7 day window so that you don’t tip over the  2 million cell limit. Enjoy!

Twitter Archive Google Sheets (TAGS) just got a bit easier with an easy setup

If you haven’t been following the TAGS story this is my long-standing project to make it easier for people to archive Twitter results to a Google Sheet. The brief history lesson is this project started in 2010 when you didn’t need any authentication, you could copy the template enter your search term and start collecting tweets. In 2012 Twitter announced changes to the way you could get access to the data requiring user authentication and so in 2013 TAGS v5.0 was born.

The way TAGS v5.0 implemented authentication was to get the user to register for a Twitter Developer account. There is no cost to doing this and to this day you can still sign up as a developer with Twitter. Setting up a developer account requires some form filling which is prone to users copy/pasting the wrong thing. Perhaps a bigger deal breaker for some is Twitter Developer accounts needs a validated mobile phone number. When I was developing TAGS v5.0 there was an opportunity to use a single developer account which would then only require the user to sign in with their Twitter account. At the time I chose not to go down that route as part of the philosophy behind the project was a guerrilla approach, you archive your own tweets with your access details if Twitter shut me down.

Three years on from TAGS v5.0 I’m still here. Having also recently given a workshop at University of Edinburgh’s Digital Day of Ideas I was reminded of the pain of setting up TAGS for the first time. With that in mind I’m beta testing TAGS v6.1. This removes the need to set up and Twitter Developer account, hopefully bring more people to the Twitter archiving party. There’s still an option to use your own Twitter Developer account. So now all you need to do is copy the template and sign in using a regular Twitter account.

Twitter Auth Options

New authentication option to use ‘Easy Setup’

Twitter Authorization 2nd Step

With ‘Easy Setup’ you just need to sign in using a Twitter account

Head over to the Get TAGS page to get the latest version.

Note: TAGS 6.1 runs separately from earlier versions of TAGS so your existing archives will keep collecting tweets using your existing authentication.

TAGS/TwrtService is now published on Github

Following a request from the forums I’ve put a copy of the TAGS/TwtrService code on Github. To view, fork, comment make improvements visit https://github.com/mhawksey/TwtrService

List of TAGS column names that can be included in Archive sheet

Something I’ve neglected to highlight is what extra data can be pulled in with each tweet in a TAGS archive. By default the Archive sheet has about 20 fields/columns. The way TAGS works is it looks for a column name that matches the data field returned by Twitter for each tweet (if you understand JSON this document highlights the anatomy of a tweet). As some of the data for a tweet isn’t in a single level, in particular the user object, TAGS flattens this to make accessible. So to add or remove data from the Archive sheet add or remove a column with one of the titles below:


Keep your Twitter Archive fresh on Google Drive

Keep your Twitter Archive fresh on Google DriveTwo years ago I released a project that lets you keep your downloaded Twitter archive fresh by updating it with your new tweets. This project is different to TAGS because it uses the interface developed by Twitter for the archives they give you. Like earlier versions of TAGS one of the sticky bits was getting people to register and enter the Twitter API details. Using the TwtrService library, developed alongside TAGS v6.0, this has been made easier and for  existing TAGS v6.0 users means you don’t have to re-enter your API details at all. So if you haven’t already tried this project why not give it a go. Details for setting this up are in the post on my personal blog Keep your Twitter Archive fresh on Google Drive


TwtrService: A Twitter API client library for Google Apps Script

Cross-post from my main blog

As part of the latest release of TAGS (Twitter Archiving Google Sheet) I moved a lot of the code into a Google Apps Script Library. Libraries are a great way to bundle and release code allowing you to write your own classes and methods. To see some of the functionality already being developed by the Apps Script community you should have a look at the Google Apps Script Samples site and the excellent work Bruce McPherson has done which includes a EzyOauth2 library.

One of the things you can do with libraries is wrap one library into another. When rewriting TAGS it made sense to strip out a separate Twitter client library that I and others could use in different projects. Based on the work by Arun Nagarajan at Google, TwtrService provides  access to Twitter’s REST API. The work I’ve done is to add some UI and configuration methods to try to streamline the authentication flow. As part of this developers can offer authentication routes using their own Twitter application or use an application created by users. This particular angle is a result of one of the design principles for TAGS, that every copy of the template should use a Twitter application owned by the user. The reason behind this is to distribute the risk. If Twitter were to suspend my data access because a TAGS user abused their API it would suspend access for all TAGS users. By requiring TAGS users to register their own application with Twitter the responsibility to abide by Twitter’s terms of service lies with them. So in TAGS the auth flow looks like this

The result is hopefully a flexible library that developers can integrate into their own projects or by getting users to register their own.

Over the next couple of weeks I’ll be sharing some examples applications we’ve developed at ALT. In the meantime this post serves as a basic introduction to TwtrService and covers:

Overview of TwtrService

The TwtrService library for Google Apps Script centrally stores your Twitter access details allowing them to accessed from multiple script projects without the need for re-authentication. TwtrService is designed to allow you to directly use the Twitter’s v1.1 REST API GET and POST methods. For example to return Twitter search results for the search ‘Google Apps Script’ you would use:

var data = TwtrService.get('https://api.twitter.com/1.1/search/tweets.json', {q: 'Google Apps Script'});

The url string can also be abbreviated to:

var data = TwtrService.get('search/tweets', {q: 'Google Apps Script'});

Additional options can be passed in the parameters array. For example to return 100 results for the same search you would use:

var data = TwtrService.get('search/tweets', {q: 'Google Apps Script', count: 100});

The project key for this library is MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh and the TwtrService methods are documented here.

To use the Twitter REST methods TwtrService first needs authenticated access. TwtrService has some built-in methods to do this detailed below. Once a user has authenticated access the TwtrService library stores these as User Properties. This means when a user has authenticated once with TwtrService using the library in another container-bound or standalone Apps Script immediately gives them access to Twitter API results using the get/post methods. In terms of security User Properties are limited to the effective user of the current script.


Quick start: Personal access

If you would just like to use TwtrService for your Apps Script projects the easiest way to get started is to register a Twitter application and enter it’s details on this page (if you are interested here is the source code for the interface).

Note: If you are already a user of TAGS you’ll already be able to use TwtrService without the step above.

In your Apps Script project you’d like to use the Twitter API in the Script Editor window use Resources > Libraries and add the service  using the project key MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh.

In your project you can now use the TwtrService.get() and TwtrService.post() methods. The documentation for get() is detailed below (post is the same but uses HTTP POST):

get(string url, Object parameters)

GET wrapper for request to the Twitter REST API. For full documentation of API method endpoints and parameters see https://dev.twitter.com/rest/public. For example to get last 100 tweets containing ‘Google Apps Script’: var data = TwtrService.get(‘search/tweets’, {q: ‘Google Apps Script’, count: 100});

Name Type Description
url string Twitter REST API resource url. This can be either long form e.g. https://api.twitter.com/1.1/search/tweets.json or abbreviated e.g. search/tweets
parameters Object additional API parameters as detailed in the Twitter REST API documentation e.g. for search results a search string and count is specified by {q: ‘Google Apps Script’, count: 100}.
Return Values:
Type Description
Object API response in JSON format.

upload(string url, Object parameters)

The upload method for adding media to Twitter. For full documentation of media upload see https://dev.twitter.com/rest/reference/post/media/upload.

Name Type Description
url string Twitter REST API upload url. Currently the required string is ‘media/upload’
parameters Object additional API parameters as detailed in the Twitter REST API media upload documentation. Current valid parameters are media and media_data
Return Values:
Type Description
Object API response in JSON format.

var picture = DriveApp.getFileById('ID_OF_PICTURE_IN_DRIVE').getBlob();
var picture_data = Utilities.base64Encode(picture.getBytes());
var parameters = { "media" : picture_data };
var res = TwtrService.upload('media/upload', parameters);

Quick start: Personal access in Sheets and Documents

If you would like to replicate the TAGS authentication flow where users enter their Twitter application key/secret TwtrService comes with a number of UI methods. For TAGS the following code is used:

* 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?",
    // Process the user's response.
    if (result == 'yes') {
      // User clicked "Yes".
  } else {
* Used as part of setup() to process form data
function processForm(formObject) {

Quick Start: Shared Twitter Key/Secret

The earlier examples have assumed the user registers their own Twitter application. For scenarios where you would like to have the option for users to have authenticated access using a dedicated Twitter API key/secret it is possible to initialize these values. An example application code can be found here which is also deployed here.

Similar to earlier examples once a user authenticates access with your key/secret as long as these values are also initialized in other script projects the user will have access to the Twitter API via TwtrService.

Instructions for creating a Twitter Application to use with TwtrService

TwtrService requires you to have a registered Twitter application. If you are If you haven’t already done this here are some steps you need to get started:

  1. Register for an API key with Twitter at https://dev.twitter.com/apps/new (if you’ve already registered for a TAGS sheet you can reuse your existing API Key and Secret).
    • Name, description and website can be anything you like
    • Important Include the Callback URL https://script.google.com/macros/
  2. Read the ‘Developer Rules of the Road’ before clicking ‘Create your Twitter application’

On the Twitter site your application should include a ‘Keys and Access Tokens’ tab which includes the Consumer Key (API Key) and Consumer Secret (API Secret) you’ll need.

Hello world! TAGS v6.0 is here

Welcome to the new TAGS support site. The old blog post threads publishing TAGS were getting a bit messy so hopefully this will be a better place to share expertise and experiences. You can get the new TAGS here.

Amazing to think TAGS has been going for over 4 years now. Version 6 is a major code rewrite and the most important new feature is a better setup processes. No more digging into the script editor to run functions, no more entering your Twitter API key and secret each time you create a new archive. Both these things are history thanks to some base code released by Arun Nagarajan at Google. Now you enter/register for a Twitter API key and secret once and each copy of TAGS you make will remember these. This is made possible by incorporating this functionality as a custom Apps Script library I’ve called TwtrService. TwtrService makes it easy to make calls to all of Twitter’s API and I’ll be explaining how it works in another post.

Version 6 comes with some other new features. The one that was most requested was archiving favourited tweets. There was a quick hack version of TAGS that did this but was limited to the last 200. Now when you setup a favourite archive you can get up to the last 3,000 favourited tweets. Another option with TAGS v6.0 is to use Google’s new version of Sheets. This gives TAGS more capacity and performance. One issue however with new Sheets is it isn’t very stable with the Google Visualisation API which is used in TAGSExplorer.