Before

After

By using the GET friends/list API reference we're able to export the friends' data of any Twitter profile. This might come in handy either when analysing your own Twitter network or while doing competitive analysis. 

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 friends data from the username

Now that we've been authenticated, we can make a fetch request to the friends/list API and get the friends' data from the username that we've filled in at cell "B3".

var ApiUrlFollowers = 'https://api.twitter.com/1.1/friends/list.json?cursor=' + paging + '&screen_name='+ Username + '&include_user_entities&skip_status=true=false&count=200';

var parsedToken = resultToken.access_token;
var options = {
  headers : {
    Authorization: 'Bearer ' + parsedToken
  },
  "method" : "get"
 };
var responseApiUrlUsers = UrlFetchApp.fetch(ApiUrlFollowers, options);
var response = responseApiUrlUsers.getContentText()
var result = JSON.parse(response);

Step 4 - Insert data into arrays

Once we've requested the data and converted it into JSON, then the next step is to select the values that we want to add into the sheet. We'll define the arrays in which we'll add the values after which we loop through the data to add them one by one.

//Define empty arrays to push data in
var profileImage = [];
var screenName = []
var userName = [];
var description = [];
var followersCount = [];
var friendsCount = [];
var favouritesCount = [];
var statusesCount = [];
var location = [];
var dateCreated = [];
var verified = [];
var protected = [];
var url = []; 
var paging = -1

//loop through results
for(var i=0;i<result.users.length;i++){
    
//Push data into arrays
profileImage.push(['=IMAGE("' + result.users[i].profile_image_url + '")']);
screenName.push([result.users[i].screen_name]);
userName.push([result.users[i].name]);
description.push([result.users[i].description]);
followersCount.push([result.users[i].followers_count]);
friendsCount.push([result.users[i].friends_count]);
favouritesCount.push([result.users[i].favourites_count]);
statusesCount.push([result.users[i].statuses_count]);
location.push([result.users[i].location]);
dateCreated.push([result.users[i].created_at]);
verified.push([result.users[i].verified]);
protected.push([result.users[i].protected]);
url.push([result.users[i].url]);       
}

Step 5 - Add values to sheet

All values have now been added to the arrays, so all is left is to indicate in which range we want to insert (and overwrite) the array of data. 

//Clear prior data
sheet.getRange("A6:M").clear();
  
//Insert new data
sheet.getRange("A6:A"+(profileImage.length + 5)).setValues(profileImage);
sheet.getRange("B6:B"+(screenName.length + 5)).setValues(screenName);
sheet.getRange("C6:C"+(userName.length + 5)).setValues(userName);
sheet.getRange("D6:D"+(description.length + 5)).setValues(description);
sheet.getRange("E6:E"+(followersCount.length + 5)).setValues(followersCount);
sheet.getRange("F6:F"+(friendsCount.length + 5)).setValues(friendsCount);
sheet.getRange("G6:G"+(favouritesCount.length + 5)).setValues(favouritesCount);
sheet.getRange("H6:H"+(statusesCount.length + 5)).setValues(statusesCount); 
sheet.getRange("I6:I"+(location.length + 5)).setValues(location); 
sheet.getRange("J6:J"+(dateCreated.length + 5)).setValues(dateCreated); 
sheet.getRange("K6:K"+(verified.length + 5)).setValues(verified); 
sheet.getRange("L6:L"+(protected.length + 5)).setValues(protected);  
sheet.getRange("M6:M"+(url.length + 5)).setValues(url); 

That's it! You can now hit "run" and see the friends being added to the sheet. 


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