Before
After
The goal of this tutorial is to create a growth timeline that gets updated every day with the latest statistics from a Twitter profile. For this tutorial, we'll be looking at the total number of Followers, Friends, Favourites and Statuses by using the GET users/show reference (read more here).
To get started, make a copy of the above Google Sheet and open your script editor by going to "Tools" -> "Script editor".
After
The goal of this tutorial is to create a growth timeline that gets updated every day with the latest statistics from a Twitter profile. For this tutorial, we'll be looking at the total number of Followers, Friends, Favourites and Statuses by using the GET users/show reference (read more here).
To get started, make a copy of the above Google Sheet and open your script editor by going to "Tools" -> "Script editor".
Step 1 - Import API key & API secret key
To generate your API keys, you either need to create a new Twitter app or use an existing one. Within the keys & tokens tab, you can find the keys and copy them into your sheet. The following code then gets the values from the sheet.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var ApiKey = sheet.getRange("B1").getValue();
var ApiSecret = sheet.getRange("B2").getValue();
var Username = sheet.getRange("B3").getValue();Step 2 - Generate a bearer token
Twitter requires you to authenticate yourself in order to ensure data security. There're are a few methods for authentication. In this tutorial, we'll be using the OAuth 2.0 Bearer Token but you could opt for other ways. Read more about Twitter authentication methods here.
var ApiUrlAuthorize = "https://api.twitter.com/oauth2/token";
var token = Utilities.base64EncodeWebSafe(ApiKey + ":" + ApiSecret);
var options = {
headers : {
Authorization: "Basic " + token,
"Content-Type": "application/x-www-form-urlencoded;charset=UTF-8"
},
method: "post",
payload: "grant_type=client_credentials"
};
var responseApiUrlAuthorize = UrlFetchApp.fetch(ApiUrlAuthorize, options);
var resultToken = JSON.parse(responseApiUrlAuthorize);Step 3 - Request user info
Together with bearer token and a GET request to the users/show API we can get all the data we need and convert it into JSON.
//Get user info
var ApiUrlUsers = 'https://api.twitter.com/1.1/users/show.json?screen_name=' + Username;
var parsedToken = resultToken.access_token;
var options = {
headers : {
Authorization: 'Bearer ' + parsedToken
},
method" : "get"
};
var responseApiUrlUsers = UrlFetchApp.fetch(ApiUrlUsers, options);
var result = JSON.parse(responseApiUrlUsers);Step 4 - Insert data into the sheet
Now that we have the latest number of Followers, Friends, Favourites and Statuses for our profile, we just have to define them as variables and insert them inside the sheet. As we want to add a new row each day, instead of overwriting prior data, we'll check what the current last row is and insert the data below. That way you get a nice overview over time when using a daily trigger, which can be set up inside the script editor by clicking on the stopwatch icon.
//Define variables
var followersCount = result.followers_count;
var friendsCount = result.friends_count;
var favouritesCount = result.favourites_count;
var statusesCount = result.statuses_count;
//Find the row to inser the data into
var lastrow = sheet.getLastRow() + 1;
//Insert new data
sheet.getRange("A" + lastrow).setValue(Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy"));
sheet.getRange("B" + lastrow).setValue(followersCount);
sheet.getRange("C" + lastrow).setValue(friendsCount);
sheet.getRange("D" + lastrow).setValue(favouritesCount);
sheet.getRange("E" + lastrow).setValue(statusesCount); 