CODEWITHSUNDEEP
htmlyoutubegoogle-sheets
user8162574
user8162574

Reputation:

Get Youtube channel info in Google spreadsheet

I am creating a spreadsheet to monitor the growth of my youtube channel. In doing so I want to compare the amount of youtube subscribers and youtube total views of my channel to some other data in the spreadsheet.

I know there is a way to import tables and lists from wikipedia using a formula but is there a formula for selecting one piece of information from the html page.

Thanks

Upvotes: 2

Views: 3022

Answers (1)

AngYC
AngYC

Reputation: 3903

Method 1

=REGEXEXTRACT(JOIN("", IMPORTXML("https://www.youtube.com/user/pewdiepie/about", "//html")), "Error sending your message\.Close(.*) \nsubscribers • (.*) views")

Fetch Youtube About page of a user as XML, then parse the data using Regex, the string might change in the future so it is not future proof (But no authentication/login required)

Method 2

=REGEXEXTRACT(TEXTJOIN("|", false, IMPORTDATA("https://content.googleapis.com/youtube/v3/channels?id=CHANNEL_ID&part=statistics&key=YOUTUBE_API_KEY")), "\|\|statistics: {\|\|viewCount: " & CHAR(34) & "(.+?)" & CHAR(34) & "\|\|commentCount: " & CHAR(34) & "(?:.+?)" & CHAR(34) & "\|\|subscriberCount: " & CHAR(34) & "(.+?)" & CHAR(34) & "\|\|")

Fetch JSON response from Youtube API, then extract the data using Regex (Unfortunately there is no JSON parser in Spreadsheet), CHAR(34) is escaped version of " for Google Spreadsheet

Was playing with Google spreadsheet and comes out with 2 methods (First method is more like scraping, and second method is using API to do it), no external script, just plain Google spreadsheet formula =)

Youtube channel info in Google Sheets Live Demo: https://docs.google.com/spreadsheets/d/1A3-Al9ps8sZFmpVqNt-WJlpw3cQYjsTkFjJSHc7jH-8/

If you want to modify it, you have to make a copy first

Upvotes: 3

Related Questions