Monday, 16 December 2013

Monday, December 16, 2013
Facebook Likes Counter
Do you have a Facebook Page? Or do you have multiple Facebook page? Is it hard to keep track of the Facebook Likes that each page is getting? Are you manually entering the stats of Likes in a Spreadsheet? Do you want something easy and simple that you do all the tracking and updating for you, leaving you plenty of time to do other stuff?

If the answer to all these questions are "YES". Then you are a the perfect place and we are here with the perfect solution. To do all that we have stated above we will be using Google’s spreadsheets to track Facebook likes online and we will count the likes in two ways. We will either open or totally automate the spreadsheet using a timer. Just follow the steps mentioned below and use the pictures given as reference.

In this example that we will now work on we have decided to work with the "number of Facebook Followers", but this code can be easily used to update and fetch other stored data as well from different sources.

A GOOGLE ACCOUNT IS NEEDED TO PERFORM THESE STEPS

Step 1

Click on the CREATE button and when the above list appeared select “Spreadsheet” from the list. You will see a blank Spreadsheet will open. (* If you do not have a Google account then please create one then try these steps.)
STEP 1
(Click to Enlarge and see properly)

Step 2

We will now rename the Spreadsheet. To do this click on its name at the left-upper corner. The default name is "Untitled spreadsheet". We will be renaming it as "FB Like Cunter Report".
STEP 2
BEFORE : CLICK TO ENLARGE

STEP 2
AFTER RENAMING : CLICK TO ENLARGE

Step 3

Now we will attach the formula for getting link concatenate in next cell of Account which is known as “Facebook Graph URL”. 

In the first column, we will list the Facebook accounts we want to track.
In the second column, we will create a formula to find number of likes from the API using this format:

https://graph.facebook.com/accountID (replace that with the account ID or name)

In order to do so, your column B formula needs to add the URL root "https://graph.facebook.com/" to the account name from column A,

Like “=CONCAT("https://graph.facebook.com/",A2)” without the external quotes as it shown in following picture and so on for the other rows.

STEP 3
Click to Enlarge

 Step 4

In this step we will be using a custom function that is not defined in the spreadsheet. To do this we will create a script file with a ".gs" (Google Script) extension. Go to Tools->Script Editor.
step 4
Click to Enlarge

Step 5

Now in the page a tab named as “Untitled Project” and a dialog box will appear in center “Google Apps Script”.  Choose “SpreadSheet” under “Create script for” at left panel as following picture.
step 5
Click to Enlarge

In the script file you will notice that there are some default function present for example, readRows() and onOpen(). We will now define a Likes Counter Function to count the likes in our facebook page. To do this simply, create a new blank function and call it anything that you wish, although I prefer using a name that's more understandable. Here we will be calling it "facebookLike" and it will contain a single argument.
function facebookLike(url)
{ // We will add the JSON code here after retrieving it.
}

step 5
Click to Enlarge
 You can rename the project same way by clicking on “Untitled project”.

Step 6

Now we have to define fetch data from the link of “https://graph.facebook.com/aj.india.13” to know latest number of Likes in a particular page. What we are looking for, is the number of likes in this returned JSON data that will look something like the data in the picture below.
Step 6
JSON Data from Facebook Graph API


According to the Google's Developer Documentation it shows that fetching data uses :
UrlFetchApp.fetch(url);
So we'll create a variable holding that
var result = UrlFetchApp.fetch(url);
and then parse the JSON result.

Step 7

Now when we parse the JSON, we can extract the number of likes from the JSON with the following code:
function facebookLike(url){
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);
var likes = data.likes;
return likes;}

Step 8

In this step we will write function's name and it’s parameter in “Current number of likes” as follows
    =facebookLike(B2)



Step 8
Click to Enlarge
On pressing Enter you will get the following result :
Result
Click to Enlarge

Step 9


Now we need to save these data automatically so we will use this formula in the rest of column C, and values will automatically fill in for other accounts you're tracking. Although, these values won't be saved as they will change each time you open the sheet.

In order to keep historical data as the sheets currently designed, you will need to copy and paste values manually into another column or spreadsheet. Now you might be thinking that we earlier said that there wont be any manual work required after this set up, so instead, let's create a new function to:
  • Find the first empty column
  • Title the column with the Last Updated
  • Copy the value from column C into that first empty cell.

Step 10

We now have to write another custom function to store the data. In order to store data, we need to find the first empty column. Now set a variable name for the active spreadsheet, get the data range in that existing sheet with the getDataRange() command and then create variables to hold the number of columns and number of rows.

Here is the storeLikes() function that you will need.

function storeLikes()
{
    var sheet=SpreadsheetApp.getActiveSheet();
    var range=sheet.getDataRange();
    var row=range.getNumRows();
    Var col=range.getNumColumns();
    var ncol=col+1;

    sheet.getRange(1, col+1).setValue(new Date());
    for(var i=2; i<=row; i++){
    var likes=sheet.getRange(i, 3).getValue();
    sheet.getRange(i, col+1).setValue(likes);
    }
}

Step 11

If you desire to know the date of data stored and fetched. Just put the current date that the function runs in row 1 of your first empty column.
Use command: sheet(1,numColumns + 1).setValue(new Date());
will set the value of row 1 of the first column without any data, cell 1, numColumns + 1, to the current date and time. Then loop through the rest of the cells in the column with your latest data by using the for loop at left.

Step 12



Now we will automate the report. To do this put this script on a timer and it will run on scheduled time by user.
Follow the steps:

  • Click on the clock icon.
  • Choose "Add a new trigger."
  • Set your function to run whenever the spreadsheet is opened manually or on an automated schedule hourly, daily, weekly or monthly.
 
Set timer
Click to Enlarge

Click on the save button. Now you have to authorize it to make changes on Spreadsheet using your Google account so you need to click on allow button to do so.


Contributors: 


Author Editor

0 comments:

Post a Comment