How to get YouTube's channel subscriber count in Google Sheet with IMPORTXML

Question

I want to get the number of subscribers of a YouTube Channel into a Google Sheet without using the YouTube API.

I did some research and found some examples but I'm still stuck.

The input is a url like this: https://www.youtube.com/@youtube

The output I want is: 39.1 million

In a Google Sheet I tried with IMPORTXML like these:

=IMPORTXML($A1,"//meta[@property='og:description']/@content")

and I can get the property of the page but as soon as I do the right XPath for the subscriber count (which works in the browser's console):

=IMPORTXML($A1,"//*[@id=""subscriber-count""]")

I always get an Error "Imported content is empty".

Looking at other examples in Stackoverflow I also found this:

=QUERY(FLATTEN(IMPORTDATA(A1)),"where Col1 starts with 'subscriberCountText:'",0)

And I receive this output:

subscriberCountText:{"accessibility":{"accessibilityData":{"label":"39.1 million subscribers"}}

But can't extract "39.1 million" properly from that.

Any help would be much appreciated.

Answer 1

For me, to grab the subscriber count from a YouTube channel into a Google Sheet without using the API, you can tweak your approach a bit. Instead of IMPORTXML, try using REGEXEXTRACT. Here's an example formula you can use:

=REGEXEXTRACT(QUERY(FLATTEN(IMPORTDATA($A1)),"where Col1 starts with 'subscriberCountText:'",0), "\d+(\.\d+)?")

This formula extracts the number, including decimals, from the output you're getting. It uses REGEXEXTRACT to find any sequence of digits, with or without a decimal point. Give it a shot, and it should work for getting the subscriber count into your Google Sheet.

Answer 2

=REGEXEXTRACT(QUERY(FLATTEN(IMPORTDATA(A1)),"where Col1 starts with 'subscriberCountText:'"),"([0-9,.,\s,a-z]+)subscriber")

Result: 39.1 million

Answer 3

Here's a possible solution:

=REGEXEXTRACT(CHOOSEROWS(QUERY(TOCOL(IMPORTDATA(A1),1),"where Col1 like '%subscriberCountText%'",),-1),".*(?:""|\\x22)(.*)(?:""|\\x22)")