CODEWITHSUNDEEP
google-sheetsinstagram
Mishal K P
Mishal K P

Reputation: 3

Scraping Instagram data using google spreadsheet?

I need data like bio, and number of posts from public Instagram account using google spreadsheet. I'm able to extract number of followers and following. Can you help ?

Upvotes: 0

Views: 7281

Answers (1)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

This formula is going to look really complicated but really all it is - is an importxml formula to pull in the data from the "script" section which has the pieces you want... then using a bunch of regexreplace/extract functions I clean up the data into a readable format:

take this public page for example: http://www.instagram.com/salesforce/

Then in B1 or C1 enter this: 

=iferror(arrayformula(regexreplace({arrayformula(regexextract(transpose(split(regexreplace(regexreplace(concatenate(IMPORTXML(Sheet2!A1,"//script")),"\n",""),"(^.*""ProfilePage"": \[{""user"": {""username"": "")(.*)(nodes.*)","$2"),", """,false)),"(^.*)"": .*")),arrayformula(regexextract(transpose(split(regexreplace(regexreplace(concatenate(IMPORTXML(Sheet2!A1,"//script")),"\n",""),"(^.*""ProfilePage"": \[{""user"": {""username"": "")(.*)(nodes.*)","$2"),", """,false)),"^.*"": (.*)"))},"[""}{]","")))

I ended up using a literal array so that I could effectively split the field names from the values , obviously you can format however you really want , but see the image here that demonstrates the fields it pulls:

enter image description here

ALso note that the followers, followed_by, and media: count are the fields your mentioned (e.g. # of posts is called media count) and then the biography of course is self explanatory

Update: In answer to your comment - if you want to get the other 2 values out you can do it either in a single regexextract function like this:

If you using the raw import data these regexes work:

Media count:

=REGEXEXTRACT(concatenate(IMPORTDATA(E1)),"""media: {""count"": (\d+)page_info: {")

Biography:

=REGEXEXTRACT(concatenate(IMPORTDATA(E1)),"biography: ""(.*)""full_name")

If your using the importxml method these work:

=REGEXEXTRACT(A1,"biography"": ""(.*)"", "".*""media"": {""count"": (\d+), ""page_info""")

That creates 2 capture groups which automatically puts them into their own adjacent cells, or you can do them individually which is:

and for biography:

=REGEXEXTRACT(A1,"biography"": ""(.*)"", "".*""media")

media count: 

=REGEXEXTRACT(A1,"media"": {""count"": (\d+), ""page_info""")

Upvotes: 3

Related Questions