Instagram Analytics with Google Sheets

9 min read

Does this still work?

Last checked: November 2019 and it worked! However, Instagram can change their code at anytime and it may break the script. In addition, the interface for Google Sheets and it's tools may change, so my images may not look exactly like the ones you will see. I will do my best to update the script and this article.

What is Google Sheets?

If you have ever used Microsoft Excel, you will be very familiar with Google Sheets. Google Sheets is a cloud-based solution, which means it has auto-save after every change with a log of revisions. In addition, it 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.

Instagram Stats

Thanks and credit

Before going into the spreadsheet and script setup, thanks has to be given to Nick Boyce and Damian Bast. I used 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 used 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.

Setting up the spreadsheet and script

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 similar 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 Ratio is not 100% accurate.

Instagram Setup

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 will need to change a couple of things. 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 IDs for the Instagram accounts you are tracking. You can only track public Instagram accounts.

For this example, I named my sheet Stats for @iam.alanyang and I am tracking my own Instagram, @iam.alanyang. In addition, you can change the lastPosts and timeZone variables. You only really need to change the lastPosts if the account you are tracking has less than 50 posts. The timeZone variable should match the same timezone set in your Google Sheets spreadsheet.

google-sheets-script
// You can monitor multiple accounts by creating new sheets and adding to the arrays
// Caution: The more data in your Google Spreadsheet, the longer it'll take to load

// var sheetNames = [ 'Sheet 1', 'Sheet 2', 'Sheet 3' ];
// var instagramAccounts = [ 'Account 1 ID', 'Account 2 ID', 'Account 3 ID' ];
// You can find your ID here: https://codeofaninja.com/tools/find-instagram-user-id
// Example: iam.alanyang's ID is 5502131244
var sheetNames = ['Stats for @iam.alanyang'];
var instagramAccounts = ['5502131244'];

// Stats from last number of posts / Use value: 0-50
var lastPosts = 50;

// 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
var timeZone = 'GMT-05:00';

// =====================================================================
// DON'T MAKE CHANGES BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING
// =====================================================================

var baseUrl = 'https://www.instagram.com/graphql/query/';

function insertFollowerCount() {
  for (var index = 0; index < sheetNames.length; index++) {
    insertStats(index);
  }
}

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;
}

function insertStats(index) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(this.sheetNames[index]);
  var currentRow = getLastRow('A', index);
  var currentCell = sheet.getRange('A1');
  var followers = getFollowers(this.instagramAccounts[index], lastPosts);
  var following = getFollowing(this.instagramAccounts[index], lastPosts);
  var media = getMedia(this.instagramAccounts[index], lastPosts);
  var engagement = getEngagement(media, followers, lastPosts);

  currentCell.offset(currentRow, 0).setValue(Utilities.formatDate(new Date(), timeZone, 'MMM dd, yyyy'));
  currentCell.offset(currentRow, 1).setValue(Utilities.formatDate(new Date(), timeZone, 'E'));
  currentCell.offset(currentRow, 2).setValue(followers);
  currentCell.offset(currentRow, 3).setValue('=IFERROR(($C' + (currentRow + 1) + '-$C' + currentRow + '),0)');
  currentCell.offset(currentRow, 4).setValue(following);
  currentCell.offset(currentRow, 5).setValue('=IFERROR(($E' + (currentRow + 1) + '-$E' + currentRow + '),0)');
  currentCell.offset(currentRow, 6).setValue(media.count);
  currentCell.offset(currentRow, 7).setValue(engagement.totalLikes);
  currentCell.offset(currentRow, 8).setValue(engagement.totalComments);
  currentCell.offset(currentRow, 9).setValue(engagement.engagementRatio);
  currentCell.offset(currentRow, 9).setNumberFormat('0.00%');
  currentCell.offset(currentRow, 10).setValue('=IFERROR(($J' + (currentRow + 1) + '-$J' + currentRow + '),0)');
}

function getFollowers(userID, amount) {
  return parseInt(
    fetch(
      baseUrl +
        '?query_hash=37479f2b8209594dde7facb0d904896a&variables=%7B%22id%22%3A%22' +
        userID +
        '%22%2C%22first%22%3A' +
        amount +
        '%7D'
    )['data']['user']['edge_followed_by']['count']
  );
}

function getFollowing(userID, amount) {
  return parseInt(
    fetch(
      baseUrl +
        '?query_hash=58712303d941c6855d4e888c5f0cd22f&variables=%7B%22id%22%3A%22' +
        userID +
        '%22%2C%22first%22%3A' +
        amount +
        '%7D'
    )['data']['user']['edge_follow']['count']
  );
}

function getMedia(userID, amount) {
  return fetch(
    baseUrl +
      '?query_hash=f2405b236d85e8296cf30347c9f08c2a&variables=%7B%22id%22%3A%22' +
      userID +
      '%22%2C%22first%22%3A' +
      amount +
      '%7D'
  )['data']['user']['edge_owner_to_timeline_media'];
}

function getEngagement(media, followers, amount) {
  var totalComments = 0,
    totalLikes = 0;
  for (var i = 0; i < amount; i++) {
    totalComments += parseInt(media.edges[i].node.edge_media_to_comment.count);
  }
  for (var l = 0; l < amount; l++) {
    totalLikes += parseInt(media.edges[l].node.edge_media_preview_like.count);
  }
  var engagementRatio = (totalLikes + totalComments) / followers / amount;
  return {
    mediaCount: parseInt(media.count),
    totalComments: totalComments,
    totalLikes: totalLikes,
    engagementRatio: engagementRatio,
  };
}

function fetch(url) {
  var ignoreError = {
    muteHttpExceptions: true,
  };
  var source = UrlFetchApp.fetch(url, ignoreError).getContentText();
  var data = JSON.parse(source);
  return data;
}

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.

Google Sheets insertFollowerCount

A popup should occur asking for you to authorize this script to access data on your account. You just need to accept and go through the authorization steps for the script to work. You will need to use the Go to Script (unsafe) link since this app/script has not been verified by Google.

Google Sheets Authorization
Google Sheets Verify App

Setting up the project trigger

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. The images below may look differently because of updates to Google Sheets.

Google Sheets Project Trigger
Google Sheets Add Trigger
Google Sheets Trigger Settings

Common Errors

  • TypeError: Cannot read property "node" from undefined. - Instagram account has less posts than the value of lastPosts. 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 not fully automated is because of the last error listed here. We will 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 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 definitely recommend having your charts on a separate sheet inside your spreadsheet. You can think of your separate sheet for your charts as a dashboard.

Google Sheets Graph Follower Trend
Google Sheets Graph Follower Gain

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.