Google Sheets and Data Studio Dashboard Course

Google Sheets Data Cleaning and Pivot Tables Course

How to import social media statistics into Google Sheets: The Import Cookbook

Google Sheets has a powerful and versatile set of IMPORT formulas that can import social media statistics.

This article looks at importing social media statistics from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: these formulas occasionally stop working when the underlying website changes, but I will try to keep this post updated with working versions for the major social media statistics.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: File > Make a copy....

Import Twitter data

Start with the Twitter handle URL in cell A1, e.g.

https://twitter.com/benlcollins

Here is the formula to extract follower count:

=QUERY(IMPORTXML(A1,"//a[@data-nav='followers']"),"select Col3")

Pro-tip: If you find this formula gives an error, wrap it in an IFERROR function and call the IMPORTXML function a second time, but add a “/” to the end of the Twitter handle. This should then pull out the follower number for you (it requires that your Twitter handle in cell A1 does NOT have a “/” at the end):

Note, for large numbers of followers, the number will be in the format 250.2k for hundreds of thousands or 32.7m for millions, in which case we need to use some IFs and SUBSTITUTEs to deal with this, so our formula becomes:

I wasn’t able to import the precise “followed_by” class so I’ve used some REGEX to extract the followers count tag from the data I’ve imported.

In a nutshell, the REGEXEXTRACT formula extracts this snippet: followed_by": {"count": 120}, "country_bl. The second REGEXEXTRACT formula then extracts the the digits and finally VALUE converts to a number.

One further datapoint we can grab from the instagram page is the link, with this formula:

The following screenshot shows this formula (actually shows a prior version of this formula):

Pro-tip: If you find this formula gives an error, wrap it in an IFERROR function and call the IMPORTXML function a second time, but add a “/” to the end of the Google+ handle. This should then pull out the follower number for you (it requires that your Google+ handle in cell A1 does NOT have a “/” at the end):

Import Spotify Artist followers

Here’s a method for extracting the number of followers an artist has on the music streaming site Spotify.

First, find your favorite artist on Spotify and right-click their name. From the menu that pops up, choose “Copy Spotify URI”. Paste that into your Google Sheet in column B, and delete the “spotify:artist:” string to leave just the ID, as shown in the image further down this post.

So you should have an artist name in cell A1:

Metallica

and the artist’s unique URI in cell B1:

2ye2Wgw4gimLv2eAKyk1NB

Add this URL (the API endpoint) into cell C1:

https://api.spotify.com/v1/artists?ids=

and then combine the artist URI and API endpoint (columns C & B) with this formula, to put the full URL into column D:

=C1&B1

Then put the following formula into column E to extract the followers:

The following screenshot shows these formulas (click to view larger version):

Note, for the technical amongst you, copy the full API url (column D) and paste it into a new tab of your web browser. There you’ll see the raw JSON data before we import into Google Sheets, and you can clearly see the followers count:

Sites that don’t work and why not

I’ve tried the following sites but the IMPORT formulas are unable to extract the social media statistics:

Linkedin (see above)

Similar Web

Twitch

Mobcrush

Crunchbase

Angel.co

Majestic SEO

These are all modern sites built using front-end, client-side Javascript frameworks, so the IMPORT formulas can’t extract any data because the page is built dynamically in browser as it’s loaded up. The IMPORT formulas work fine on sites built in the traditional fashion, with lots of well formed HTML tags, where the social media statistics are embedded into the site markup that is passed from the server.

Compare this screenshot of the source code for Mobcrush, built using Angular JS it looks like (click to enlarge):

versus what the source code looks for this page on my website (click to enlarge):

You can see the code for my site has lots of tags which the IMPORT formulas can parse, whereas the other site’s code does not.

If anyone knows of any clever way to get around this, do share!

Otherwise, you’re next option is to venture down the API route. Yes, this involves coding, but it’s not as hard as you think.

Also, even when these formulas are working, they can be temperamental. If you work with them a lot, sooner or later you’ll find yourself hitting this loading issue all the time, where the formulas stop displaying any results:

Closing thoughts

These formulas are unstable and will sometimes display an error message.

I’ve found that adding or removing the final “/” from the URL can sometimes get the formula working again (the issue is to do with caching).

I can make no guarantee that these will work for you or into the future. Whilst researching this article, I came across several older articles where many of the formulas no longer work. So things change!

I’m getting an #N/a error with my Linkedin import formulas too. The actual error message is telling me “Could not fetch url”, so the import function isn’t able to access the Linkedin url, so Linkedin must have changed something on their side I think. Am looking for a solution too! I’ll update the article if I can find an alternative solution.

I set up my Google Spreadsheet as described in your article. It worked like a charm when I tested it for about 10-20 profiles at a time.

But once I expanded the formulas to several hundreds/thousands of profiles the fields just remained blank. Even the fields that were displaying follower counts just fine before went empty after refreshing.

Is there a limitation on queries? Does anybody elseexperience this problem?

Thanks for the reply Ben! I will have a look into using several separate sheets and then aggregating into one master sheet using IMPORTRANGE() formulas.

Regarding error messages:
– Facebook works like a charm, even with a thousand accounts (unsing the script)
– Twitter returns a zero even though the account clearly has more followers
– Instagram and YouTube remain blank, no error message though

Sometimes adding or removing the final “/” from the URL will make the formula work again. Caching can occasionally prevent the formulas functioning properly, so adding/removing the “/” effectively creates a new url, which forces the formula to fetch the data again. Also, these formulas are somewhat volatile overall, so will break if the structure of the webpage changes or the function cannot parse the webpage.

I’ll post any alternatives here if I find them, so keep an eye out.

A more robust way to get hold of social media data would be to use Google Apps Script to build a basic app that is connected to each service’s api, but this is obviously a great deal more work. It’s something I’ll write a post on in the future.

Big fan of the codes as it has helped me tremendously. I’m experiencing a similar issue that Luisa is experiencing. The formula does not seem to work for Instagram, and displays the follower count as “#N/A”. Also, I’ve noticed a large amount of code text located to the left-hand side of the page whenever I click on the follower count. Would you be able to assist?

And Michael, ditto for your comment. Thanks for stopping by and letting me know. I had the same weird chunk of text on the left side of my Google Sheet. I’ve removed the Instagram formula for now, pending an update!

Thanks for stopping by and leaving a comment. Yes, the instagram and linkedin ones have stopped working right now. It’s a temperamental function!

I think that whenever you open or refresh your worksheet the import formulas will go fetch the latest data, although I understand that caching can sometimes mean the most recent data is not fetched. If you have your Google Sheet open and the data changes on the underlying website (e.g. you gain a follower), then this will NOT automatically update in your sheet. You’d need to write a small script, per that (excellent) Geckoboard article.

Fellow developer here :). Thank you so much for your contribution, it really helps.

Question tho, when I use your formula for Instagram, the spreadsheet gave me an error of “#VALUE!” – “Error
Unable to parse query string for Function QUERY parameter 2: NO_COLUMNCol3”. Also, when I look at your demo, the value of Instagram on B8 is written “#N/A”. Is that normal?

Hi! Are you using the latest formula: =REGEXEXTRACT(query(IMPORTXML(A1,"//*"),"select Col3"),"followed_by.{12,}")? It’s been updated from the one which was causing issues for Sophie.

It’s working well for me. Note that these IMPORTXML formulas are somewhat limited, so won’t work well if you’re trying to extract data for thousands of profiles at once.

Other things you could try:
– Replace “Col3” with “C”, this sometimes works instead.
– Try adding a trailing backslash “/” (or removing it if you have one) to the instagram url. This forces the formula to re-run and overcomes the issue of these formulas sometimes not working because the data has been cached.

Sure thing, so “Col3” refers to the third column of the data that is imported by the IMPORTXML formula. This is the column that contains the follower information. The “{12,}” is regular expression in the REGEXEXTRACT formula, which extracts the snippet of data relating to the follower count.

You can use this formula in the adjacent cell to then extract the count from the string:

Thanks a lot for the article! Unhappily, it does not seem to be working for retrieving data from Twitter. I get the following error: Impossible to analyse the parameter 2 of Query, QUERY : NO_COLUMN: Col2.

Yes, I’ve had this happen before too. Sometimes deleting and re-copying the formula fixes the situation. Otherwise, try changing “Col1” to “A”, “Col2” to “B” etc. and see if that helps (so your query would be “select B” rather than “select Col2”).

it works good but,
everyday it get error to me from (08:00 PM) to (01:20 AM)
then run good from (01:20 AM) to all day until stoped again !!!
sometime get error from (04:00 PM) or (06:00 PM)…. et.
and get work again in (01:20 AM) !!!

if you have solution to this problem, or can help me with any information.. !

I think one of the issues is with caching, so try re-pasting the formula or adding/removing the final “/” on the url, e.g. if you’re trying “https://twitter.com/benlcollins/” with the “/” at the end, then remove the last “/” and try “https://twitter.com/benlcollins”, or vice versa.

Of the two twitter formulas in the worksheet, the second one seems to be more reliable!

For anyone else reading this, you’ll need to make a copy of the sheet (File > Make a copy…) to see the formula working. In the view-only mode it was showing a #N/A, but then worked perfectly when I made my own copy.

Phenomenal work! There is a lot to play with here. I am particularly impressed that you cracked Instagram – I had messed with that in years past with no dice.

On Twitter, a better route I found to deal with the 10K or 10M abbreviations, is to pull from the mobile URL. It gives precise numbers. You can see this in action in my (ever-evolving) TweetMasterDash: http://bit.do/tweetmasterdash

Thanks for letting me know. Facebook recently deprecated v2.0 of their API, which caused this fb_likes function to stop working. Digging a little further into the fb_likes api call, I found the following error message confirming it:

thanks for the good work – really helped me a lot setting up a google Dashboard for social media monitoring!

For the facebook problem I could find a workaround extracting the likedata out of the meta-data of the fb page. It’s clunky (I think it gets tangled up in the fb security check), but it works in the long run. Using the following formula:

Ben, One last question (where I think I know the answer). Is there a way to grab a range of channel views on YouTube, specifically “Last week,” without getting into API’s? I suspect that that info on the Analytics page isn’t XML. I’ve set my Analytics default to “Last week,” but haven’t had any luck grabbing it. I’ve tried isolating what I think is the target from the html source code, but either that doesn’t work or I’ve isolated the wrong target.

Yes unfortunately I think this is the realm of APIs. I’ve tried a few variations of IMPORTXML but can’t get past a message like this: “One account. All of Google.Sign in to continue to YouTube…..”. I’m sure it’ll be possible with Apps Script and APIs.

So what’s happening here is that the number of Pinterest follower is large enough that they abbreviate the number to show ‘8.5k’ with the ‘k’ representing 1000. So this will require some modification to the formula. Try this, where your Pinterest address is in cell A1:

Also, another note, these two formulas work for the thousands short-hand (“k”). If the Pinterest account has millions of followers, and the account shows e.g. “4.5MFollowers”, then you’ll need to change the “kFollowers” to “MFollowers” and the “00” to “00000” in the above code to make it all work.

I have some issues with the instagram import. It seems that the search for “followed_by” is not unique for every account, because sometimes the formula finds “followed_by_viewer” and that’s the wrong place to search for the “count” element. Does anyone have a solution for that?

Tried and tried, but haven’t been able to find a solution using the IMPORTXML method. So instead I found an alternative using IMPORTDATA and lots of matching array formulas – it’s a monster, so hopefully in time a better solution will surface, but for now it works.

Unfortunately, I don’t think it’s possible because you need to be logged in to see the number of clicks on any shortlinks you’ve created. So the IMPORT formula can’t get past the login to see the data.

Thank you so much for this formula! I’m working on a French version of google and translated it and it mostly works a treat!

I have a few questions for you:

1. Are you aware of a way to remove the commas from the numbers displayed? In French they are considered as points and my totals are messed up slightly.

2. Some of my instagram profile accounts don’t work with the formula for example https://www.instagram.com/rodrigo_noriega/ only brings me back 600 or so whereas the profile has 55.6K followers any ideas as to why?

3. Anyway to copy the values of this say each month or week into a different cell so as to see the progress of one’s social reach as well as not having to enter numbers? I try copying only the value of the cell and it doesn’t want to work :/

– I tried using the IG formula and it worked just in some cases. Doing some sniffing around, I tried this generic formula instead and it worked properly. I hope it helps: =REGEXEXTRACT(mid(query(IMPORTXML(B2,”//*”),”select Col3″),find(“followed_by”&CHAR(34),query(IMPORTXML(B2,”//*”),”select Col3″)),35), “[0-9]+”)

– Now the doubt: The values that I get using these formulas are updated automatically or is there a way to update them manually?

Hey Henrique, I believe they get updated every time you open the sheet (or click into the formula and hit enter). However, I know they sometimes get hung up with cached data, so fail to update. What you can do is add a “/” to the end of the URL to force it to update.

I tried Similar Web but it’s not possible to retrieve data via these IMPORT formulas because of the underlying structure of the website (more details why here). You’d have to look into the API to see if it’s possible.

Hello Ben, so much formula for fb pages and Instagram, but why nothing for the Fb groups ? Even when you are not yet a member of a Fb group you can see the information im trying to retrieve: name of the group, number of member , and Group description .
Any genius willing to help me ?

Actually ended up a little more complicated than I first thought when I dug around. Turns out that the “k” and “m” to represent thousands and millions can have various permutations with decimal places etc. So I ended up working on the formula a bit more and came up with this beast, which I think now gets all the variations correctly:

Thanks for redirecting me here. This really is gold as Andy said above. How did you find out the column #? I would like to get some exact results, as I’m tracking frequency of use. No rush on this answer! I appreciate all you’ve done!

Good question! It’s a bit of trial and error to be honest. I haven’t found a way to automate it yet… I’ll post here if I do.

So try just this plain formula in cell A3 say: =importdata(A1)

From Instagram, copy the exact number of followers (e.g. 53274) and search for that in your sheet (using Ctrl + F, or Cmd + F on Mac) making sure to remove the thousand separators “,”. Then count how many columns that is (quick tip to do this, use the formula =column() in a blank cell in this column to find the number).

Then plug this number back into your original formula and you should be set.

This article has been incredibly helpful and pulling social stats has slashed time my team spends harvesting data each week. Any help here will be much appreciated, and many many thanks to Ben for sharing this amazing article!

Update: it displayed the error for like an hour, then all of a sudden the data showed up… So I tried the formula on a few more pages, and those are still showing the error…
Perhaps it just takes more time than the rest to load..?

Hi Ben,
thanks so much for the help that you gives to all this people.

Just a little question here:

I need a way to extract the hashtags from Instagram in a easy and faster way. So i’m not Good with Google Sheets and i thought that maybe you could help me a little bit.
I was thinking 2 possible way to proceed.

2 – Write the word (for example) “#home” in a cell and automatically the word ‘#home’ go to the url without the ‘#’, so i can write the hashtags in column and see in another column the number of the hashtags.

I hope that you understand what i’m saying, and i apologize in advance for my bad english.
Thanks 🙂

Just wanted to say thank you for so generously sharing your expertise. You saved me a lot of time and heartache… now I just have to find a way to have the data automatically add itself to a sheet on a daily basis.

This is really a helpful post as it helped me extract the count for ‘likes’ on facebook and ‘followers’ on instagram using Google Sheets.

However, I was wondering if you could help me extract the ‘follower’ count on facebook and ‘post’ count on instagram, for example, on public profiles such as adidas originals?

I have a whole list of such sites that I need to extract data from on a monthly basis, and was wondering if you could help me with this task. It will most certainly save me a lot of time and increase accuracy of data extracted.

I tried extracting likes (count) on facebook for adidas (https://www.facebook.com/adidas/) using formula;
=VALUE(SUBSTITUTE(IMPORTXML(A1,”//span[@id=’PagesLikesCountDOMID’]”),” likes”,””))
and all the other formulas listed above. However, despite removing the ‘/’ at the end, I still get “#N/A” as my output.

Your best bet to get reliable and detailed Facebook data, is to write an Apps Script program to connect and fetch FB data, or use a third party service like Supermetrics to do that (see my post on this).

Your best bet to get reliable and detailed Facebook data, is to write an Apps Script program to connect and fetch FB data, or use a third party service like Supermetrics to do that (see my post on this).

I have used the function to get the amount of followers for a bunch of Instagram accounts. Unfortunately, for a few accounts, the formula has given me the amount the account is “following” which is not what I need. I want the amount of “followers”. I have retried both Instagram formulas and am still encountering this issue. Any suggestions on what to do?

very nice overview, thank you for your work! I’m trying to adapt the FB formula to display page likes >1000 but only get the last three digits. They are displayed on the page as, e.g. “3,700 likes” and the formula only grabs the 700, not the 3k 🙂

Probably not, unfortunately, as this sort of information is usually behind a password, rather than on a public page. So you can either write an apps script program to query the API to get the info or use a program like Supermetrics to do that for you.

For Alexa ranking, instead of using a “helper cell” the two formulas can be combined (simply using output of first formula as input for second.

For US rank: With your domain name in B1, i.e. mysite.com
Use the following formula in the cell where you want the rank for the domain name in B1=VALUE(REGEXEXTRACT(QUERY(ArrayFormula(QUERY(IMPORTDATA("https://www.alexa.com/siteinfo/"&B1),"select Col1") & QUERY(IMPORTDATA("https://www.alexa.com/siteinfo/"&B1),"select Col2")),"select * limit 1 offset " & MATCH(FALSE,ArrayFormula(ISNA(REGEXEXTRACT(QUERY(IMPORTDATA("https://www.alexa.com/siteinfo/"&B1),"select Col1") & QUERY(IMPORTDATA("https://www.alexa.com/siteinfo/"&B1),"select Col2"),"title='United States Flag'.alt.{50,}"))),0)),"[\d]{3,}"))

There is also another source to get this data from which allows a much simpler IMPORTXML formula.
With your domain name in B1, use the following to get US rank.

Your post helped me extract ‘likes’ on Facebook and ‘followers’ on Instagram. Regarding likes on Facebook though, the google sheet that I have absolutely refuses to fetch me data sometimes, this is despite removing the ‘/’ at the end and recreating the spreadsheet. Any idea what could be done to avoid this problem?

I was also wondering if you could help me extract ‘follower’ count on Facebook and ‘post’ count on Instagram, for example, on public profiles such as adidas originals? For the moment, to extract ‘post’ count on Instagram I use the formula ‘=IMPORTXML(A1,”//meta[@name=’description’]/@content”)’, run a text to column macro, and then a mid + len formula to extract posts. Not a terrible approach I believe, but something more direct would be always better.

Over and above all this, if there’s a way to get those Facebook followers, it’ll be just fantastic.

Not sure why the facebook formulas aren’t working for you. They’re still ok on my site. Do you have a lot of these IMPORT formulas? Sometimes you can have too many. Sometimes they just give up on you ¯\_(ツ)_/¯

The instagram formula has been updated, and I’ve now included a formula to extract Followers, Following and Posts.

Unfortunately I do not have a formula for the Facebook followers at the moment.

Thank you for these formulas 🙂 Instagram data extraction works perfectly now!
I have about 50 of these import formulas running in one go. So maybe as you said, that’s a little too much for sheets to process in case of Facebook. But now they seem to work again – I guess that’ll do 🙂
As for Facebook follower count; please do keep us posted if you manage to construct a functional formula. Currently, this a monthly nightmare 🙁
Thank you once more!! Your posts are a life saver! 🙂

Thank you so much for putting this together! It was so helpful! I’m trying to do each of these for 1000 different pages. I.e. 1000 fb pages and 1000 twitter handles etc. It is very slow to load progress, and seeing that I have so many to go through and have to update them once a week, I was wondering if there was a way to expedite the loading process. Thanks so much!

Unfortunately it’s going to be super slow because they’re complex and they have to retrieve third-party data over the web. Not sure there’s much you can do, other than ensure you minimize the number of active formulas by turning any data into values (copy > paste special > values) when you’ve finished with it.

Do you know if there is a formula that pulls your Instagram posts by date & time and a formula that shows that particular post’s engagement (eg. likes + comments)? Might be too complicated for Google sheets?

Hey Ben, thanks for all the helpful material 🙂
Just a quick question,
When i use the formula to scrape Instagram followers with big fanbases i get an error for example: “Value parameter 161.k cannot be parsed to number”. Any help is much appreciated 🙂

Thanks for bringing this to my attention! For large accounts in the hundreds of thousands or millions of followers they abbreviate numbers like this. I’ve updated the Instagram formula (click here) to account for this now, although note that we lose some precision because it’s not the exact number.

“To get other data, you’ll need to access the twitter api (custom code) or easier, use a third-party service like Supermetrics to do that step for you.” – I have been trying to extract tweets using R using the custom code, not been successful yet.
Supermetrics is a good tool, I’ll play around with it to see if I am able to extract the data that I require for my assignment.