[Editor’s Note 2024: unfortunately, most of these solutions, which are 4+ years old, no longer work. Modern websites are built dynamically in the browser with JavaScript, and Google Sheets IMPORT functions no longer work in this case. If you’re still looking for reliable formula-style importing capabilities, check out the third-party tool IMPORTFROMWEB.
I’m leaving this article here for reference purposes as it shows interesting examples of XPath constructions.]
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.
Contents
- Youtube
- Alexa rank
- Quora
- Spotify
- Soundcloud
- GTmetrix
- Bitly
- Sites that don’t work and why not
- Closing thoughts
- Resources
How to import social media statistics into Google Sheets with formulas
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 Facebook data
November 2018 update: The Facebook formula is working again! The trick is to use the mobile URL 😉 Thanks to reader Mark O. for this discovery.
Start with the mobile Facebook page URL in cell A1, e.g. this url
https://mobile.facebook.com/benlcollinsData
or this variation of it:
https://m.facebook.com/benlcollinsData
Here is the Google Sheets REGEX formula to extract page likes:
=INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(INDEX(IMPORTXML(A1,"//@content"),2)),"([0-9km,.]+)(?: likes)"),"([0-9,.]+)([km]?)"),,1) * SWITCH(INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(INDEX(IMPORTXML(A1,"//@content"),2)),"([0-9km,.]+)(?: likes)"),"([0-9,.]+)([km]?)"),,2),"k",1000,"m",1000000,1)
The following screenshot shows these formulas:
See the Facebook Import Sheet.
Import Twitter data
This formula is no longer working for extracting Twitter followers and I have not found an alternative.
Start with the mobile Twitter handle URL in cell A1, e.g.
https://mobile.twitter.com/benlcollins
Here is the formula to extract follower count:
=VALUE(REGEXEXTRACT(IMPORTXML(A1,"/"),"(?:Following )([\d,]+)(?: Followers)"))
The following screenshot shows this formula:
Note 1: This Twitter formula seems to be particularly volatile, working fine one minute, then not at all the next. I have two Sheets open where it’ll work in one, but not the other!
See the Twitter Import Sheet.
Build Business Dashboards With Google Sheets
- Learn how to build beautiful, interactive dashboards in my online course.
- 9+ hours of video content, lifetime access and copies of all the finished dashboard templates.
- Learn more
Import Instagram data
This formula is no longer working for extracting Instagram followers and I have not found an alternative.
Start with the Instagram page URL in cell A1:
https://www.instagram.com/benlcollins/
Then, this formula in cell B1 to extract the follower metadata (this may or may not work):
=IMPORTXML(A1,"//meta[@name='description']/@content")
This extracts the following info: “230 Followers, 259 Following, 465 Posts – See Instagram photos and videos from Ben Collins (@benlcollins)”
Next step is to combine this with REGEX to extract the followers for example. Here’s the formula to do that (still assuming url in cell A1):
=INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(IMPORTXML(A1,"//meta[@name='description']/@content")),"([0-9km,.]+)( followers)"),"([0-9,.]+)([km]?)"),,1) * SWITCH(INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(IMPORTXML(A1,"//meta[@name='description']/@content")),"([0-9km,.]+)( followers)"),"([0-9,.]+)([km]?)"),,2),"k",1000,"m",1000000,1)
This deals with any accounts that have abbreviated thousands (k) or millions (m) notations.
Alternative Approach
The following formulas to extract account metrics appear to only work for the instagram account when you are logged in. It makes use of the QUERY function, SPLIT function and INDEX function to do data wrangling inside the formula.
Here’s the number of followers:
=REGEXEXTRACT(INDEX(SPLIT(QUERY(IMPORTDATA(A1),"select Col1 limit 1 offset 181"),""""),1,2),"[\d,]+")
Here’s the number following:
=REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col2 limit 1 offset 181"),"[\d,]+")
Here’s the number of posts:
=REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col3 limit 1 offset 181"),"[\d,]+")
The following screenshot shows these formulas:
See the Instagram Import Sheet.
Import YouTube data
Start with the YouTube channel URL in cell A1:
https://www.youtube.com/benlcollins
To get the number of subscribers to a YouTube channel, use this formula in cell B1:
=VALUE(INDEX(REGEXEXTRACT(LOWER(INDEX(REGEXEXTRACT(INDEX(IMPORTXML(A1,"//div[@class='primary-header-actions']"),1,1),"(Unsubscribe)([0-9kmKM.]+)"),1,2)),"([0-9,.]+)([km]?)"),,1) * SWITCH(INDEX(REGEXEXTRACT(LOWER(INDEX(REGEXEXTRACT(INDEX(IMPORTXML(A1,"//div[@class='primary-header-actions']"),1,1),"(Unsubscribe)([0-9kmKM.]+)"),1,2)),"([0-9,.]+)([km]?)"),,2),"k",1000,"m",1000000,1))
See the YouTube Import Sheet.
Import Pinterest data
In cell A1, enter the following URL, again replacing benlcollins with the profile you’re interested in:
https://www.pinterest.com/bencollins/
Then in the adjacent cell, B1, enter the following formula:
=IMPORTXML(A1,"//meta[@property='pinterestapp:followers']/@content")
to get the following output (screenshot shows older version of the formula, latest one is above and in the template file):
Note, you can also get hold of the profile metadata with the import formulas, as follows:
=IMPORTXML(A1,"//meta[@name='description']/@content")
See the Pinterest Import Sheet.
Import Alexa ranking data
Here there are two metrics I’m interested in – a site’s Global rank and a site’s US rank.
Global Rank
To get the Global rank for your site, enter your URL into cell A1 (replace benlcollins.com):
http://www.alexa.com/siteinfo/benlcollins.com/
and use the following helper formula in cell B1:
=QUERY(ArrayFormula(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2")),"select * limit 1 offset " & MATCH(FALSE,ArrayFormula(ISNA(REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2"),"Global rank icon.{10,}"))),0)+1)
and then extract the rank in cell C1:
=VALUE(REGEXEXTRACT(B1,"[\d]{3,}"))
US Rank
Assuming you have the Alexa URL in cell A1 again, then the US rank is extracted with this helper formula:
=QUERY(ArrayFormula(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2")),"select * limit 1 offset " & MATCH(FALSE,ArrayFormula(ISNA(REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2"),"title='United States Flag'.alt.{50,}"))),0))
and this formula to extract the actual rank value:
=VALUE(REGEXEXTRACT(B1,"[\d]{3,}"))
The following screenshot shows these formulas:
See the Alexa Ranking Import Sheet.
Build Business Dashboards With Google Sheets
- Learn how to build beautiful, interactive dashboards in my online course.
- 9+ hours of video content, lifetime access and copies of all the finished dashboard templates.
- Learn more
Import Quora data
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:
https://www.quora.com/profile
/
And then in cell B1, add the profile name:
Barack-Obama-44
Then the formula in C1 to get the number of followers is:
=VALUE(QUERY(IMPORTXML(A1&B1,"//a[@href='/profile/"&B1&"/followers']"),"select Col2"))
The following screenshot shows this formula:
See the Quora Import Sheet.
Import Reddit data
Here, I’m using the funny subreddit as my example.
In A1:
https://www.reddit.com/r/funny/
To get the number of followers of this subreddit, use this formula in cell B1:
=IMPORTXML(A1,"//span[@class='subscribers']/span[@class='number']")
Bonus: To get the number of active viewers of this subreddit:
=IMPORTXML(A1,"//p[@class='users-online']/span[@class='number']")
The following screenshot shows these formulas:
See the Reddit Import Sheet.
Import Spotify monthly listeners
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: https://open.spotify.com/browse/featured
Copy the URL into cell A1 (it’ll look like this):
https://open.spotify.com/artist/2ye2Wgw4gimLv2eAKyk1NB
(This is Metallica, yeah ?)
Then put the following formula into cell A2 to extract the monthly listeners:
=N(INDEX(IMPORTXML(A1,"//h3"),2))
See the Spotify Import Sheet.
To get Spotify playlist data, add the playlist URL into cell A1:
https://open.spotify.com/playlist/37i9dQZF1DX1lVhptIYRda
And use this formula to extract the number of songs and likes:
=QUERY(SPLIT(REGEXEXTRACT(INDEX(IMPORTXML(A1,"//@content"),5),"^(?:[a-zA-Z. ]+ · Playlist · )([0-9]+ songs · [0-9.KM]+ likes)")," · "),"select Col1, Col3")
Import Soundcloud data
Start with the Soundcloud page URL in cell A1, e.g.
https://soundcloud.com/fleecemusic
Here is the formula to extract page likes:
=ArrayFormula(VALUE(REGEXEXTRACT(QUERY(SORT(IFERROR(REGEXEXTRACT( IMPORTXML(A1,"//script"),"followers_count..\d{1,}"),""),1,FALSE),"select * limit 1"),"\d{1,}")))
Alternative formula:
Here is an alternative formula to extract the page metadata, which includes the likes:
=IMPORTXML(A1,"//meta[@name='description']/@content")
the formula to extract likes is:
=VALUE(REGEXEXTRACT(REGEXEXTRACT(SUBSTITUTE( IMPORTXML(A1,"//meta[@name='description']/@content"),",",""),"\d{1,}.Followers"),"\d{1,}"))
and to extract the “talking about” number:
=VALUE(REGEXEXTRACT(REGEXEXTRACT(SUBSTITUTE( IMPORTXML(A1,"//meta[@name='description']/@content"),",",""),"\d{1,}.Tracks"),"\d{1,}"))
The following screenshot shows these formulas:
See the Soundcloud Import Sheet.
Import GTmetrix data
GTmetrix is a website that analyzes website performance.
You need to grab the correct URL before you can start scraping the data. So navigate to the GTmetrix site and enter the URL and hit analyze. You’ll end up with a URL like this:
https://gtmetrix.com/reports/www.benlcollins.com/BcHv78bP
Those last 8 characters (“BcHv78bP”) appear to be unique each time you run an analysis, so you’ll have to do this step manually.
Then in column B, I use this formula to extract the Page Speed Score and YSlow Score, into cells B1 and B2:
=ArrayFormula(ABS(IMPORTXML(A1,"//span[@class='report-score-percent']")))
and this formula in cell B3, to get the page details (Fully Loaded Time, Total Page Size and Requests) in cells B3, B4 and B5:
=IMPORTXML(A1,"//span[@class='report-page-detail-value']")
The following screenshot shows these formulas:
See the GTmetrix Import Sheet.
Import Bitly click data
Bitly is a service for shortening urls. They provide metrics for how many clicks you’ve had on each bitly link, e,g.
Taking a standard Bitly link (e.g. http://bitly.com/2mmW1lr) and appending a “+” to it will take you to the dashboard page, with the metrics. Then we can use the import data function, a query function and a REGEX function to extract the click metrics.
User clicks are:
=VALUE(REGEXEXTRACT(QUERY(IMPORTDATA(A9&"+"),"select Col1 limit 1"),"(?:user_clicks...)([0-9]+)"))
and global clicks are:
=VALUE(REGEXEXTRACT(QUERY(IMPORTDATA(A9&"+"),"select Col5"),"(?:global_clicks:.)([0-9]+)"))
Clicks from the Bitly network are then simply the user clicks subtracted from the global clicks.
The following screenshot shows these formulas:
See the Bitly Import Sheet.
Import Linkedin data
This formula is no longer working for extracting Linkedin followers and I have not found an alternative.
In cell A1:
https://www.linkedin.com/in/benlcollins/
This formula used to work to get the number of Linkedin followers, but no longer:
=QUERY(IMPORTXML(A1,"//div[@class='member-connections']"),"select Col1")
and the output:
There is no example sheet for Linkedin since the formula is no longer working.
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.
I’ll be posting some API focussed articles soon. In the meantime, check out my post on how to get started with APIs, or for a peak at what’s coming, take a look at my Apps Script + API repo on GitHub.
Loading error
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!
To summarize: Caveat Emptor!
Resources
- A basic introduction to web scraping using the Google IMPORT formulas
- A guide to the QUERY function in Google Sheets
- Google documentation on the IMPORTXML function
- Google documentation on the IMPORTHTML function
- Google documentation on the IMPORTDATA function
- More details on XPath Syntax
- There’s an add-on called ImportFromWeb that is like a better, more powerful version of the ImportXML function. It has a free tier and looks like a really useful tool for anyone who does a lot of web scraping.
As always, leave any comments, corrections or request other social media statistics below.
Great article, thank you! Would be possible to save imported cells to get a historical overview?
Thanks Luka. Yes, it’s possible and not too difficult. Lots of people were asking this same question, so I’ve written a follow-up post on how to do it:
https://www.benlcollins.com/spreadsheets/saving-data-in-google-sheets/
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?
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.
Worked a charm 🙂
You can see how I set this up here https://goo.gl/QDykCU using national rugby union teams as an example.
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!
Ben I really appreciate this article.
I’m having a #na issue with the linkedin code.
Any idea?
Cheers in advance.
Dave
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 🙂
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.
Cheers,
Ben
Hi Any update on IMPORTXML issue, I have trawled search engines looking for solution?
Hi Ben
I found a code that works, not sure why
=importXML(“http://services.runescape.com/m=forum/users.ws?searchname=Pad&lookup=view”,”//p[@class=’user-post-count’]/strong”)
Hi!
Great post!!!
Would be sooo great to have the LinkedIn code working.
Any news?
Best,
Fabian
Is this something you can use?
1,059
/Fabian
Something went wrong.
“1,059“
Hi Fabian, can you paste the formula you used to get this result?
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:
//*[@id=”control_gen_3″]/div[1]/p
or
//*[@id=”control_gen_3″]/div[1]/p/a/span[1]
Source from followers page:
1,068 followers
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
Hi Mike,
I understand there used to be a limit of 50 in the old Google Sheets (see here: http://gappstips.com/google-sheets/google-spreadsheet-limitations/), but it was removed in the new Google Sheets, although there still seems to be some confusion (see: https://productforums.google.com/forum/#!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.
Ben
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
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.
Best
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
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?
Cheers,
Gracias.
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?
Thanks,
Michael
Michael – – Updated the Instagram formula in the post now. Thanks, Ben
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 😉
Ben
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!
Ben
Luisa – Updated the Instagram formula in the post now. Thanks, Ben
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: https://support.geckoboard.com/hc/en-us/articles/207238327-Using-Google-Sheets-IMPORTXML-function-to-display-data-in-Geckoboard#automaticupdate
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!
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.
Thanks,
Ben
Jamie – Updated the Instagram formula in the post now. Thanks, Ben
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?
Thanks,
Sophie
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! 🙂
Ben
Sophie – Updated the Instagram formula in the post now. Thanks, Ben
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?
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!
I’ll try it. Thanks!
Can you just tell me what “Col3” and “{12,}” mean?
Maybe it could help me!
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:
=mid(B1,find("{",B1)+9,find("}",B1)-find("{",B1)-9)
So like this:
Thanks!
Hey ben is there a way to change the FB formula to give you a profile’s friend count instead of likes?
Hi Jodeci,
This solution might do the trick for you (temporarily): http://labs.met.cz/#fbfanscounter
It’s based on something called Facebook Query Language, FQL, which is being discontinued on 8th August: https://developers.facebook.com/docs/reference/fql/
So, back to the drawing board for now!
Thanks,
Ben
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.
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”).
Thanks,
Ben
Hi Ben,
could it be that the instagram formula is not working anymore? It would be cool to update this one.
Cheers,
Daniel
Hi Daniel,
Hmm, seems to still be working for me.
Now it’s working for me! Thanks!
Great!
Hey Ben,
First of all thanks of these! Quite useful stuff!
I’ve been having a problem with the IG formula
=VALUE(IFERROR(REGEXEXTRACT(REGEXEXTRACT(QUERY( IMPORTXML(A1,”//*”),”select Col3″),”followed_by.{15,}”),”[0-9]+”),REGEXEXTRACT(REGEXEXTRACT(QUERY( IMPORTXML(A1,”//*”),”select Col3″),”.{1,}.has_blocked_viewer”),”[0-9]+”)))
Its giving me back the number of people the account follows instead of the followers it has. Do you know why?? What do I need to change in the formula?! Thanks in advance
Hmm, not sure. But try this formula:
=REGEXEXTRACT(REGEXEXTRACT(query(IMPORTXML(A9,"//*"),"select Col3"),"followed_by.{12,}"),"[0-9]+")
which is just a shortened version of that long formula. I’ve checked mine now and it’s giving me the followers count.
Cheers,
Ben
Cool, but it’s a shame because actually the instagram forms doesn’t work anymore.
Do you know how to fix it?
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
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: https://productforums.google.com/forum/#!topicsearchin/docs/category$3Aspreadsheets or try posting to Stack Overflow: http://stackoverflow.com/questions/tagged/google-apps-script
Thanks,
Ben
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!
Daniel
Hey Daniel,
Yes, sometimes these import formulas stop working
Hi Ben,
I found a workaround for the twitter formula: https://docs.google.com/spreadsheets/d/17QruvUVKK1TXc4wI2uplQArwqq2Ppe0qdLOqNIi_j4k/edit?usp=sharing
Thanks,
Daniel
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 😉
This Twitter fix for follower count works! Thanks!!
here is the formula from Daniel’s sheet:
=iferror(substitute(substitute(query(importHTML(D2,”table”,3),”select Col3″),char(10)&”Followers”,””),”,”,””),substitute(substitute(query(importHTML(E2,”table”,3),”select Col3″),char(10)&”Followers”,””),”,”,””))
Replace D2 with the cell containing the https://twitter.com/{username}
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
Heart @yorudan
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
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).”
Regards,
Daniel
Same here http://prntscr.com/c7neab
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!
Cheers,
Ben
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: https://developers.facebook.com/docs/apps/changelog
Boo!
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!
Cheers,
Ben
hey ben,
any update on this?
Jake
Hey Jake,
Both of these formulas appear to be working at the moment to import Facebook page likes:
=value(REGEXEXTRACT(REGEXEXTRACT(substitute(IMPORTXML(A1,"//meta[@name='description']/@content"),",",""),"\d{1,}.likes"),"\d{1,}"))
or
=substitute(importxml(A1,"//span[@id='PagesLikesCountDOMID']")," likes","")
Thanks. I need to update the post!
Ben
do you have any formula that would allow to get the number of member and the title of a facebook group .. i spent a whole night from your page formula but i reached by limit 🙁
for the members its in that code :
1 710 membres
for the title and example :
HERE IS GROUP NAME
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(https://www.facebook.com/buzzfeed, “//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:
=Value(Regexextract(G59,”([0-9]\d{0,3}.[0-9]\d{0,3}.[0-9]\d{0,3}|[0-9]\d{0,3}.[0-9]\d{0,3}|[0-9]\d{0,3}).likes”))
(which for sure can be optimized).
Best,
Jo
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:
=REGEXEXTRACT(REGEXEXTRACT(substitute(B2,",",""),"\d{1,}.likes"),"\d{1,}")
and the whole thing can be combined into a single formula:
=value(REGEXEXTRACT(REGEXEXTRACT(substitute(IMPORTXML(A8,"//meta[@name='description']/@content"),",",""),"\d{1,}.likes"),"\d{1,}"))
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.
Cheers,
Ben
Quick follow up – try adding/removing a final “/” to the URL (https://www.facebook.com/benlcollinsData versus https://www.facebook.com/benlcollinsData/) if the formula stops working for you. This’ll force Google to re-import the data and should solve any “#N/A” error messages.
Hi Ben,
the instagram formula doesn’t seem to work anymore. Do we have any solution for that?
Thx!
Daniel
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:
=mid(B1,find("{",B1)+9,find("}",B1)-find("{",B1)-9)
Cheers,
Ben
The Instagram formula doesn’t work if the user has more than a million followers.
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.
Cheers,
Ben
A more direct method for Alexa is
=INDEX(IMPORTXML(“http://www.alexa.com/siteinfo/”&A2,”//strong[@class=’metrics-data align-vmiddle’]”),2,1) where A2 is http://www.whatever.com
Looks like the comments system added http:// automatically.
Web site in A2 should be just www . whatever . com (without spaces).
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!
Ben,
Your formula to grab the subscribers count on YouTube works great! Is there anything similar that will show a channel’s total views?
Hey Jim,
Sure thing! Try this formula with the about page:
=VALUE(INDEX(IMPORTXML(A1,"//span[@class='about-stat']"),2,2))
where you would have URL like this in A1:
https://www.youtube.com/user/benlcollins/about
Thanks,
Ben
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!
Jim
I see now that you indicate the “about” page in your original post. Reading too quickly!
Jim
Ha! No, I updated the main page once I found that solution so you didn’t miss it first time 😉
Hello,
This worked for Alexa like a charm thank you so much for the amazing work.
Do you know if something similar is possible for
http://www.similarweb.com ?
http://www.GTMetrix.com ?
and http://www.majesticseo.com ?
I tried by changing the classes but apparently it’s not enough…
Thanks again !! Saved me so much time already !
Hi Waina,
Have a look at this article I wrote, which gives a bit more detail on how you can find the class etc: https://www.benlcollins.com/spreadsheets/google-sheet-web-scraper/ and see if that helps.
If I get time to look at those examples I’ll post them here.
Cheers,
Ben
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.
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.
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.
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!
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
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
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!
Ben
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,
Roselle
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.
Cheers,
Ben
Anyone else having with the Instagram import?
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?
Cheers,
Daniel
Hi Daniel, Am working on a fix and I’ll post an updated version here. Thanks for the info!
Hey Daniel,
I’ve updated the article with a new Instagram formula.
Here’s an example in a Google Sheet too: https://docs.google.com/spreadsheets/d/1lBJVPgD_bUPilxkoqiHcYNUms3uSLLcnAP6QPaPL3JU/edit?usp=sharing
Cheers,
Ben
The instagram formular doesn’t work for me, see example here: https://docs.google.com/spreadsheets/d/1RDa7ARIEjV_D0u9d_9J9zmmsuqUKRbVgzcnZFfPGbIY/edit?usp=sharing
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.
Cheers,
Ben
Hey Daniel,
I’ve updated the article with a new Instagram formula.
Here’s an example in a Google Sheet too: https://docs.google.com/spreadsheets/d/1lBJVPgD_bUPilxkoqiHcYNUms3uSLLcnAP6QPaPL3JU/edit?usp=sharing
Cheers,
Ben
Any luck with fixing the instagram follower count script?
Hi Dave, Am working on a fix and I’ll post an updated version here. Thanks!
Thanks Ben 🙂
I’m back to manually entering my following count atm 🙁
Not fun.
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
Hey Ben,
No joy, I got this error message on column c, row 1 – “In FIND evaluation, cannot find ‘}’ within ‘follows”: {“count”: ‘.”
Hey Dave,
I’ve updated the article with a new Instagram formula. Here’s an example in a Google Sheet too: https://docs.google.com/spreadsheets/d/1lBJVPgD_bUPilxkoqiHcYNUms3uSLLcnAP6QPaPL3JU/edit?usp=sharing
Cheers,
Ben
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.
Cheers,
Dave
Thanks Dave, I saw this as well. Am working on a fix and will post here once it’s done.
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!
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:
=value(regexextract(B1,”[\d]{3,}”))
The Global rank formula is even worse! Here’s a link to a sheet with the solution:
https://docs.google.com/spreadsheets/d/17Zcwuxxpm72elqE8QgA54qSAjz6OnSKsB2RMpP_GA_s/edit?usp=sharing
Cheers,
Ben
Thanks for your code, Ben. Used it.
Hi Ben,
Please could you assist me in coming up with the correct xPath for importing the number of Total Clicks from the analytics pages of goo.gl shortened links
For example here: https://goo.gl/#analytics/goo.gl/sHt37t/all_time
Thanks!
Jamie
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.
Ben
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”””
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:
https://www.instagram.com/benlcollins/
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: https://docs.google.com/spreadsheets/d/1lBJVPgD_bUPilxkoqiHcYNUms3uSLLcnAP6QPaPL3JU/edit?usp=sharing
Hope that helps!
Ben
Thanks for your code, Ben. Used it.
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: https://www.instagram.com/p/BNTMO8eg_Z_/ — 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!
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 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 :/
Again thank you! This really has saved me a lot of time 🙂
Elena
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,”,”,””,)
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!
Ben
Thank you Ben!
I look forward to reading more,
Elena
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’]”)
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”.
Cheers,
Ben
But this is not showing the correct/updated value in my case
The value saw when inspecing the browser page was correct , but the query returned an old/cached value .
Even suffixing the random number didn’t help
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
Hi! Just commenting to be notified when you have the new Instagram update 🙂
Thanks in advance!
Amy – post updated now 🙂
I’ll also look for the new update!
Henrik – post updated now 🙂
Amazing!
Hi Ben,
Is there a way you could include streaming sites? Twitch and Mobcrush? We really want to collect an accurate follower count.
Hi Sarah,
Can you send example URLs for a user for each site? I’ll try to include them in the next update.
Cheers,
Ben
Hey Sarah,
So I tried both of these sites but unfortunately I couldn’t extract data with the IMPORT formulas. 🙁
They’re built with client side Javascript, so don’t really have the metrics data in a structured XML or HTML format for the formulas to extract. You’ll have to see if they have APIs you can use.
Cheers,
Ben
Great article! Helped me a lot.
I have a comment and a doubt:
– 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?
Thanks!
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.
Cheers,
Ben
Hello Ben,
Thank you so much for the update everything is working great! Would you have a suggestion on how to extract the estimated unique views of a website via, say, similar web?
Thank you for all your help!
Elena
Hey Elena,
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.
Cheers,
Ben
Hi Ben,
Is there any tricks to get the live cricket score from websites like cricbuzz.com etc. to in the spreadsheet. And is it possible to automatically get refreshed.
Hope i get the answer.
Thanks,
Hey Krishna,
I’m looking at the current live score of the KXIP vs RCB match here: http://www.cricbuzz.com/live-cricket-scores/18128/kxip-vs-rcb-8th-match-indian-premier-league-2017
This formula extracts the current batting score and run rate:
=IMPORTXML("http://www.cricbuzz.com/live-cricket-scores/18128/kxip-vs-rcb-8th-match-indian-premier-league-2017","//div[@class='cb-min-bat-rw']")
Hope that helps!
Cheers,
Ben
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 ?
Hi,
i tried implementing the code for facebook to get page views and total reach for my page but I am getting #REF error. What to do?
Ben! This is tremendously helpful, thank you for sharing your expertise.
On Instagram, is there a way to pull the “likes” and “comments” reported from recent Instagram posts from an Instagram main page the way you pulled Instagram followers?
Thanks,
-Brian
Hey Ben,
My twitter one was working fine but now it just returns the word ‘Followers’ instead of the number. Can you think of a solution to this?
Hi Sam, I had the same problem. The fix is pretty easy: change ‘select Col2’ in the formula to ‘select Col3’ and you’re back on track.
Great fix Ruben. Thanks for jumping in!
The script for twitter is not working for me. Anybody can help me out? At the meantime, does anybody has a script for retrieving weibo followers count? Thanks in advance
Hey Matthew, see my response to Sam Moore’s question: the fix is pretty easy: change ‘select Col2’ in the formula to ‘select Col3’ and you’re back on track.
Thanks again Ruben. I’ve updated the post to reflect this now. Cheers, B
You’re welcome Ben. Your post is really helpful and has saved me hours of time. Glad I could be of help in return.
Has anyone figured out the script for Weibo?
this rules. you rule. thank you!
Ha, thanks Kris! 😉
Hi Ben,
Is there a way to extract the amount of posts under a certain hashtag for Instagram?
e.g. “Home” (https://www.instagram.com/explore/tags/home/)
would return 77.578.514?
Kind regards.
Morten
I need this, too!
It would be great if possible.
If it helps. I am currently experimenting with:
=VALUE(IFERROR(REGEXEXTRACT(REGEXEXTRACT(QUERY( IMPORTXML(B19,”//*[@id=’react-root’]/section/main/article/header/span/span”),”select Col3″),”followed_by.{15,}”),”[0-9]+”),REGEXEXTRACT(REGEXEXTRACT(QUERY( IMPORTXML(B19,”//*[@id=’react-root’]/section/main/article/header/span/span”),”select Col3″),”.{1,}.has_blocked_viewer”),”[0-9]+”)))
Which returns: “Imported content is empty.”
I believe this is unfortunately down to the way that the Hashtag pages are built, being mostly Javascript and dynimac loading.
Though since I am not an expert on the subject, I could also be wrong.
See reply above to Morten’s original comment. Hope that helps!
Hi Morten,
I’ve found two ways to do it – one is reliable but loses a bit of accuracy, whilst the other extracts the correct number but is a fragile formula and needs changing for different hashtags.
Method 1:
=value(substitute(substitute(regexextract(substitute(IMPORTXML(A1,"//meta[@name='description']/@content"),".",""),"\d{1,}.\d{1,}."),"m","00000"),"k","000"))
and the output should be: 77,700,000
This formula should work for different hashtags, e.g. I tried it with #data and got 327,900
Method 2:
This formula will give the exact result, but relies on a specific column reference which changes for different hashtags, so there is not guarantee this always work:
=value(regexextract(query(sort(query(importdata(A1),"select Col242"),1,true),"select * limit 1"),"\d{1,}"))
which gives the result: 77,763,164
For the data hashtag, I have to change the reference to Col242 to Col224, as follows (in bold):
=value(regexextract(query(sort(query(importdata(A1),"select Col224"),1,true),"select * limit 1"),"\d{1,}"))
So it’s not really ideal. There may be a way to search for the column, but haven’t had time to work on that yet.
Hope that helps!
Cheers,
Ben
Hi Ben,
That’s perfect. The first one is accurate enough for me. One thing I did run into is with #Denmark and #Fotos. For some reason for both of them I get the error:
—
Function REGEXEXTRACT parameter 2 value “\d{1,}.\d{1,}.” does not match text of Function REGEXEXTRACT parameter 1 value “27m Posts – See Instagram photos and videos from ‘fotos’ hashtag”.
—
Which to be honest, I am a bit clueless about what means.
Ah ha! The culprit here is that the extract has ’27m’ with no decimal place (before it was 77.7m) so we should change the formula very slightly to this:
=value(substitute(substitute(regexextract(substitute(IMPORTXML(A1,"//meta[@name='description']/@content"),".",""),"\d{1,}.\d{0,}."),"m","00000"),"k","000"))
I’ve changed the second \d{1,} to \d{0,} (bolded above) which fixes this issue.
Cheers,
Ben
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:
=value(substitute(if(iserror(search(".",REGEXEXTRACT(IMPORTXML(A1,"//meta[@name='description']/@content"),"[\d]{1,}[\D]{0,1}[\d]{0,3}.{0,1}\s"))),substitute(substitute(REGEXEXTRACT(IMPORTXML(A1,"//meta[@name='description']/@content"),"[\d]{1,}[\D]{0,1}[\d]{0,3}.{0,1}\s"),"k","000"),"m","000000"),substitute(substitute(REGEXEXTRACT(IMPORTXML(A1,"//meta[@name='description']/@content"),"[\d]{1,}[\D]{0,1}[\d]{0,3}.{0,1}\s"),"k","00"),"m","00000")),".",""))
Phew!
Hi Ben, the last formula was so smooth, I used it a lot. But it doesn’t work anymore. What could be the reason? Is there any solution?
Thanks to your talent.
Hi Ben,
Thank you for all the info this is truly gold! I was wondering is there any way to use these formulas for posts as well? for example to get the number of likes and comments? Thanks in advance!
Possibly! Which particular service? It’s hard to go beyond the basics with these formulas alone though, and to really get any data you want then you’ll want to be connecting to the API with apps script or using a tool to do this (Supermetrics is my choice, e.g. see this article on using it with Facebook data).
Cheers,
Ben
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.
Hope that helps!
Ben
Hey Ben, it seems the Pinterest formula is not working anymore. We have only a handful of followers but the formula returns a 0 (so not the *k issue here) . Your sheet also shows a 0.
I tried changing the column numbers and hacking the formula a bit as I did with Twitter, but without the desired result this time. Any ideas on how to fix it?
Thanks for the heads up Ruben!
I’ve changed the formula to this, which seems to work better:
=IMPORTXML(A1,"//meta[@property='pinterestapp:followers']/@content")
I’ll update the article shortly.
Cheers,
Ben
Hi Ben! The pinterest formula doesn’t seem to work anymore. I’m getting “Resource at url contents exceeded maximum size.”
But the other formulas I’ve used from here are doing great. Thank you so much for you help with these!
How can I get the number of Facebook followers?
Thanks for this usefull article
Hi Ben
I have tried to import number of visits from Similarweb with using of Importxml function, Without success.
Can you help me ?
Thanks
Hi Karim,
Unfortunately it’s not possible to extract Similar Web visits with these IMPORT formulas. See this section of the article: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#notWorking
Hi, I’e been using the formula listed here to pull Spotify followers into a google sheet and recently it stopped working for all of them. Is anyone having this issue? Here is a sample
A1 »» Weyes Blood
B1 »» 3Uqu1mEdkUJxPe7s31n1M9
C1 »» https://api.spotify.com/v1/artists?ids=
D1 »» =C3&B3
E1 »» =VALUE(MID(QUERY(IMPORTDATA(D1),”select Col1 limit 1 offset 7″),9,25))
When I click the link in D1 this is what shows up:
{
“error” : {
“status” : 401,
“message” : “No token provided”
}
}
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!
whoops! One tiny syntax correction:
D1 »» =C1&B1
Hi Warren,
I believe the issue is related to Spotify requiring authentication for all api end points now… see here: https://twitter.com/SpotifyPlatform/status/865319073281912836
I’m on paternity leave at the moment, will take a deeper look when I return to work. If anyone else has an answer, please share! 🙂
Cheers,
Ben
Good Morning,
I was just curious if you were able to look in to the Spotify “access token” issue. Thanks! Great article!
Any news?
Thank you for this article Ben! It’s amazing.
I was also curious if you’ve had a chance to look into getting the Spotify import working again.
Hi Ben,
This is an awesome post, super helpful! The Twitter code is not working for me though: it’s returning an error that says “Imported Xml content can not be parsed.”
Any ideas on how to fix this?
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..?
Yes, they can be funny like that. Pretty volatile! 😉
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.
1 – A formula to extract the word (for example) “#home” from this url ( https://www.instagram.com/explore/tags/home/ ) and see it in a single cell.
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 🙂
Hi Marco,
Here goes:
1) For specific examples like the one you shared, this formula will extract the final word assuming that the Instagram URL is in cell A1:
=left(RIGHT(A1,len(A1)-39),len(RIGHT(A1,len(A1)-39))-1)
This won’t work if the start of the URL is different however. In that case you could try this formula:
=ArrayFormula(lookup(2,1/(transpose(split(A1,"/"))<>""),transpose(split(A1,"/"))))
2) For the other way around, supposing you type the word “home” into cell A1, this formula will give you the URL in another cell:
="https://www.instagram.com/explore/tags/"&A1&"/"
Hope that helps!
Ben
Hi Ben. Thanks for sharing this post. Really Helpful! Further, can you help me in importing statistics form MEDIUM? Thanks again.
Hey Kritesh, can you share the Medium URL and the particular statistics you’re after?
Hello Ben,
here’s a Medium publication whose stats could be extracted:
– # followers
– # writers
– # editors
– individual story’s meta
Reference URLs:
https://medium.com/the-mission/about
https://medium.com/the-mission/latest
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.
Again, much appreciated.
Thanks Robert! 🙂
Have a look at this post, might be what you’re looking for: https://www.benlcollins.com/spreadsheets/saving-data-in-google-sheets/
Hi Ben,
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.
Would appreciate your feedback.
You deserve a big fat kiss for this post! Superb!
Hi Ben,
Again, one more question – more of an issue that I ran in to.
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.
Any idea what is it that I could be doing wrong?
Thanks again.
I am having the exact same issue as Diana. Also, I’ve tried to copy your google sheet but it still doesn’t deliver the follower counts. 🙁
Thank you for your help!
Flora
In the last few days I’m getting an “#N/A” error for the Facebook Likes most of the times. Before it worked quite stable … I wonder if Facebook changed anything?
What is the best/ most stable formular for grabbing Facebook Page Likes at the moment?
Thanks in advance!
This would be really useful if anyone can fathom out the changes to the API access and how we get around it?
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).
Cheers,
Ben
Hey Sepp,
As you’ve noticed these formulas are somewhat volatile! 😉
Your best bet, to get reliable Facebook data, is to write an Apps Script program to connect, or use a third party service like Supermetrics to connect (see my post on this).
Cheers,
Ben
hi ben, is there a formula to track likes, shares and comments for a particular facebook post instead of the page itself?
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).
Cheers,
Ben
Hi Ben,
This has been extremely helpful. Just a quick question…
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?
How do I get hashtag numbers statistics on instagram?
Hey Ervin,
Try the formula in this comment: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#comment-33035
Also, the comments in this thread above discuss the Instagram hashtag data import.
Cheers,
Ben
Hi Ben,
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 🙂
Do you have an idea what I need to change?
Thanks!
-F.
Oh, never mind – it seems to be working now. Not sure what did it, but suddenly the other formula
=value(substitute(importxml(A13,"//span[@id='PagesLikesCountDOMID']")," likes","")
produced the desired results.
Sweet!
Hey, thanks for the formulas. I wonder, can you also adapt the formula to extract Ad data, such as costs, clicks, etc.? Thanks.
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.
=IMPORTXML("https://data.alexa.com/data?cli=10&url="&B1,"//COUNTRY/@RANK")
Or use the following for Global rank
=IMPORTXML("https://data.alexa.com/data?cli=10&url="&B1,"//POPULARITY/@TEXT")
Hey Dave,
Thanks for commenting. I decided against combining the formulas because it became so obnoxious and long! Thank you for persevering!
But woah, I much prefer the new formulas you’ve submitted! Much neater and simpler. I wasn’t aware of these endpoints. Thanks for sharing 😉
Cheers,
Ben
Hi Ben,
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.
Hey Diana,
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.
Cheers,
Ben
Hi Ben,
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! 🙂
Kind Regards,
Diana
Great and will do! Thanks 🙂
Hi Ben!
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!
Hey Betsy!
That’s a lot of complex formulas to run!! Your poor computer
Hi Ben
Thanks for this – fantastic!
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?
Cheers
You can do it with IFTTT and Ben’s formulas.
I have done this and a lot of other things for my Instagram.
Sounds cool! Nice work 😉
Can you share any more details for the audience?
Yeah I would love to know how you’re using IFTT to calculate engagement for a profile. Essentially we could use Ben’s formulas for pulling followers and then using IFTT to pull the posts total comments + likes and divide it by followers x 100. Going to attempt to figure this out!
Hey Chrysti,
This is beyond the power of a formula, which leaves you with two options:
1) Write an apps script program to connect to Instagram’s API and grab the data you want (haven’t tried this API, but here’s a general getting started guide to APIs with Google Sheets).
2) Use a third-party (paid) service like Supermetrics to connect and they create the connection for you.
Cheers,
Ben
There is a third way too! Thanks to cutecatskittens comment above, this sounds like it’s possible using the If This Then That service, which is free. I don’t know the details of this however 😉
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!
Hey Spyros,
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.
Cheers,
Ben
Hi Ben,
Is there a code I could use to extract tweet data from twitter. I am working on a university assignment on finding a correlation between social media and an athletes performance.
Hey! You can get a lot of analytics data from this page but only for your own account (change to your twitter handle):
https://analytics.twitter.com/user/benlcollins/tweets
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.
Cheers,
Ben
Hi Ben,
Thank you for your reply.
“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.
Regards,
Shalin
Can you please help me for extracting Soundcloud and Youtube likes count?
See: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#soundcloud for Soundcloud
And: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#youtube for YouTube
Ben, this is amazing! Is there any way to import Telegram and Slack members? For Slack, without creating an account because I’ don’t trust public Slack channels at this point.
Unfortunately, it’s probably not possible with these formulas. Do you have an example url for each service? It needs to be a publicly facing web page, and you can only scrape data that is not behind a membership wall.
https://telegram.me/funscience
If you have that url in cell A1, then this formula will extract the number of members:
=value(substitute(regexextract(importxml(A1,"//div[@class='tgme_page_extra']"),"[0-9 ]+")," ",""))
Hope that helps!
Ben
Yes, its helps. Thank you
I’ve integrated your formulas into a Google Sheet that is receiving data from TypeForm via Zapier so when someone submits their Instagram URL it would be great for data to autogenerate. I can’t seem to figure out how to populate each of these formulas as a new entry comes into this Google Sheet.
Hi Ben!
Thanks so much for this super helpful article.
I’m getting this error message for a few of my fb pages, but the formula is working for others.
Function REGEXEXTRACT parameter 2 value “\d{1,}.likes” does not match text of Function REGEXEXTRACT parameter 1 value “FACEBOOK PAGE NAME HERE. 1.3K likes.”
Any idea what this means and/or how to fix it?
Thank you in advance!
Hmm, that is strange. They are quite temperamental, so it’s not uncommon to have them work one day but not the next. You can try adding (or removing) a final slash “/” to your urls and this resets the formulas which sometimes helps.
Hey Ben,
thx for this article, it’s awesome.
I have a big problem, I tried to copy all the formula and your entire file too, but an error appears: “Imported Xml content can not be parsed.”
Can you help me? It’s very strange!
Bro, words can’t describe how much I love your Instagram G spread sheet! Thank you so much for that awesome piece of work – you just saved me hours, if not days of manual research!!!
Pure value, much love.
Tim.
Amazing overview Ben, thanks a lot!
An idea for facebook followers (tested & works):
=iferror((index(split((index(Importxml(EJ2, "//div[contains(div, 'follow this')]"),13)), " "), 1, 1)), "")
For medium:
=iferror((substitute((SUBSTITUTE((index((importxml(EF5, "//div[@class='buttonSet u-noWrap u-marginVertical10']/a[2]")), 1)), "K", "00")),".", "")), "")
For twitter your formula didnt work for me, so I used this one:
=IFERROR({(substitute((substitute((index(IMPORTXML(EL2,"//span[@class='ProfileNav-value']"), 3)), "K", "00")),".", ""))}, "")
and I am working on telegram now. If you have any suggestions how to simplify one of these formulas, let me know 🙂
here is telegram, that was an easy one:
=iferror((substitute((substitute((index((IMportxml(EE2, "//div[@class='tgme_page_extra']")), 1)), " ", "")), "members", "")),"")
Hey Yunus,
Thanks so much for your input here! I’ll take a look at these and update the article.
Cheers,
Ben
Hello Ben,
This is working for Facebook likes:
=REGEXEXTRACT(SUBSTITUTE( IMPORTXML(A2,"//meta[@name='description']/@content"),",",""),"\d{1,}\w{1,}\s\w{1,}")
Hope that this help people!
Thank you, this worked for me! 🙂
Thanks for sharing your solution Giordano!
Hi Ben,
Do you happen to know if there’s a way of important likes/shares on individual posts that updates automatically? I know I can download from insights but it’s still a very manual process for me filtering out the posts that I want.
Thanks in advance!
Ellie
*importing!
Hey Ellie,
I don’t know a way to do this with these IMPORT formulas. They’re somewhat limited. You’ll need to look at using a third-party tool like supermetrics, or creating a script to talk to the api, to do this.
Cheers,
Ben
Do you know if it is possible to show a youtube videos play count? I want to keep track of a number of videos in one sheet if possible.
Thanks
Andrew
Hi Andrew,
Unfortunately I don’t have a formula that extract youtube video play count.
So you could try using a third party paid service like Supermetrics or code something yourself with Apps Script, like in this example: https://developers.google.com/apps-script/advanced/youtube-analytics
Ben
Hi Ben,
This page is epic thank you!
I’m having some trouble getting Twitter follower count to work.
It works sometimes but I’m trying to find a way to populate it as 100,000 instead of 100k, 1,500,000 instead of 1.5m etc.
Are there any solutions to display the actual follower count?
Thank you
Hey Jago,
Use a quick substitute formula to do this, e.g.
=SUBSTITUTE(B1,"M","")*1000000
For a full example of how to incorporate with the IMPORT function, have a look at the Instagram section 🙂
Cheers,
Ben
How could I apply the same formula in each cell down the entire column without having to drag or copy the formula each time I add a row? Would it be something similar to …
Facebook
=ARRAYFORMULA(IFERROR((INDEX(SPLIT((INDEX(IMPORTXML(I2, “//div[contains(div, ‘follow this’)]”),13)), ” “), 1, 1)), “”))
Thank you!
Thank.
I want. Please also explain how to add flip board followers and linked in followers.
Hey Andy,
I used to have a working Linkedin formula but it stopped working last time they updated their site. Haven’t found an alternative yet, will post it here if I do. I’ll add Flipboard to the shortlist for the future updates.
Ben
Hi Ben,
As you know from many people thanking you this post is gold, you did an amazing job giving it free to us, thanks a lot.
I have been using the instagram hashtag count for over a month and noticed that today the hashtag count stopped for hashtag that have less than a 20,000 (odd number I know).
I can’t seem to understand why… Any idea?
Tristan
Hmm, not sure, it’s still working for me for accounts under 20k. You could try adding or removing a final back slash from the url ( “/” ) to force it to recalculate…
Ben
Hi Ben – trying to extract instagram followers into google sheets for quite a large group, about 1500 handles (all the accounts I follow) but the formula is delivering errors 90% of the time.
Wondering if there is a computing power issue with doing this many, or if Instagram has changed their API? Can’t seem to identify why it works for some and not others – have checked all the instagram links to make sure they are current and working
Also forgot to mention that the formula to correct 12.9k followers into number format is delivering some weird results.. e..g 12.9k has come up as the wrong number, 900 odd thousand?
How can I use this to create a single sheet with Facebook & Twitter metrics?
Hi Ben, awesome article! It helped me a lot!
One question: Is it also possible to track Facebook Event Pages? So that Google Sheets will give me the number of people going/interested in an event? With your formula google sheets is only giving me the likes of a FB page..
Maybe you can help me with that 🙂 Thanks!
Hi this is magic! I’m using it to count my Instagram followers on 6 accounts, but your Followers formula returns a X,XXX text which I cannot perform mathematical calculations on! The Following formula is fine. How do I modify the text to number? Formatting the cell doesn’t seem to work.
Hi Debbie, did you find a solution for this problem?
Is it possible to use regex to crawl data that’s in a script? e.g. for Instagram – because you can get a lot more insights from that…
window._sharedData = {“activity_counts”:
…
“edge_followed_by”:{“count”:16931}
Recently the Twitter’s Followers count is returning “#N/A”
Any solution for that ? Earlier it was working fine.
I have tried on different sheets but with no luck.
Has twitter changed their webpage code ?
This is seriously the IMPORTXML stat bible! 😛
I’m having problems getting the Facebook like count to update. And it happens with some pages only for some reason.
This works for instance (some times it returns #N/A though):
=REGEXEXTRACT(SUBSTITUTE( IMPORTXML(“https://www.facebook.com/benlcollinsData”,”//meta[@name=’description’]/@content”),”,”,””),”\d{1,}\w{1,}\s\w{1,}”)
But if I change to another page like “https://www.facebook.com/TappingIntoWealthCoachTraining” it always shows #N/A.
Any ideas on what’s causing this and how to fix it?
I’ve also experienced that a formula works in one Google Sheet, but when I copy it to another Google Sheet (editing the references of course), it shows #N/A.
Here’s the updated Facebook formula: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#facebook
Hi Ben,
I’ve been able to replicate and adapt your code regarding Twitter’s follower count and it works great, thanks a lot! I immediately decided to see if I could do this with followers name or bio, but I’m struggling to get a result.
Am I doing something wrong?
=query(IMPORTXML(“https://twitter.com/TwitterFrance/followers”,”//span[@class=’username u-dir'[1]”))
Any ideas on what’s causing this and how to fix it?
How can I use this to create a single sheet with Facebook & Twitter metrics?
Hi Ben, awesome article! Really helpful!
One question: Is there a way to also extract the link to the businesses website using this method? I’m sure there is, I guess the problem are my XPath skills…
Thanks,
Niko
Hi Ben, awesome article! Really helpful!
Any ideas on what’s causing this and how to fix it?
Reddit returns a N/A. Any idea?
I’ve looked at this article a thousand times, and thank you for all of it! But…I’m here with bad news. Looks like the Facebook formula isn’t working. It returns an error “Resource at url contents exceeded maximum size”, both on my sheet and yours.
Agreeing that this is happening.
Hi @Ben, I’m having the same problem and can’t find a solution. Do you have any idea on how to solve this? Thank you!
Here’s the updated Facebook formula: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#facebook
Hi Ben,
Do you know how often this refreshes the data in Google Sheets?
Thanks,
Connor
Amazing! Finally one for IG that works 🙂 Thanks a lot
Hello 🙂 I have kind of a specific question:
Is there a way to build a script that calculates the average number Likes on a given Instagram Profile, then only selects posts that have more then average likes and sorts them by performance?
Hi Ben. any update on the FB import formulae?
Yes! Here’s the updated Facebook formula: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#facebook
Yeah if you take a look at the data returned from Facebook it seems they are now blocking these requests from Google sheets which is a huge shame.
Here’s the updated Facebook formula: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#facebook
Hello Ben,
Thanks a lot for your great article.
We have tried the below formula to extract the followers from twitter for a particular page.
cell.setFormula('=QUERY(IMPORTXML(\"'+url+'\","//a[@data-nav=\'followers\']"),"select Col3")');
where url is the twitter page url
It returns the value of followers of that page , as expected.
But the value is incorrect . It is not exactly equal to the value in the twitter page.
But when we inspected the twitter page in the browser , the particular html tag was actually showing the updated and correct value.
Can you please help on this.
Hello Ben,
Thanks a lot for this awesome article
The below code which I have tried is not showing updated/correct values for twitter followers count
cell.setValue('=QUERY(IMPORTXML(\"'+url+'\","//a[@data-nav=\'followers\']"),"select Col3")');
We also tried the random number parameter as a suffix to the url
Even that didn’t help
Can you please help on this
Hey,
thanks for updating the Facebook likes formula. It still seems a bit buggy since in some cases, the page just shows the text as:
XXX people like this (e.g. https://mobile.facebook.com/aveeno/) – in this case, the formula works great and captures the number as XXX.
However, in some cases, the page shows the number of likes as:
John Doe and XXX people like this page (e.g. https://mobile.facebook.com/Eau.Thermale.Avene.Israel/?_rdr) – in this case, the formula breaks and captures only part of the number.
Would be great if this can some how be fixed and shared everyone
Hi,
Got really excited that there was an update for the Facebook formula but it works for some pages, it doesn’t for others 🙁
Really looking forward for a new one, crossing fingers it won’t take long!
Thank you to everyone making these super useful magic formulas work!
Clare – can you share an example of a FB url that doesn’t work so I can check it out? Thanks!
Hi Ben,
I created this sheet with 3 different Facebook pages:
https://docs.google.com/spreadsheets/d/1MLz1mzHuTSjwD69DLcTMr2u9pZhQj_l3INMRFFZoUWg/edit?usp=sharing
One seems to work fine. It other reports the nrs in thousands (so, 75.000 displays only 75). And the third doesn’t even work.
And I copied the formula to another sheet, and some work, others don’t. Others don’t refresh. Just can’t “trust” the numbers it is pulling.
Hey Clara, thanks for sharing that. I’ve updated the formula to account for this issue I hope. Turns out that some of the data comes back with 75k (or m for millions) which wasn’t being picked up. Any thousand or million numbers with the k or m abbreviations should be converted into full numbers now. Updated version of your spreadsheet here: https://docs.google.com/spreadsheets/d/11pBUM9bx3g1_yXYHESZWcBKe7YHbaqvy0iRYFub4OYY/edit?usp=sharing
Cheers,
Ben
Hi
I would like to follow up on this. Our page description shows 1.7k likes so it only displays 1 in the cell. Any workaround for that? The link is https://mobile.facebook.com/campaignOAAT
Thank you!
Hey Omer, the updated formula in this post is now working for the aveeno example you shared.
Cheers,
Ben
Hey, Ben!
I can’t get the Instagram follower formula to work with large numbers (even the big one). Do you think you could take a look?
Sometimes it gives out an error saying MULTIPLY parameter 1 expects numbers but got text instead.
And sometimes it generates a wrong, much bigger number.
I’ve used the following with B18 being the cell that contains https://www.instagram.com/mcdonalds/:
=if(isnumber(search(“k”;REGEXEXTRACT(IMPORTXML(B18;”//meta[@name=’description’]/@content”);”(.{1,})(?: Followers)”)));substitute(REGEXEXTRACT(IMPORTXML(B18;”//meta[@name=’description’]/@content”);”(.{1,})(?: Followers)”);”k”;””)*1000;if(isnumber(search(“m”;REGEXEXTRACT(IMPORTXML(B18;”//meta[@name=’description’]/@content”);”(.{1,})(?: Followers)”)));substitute(REGEXEXTRACT(IMPORTXML(B18;”//meta[@name=’description’]/@content”);”(.{1,})(?: Followers)”);”m”;””)*1000000;REGEXEXTRACT(IMPORTXML(B18;”//meta[@name=’description’]/@content”);”(.{1,})(?: Followers)”)))
For some reason the result is 43162000000 (they have many followers but not this many).
Can you help? Thanks!
Hello Ben
This article is very interesting! I started out building a Google sheet like yours and am having problems using the Facebook data import. I am hoping you can help me understand what is wrong.
When I paste in the formula to count FB likes, Google sheets produces the following error:
Error
Function REGEXEXTRACT parameter 2 value “(\d*\.**\d+)([km]*)” does not match text of Function REGEXEXTRACT parameter 1 value “noarchive”.
I notice a difference between our FB page URLs which might be related to this issue: mine has periods in the URL string.
Any help or guidance you could provide would be appreciated!
Michael – can you share the FB page url? Feel free to email me if you’d prefer ben@benlcollins.com
Ben
Thanks for the amazingly quick response!
I am a little paranoid about web security, so I have opted to e-mail you my FB page URL.
Thanks so much for being understanding, writing great articles like this one and just being so helpful!
Your efforts are appreciated!
Sincerely
Michael Wangerin
Had the same problem with period in the URL string. I just used encoded character in the URL instead (%2E), and that seems to be working much better 🙂
Hi Ben,
Thanks for the great article!! Really informative and super useful. I hope you can help me with my issue; I would like to be able to get the number of profile visits to my twitter page. Any way to do this?
Hey Ben,
Awesome insights. However, this is not really a scalable solution: when you combine so`me of them in a medium-sized URL database, importXLM just turns crazy. Are there any other solutions/scripts to simply pull usernames (when not visible via the URL itself) from the URL?
Daniel
Thats really good.
I’m interested in video views in Twitter, facebook, Instagram and YouTube, I just know the formula of youtube to get the vid views:
=value(REGEXREPLACE(text(importxml(A2,”//*[contains(@class, ‘watch-view-count’)]”),0),” view(s)?”,””))
Anyone can help to share the formula to get video views from the video link of twitter, facebook and Instagram?
example: https://twitter.com/BleacherReport/status/1086847886208499718
can i get the video views formula?
Hi Ben,
First of all – WOW, THANK YOU! I work at professional sports league and we have been trying to aggregate public data of our affiliates’ social accounts for months and failed to find a solution until now. With the number of accounts climbing into the hundreds now, it was impossible to do manually so I searched for weeks to find a software that does this. I am convinced that either A) a software that does this using APIs does not exist or B) I have officially lost the ability to Google search such a platform when needed. If anybody knows of such a platform, PLEASE link it to me, we are willing to evaluate whatever cost it takes.
Anyhow, this article saved us and has made this so much of an easier process. However, it is very important that we be able to consistently pull this data every month or so, meaning the volatility of these formulas are the buzzkill. There are always for some reason just a few links on each platform that come back with an error code. Would you mind looking at my sheet and telling me what went wrong/how we can fix it? I would really appreciate it.
https://docs.google.com/spreadsheets/d/1yZNBkKJI9K5-cMVG3QLgxjA97ZKGD_M0MWJdY9N7hLk/edit?usp=sharing
Thanks a TON!
Alex
WOW.. fantastic work. I have question if you also have formula also of Goodreads overall data rating of an AUTHOR: For instance this one (https://www.goodreads.com/author/show/566.Paulo_Coelho) it has around 2,613,278 ratings overall. And how about IMDb ratings for film/TV (http://www.imdb.com/title/tt0111161) overall rating is 2,050,177.
Thanks,
John
Hi Ben,
No words to explain my gratitude.. This saved lot of time for me. Your original post was published like 2yrs back, but still it helps several people in saving time and effort.
I tried my luck to write a similar formula for pull location info for instagram. Is there a way to do it? Is it possible?
Thanks,
Arun
Thanks Ben.
Apologies am new. How do i make it copy all my data? i have 5 rows of various social media stats. Currently it only copies the first.
Appreciate!
This is such a great resource, thank you!
I’m having trouble scraping likes from Facebook pages – some pages work, but many are displaying incorrect info, and others are #N/A. Is there an update?
Awesome work and code!! Works almost perfectly although I have noticed that the Facebook code seems to be hit and miss.
I’m monitoring 11 pages, the vast majority seem to be okay although some accounts are showing things like 2,000 rather than 20,000.
Weirdly this doesn’t affect all of them though, for example an account that has 64,000 shows 64,000.
Any ideas what could cause this?
I’m having this issue as well. And similarly it seems to be an issue where we have 2000 likes and we’re showing 200 instead. Any other page with a number of likes that doesn’t begin with 2 seems to work fine.
It’s happening exactly the same to me. Anyone can help me with this problem please? Thanks a lot!
PS: Great article, thanks for sharing!
Hi, these formulas are working great for us. I’m having an issue with exactly one of our FB pages’ likes that I’m tracking. It has 2092 followers. I’m using this:
=VALUE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(JOIN(“”,REGEXEXTRACT(LOWER(SUBSTITUTE(INDEX(IMPORTXML(B3,”//@content”),2),”,”,)),”(\d*\.*\d+)([km]*)”)),”\.”,),”k”,”00″),”m”,”00000″))
The output is showing as 200 likes. All other pages that have less than 2000 likes are showing correctly. Any fix?
Thank you.
Hi Ben,
again thank you for your great work – I was repeatedly visiting your site and was very grateful to see that facebook works again – great fix and idea @Mark!
Regarding the other comments for the weird behaviour: My worksheet also sometimes produced only e.g. 2400 instead of 24000 likes. I think there is a bug in your formula: at the end it should say […,”k”,”000″),”m”,”000000″))] instead of […”k”,”00″),”m”,”00000″))]; both k and m miss a zero, if I am correct? With this it works perfectly for me again.
The erratic behavior seems to be that sometimes the pages do not show the k but absolute numbers.
I had a problem with instagram followers formula and got it fixed by using another reference to number of followers past 10K
=VALUE(REGEXEXTRACT(REGEXEXTRACT(QUERY( IMPORTXML(A1,”//*”),”select Col1″),”userInteractionCount.{15,}”),”[0-9]+”))
Sweet! Thanks for sharing
Hey Ben,
This is awesome. I have another question. Is there a formula, where I can track account followers, but from a Instagram post URL?
In your formula you enter the profile URL and from that meta data, you get followers, etc.
What if I only have a post URL (they usually don’t contain the meta data for the authors followers). Is there a way how I can solve this?
Warmest,
Chris
Hey Ben,
This is fantastic! Is there a way how I can extract the Instagram profile URL from a specific Instagram post? Or convert it in two or more steps?
E.g.
Post URL: https://www.instagram.com/p/BwjT0CKBTcN/
Profile URL https://www.instagram.com/yvana.muradin/.
If I only have the specific post URL, how can I get the profile URL without having to click on the post in Instagram and then copy paste the profile URL into my sheet?
Warmest,
Chris
Hi Ben,
thank you for formula. Do you think, that is possible create Instagram diameter of likes (and comments) maybe on last 3 post on profile? 🙂
Or diameter of total post likes? 🙂
Thank you! 🙂
Hi Ben,
Your information has been extremely invaluable. I really appreciate you posting this. We are in the beginning of using Data Studio and social media is a big part of that. I loved the ability to have the auto trigger to update the information automatically by appending a line for tracking history in Google Sheets and that is working like a charm. The problem I’m running into and can’t find a solution for is how to reflect this in Data Studio. When I try to connect it, it automatically gives me a sum of all the numbers in the column, but not just the last number of followers. This applies to Instagram, Facebook, Pinterest, and Twitter sheets. I want the historical data and ultimately would love a way to pull a month by month comparison in data studio, but I cannot locate any formulas to help do this. Do you have any suggestions?
Thanks so much!
Are the statistics updated automatically?
Hi Ben,
The Alexa ranking does not work since 15-May-2019. Checked on multiple computers, phones and also your own file (was worried I got blocked by Amazon – but does not work anywhere). A script change or a total ban?
Any recommendations?
Thanks, Martin
Thank you.
Hi Ben,
First of all, thank you for your formula’s! It’s really valuable and helps us with getting insights in our followers.
One problem I ran into was when for example a Facebook page has 87.650 likes, the formula generates 8,700 likes. Do you have a solution for this problem?
Thanks! The Instagram and Twitter are working great but the Facebook isn’t returning anywhere near the correct number of total likes. Not sure where to start looking.
Hi Ben!
Awesome work you have here! I tried your FB formula and it worked just a moment ago but now it returns a #REF! error with the message, “Function INDEX parameter 3 value is 2. Valid values are between 0 and 1 inclusive.”
I randomly tried replacing the parameter 3 with 0, 1 and 0.5 but those returned #N/A. Perhaps let’s see if anyone of us could find a fix for this. 🙂
I got the same results and now this month none of the formulas run. Any ideas Ben? I’m wondering if Google or maybe FB made changes that broke this formula.
Can someone help me with a Script to Collect Playlist follower data?
Thank you.
Don’t suppose there’s a workaround for age-restricted pages on Facebook, is there? Looking to track follower counts for 27 whiskey distilleries and most are age-restricted, which seems to lead to “#N/A” content in the fields (the specific error states “Function REGEXEXTRACT parameter 2 value “([0-9km,.]+)(?: likes)” does not match text of Function REGEXEXTRACT parameter 1 value “log into facebook to start sharing and connecting with your friends, family, and people you know.”)
Hi Ben,
I was wondering if there was a way of getting the date of the last post for an Instagram so that I can pick out inactive ones?
Cheers,
Chris
This is great! I’m using the Spotify monthly listener import formula, and while the value I get is close to the value I see on the Spotify app it’s not exact. (For J. Cole, the formula gives me 23061675 vs. what I see on the app, 23059005. Why is that? Can anyone help me out?
Hi Ben,
I’m looking for the codes to get a specific song’s streams on Spotify and Soundcloud. Also the Youtube Page views is showing 0 for any account I try. Is there a fix for this?
To see the correct figure for the number of subscribers on Youtube, you must use the current formula:
=MID(QUERY(IMPORTXML(A1,”//div[@class=’primary-header-actions’]”),”select Col1″),31,20)
Then do another formula to remove the K:
=IFNA(LEFT(A2,LEN(A2)-1)*CHOOSE(MATCH(RIGHT(A2,1),{“K”,”M”},0),1000,1000000),A2)
how would i type the entire thing out ?
Hello I am getting an error when trying to set this up for Instagram and others for that matter.
I get “Function REGEXEXTRACT parameter 2 value “(.{1,})(?: Followers)” does not match text of Function REGEXEXTRACT parameter 1 value “Welcome back to Instagram. Sign in to check out what your friends, family & interests have been capturing & sharing around the world.”.
Not sure if there is a work around here?
Hi, is there any code that can import from reddit directly, any help/reply would be appreciated .
Hi Ben
Am I right in thinking the Instagram import function has now broken? Your demo Google sheets, Ben shows an error ?
Hi Ben,
I frequently visit Pintrest. This post really helped me
Thanks
Hello Ben,
Reditt data is goldmine… thanks for your details about it
Hi Ben!
How would you import a Facebook Page’s ID?
Thank you
Hello I am getting an error when trying to set this up for Instagram and others for that matter.
I get “Function REGEXEXTRACT parameter 2 value “(.{1,})(?: Followers)” does not match text of Function REGEXEXTRACT parameter 1 value “Welcome back to Instagram. Sign in to check out what your friends, family & interests have been capturing & sharing around the world.”.
Not sure if there is a work around here?
I’ve been getting this error message and it’s very inconsistent. Within the same sheet the Instagram formula works for some groups and then for others I get the “Welcome back” results. There doesn’t seem to be much of a pattern to this and I’m also wondering if there’s a fix? Could it be anything to do with Instagram account settings?
Any help would be much appreciated. Cheers
Did you find the solution?
Amazing Information Ben, I didn’t even know about this before. Great help!
Hi Ben! How are you doing?
I think the instagram formulas are broken except the metadata formula.
By the way in your example sheet they are not working also.
The followers formula get the #value! error and in the box: Function SPLIT parameter 1 value should be non-empty.
The posts counter formula gets the #N/A error and inthe box: Function REGEXEXTRACT parameter 2 value “[\d,]+” does not match text of Function REGEXEXTRACT parameter 1 value “”.
May you help us?
Quick question. How do I extract datewise Facebook like data? For example, I want to understand how many likes is a page receiving every day. At this moment, your formula is showing only 1 day data.
Awsome sheets and some great concepts, do you know a why to plug the URLs into a sheet and GTmetrics run automatically rather than having to run it the upload the URL?
And is there anyway to add a snapshot each day? I’ve tried my self but my skills aren’t up to scratch to do it.
Hi James, you can use a few lines of apps script to save a copy each day: https://www.benlcollins.com/spreadsheets/saving-data-in-google-sheets/
Hi Ben,
Thank you very much for that. It was helpful.
Can you please explain how to share a feed from Google sheet to instagram automatically?
Best regards
Javad
I found a solution for Instagram’s K that isn’t working (when a number is over 9,999 and ends with a k), if anyone needs it!
=IF(ISNUMBER(SEARCH(“k”,REGEXEXTRACT(IMPORTXML(A1,”//meta[@name=’description’]/@content”),”(.{1,})(?: Followers)”))),SUBSTITUTE(REGEXEXTRACT(IMPORTXML(A1,”//meta[@name=’description’]/@content”),”(.{1,})(?: Followers)”),”k”,””)*1000,IF(ISNUMBER(SEARCH(“m”,REGEXEXTRACT(IMPORTXML(A1,”//meta[@name=’description’]/@content”),”(.{1,})(?: Followers)”))),SUBSTITUTE(REGEXEXTRACT(IMPORTXML(A1,”//meta[@name=’description’]/@content”),”(.{1,})(?: Followers)”),”m”,””)*1000000,REGEXEXTRACT(IMPORTXML(A1,”//meta[@name=’description’]/@content”),”(.{1,})(?: Followers)”)*1))
not working –> parsing error
I’m having an issue with the instagram followers above 9,999 like the above post. I used the formula Andrea left but am getting a parse error. Any ideas how to fix it?
Hi Ben
Thank you for this valuable information. Do I have a way to ask you to take the follower data for a certain date? Example: How many followers did 4/1 have? I can also consult and bring G Sheets likes, comments and shares on Facebook, Twitter and Instagram. Thank you!
Much thx!!!
Hi Ben,
Seems that twitter may have changed how it can be queried by sheets?
We have been using this formula successfully with a scripted 5 min refresh
=query(IMPORTXML(B2,”//span[@class=’ProfileNav-value’]/@data-count”),”limit 1″)
The formula returns
Error
Imported content is empty
https://docs.google.com/spreadsheets/d/1xRDc-2jQIYLzM0JEC4pjFdxt-dZ4iTm0LkK-xOFyAwo/edit?usp=sharing
Is there anything we might do to fix this?
Yes, does not appear to work anymore. Twitter must have changed the website code. As you can see, these formulas are rather volatile!
I’ve posted an updated formula in the article above. Note, it relies on using the mobile.twitter form of the URL. Hope that helps! 🙂
You are a star BEN!! Hugely appreciate will have to learn a little more about regextract to get back on track.
Hi Ben, Thanks for sharing. Looks like twitter changed something as the import is not working as of June 3, 2020.
Thoughts?
Thanks.
Just posted an update. Hopefully that works for you. 🙂
it looks twitter is no longer working :/
Just posted an update 🙂
Hi,
I’m trying to use the Intagram formulas, but they are broken.
It gives me an error when I copy them into my worksheet. Does the same happen to anyone? Did you find a solution?
Thanks,
Julia
Hi ben,
Yesterday your Instagram formula works propely on my Gsheet. But today it doesn’t work anymore. Any suggestions?
Hey Ben – so helpful! Thankyou for sharing!
Any idea why for some Facebook Groups it works, but for some it shows a Login Error?
I have created a new group that is public and not private and I’m still getting the same error shown in this test sheet:
https://gun.link/fb-group-count-error
Hi Ben,
I really appreciate the formulas you have provided. I am interested in generating data about #hashtags beyond Instagram. I tried changing the formula for other socials (facebook, twitter, linkedIn) but it provided the ‘NA’ response. Any tips?
Thanks.
Looks like the soundcloud example isn’t working for number of tracks.
Hi!! That’s a great work Ben! Congrats!, I’m having a problem with the Facebook formula. I’m getting this error:
“The value “([0-9km ,.] +) (?: I like)” of the parameter 2 of the REGEXEXTRACT function does not match the text of the value “international amnesty argentina, centro, federal district, argentina. 181k likes The world can change, but it will not change by itself. We fight so that … “of Parameter 1 of the REGEXEXTRACT function.”
I’m putting the mobile ULR as you suggest but I’m keeping getting N/A error… Maybe it could be because the page is on spanish?
Thank You!
Hi Ben,
First of all thank you a million for this page, saved me hours and hours of hunting numbers on social media.
Sadly, it seems that youtube broke importxml formula overnight, no content is being loaded.
Any help? If you could teach us what to look for when there is a change in these formulas maybe the community could help.
Cheers
Thank you for this great post
How can we use it to import a Facebook profile public liked pages ?
with the type of the page ??
Hi,
Thanks a lot ! Can we import other data, like site link/ meta desc from twitter or facebook profile?
As social media is one of the biggest ways for marketing and advertising. This really helps ease on managing and monitoring. Thanks a lot!
Thank you, it was really helpful. How can I extract data from Google search results? for example the “number of results”? I tried some formulas but they didn’t work. Thanks
I stumbled upon this post while looking for solutions to automate updating monthly stats for Facebook pages. Thanks for the incredible work!
Regarding Facebook, is there a way to extract followers rather than page fans?
Thanks!
Hi Ben,
Seems that twitter may have changed how it can be queried by sheets. I think its not working for twitter
Hi Ben, with the Twitter grab I now get an error of:
‘Error
Function INDEX parameter 3 value is 3. Valid values are between 0 and 1 inclusive.’ and it shows as a #REF! error ? I am using the mobile URL and it seemed to stop working aroun March. Thanks for your work on these they save a lot of time!
I’m getting this now too.
I started getting that error too. It seems to crop up, especially if you are running that formula in multiple cells on the same sheet. Can work fine in one cell, then crash the next.
I cobbled together this solution (note – you still need to use the mobile site for the url – using Ben’s site as the example here):
=TRIM(REGEXEXTRACT(IMPORTXML(“https://mobile.twitter.com/benlcollins”,”/”),”[\n\r].*Following \s*([^_Followers]*)”))*1
Currently this works, no matter how many cells you put it in. The *1 at the end is to coerce the result into a number value so that you can immediately call it into charts etc.
Hope that helps.
Also, thank you Ben for the valuable website.
Nice work, Craig! Thanks for sharing.
Glad I could contribute something back. You might want to refine it and then use it to as an alternative solution to the one in your original post.
There is definitely a bit of strangeness going on. When your original solution in the post returns the “Error
Function INDEX parameter 3 value is 3. Valid values are between 0 and 1 inclusive.” it can be solved by changing the index parameter to 1, but then it seems to only work for a single cell or two, or certain urls, as subsequent cells can return a “Error
Function REGEXEXTRACT parameter 2 value “(?:Following)(.+)(?:Followers)” does not match text of Function REGEXEXTRACT parameter 1 value” followed by the XML of the site – and even though Following or Followers are still in the XML, it no longer recognizes them.
The formula I worked out seems to be stable in every instance. The regular expressions [\n\r] aren’t entirely necessary (they were carried over accidentally when I was adapting from another formula I had) but I found that when I took them out, Google Sheets started changing the color of the font to blue (and adjusting the font size?) for no discernible reason. The TRIM might not be necessary either except that [\n\r] occasionally did bring a line break into the result. Go figure. Maybe you can look at it and improve it.
Thanks again for the wonderful site and sharing your knowledge with us. It has been immensely helpful – multiple times.
I’ve updated the Twitter formula, should be working now:
=VALUE(REGEXEXTRACT(IMPORTXML(A1,"/"),"(?:Following )([\d,]+)(?: Followers)"))
la formula de facebook dejo de funcionar
Hi! Any update on the Facebook formula?
Also, would be great if a TikTok formula was made available 🙂
Thank you!
Hi Ben,
first: thank you for this extremely helpful article! Pure Gold!.
Updating my twitter formula today I found the following line in my IMPORTXML return:
“This is the legacy version of twitter.com. We will be shutting it down on 15 December 2020. Please switch to a supported browser
or device. You can see a list of supported browsers in our Help Center.”
So mobile.twitter.com will be gone soon. Any ideas how to best parse the current site?
Thanks for the heads up, Wolf!
I got this formula working for a standard profile url (https://twitter.com/benlcollins):
=VALUE(REGEXEXTRACT(CONCATENATE(QUERY(IMPORTDATA(A1),"select * limit 1 offset 105")),"\d+"))
I’ll update the article once I’m sure this is working for a longer time period.
But in general, these formulas are pretty volatile, so I’d be careful relying on them for anything critical.
A much more robust approach is to use a third-party (paid) tool like Supermetrics that can import a rich set of your Twitter data.
Cheers,
Ben
Hi,
This formula still seems to work although because my followers are in M it’s only returning the last digit.
Do you have recommendations for accounting for this in the formula to get a full follower number?
Thanks
Hello
Thanks for writing this blog. it’s really helpful.
For instagram is there any formula to grab Engagement Rate ?
if its available i’ll be glad to see the formula
thank you in advance
I had to tinker around but I got this formula working for Sub-Reddit followers:
=IMPORTXML($A6,”//div[@class=’_3XFx6CfPlg-4Usgxm0gK8R’]”)
Can’t seem to get rid of the active users that this also imports though. Do share if you have any ideas on how to do that.
Thanks for your detailed guide!
Hey Ben –
Having an issue with Instagram – when using Importxml on the IG link, I continually get the IG homepage and it is not pulling the contents of the profile, despite them being public profiles.
Is there a way to keep my google sheet logged into my IG so the pulls can go through?
Great stuff. Is there tiktok and twitch version
Hello Ben,
Thank you for your help! This tools is awesome!
Unfortunately, the Instagram and twitter formula which used to work stopped working and I haven’t found a solution yet to fix it.
I checked on your file sample and it seems to fail to.
Do you have any clue how to fix this?
Thank you in advance!
Same with me! Tried a few formula and didn’t work 🙁
Same! Unfortunately I haven’t found a replacement formula that works yet either…
It’s probably no longer possible for reasons explained here: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/#notWorking
These import formulas were built for websites 10 years ago, not modern dynamic JavaScript sites.
I suggest trying a 3rd-party tool like: https://nodatanobusiness.com/importfromweb/
Since these are mostly outdated now it would be a fantastic resource to reproduce these using Apps Script! Either scraping pages for follower counts or using APIs like YouTube public data API.
Would love a walk through around this!
Hello, thanks! Got it working!
I would like to know if it is possible to create a sheet table that keeps adding the new information as it arrives (https://www.marketbeat.com/market-data/trading-halts/)
I added,2, 3,4…. to keep them all on the table. I believe it will delete as a new symbol shows up in the URL
Thank you, sir!
HI Ben,
This really helped me alot and made simpler now to analyze my data as i was used to sheets.
Thanks.
Nice work Craig, Thanks for sharing.
Hi Ben,
Thanks for all the work here. I’m trying to bring in Twitter last post date, to see if an account is still active. Do you know if this is possible?
Thanks
Thanks for sharing the great post about social media statics, I have used this extracting features for Instagram followers previously, but the option is no more working.
The Apps Script capability would be a wonderful resource for reproducing these now that they are mostly outdated! The following pages are scraped or APIs like YouTube’s public data API are used to find followers.
It would be great to see how this all works!
I am unable to make a copy of the sheet, only can download in excel. Any chance to make a copy of the sheet?
https://www.digrin.com/stocks/detail/O/
Dividend Per Share:2.96 USD
I’m failing to scrape the “Dividend Per Share” from the above website.
I believe importxml has to be used…
Please give the proper formula so I can find out where I’m doing it wrong.
Thank you – Much obliged!
Thanks for sharing the great post about social media statics, I have used this extracting features for Instagram followers previously, but the option is no more working.
Unfortunatly I don’t think this works anymore.
The formula for YouTube channel subscribers needs a refresh it seems! Thanks though for these invaluable resources.
Very helpful post! Thank you. Any idea what the formula would be to scrape the # of Artsy.net followers for an artist profile page? Here’s a random profile page. I’d want the cell to ideally display the actual # of followers rather than the rounded #, IE, for the below profile, I’d want the cell to display “1,199” (or whatever it actually is) rather than the rounded “1.2K”
https://www.artsy.net/artist/thierry-noir