YoutubeData.ps1

Clear-Host
# This script assumes that you alread have a database table with at least the Youtube Code of a video as well as the ranking as columns
$QueryTarget = 'query.sql'
$Database = '`youtube`'
$Table = '`videos`'
$viewsinlanguage = " views"      # Enter here whatever Youtube puts behind the number of views in your language, make sure to include the space
$1000sep = "."                   # Enter here the thousand seperator set in your regional settings
# The following should be a CSV file that stores the part of the URL after v= for all the Youtube video's you want to update
# The CSV file needs to have a column with header 'Youtube Code' and in the colum all the Youtube codes.
$Target = 'data.csv'
$data = Import-Csv $Target    

# Fetch the URL for each Youtube Code and do a Web Request
$data | ForEach-Object {
    $YoutubeCode = $_.'Youtube Code'
    $URI = "https://www.youtube.com/watch?v=$YoutubeCode"
    $HTML = Invoke-WebRequest -URI $URI
    # Store the number of views
    $views = ($HTML.ParsedHtml.getElementsByTagName("div") |
    Where{$_.className -eq "watch-view-count"}).innerText.replace($viewsinlanguage,"").replace("$1000sep","")
    
    # Add to each object two properties with the number of views
    # One property is named after the date the data is retrieved, the other stores the most recent data
    # I did it because I want to be able to tell whether the data has already been updated for today
    # I want to do this smarter in a future script
     $_ | Where{$YoutubeCode -eq $_.'Youtube Code'} | Add-Member -NotePropertyName (Get-Date -format d) -NotePropertyValue $views;`
    if (Get-Member -InputObject ($_ | Where{$YoutubeCode -eq $_.'Youtube Code'}) -Name MostRecent -MemberType Properties){$_.MostRecent = $views}else{
    ($_ | Add-Member -NotePropertyName MostRecent -NotePropertyValue $views)}
    
    # Add to each object a property with the most recent date of update
    # Maybe I could leave out -format d so it also has the time of the upate
    if (Get-Member -InputObject ($_ | Where{$YoutubeCode -eq $_.'Youtube Code'}) -Name Update -MemberType Properties){$_.Update = (Get-Date -format d)}else{
    ($_ | Add-Member -NotePropertyName Update -NotePropertyValue (Get-Date -format d))}
}

# Rank the videos by number of views
$data | Sort-Object -Property MostRecent -Descending | 
ForEach-Object {$i=1}{$_ | Add-Member -NotePropertyName Rank -NotePropertyValue $i;$i++}{}
# Store the object, your CSV file will also be a backup of whatever you put in your database
# Might be smart to create a new file every time the script is run so that you'll get a history of whatever was in your database at some point
$data | Export-CSV -Path $Target -NoTypeInformation

# Now we can build the SQL-query
# First we add a column to the table with the current date (might remove -format d to also include a timestamp)
# I'm not sure that this is the best way to do it since it might lead to a database with a huge amount of columns, but it's always possible to reorganise the database later
$query = "ALTER TABLE $Database.$Table `r`n"
$query += "`tADD ``$(Get-Date -format d)`` INT; `r`n `r`n"

# Update the number of views and the ranking for each Youtube video
$data | ForEach-Object{
    $query += [string[]]"UPDATE $Database.$Table SET `rank`='$($_.Rank)', ``$(Get-Date -format d)``='$($_.MostRecent)' WHERE `youtubecode` = '$($_.'Youtube Code')'; `r`n"
}

# Store the query as a text file
$query | Out-File -Encoding utf8 $QueryTarget

# It might be fun to execute the query on the database with the use of PowerShell later on