How would I import YouTube Likes and Dislikes and a ratio from YouTube onto Google Sheets? [closed]

by

TITLE:

=IMPORTXML("https://www.youtube.com/watch?v=MkgR0SxmMKo","//*[@id='eow-title']")

=REGEXEXTRACT(QUERY(ARRAY_CONSTRAIN(IMPORTDATA(A12), 500, 1),
 "where Col1 contains '/title'", 0), ">(.+)<")

enter image description here

VIEWS:

=VALUE(REGEXREPLACE(TEXT(IMPORTXML("https://www.youtube.com/watch?v=MkgR0SxmMKo",
 "//*[contains(@class, 'watch-view-count')]"),0)," view(s)?",""))

DURATION:

=SUBSTITUTE(REGEXREPLACE(IMPORTXML("https://www.youtube.com/watch?v=MkgR0SxmMKo","//*[@itemprop='duration']/@content"),"PT|S",""),"M",":")

LIKES:

=IF(ISNA(IMPORTXML("https://www.youtube.com/watch?v=MkgR0SxmMKo","(//*[contains(@class,'like-button-renderer-like-button')])[1]"))=TRUE,0,
         IMPORTXML("https://www.youtube.com/watch?v=MkgR0SxmMKo","(//*[contains(@class,'like-button-renderer-like-button')])[1]"))

DISLIKES:

=IF(ISNA(IMPORTXML("https://www.youtube.com/watch?v=MkgR0SxmMKo","(//*[contains(@class,'like-button-renderer-dislike-button')])[1]"))=TRUE,0,
         IMPORTXML("https://www.youtube.com/watch?v=MkgR0SxmMKo","(//*[contains(@class,'like-button-renderer-dislike-button')])[1]"))

UPLOADED:

=REGEXREPLACE(IMPORTXML("https://www.youtube.com/watch?v=MkgR0SxmMKo",
 "//*[contains(@class, 'watch-time-text')]"),"((Uploaded)|(Published)|(Streamed live)) on ","")

SUBSCRIPTIONS:

=IFERROR(MID(QUERY(IMPORTXML("https://www.youtube.com/channel/"&A1,
 "//div[@class="primary-header-actions"]"), "select Col1"), 31, 20), )

6

CHANNEL NAME:

=INDEX(IMPORTHTML("https://www.youtube.com/channel/UC7_gcs09iThXybpVgjHZ_7g","list",1),1,1)

CHANNEL ID:

=ARRAYFORMULA(REGEXREPLACE(QUERY(SUBSTITUTE(ARRAY_CONSTRAIN(
 IMPORTDATA(https://www.youtube.com/watch?v=rckrnYw5sOA), 3000, 1), """", ""),
 "where Col1 contains '<meta itemprop=channelId content=""),
 "<meta itemprop=channelId content=|>", ""))

UPDATE:

channel name (07/07/2021):

=REGEXEXTRACT(QUERY(FLATTEN(IMPORTDATA(A4)), 
 "where Col1 contains "\x22channelName\x22:\x22'", 0), ":\\x22(.+)\\x22$")

video title (08/08/2021)

=REGEXEXTRACT(QUERY(FLATTEN(IMPORTDATA(A1)), 
 "where Col1 starts with 'title:""'", 0), """(.*)""")

enter image description here

duration (21/4/2022)

=TEXT(1*REGEXEXTRACT(QUERY(FLATTEN(IMPORTDATA(B1)), 
 "where Col1 contains 'approxDurationMs' limit 1", ), 
 "\d+")/3600000/24, "mm:ss")

enter image description here

channel views (07/06/2022)

=REGEXEXTRACT(QUERY(FLATTEN(IMPORTXML(A1, "//*")), 
 "where Col1 contains '"&CHAR(10)&"Creators'", ), 
 ".x22text.x22:.x22(.+).x22,.x22bold.x22")

enter image description here

More Related Contents:

  1. Google Sheets Formula for Extracting Domain From Website?
  2. ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
  3. Google Sheets importXML Returns Empty Value
  4. Periodically refresh IMPORTXML() spreadsheet function
  5. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  6. Multiple regex matches in Google Sheets formula
  7. Selecting the last value of a column
  8. ArrayFormula and “AND” Formula in Google Sheets
  9. Difference between two dates expressed as years, months, days (in one column)
  10. ARRAY_LITERAL, an Array Literal was missing values for one or more rows
  11. How to use arrayformula with formulas that do not seem to support arrayformulas?
  12. In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
  13. How to create INDIRECT array string of multiple sheet references in Google Sheets?
  14. RegEx: Grabbing values between quotation marks
  15. Regular Expression to match valid dates
  16. How to match numbers between X and Y with regexp?
  17. Regex for Comma delimited list
  18. What’s the regular expression that matches a square bracket?
  19. How does the regular expression ‘(?
  20. Regex AND operator
  21. Regex for alphanumeric, but at least one letter
  22. Microsoft office Access `LIKE` VS `RegEx`
  23. What regular expression can never match?
  24. Regular Expression Validation For Indian Phone Number and Mobile number
  25. htaccess force https and redirect www to non-www, but no other subdomains
  26. Filtering a diff with a regular expression
  27. Regex for field that allows numbers and spaces
  28. Remove all characters before a period in a string
  29. powershell extract text between two strings
  30. Get Twitter @Username with Regex in R

Leave a Comment