Before

After

If you ever wanted to quickly analyse hashtags and see who's using them then this script might come in handy. We'll be downloading the latest 100 tweets that used a certain hashtag. This can be especially handy when you're the originator of the hashtag and would want to know large twitter account use it.

We'll be using the GET search/tweets reference, which you can read all about in the API documentation 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 - Get the latest tweets

Now that we're authenticated we can request the last 100 tweets for the hashtag in Cell B3. To do so we'll make a fetch request to the search/tweets API.

var ApiUrlTweetIds = "https://api.twitter.com/1.1/search/tweets.json?count=100&include_entities=false";
ApiUrlTweetIds += "&result_type=recent&q=" + stringEncoder(Hashtag);
var parsedToken = resultToken.access_token;
var options = {
  headers : {
    Authorization: 'Bearer ' + parsedToken
  },
  "method" : "get"
 };
var responseApiUrlTweetIds = UrlFetchApp.fetch(ApiUrlTweetIds, options);
var response = responseApiUrlTweetIds.getContentText()
var result = JSON.parse(response);

Step 4 - Add data into arrays

Now that we have the desired data nicely formatted in JSON, we just need to loop through it and push the correct values into the associated arrays.

//Define empty arrays to push data in
var text = [];
var created_at = [];
var retweet_count = [];
var favorite_count = [];
var followers_count = [];
var friends_count = [];
var favourites_count = [];
var name = [];

//Loop through results
for(var i=0;i<result.statuses.length;i++){
    
//Push data into arrays
 text.push([result.statuses[i].text]); 
 created_at.push([result.statuses[i].created_at]); 
 retweet_count.push([result.statuses[i].retweet_count]); 
 favorite_count.push([result.statuses[i].favorite_count]); 
 followers_count.push([result.statuses[i].user.followers_count]); 
 friends_count.push([result.statuses[i].user.friends_count]); 
 favourites_count.push([result.statuses[i].user.favourites_count]); 
 name.push([result.statuses[i].user.name]); 
};

Step 5 - Insert data into the sheet

Now that all data is sorted nicely into the correct arrays, all that's left is to state in which sheet range we want to insert the data. 

//Clear prior data
sheet.getRange("A6:M").clear();
  
//Insert new data
sheet.getRange("A6:A"+(text.length + 5)).setValues(created_at); 
sheet.getRange("B6:B"+(text.length + 5)).setValues(name); 
sheet.getRange("C6:C"+(text.length + 5)).setValues(retweet_count); 
sheet.getRange("D6:D"+(text.length + 5)).setValues(favorite_count);
sheet.getRange("E6:E"+(text.length + 5)).setValues(text); 
sheet.getRange("F6:F"+(text.length + 5)).setValues(followers_count);  sheet.getRange("G6:G"+(text.length + 5)).setValues(friends_count); 
sheet.getRange("H6:H"+(text.length + 5)).setValues(favourites_count);


Let me know if this was useful & let's connect on     or