Before

After

Using the Facebook Marketing API we can make a fetch to the ratings reference in order to extract any old or new testimonials. For all the details on page ratings and the associated values, I recommend skimming through the API documentation here.

The goal of this tutorial is to analyse your ratings and give you a way to then import them to Slack while they happen or just analyse the performance.

To get started, make a copy of the above Google Sheet and open your script editor by going to "Tools" -> "Script editor".

Step 1 - Create a Facebook access token

To get an access token, you first have the create a Facebook app. You can do this right here.

From there on head over to the Graph API explorer to generate an access token. Make sure to give your token the following permissions:
  • pages_read_user_content
  • manage_pages

Step 2 - Increase the lifespan of the access token

Standard access tokens are only valid for around 30 minutes, which isn't too handy if you want to use this script many times.

Luckily you can quite easily extend that duration via the access token debugger.  Open the debugger, paste in your access token, and extend its expiry date. It should now last at least a few months, but it might still be good to set up a reminder somewhere.

Step 3 - Request page IDs and names

After you've copied your long-lived access token into the sheet we can run the script. Firstly, we need to figure out to which account you've got access. We do that with the /me/accounts request and then a  /accounts request.

// make fetch to Facebook Marketing API to get userId
var apiUrl = 'https://graph.facebook.com/v7.0/me?fields=id,name&access_token=' + accessToken; 
var responseApi = UrlFetchApp.fetch(apiUrl);
var result = JSON.parse(responseApi.getContentText());
  
// make fetch to Facebook Marketing API to get all pages
var apiUrl = 'https://graph.facebook.com/v7.0/' + result.id + '/accounts?access_token=' + accessToken; 
var responseApi = UrlFetchApp.fetch(apiUrl);
var result = JSON.parse(responseApi.getContentText())

Step 4 - Request ratings from page

Once you've selected the correct Page ID in "B4" we can make a fetch for the ratings.

// get input values from the sheet 
var accessToken = sheet.getRange("B3").getValue(); 
var pageID = sheet.getRange("B4").getValue(); 
  
// make fetch to Facebook Marketing API to get all ratings
var apiUrl = 'https://graph.facebook.com/v7.0/' + pageID + '/ratings?access_token=' + accessToken; 
var responseApi = UrlFetchApp.fetch(apiUrl);
var result = JSON.parse(responseApi.getContentText());
  
// create empty Arrays to fill with the JSON response 
var created_time = [];
var recommendation_type = [];
var rating = [];
var reviewer = [];
  
// find length of result array to set as the end of the loop 
var arrayLength = result.data.length;
  
// push results into the arrays for Interests
for (var i=0; i<arrayLength; i++){
  created_time.push([result.data[i].created_time]); 
  recommendation_type.push([result.data[i].recommendation_type]); 
  rating.push([result.data[i].rating]); 
  reviewer.push([result.data[i].reviewer.name]); 
};

Step 5 - Insert ratings into sheet

The last step of this script is to insert the ratings into the sheet.
// remove prior data from the sheet in range
var sheetRange = sheet.getRange("C6:G").clear();

// insert data
sheet.getRange(6, 4, arrayLength, 1).setValues(created_time);
sheet.getRange(6, 5, arrayLength, 1).setValues(recommendation_type);
sheet.getRange(6, 6, arrayLength, 1).setValues(rating);
sheet.getRange(6, 7, arrayLength, 1).setValues(reviewer)

I'd recommend extending this script with the Sheets to Slack tutorial so that you get a Slack notification each time someone leaves a rating. This might be handy, especially for poor ratings.



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