Instagram Analytics with Google Sheets

Table of Contents

• What is Google Sheets?
• Thanks and credit
• Setting up the spreadsheet and script
  • Retrieve your cookie
• Setting up the project trigger
• Common Errors
• Visualize the data
• Conclusion

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.

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 like 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 LATEST_POSTS (by default the value is 50), so the Engagement Ratio is not 100% accurate.

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 SHEET_NAMES by going to your spreadsheet and looking on the bottom. You need a different sheet for each account you are monitoring.

• The INSTAGRAM_ACCOUNTS should be the IDs for the Instagram accounts you are tracking. You can only track public Instagram accounts.

• The INSTAGRAM_COOKIE will be the unique code that you retrieve. You must be logged in to and Instagram account to be able to retrieve a cookie. Instructions for this are below.

• You need to change the LATEST_POSTS if the account you are tracking has less than 50 posts or else there will be an error. (Minimum: 1 / Maximum: 50)

• The TIME_ZONE variable should match the same time zone set in your Google Sheets spreadsheet.

Retrieve your cookie

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.

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

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.

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 INSTAGRAM_COOKIE variable would be replaced by your own. The LATEST_POSTS is the default 50 because I have more than 50 posts on my Instagram. The TIME_ZONE is set to US Eastern Time.

// =====================================================================
//   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

// const SHEET_NAMES = [ 'Sheet 1', 'Sheet 2', 'Sheet 3' ];
const SHEET_NAMES = ['Stats for @iam.alanyang'];

// const INSTAGRAM_ACCOUNTS = [ '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
const INSTAGRAM_ACCOUNTS = ['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
const INSTAGRAM_COOKIE = 'YOUR COOKIE HERE';

// Stats from last number of posts / Use value: 0-50
const LATEST_POSTS = 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
const TIME_ZONE = 'GMT-05:00';

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

const BASE_URL = 'https://www.instagram.com/graphql/query/';

function insertFollowerCount() {
  for (let i = 0; i < SHEET_NAMES.length; i++) {
    insertStats(i);
  }
}

function getLastRow(column, index) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(this.SHEET_NAMES[index]);
  const range = sheet.getRange(column + '1:' + column);
  const values = range.getValues();
  let newArr = [];
  for (let i = 0; i < range.getLastRow(); i++) {
    if (values[i][0]) {
      newArr.push(values[i][0]);
    }
  }
  return newArr.length;
}

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

  currentCell.offset(currentRow, 0).setValue(Utilities.formatDate(new Date(), TIME_ZONE, 'MMM dd, yyyy'));
  currentCell.offset(currentRow, 1).setValue(Utilities.formatDate(new Date(), TIME_ZONE, '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(
      BASE_URL +
        '?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(
      BASE_URL +
        '?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(
    BASE_URL +
      '?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) {
  let totalComments = 0,
    totalLikes = 0;
  for (let i = 0; i < amount; i++) {
    totalComments += parseInt(media.edges[i].node.edge_media_to_comment.count);
  }
  for (let j = 0; j < amount; j++) {
    totalLikes += parseInt(media.edges[j].node.edge_media_preview_like.count);
  }
  const engagementRatio = (totalLikes + totalComments) / followers / amount;
  return {
    mediaCount: parseInt(media.count),
    totalComments: totalComments,
    totalLikes: totalLikes,
    engagementRatio: engagementRatio,
  };
}

function fetch(url) {
  const header = { Cookie: INSTAGRAM_COOKIE };
  const options = {
    headers: header,
    muteHttpExceptions: true,
  };
  const source = UrlFetchApp.fetch(url, options).getContentText();
  const 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.

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.

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 would 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.

Common Errors

• TypeError: Cannot read property "node" from undefined. - Instagram account has less posts than the value of LATEST_POSTS. Change LATEST_POSTS 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 INSTAGRAM_ACCOUNTS.
• 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 recommend having your charts on a separate sheet inside your spreadsheet. You can think of your separate sheet for your charts as a dashboard.

Conclusion

There is a lot more you can do with Google Sheets. You can 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.