How to import social media data into a Google spreadsheet with the ImportXML function

Update January 2017: This article is over a year old and it looks like many of these formulas are no longer working. As I mention in the article, these are brittle formulas that require maintenance and seem to have a short shelf life. I plan to create a comprehensive, updated set in 2017, so keep your eye out!

Google Sheets has a powerful and versatile set of IMPORT formulas that can import data from other websites.

Let’s look at importing data 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 all 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.

The full Google sheet for this exercise is available here.


  1. Linkedin
  2. Pinterest
  3. Alexa rank
  4. Facebook
  5. Twitter
  6. Instagram
  7. Google+
  8. Quora
  9. Reddit
  10. Youtube
  11. Spotify

Linkedin followers

STOP PRESS: since writing this article, this formula is no longer working for extracting Linkedin followers. I’m seeking an alternative and will post here when I find one.

In cell A1, I’ve entered the URL of my Linkedin profile (so go ahead and replace “benlcollins” with whatever profile you’re after):

Then in the adjacent cell, B1, enter the following formula to get the number of Linkedin followers:

=query(IMPORTXML(A1,"//div[@class='member-connections']"),"select Col1")

The QUERY function wrapper is used to select only the number and remove the word “connections” from showing up in the adjacent cell:

linkedin import

Pinterest followers

In cell A1, enter the following URL, again replacing benlcollins with the profile you’re interested in:

Then in the adjacent cell, B1, enter the following formula:

=query(IMPORTXML(A1,"//div[@class='FollowerCount Module']"),"select Col1")

to get the following output:

pinterest import

Update 08/26/16: So it seems my original formula above is no longer working… But fear not, here’s an updated one that is working as of late August 2016:

=substitute(query(IMPORTXML(A1,"//a"),"select * limit 1 offset 5"),"Followers","")*1

Alexa rank

Here there are two metrics I’m interested in – a site’s Global rank and a site’s US rank.

To get the Global rank for your site, enter your URL into cell A1 (replace

and use the following formula in cell B1:

=INDEX(IMPORTXML(A1,"//strong[@class='metrics-data align-vmiddle']"),1,2)

as follows:

Alexa global import to Google Sheets

For the US rank, use this formula:

=INDEX(IMPORTXML(A1,"//strong[@class='metrics-data align-vmiddle']"),2,1)

to get:

Alexa US ranking to Google Sheets


The above IMPORTXML formulas are no longer working, so here’s 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.

For the US rank, assuming the URL is in A1, then in B1:

=trim(query(sort(ArrayFormula(regexextract(query(importdata(A1),”select Col1″)&query(importdata(A1),”select Col2″),”United States Flag.+span.{10,}”)),1,TRUE),”select * limit 1″))

Then in C1, extract the number:


The Global rank formula is even worse, so I’m not posting it here! Both solutions can be found in this sheet:

Facebook Page

Update December 2016: Both of these formulas are working at the moment to import Facebook page likes, where you have the Facebook page url in cell A1:



=substitute(importxml(A1,"//span[@id='PagesLikesCountDOMID']")," likes","")

Update August 2016: the script below no longer works as Facebook have sunsetted that old version of the API. However, it seems the formula is working more reliably these days. Just proving what a dark art these IMPORT formulas are 😉

I’m going to import the followers for a Page that I follow, namely Flowing Data. So I start with the URL in cell A1:

There are two methods here – (attempting to) use a formula or write a small macro program. Well, really there is only one method because the formula has only worked a handful of times for me (maybe 1 time in 100) so I can’t recommend it. I’m only including here for completeness:

=substitute(IMPORTXML(A1,"//span[@id='PagesLikesCountDOMID']")," likes","")

Almost certainly you’ll get an #N/A error “Imported content is empty” so let’s put that to one side and find a better solution.

We’re going to need to write a little bit of code to import Facebook likes. Here I tip my hat to Martin Hassman who created a Facebook likes counter. I’ve used this to create a simplified version with the following steps:

Step 1: Go to menu Tools > Script editor...

Step 2: In the newly opened script editor tab, replace the default code with this new code:

Step 3: Save the script and return to your Google sheet.

Step 4: In cell B1, enter the following formula to get the number of likes for the Facebook page in cell A1:


The result is:

facebook import

Twitter followers

Using the IMPORT functions to retrieve Twitter data is possible, but somewhat temperamental. I’ve listed two formulas below, and usually one or the other will work (but not both at the same time)!

I’ve tried with a number of different twitter handles with followers ranging from a few hundred to a few million.

In cell A1, I put my twitter handle

First try this formula in cell B1:

=substitute(substitute(query(importHTML(A1,"table",3),"select Col3"),char(10)&"Followers",""),",","")

which gives a result:

twitter_v1 import

If that isn’t working for you, then try this formula in cell B1:

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

Note 1: For Twitter handles with extremely large followings, in the millions, this will return a result with millions shown as “M” (e.g. 5,650,000 = 5.65M):

twitter_v2 import

We can use a quick SUBSTITUTE formula in C1 to convert to the equivalent million number:


Note 2: this introduces a degree of inaccuracy, as it records the number to the nearest ten thousand.

Update 8/1/2016:

Thanks to readers for leaving some great suggestions in the comments below. In particular, thanks to Daniel Dirks for sharing his version of the Twitter formula (link in comment here) and to @Yorudan for sharing his awesome Tweet Dashboard (link in comment here).


Updated formula – November 2016:

Put the URL in cell A1:

Then, this formula in cell B1:

=mid(query(IMPORTXML(A1,"//*"),"select Col3"),find("ProfilePage",query(IMPORTXML(A1,"//*"),"select Col3")),190)

and this formula in cell C1:


and finally, extract the follower count in D1:

=value(TRIM(RIGHT(SUBSTITUTE(C1," ",REPT(" ",LEN(C1))),LEN(C1))))

Here’s a Google Sheet with these updated examples.

Prior formula (still works for me, but others have reported issues):

By now you know the drill. Put the URL in cell A1:

and this formula in cell B1:

=REGEXEXTRACT(query(IMPORTXML(A1,"//*"),"select Col3"),"followed_by.{12,}")

and then this formula in cell C1:


which gives a result:

instagram scraper

Oo-er, this one’s a bit funky!

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 formula in cell C1 then extracts the number (120) by finding the “{” and “}” brackets and extracting the number, regardless of how many digits it has.

(Note: it is possible to combine these two formulas into a single cell, but I wouldn’t advocate doing that because it makes 4 calls with the IMPORTXML formula then.)

STOP PRESS: since writing this article, my original formula is no longer working for extracting Instagram followers. I leave it here for reference:


=mid(regexextract(query(IMPORTXML(A1,"//*[@class]"),"select Col3"),"followed_by.{4}[a-z]{5}?..[0-9]{1,}"),23,20)


instagram import

++++++ END OF OLD FORMULA ++++++++


The URL in cell A1:

The formula in cell B1, again using REGEX to extract the specific datapoint I’m after:

=regexextract(regexextract(query(IMPORTXML(A1,"//div[@class]"),"select Col2"),"[0-9]{1,}.followers"),"[0-9]{1,}")

to get the result:

google_plus import

Update 2/18/2016:

A reader shared their workings recently and had an alternative approach to extracting G+ followers. It’s much neater and more succinct and gets my vote! In B1:


The [1] inside the IMPORTXML function returns the first value in the array, which is the followers we’re after. The out INDEX(....,1,1) wrapper restricts the returned value to a single item, since the import formula returned an array with two copies of our follower count. Nice!


In this instance, I’ve imported the number followers Barack Obama has on Quora.

Quora is a little bit different because I need to use the URL and the profile name in my formula, so I’ve kept them in separate cells for that purpose. So in cell A1, add the generic Quora URL:

And then in cell B1, add the profile name:


Then the formula in C1 to get the number of followers is:

=query(importxml(A1&B1,"//a[@href='/profile/"&B1&"/followers']"),"select Col2")


quora import

Bonus: to get the number of answers, use this formula in cell C1:

=query(importxml(A1&B1,"//a[@href='/profile/"&B1&"/answers']"),"select Col2")


Here, I’m using the space subreddit as my example. In A1:

To get the number of followers of this subreddit, use this formula in cell B1:



reddit import

Bonus: To get the number of active viewers:


with the result:

reddit_v2 import

YouTube subscribers

To get the number of subscribers to a YouTube channel, use this formula in cell B1:

=mid(query(IMPORTXML(A1,"//div[@class='primary-header-actions']"),"select Col1"),31,20)

To get a channel’s total views, try this formula on the about page:


where you would have URL like this in A1:

Youtube channel views 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.

Add this URL (the API endpoint) into column C:

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

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

=mid(query(importdata(D2),"select Col1 limit 1 offset 7"),9,25)

Here’s this process in action:

spotify import

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:

spotify JSON data

Read more about the Spotify API and find other end points here:

And the detailed information on the get artist information end point we’ve used in this example:

Closing thoughts and a caveat to finish

These formulas are unstable and 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 whether the XML is well formed on the destination page, and therefore whether the Google formula can parse it, but I don’t pretend to know the precise details).

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 the formulas no longer work. So things change!

To summarize: Caveat Emptor!

As always, leave any comments, corrections or request other social media channels below.

See also this basic introduction to web scraping using these IMPORT formulas.

116 thoughts on “How to import social media data into a Google spreadsheet with the ImportXML function”

  1. This is awesome! Thanks for putting it together.

    One metric I’m interested in adding is YouTube Subscribers, any chance you would know how to do this already?

    1. Thanks Michael, glad it was helpful!

      Try this formula to get YouTube subscribers:

      =mid(query(IMPORTXML(A1,"//div[@class='primary-header-actions']"),"select Col1"),31,20)

      where your YouTube account url is in cell A1.

        1. Nice work! Interesting to see that it takes a minute or so to load all of the data, with that many formulas.

          Also, I like your G+ formula, it’s much neater and more succinct than mine, so I’ll update the article. Cheers!

    1. Me too, can’t see anything that has changed in the source code though. Sometimes it grabs the followers counts, sometimes not. It’s a temperamental system as advertised 🙂

    2. Hey Dave,

      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.


  2. Hi Ben

    I found a code that works, not sure why


  3. Trying to post some code here, but it seems to disappear in the replay. Let’s try again…

    Copied the actual number (XPath) from Inspect view on followers page:

    Source from followers page:


  4. Hi and thanks for sharing this Ben!

    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! Mike

    1. Hi Mike,

      I understand there used to be a limit of 50 in the old Google Sheets (see here:, but it was removed in the new Google Sheets, although there still seems to be some confusion (see:!topic/docs/AYGh1hZXKgI).

      Do the formulas give you any error messages? You could try running them in several separate Sheets and then aggregating into one master sheet using IMPORTRANGE() formulas.


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

        Hope that helps with further analysis! 🙂

        Best, Mike

  5. Thanks again. It works perfectly. Would you happen to know why the Twitter one gives perpetual error (sometimes it shows and others it disappears).

    Would you have an alternate string.


    1. Hi ssen,

      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.

      Cheers, Ben

  6. Hi! Ben,
    I’m super happy with this post that you made. Two days ago I noticed that Instagram code (probably with their update) its making GSheets crash so bad.
    Will you help me to figure it out?


    1. Hi Ben,

      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?


    2. Hi Luisa!

      Yes, I had the same problem when I revisited my sheet. The code on Instagram’s website has probably changed. Thanks for letting me know! I’ll work on it and see if I can find an alternative 😉


      1. 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!


  7. Hi Ben

    Thank you — and all your commenters — for doing the hard work on this and sharing. This is an absolutely fantastic resource!

    As with other commenters, i’ve noticed the Regex for the Instagram formula returns quite a strange error.

    Finally, i’m wondering how often this data is automatically updated from the source? In searching around Google for how to get social media data into Sheets, I found this website which has similar formulas, and offers a script to re-fetch the data:

    Would that be necessary? Or is simply reloading the Sheet each time its reopened enough to fetch any updated data?

    Thanks once again, this really is a fantastic post!

    1. Hey Jamie,

      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.


  8. Hi Ben,

    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?


    1. Hey Sophie, thanks for stopping by and leaving a comment. Unfortunately the instagram formula as described had stopped working. I’m working on a fix tho! 🙂


      1. Hi Ben,
        I’m experiencing the same problem as Sophie; in some rows it gives results, but in most of them it returns to

        “#VALUE!” – “Error
        Unable to parse query string for Function QUERY parameter 2: NO_COLUMNCol3”

        I am using it for number of likes and comments of the posts in Instagram.

        Any help?

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

          Hope that helps!

          1. 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:


            So like this:

            Instagram extract


  9. Hi Ben,

    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.

    1. Hi Vladimir,

      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”).


  10. Hi Ben,

    could it be that the instagram formula is not working anymore? It would be cool to update this one.


    1. Hi Daniel,

      Hmm, seems to still be working for me. 🤔

      Are you using the updated formula I posted?

      In cell A1:

      In cell B1:
      =REGEXEXTRACT(query(IMPORTXML(A1,”//*”),”select Col3″),”followed_by.{30}”)

      In cell C1:

      On my sheet, this one appears to still be working fine. The previous formula I posted had stopped working.

      Otherwise, sometimes re-pasting the formula resolves the issue, or adding/removing the final “/” on the url, e.g. try “” without the “/” at the end.

      Let me know if it works for you.


  11. I have a problem…

    the Facebook script

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

    and thank you alot dear

    1. Hi Mohamed,

      Hmm, very strange. I’ve occasionally had a script stop working for a short period of time, but usually start working again within a few hours. I’ve not had a time issue like this one though.

      If it’s still happening, I’d suggest posting a question on the Google forums to see if anyone there can help you out:!topicsearchin/docs/category$3Aspreadsheets or try posting to Stack Overflow:


  12. Hi Ben,

    the twitter formula is working at the beginning, but then suddenly you get a #value error that i can’t get rid of anymore. Do you know how to solve this?

    Thank you so much!

    1. Hey Daniel,

      Yes, sometimes these import formulas stop working 😕

      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 “” with the “/” at the end, then remove the last “/” and try “”, or vice versa.

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


        1. Nice work Daniel!

          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.

          Going to go away and spend sometime decoding this now 😉

  13. 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:

    Heart @yorudan

    1. Thanks @yorudan! Great work with your TweetMasterDash spreadsheet, impressive stuff. Clever idea to pull from the mobile URL to get around that 10K or 10M abbreviations. Thanks for sharing!

      Cheers, Ben

  14. Hi Ben,

    this morning I realized that the fb_likes function is not working anymore. Are you experiencing the same problem? It says “TypeError: Cannot call method “getText” of null. (line 8).”


      1. Hi Ziya,

        See my comment to Daniel above. That version of the api is no longer supported! Will post a fix if/when I find one!


    1. Hi Daniel,

      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:

      REST API is deprecated for versions v2.1 and higher (12)

      Also confirmed on the following FB page:

      Facebook API v2.0 deprecated


      Working on a solution, though I fear it will be more complex and involve authenticated calls to the API. Great project for the future though!


        1. Hey Jake,

          Both of these formulas appear to be working at the moment to import Facebook page likes:



          =substitute(importxml(A1,"//span[@id='PagesLikesCountDOMID']")," likes","")

          Thanks. I need to update the post!


  15. Hi Ben,

    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:

    =IMPORTXML(, “//meta[@name=’description’]/@content”)

    returns the string:

    “BuzzFeed. 8.189.336 likes · 1.507.877 talking about this. Worth sharing.”

    REGEXEXTRACT gets the value of likes/followers out of the string, here for values up to hundreds of millions:


    (which for sure can be optimized).


    1. Hi Jo,

      This is AWESOME! Thank you for sharing your solution with the rest of us, worked perfectly for me. Here’s an alternative to your regex formula:


      and the whole thing can be combined into a single formula:


      I’m sure it can still be optimized!

      Cool to see you use the VALUE function as well. I’d forgotten about that, so have been using this trick to convert to numbers =A1*1.

      Here’s a sheet with all of these setup.


    1. Thanks for the heads-up. Minor tweak to the REGEX formula and it’s working again. The new formula is:

      =REGEXEXTRACT(query(IMPORTXML(A1,"//*"),"select Col3"),"followed_by.{12,}")

      and then, to extract the exact follower count:



    1. Hey Steven, I’ve updated the instagram formula now and it works on any size account for me. Hopefully that fixes it for you too.


    1. Yes, you can. Great solution. I guess I had QUERY on the brain when I was writing all these different formulas, so missed the better solution to use INDEX. Will update the post to reflect. Thanks!

  16. Ben,
    Your formula to grab the subscribers count on YouTube works great! Is there anything similar that will show a channel’s total views?

      1. Brilliant! I’ve never paid much attention to the “about” page and didn’t realize that info was there. It works like a charm. Thanks for your help!

  17. Hello, trying tiny coping facebook method and always getting a “TypeError: Unable to call the method “getText” of the object null. (line 6).” message. Please, link me for updating info, best and thank in advance.

    1. Hi! The custom Facebook formula no longer works because they’ve changed the API. However, the other formulas listed in the article using the IMPORTXML method are still working.

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

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

  19. Hi! This is a big help. However, I can’t seem to get Pinterest’s to work any longer- even the update you posted in the end of August.

    Any help would be appreciated! Thanks!

    1. Hey Daria,

      The updated formula:

      =substitute(query(IMPORTXML(A1,"//a"),"select * limit 1 offset 5"),"Followers","")*1

      is still working for me. The Google Sheet was out of date though, which I’ve updated just now.

      Try adding or removing a final “/” to the address – sometimes these IMPORT formulas get “stuck” with cached data, so you need to make Google think it’s a new URL and get the formula to re-import.

      Hope that helps.

      Cheers, Ben

  20. I am also having a problem getting the Pinterest followers. Using the code you provided I get this error.

    Function MULTIPLY parameter 1 expects number values. But ‘8.5kPins’ is a text and cannot be coerced to a number.

    I can of course take out the *1 at the end and it shows the text value. But I am wanting to of course perform analysis on the numbers

    1. Hey James,

      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:

      =value(substitute(substitute(query(IMPORTXML(A1,"//a"),"select * limit 1 offset 3"),"kPins","00"),".",""))

      Note – this pulls out the number of pins, it’ll be different if you want the number of followers:

      =value(substitute(substitute(query(IMPORTXML(A1,"//a"),"select * limit 1 offset 5"),"kFollowers","00"),".",""))

      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.

      Hope that helps!


  21. Hello Ben

    Thank you very much for sharing these techniques.
    Might there be a way to extract the likes for one certain Facebook post, not the likes for the account?

    Thank you in advance,


    1. Hi Roselle,

      Haven’t tried this but I think this will require using script to access the API, which is a lot more complext than the IMPORT formulas. If I get time I’ll look into it tho and update you.


    1. 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?


      1. Hey Daniel,

        So that example has 0 followers right? So the HTML is different and the original formula can’t parse this example. A slight modification to the formula picks out the zero count:

        =REGEXEXTRACT(query(IMPORTXML(A1,"//*"),"select Col3"),"follows.{12,}")

        The original formula is working fine otherwise for me. If you have any other accounts you know where the formula isn’t working then let me know. The fix above may work in those cases too.


        1. Dave – can you share the Instagram example that’s not working? Formula still working fine for me, so I need to see what the error is.

          In the meantime try this slight modification, which might do the trick:

          =REGEXEXTRACT(query(IMPORTXML(A1,"//*"),"select Col3"),"follows.{12,}")

          Let me know if that works…

          Thanks, Ben

          1. Hey Ben,

            No joy, I got this error message on column c, row 1 – “In FIND evaluation, cannot find ‘}’ within ‘follows”: {“count”: ‘.”

  22. Ben,

    Looks like Alexa formula needs a tweak. There are now two strong tags with class = “metrics-data align-vmiddle”. And the first one also has an html comment in it (not sure if that is what breaks it).

    IMPORTXML returns “Imported Xml content can not be parsed”.

    I’m trying to get just the US rank, but I still struggle with understanding xpath queries.


      1. I’d love to see a solution to this as well. It looks like they’re pointing to their API for a solution now; was that there before?

        Thanks for opening my eyes to all sorts of new possibilities with Sheets, Ben! Between this and your page scraping article, I’ve got a lot of fun new things to try!

    1. Hey Dave,

      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.

      Here’s the US rank formulas, with URL in A1, then in B1:

      =trim(query(sort(ArrayFormula(regexextract(query(importdata(A1),”select Col1″)&query(importdata(A1),”select Col2″),”United States Flag.+span.{10,}”)),1,TRUE),”select * limit 1″))

      Then in C1, extract the number:


      The Global rank formula is even worse! Here’s a link to a sheet with the solution:


    1. Hi Jamie,

      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.


  23. Hey Ben,

    The Instagram update was working fine for awhile but now it’s not returning the followers count, just the following.

    “ProfilePage””: [{“”user””: {“”username””: “”uidesignpatterns””, “”country_block””:
    null, “”connected_fb_page””: null, “”follows””: {“”count””: 5820},
    “”requested_by_viewer””: false, “”followed_by””: {“”count”””

    1. Hey Dave,

      So you’ll need to change the formulas that parse this “ProfilePage” block. Try putting these two formulas in the adjacent cells:

      In A1:

      In B1: =mid(query(IMPORTXML(A1,"//*"),"select Col3"),find("ProfilePage",query(IMPORTXML(A1,"//*"),"select Col3")),190)

      In C1: =trim(regexreplace(regexreplace(B1,"[[:punct:]]",""),"[[:alpha:]]",""))

      In D1: =value(TRIM(RIGHT(SUBSTITUTE(C1," ",REPT(" ",LEN(C1))),LEN(C1))))

      Here’s a link to a working example in a spreadsheet:

      Hope that helps!


  24. Hi, Ben,

    Is it possible to grab likes count from a specific Instagram publication?

    For example I tried to parse viewer_has_liked from your photo: — with formula: =REGEXEXTRACT(query(IMPORTXML(A1;”//*”);”select Col3″);”viewer_has_liked.{25}”)

    But the result was only: viewer_has_liked”: false, “nodes”: [{“use
    There is no: count”: 26.

    Thanks for your articles, Ben!

  25. Hi Ben,

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

    Again thank you! This really has saved me a lot of time 🙂


    1. I just answered my q1 with an extra cell using replace/substiute formula it’s this in the French version of sheets: =SUBSTITUE(A1;”,”;””;)
      and this in the English version; =SUBSTITUTE(B6,”,”,””,)

    2. Hey Elena,

      Catching up on the blog comments now! Great that you’ve solved question 1, nice work 🙂

      For Q2: Hmm, I’m getting errors with the Instagram formulas now, so looks like I’ll need to rework them. I’ll try to get round to some kind of update article in early 2017…

      For Q3: Have a look at this post about saving social media data using a little snippet of Apps Script. I think that should do the trick for you.

      Hope that helps!


  26. Hi Ben,

    Why are you using the xyz function even if we get the number of followers by using pqr function?

    xyz = query(IMPORTXML(A1,”//a[@data-nav=’followers’]”),”select Col2″)

    pqr = IMPORTXML(A1,”//a[@data-nav=’followers’]”)

    1. Hi Sowmay,

      The QUERY() wrapper function is used to pull out the follower number only, because the output of the IMPORT formula spreads across two columns, something like:

      Followers | 1,919

      So the QUERY just returns 1,919 and removes the word “followers”.


  27. Hi Ben,

    This is amazing work 🙂

    Is there a way to pull the latest tweet date? I’d like to pull that to show when the last time an account tweeted.

    Many thanks

Leave a Reply

Your email address will not be published. Required fields are marked *