Sunday, October 14, 2012

Google Spreadsheet formula to track YouTube views

Question:


( by 
DigitalSauce )


Hello, Currently I am helping a friend track his YouTube views using a google doc. I want to automate this process by having a script grab this line of code:
<td colspan="2" bgcolor="#cccccc"><font color="green">+4,677</font><br><font color="green">+140,303</font> <br></td>
This line of code displays how many views a day he is getting(4,677) per the site: http://socialblade.com/youtube/user/morninafterkill. This line of code also displays the views gotten per month (140,303) How ever I need the script to be able to just grab the socialblade URL from the cell next to it, so in turn I can track multiple channels on the same forum. I would like for it to update its self daily.Can anyone help me with this?

More info: 
Url will be in cell: G*
Views a day in cell: H*
Views a Month in cell: I*
(*any number)

Solution:

Put the following link (URL) in the Cell G2:
http://socialblade.com/youtube/user/morninafterkill

Put the following formula in the cell where you want to get the (average of last 30 days) Views:
=Query(SPLIT(Query(transpose(ImportHtml(G2;"table";0));"select Col34 where Col2 = 'Video Views'");Char(10));"select Col1")

Put the following formula in the cell where you want to get the (average) views for month:
=Query(SPLIT(Query(transpose(ImportHtml(G2;"table";0));"select Col34 where Col2 = 'Video Views'");Char(10));"select Col2")

And if you wish to get the "Views" and "Views a Month" in adjacent cells then you can get both values using the following single formula:
=SPLIT(Query(transpose(ImportHtml(G2;"table";0));"select Col34 where Col2 = 'Video Views'");Char(10))


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 


Thanks,
Kishan,

1 comment:

  1. Is there a script to track subscribers? Do you know?

    ReplyDelete