Well 'be using the GET users/show Twitter API to extract username, # followers, # friends, # likes, # tweets, location, creation date, url and whether the profile is verified and/or protected. To get additional data you can have a look at what is available 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 - Request user info
Together with the bearer token and username, we can now make a request to fetch all the data.
//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 - Define variables
Before we add the data into the sheet, we have to state which data we actually want to use. This is the place where you could add more variables depending on what's available according to the API documentation.
//Define variables
var profileImage = '=IMAGE("' + result.profile_image_url + '")';
var screenName = result.screen_name;
var userName = result.name;
var description = result.description;
var followersCount = result.followers_count;
var friendsCount = result.friends_count;
var favouritesCount = result.favourites_count;
var statusesCount = result.statuses_count;
var location = result.location;
var dateCreated = result.created_at;
var verified = result.verified;
var protected = result.protected;
var url = result.url; Step 5 - Insert user data into the sheet
Now that all variables have been defined, we just need to state in which cell we want them to be imported.
//Insert new data
sheet.getRange("B5").setValue(profileImage);
sheet.getRange("B6").setValue(screenName);
sheet.getRange("B7").setValue(userName);
sheet.getRange("B8").setValue(description);
sheet.getRange("B9").setValue(followersCount);
sheet.getRange("B10").setValue(friendsCount);
sheet.getRange("B11").setValue(favouritesCount);
sheet.getRange("B12").setValue(statusesCount);
sheet.getRange("B13").setValue(location);
sheet.getRange("B14").setValue(dateCreated);
sheet.getRange("B15").setValue(verified);
sheet.getRange("B16").setValue(protected);
sheet.getRange("B17").setValue(url);
That's all. You can now run the script and check the data.
