Instagram Analytics with Google Sheets

Drawing of me
Alan Yang11 min read

Does this still work?

January 2021
I have no idea if this script still works. I am no longer supporting or updating this project. Please do not contact me for help with this script.

Disclaimer: Instagram can change their code at anytime and it may break the script. I am no longer updating the script, so if it stops working, it stops working. 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.

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 last number about of posts that you set for lastPosts (by default the value is 50), 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.
  • The cookie will be the unique code that you retrieve. You have to be logged in to and Instagram account to be able to retrieve a cookie. Instructions for this are below.
  • You need to change the lastPosts if the account you are tracking has less than 50 posts or else there will be an error. (Minimum: 1 / Maximum: 50)
  • The timeZone variable should match the same timezone set in your Google Sheets spreadsheet.

Make sure you are logged into an Instagram account or else you will not receive a Cookie.

Open a new tab in your web browser, I will be using Google Chrome for this. Right-click inside of the new tab and click Inspect to open the Chrome Dev Tools.

Inspect

After you have the Chrome Dev Tools open, select the Network tab to see requests.

Network Tab

Right-click this URL and click Copy link address and go to this address in the tab with the opened Chrome Dev Tools. It will make a request to Instagram's GraphQL endpoint. In the Network tab, you will see new entries. Click on the entry with the name that contains ?query_hash= and you will be able to see the Headers. Scroll down until you see Request Headers and you will be able to find the cookie value.

Cookie

You will need to copy the value after the cookie: and replace the YOUR COOKIE HERE text in the script with your cookie. Make sure the value is between single quotes.

As an example, I named my sheet Stats for @iam.alanyang and I am tracking my own Instagram, @iam.alanyang. The YOUR COOKIE HERE text in the cookie variable would be replaced by your own. The lastPosts is the default 50 because I have more than 50 posts on my Instagram. The timeZone is set to US Eastern Time.

google-sheets-script
// =====================================================================
//   https://alanyang.com/blog/instagram-analytics-with-google-sheets
// =====================================================================

// 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 sheetNames = ['Stats for @iam.alanyang'];

// 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 instagramAccounts = ['5502131244'];

// You have to be logged into an Instagram account to be able to get a Cookie
// You can use the Dev Tools in your web browser to retrieve a Cookie
var cookie = 'YOUR COOKIE HERE';

// 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 header = { Cookie: cookie };
  var options = {
    headers: header,
    muteHttpExceptions: true,
  };
  var source = UrlFetchApp.fetch(url, options).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.