Instagram Analytics with Google Sheets

August 21, 2018
In my previous blog post, Journey of growing an Instagram account, I mentioned that Google Sheets is one of the biggest tools that I use when it comes to tracking Instagram profiles. It is mostly automated after the initial setup and best of all, it is completely free!

Data Table

What is Google Sheets?

If you have ever used Microsoft Excel, you will be very familiar with Google Sheets. In addition, Google Sheets is cloud-based, has auto-save after every change with a log of revisions, allows for add-ons, and allows for multiple people to edit and view the spreadsheet at the same time. These are just some of the features that Google Sheets has to offer. I am not sponsored in any way, I just love using Google Sheets.

Thanks and credit.

Before going into the spreadsheet and script setup, thanks has to be given to Nick Boyce and Damian Bast. I use to track Instagram with a very different code, but Instagram's code has changed a lot since then and it broke the script that I use to use. Fortunately, their code works for now. My code just adds onto their original code and offers a few more things on the spreadsheet like change amount in followers/following, engagement ratio, and multiple account tracking.

The Spreadsheet and Script Setup.

The first step in setting up this Google Spreadsheet is creating headings for the first row and freezing that row. To freeze the row, you just need to highlight the first row and go to View > Freeze > 1 row. If you have completed these first initial steps, your sheet should look similiar to mine below. Something that should be noted is that the Likes, Comments, and Engagement Ratio are only tracked for the 12 latest posts, so the Engagement Raio is not 100% accurate.

Setup Step 1

To make this spreadsheet mostly automated, we will need to add some functions to Google Sheets script. We can do this by going to Tools > Script editor and then copying and pasting the code I have provided below into the script editor. After pasting the code into the script editor, you need to make sure that the sheetNames and instagramAccounts are correct. You can only track public Instagram accounts. You can see your sheetNames by going to your spreadsheet and looking on the bottom. You need a different sheet for each account you are monitoring. The instagramAccounts should be the Instagram account or accounts you are tracking. For this example, I named my sheet Data and I am only tracking my own Instagram, @iam.alanyang.

Setup Step 2

// You can monitor multiple accounts by creating new sheets and adding to the arrays
// var sheetNames = [ 'Sheet 1', 'Sheet 2', 'Sheet 3' ];
// var instagramAccounts = [ 'Account 1', 'Account 2', 'Account 3' ];
// Caution: The more data in your Google Spreadsheet, the longer it'll take to load
var sheetNames = [ 'Data' ];
var instagramAccounts = [ 'iam.alanyang' ];
// Data from last number of posts / Use value: 0-12
var lastPosts = 12;
function insertFollowerCount() {
for(var index = 0; index < sheetNames.length;index++){
Logger.log(this.sheetNames[index]);
Logger.log(this.instagramAccounts[index]);
insertStats(index);
};
};
function insertStats(index){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(this.sheetNames[index]);
accountData = getInstagramData(this.instagramAccounts[index]);
var currentRow = getLastRow('A', index);
var currentCell = sheet.getRange('A1');
// Adjust the time zone to be the same as the one used in your Google Sheet
// You can find the time zone by going to your Goole Sheet. File --> Spreadsheet settings
// Used setNumberFormat to format the Date and Engagement Ratio cells
currentCell.offset(currentRow, 0).setValue(Utilities.formatDate(new Date(), 'GMT-05:00', 'MMM dd, yyyy'));
currentCell.offset(currentRow, 1).setValue(Utilities.formatDate(new Date(), 'GMT-05:00', 'E'));
currentCell.offset(currentRow, 2).setValue(accountData.followerCount);
currentCell.offset(currentRow, 3).setValue('=IFERROR(($C'+(currentRow+1)+'-$C'+currentRow+'),0)');
currentCell.offset(currentRow, 4).setValue(accountData.followCount);
currentCell.offset(currentRow, 5).setValue('=IFERROR(($E'+(currentRow+1)+'-$E'+currentRow+'),0)');
currentCell.offset(currentRow, 6).setValue(accountData.mediaCount);
currentCell.offset(currentRow, 7).setValue(accountData.totalLikes);
currentCell.offset(currentRow, 8).setValue(accountData.totalComments);
currentCell.offset(currentRow, 9).setValue(accountData.engagementRatio);
currentCell.offset(currentRow, 9).setNumberFormat('0.00%');
};
function getInstagramData(username) {
var r = new RegExp('<script type="text\/javascript">' +
'([^{]+?({.*profile_pic_url.*})[^}]+?)' +
'<\/script>');
var instagramUrl = 'https://www.instagram.com/' + username, totalComments = 0, totalLikes = 0;
var source = UrlFetchApp.fetch(instagramUrl).getContentText();
var jsonStr = source.match(r)[2];
var jsonData = JSON.parse(jsonStr);
console.log('data', jsonData);
var oldVariantOfData = jsonData['entry_data']['ProfilePage'][0];
console.log('oldVariantOfData', oldVariantOfData);
for(var i = 0; i < lastPosts; i++) {
var eottm = oldVariantOfData.graphql.user.edge_owner_to_timeline_media.edges[i];
if(eottm != undefined){
totalComments += parseInt(eottm.node.edge_media_to_comment.count);
totalLikes += parseInt(eottm.node.edge_liked_by.count);
};
};
return {
followerCount : oldVariantOfData.graphql.user.edge_followed_by.count,
followCount : oldVariantOfData.graphql.user.edge_follow.count,
mediaCount : oldVariantOfData.graphql.user.edge_owner_to_timeline_media.count,
totalComments : totalComments,
totalLikes : totalLikes,
engagementRatio : (((totalLikes+totalComments))/oldVariantOfData.graphql.user.edge_followed_by.count)/lastPosts,
};
};
function getLastRow(column, index) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(this.sheetNames[index]);
var range = sheet.getRange(column + '1:' + column);
var values = range.getValues();
var newArr = [];
var count = 0;
for (var i = 0; i < range.getLastRow(); i++) {
if (values[i][0]) {
newArr.push(values[i][0]);
};
};
return newArr.length;
};

We can test our code to see if it works by triggering our main function insertFollowerCount. We can do this by selecting the function we want to run and then hitting the play button. If everything is set up correctly in the script, our spreadsheet should populate data and we can move onto automation with a project trigger.

Setup Step 3

Setup Step 4

The Project Trigger Setup.

That is not all we need to do to make the spreadsheet mostly automated. We need to add a project trigger which will periodically trigger our main function insertFollowerCount and that will populate our spreadsheet with information. Some settings in the project trigger will be up to personal preference. I like to have my project trigger as a time-driven trigger that initiates between 10pm - 11pm every day. We will also be adding an email notification that will alert us immediately if the script had any errors or did not work correctly.

Setup Step 5

Setup Step 6

Setup Step 7

Common errors and their fixes.

TypeError: Cannot read property "node" from underfined. - Instagram account has less than 12 posts. Change lastPosts value to something lower.
TypeError: Cannot call method "getRange" of null. - Make sure your sheet's name is correct in sheetsName.
Request failed for ... returned code 404. Truncated server - Make sure your Instagram account is correct in instagramAccounts.
Request failed for ... returned code 429. Truncated server response - Timed out. Only solution is to run the command again.
Remember how I kept saying "mostly automated" earlier? The main reason why the spreadsheet is mostly automated is because of the last error listed here. I see this error from time to time, but since we have the email notification, we will be notified when we receive that error from the project trigger. I have not found any solutions for this error besides just manually running the main function.

Visualize the collected data.

Some people hate looking at a huge table of numbers. You can make charts in Google Sheets to display your data. This is a personal preference and there are so many possibilities for your charts. Creating charts is very similar to creating charts in Microsoft Excel. You just select what chart type you want and populate it with data. I would defnitely recommend having your charts on a separate sheet inside your spreadsheet. You can think of your separate sheet for your charts as a dashboard.

Data Graph

Data Chart

Conclusion.

There is a lot more you can do with Google Sheets. You just have to manipulate the data that the sheet collects. You can look at your weekly, monthly, or even yearly averages. You can use the data to predict future values. You can see what happens to your engagement ratio when you perform certain actions (likes, comments, follows) on other Instagram profiles. These are just some examples off the top of my head! There are just so many possibilities. I hope this can help you with growing and tracking on Instagram.

Share this article:

© 2018 Alan Yang — All Rights Reserved.