엑셀에서 실시간 유튜브 구독자수 모니터링

 

엑셀에서 실시간으로 유튜브 채널들의 구독자수, 조회수 등을 모니터링하는 예제입니다.

 

>> 관련: 지식인

 

유튜브 구독자수를 구글 API를 이용해서 가져와서 60초, 5분 등 특정 시간마다 갱신하도록 할 수 있습니다.

 

 

​

일반적인 크롤링으로는 45K, 1.6M 등 정확한 구독자수가 안나오기도 하고 서버에 접속하는데도 시간이 더 걸리기 때문에 구글에서 제공하는 정식 API를 이용하는 것이 좋겠습니다.

 

​

1.일단  https://console.cloud.google.com/apis/dashboard 에서 구글 API 키를 발급받아야 합니다.

참고 : 링크

 

- 라이브러리에서 'YouTube Data API v3' 를 선택하고 '사용'을 눌러 활성화시켜야 합니다.

- 그리고 사용자 인증정보 > +사용자 인증정보 만들기 를 눌러서 API키를 발급받으세요.

​

2. 엑셀에서 B2 셀부터 아래로 유튜브 계정아이디를 쭉 적습니다.

API로 조회할 때 채널ID로도 조회할 수 있지만 계정 아이디가 좀 더 알기 쉽습니다.

​

3. 코드를 추가합니다.

​

Alt-F11 을 누르고 개발도구 코드창을 엽니다.

​

1) JSON 라이브러리를 사용하므로

링크의 파일에서 JsonBag.cls 파일의 압축을 풀어서 클래스 모듈로 드래그&드랍합니다.

https://www.vbforums.com/showthread.php?738845-VB6-JsonBag-Another-JSON-Parser-Generator&p=5366455&viewfull=1#post5366455

JsonBag Treeview.zip 파일을 다운 받아서 JsonBag.cls 파일을 압축을 풀어서 프로젝트 창으로 드래그 합니다.

​

2) 현재 통합 문서를 누르고 아래 코드를 넣습니다.

엑셀파일이 열리자 마자 정보를 가져오고 반복 갱신하도록 타이머를 추가하는 역할입니다.

Option Explicit

Private Sub Workbook_Open()
      
    zStartTimer

End Sub

 

3) 삽입 > 모듈을 추가하고 아래 코드를 붙여넣습니다.

Option Explicit

Const API_KEY = "Your Google API KEY ==== 40 Chars ====="
'Const API_KEY = "***************************************"   '40 Chars
'1) Visit 'https://console.cloud.google.com/apis/dashboard.'
'2) Create a project with the default name.
'3) Allow the library called 'YouTube Data API v3'
'4) Generate your API KEY by 'Add user authentification.'

Dim Http As Object  'MSXML2.ServerXMLHTTP60    ' Object
'Dim Html As New MSHTML.HTMLDocument
Dim JSON As New JsonBag

Dim LastCall As Date    '가장 최근 실행시간


Sub getYoutubeStats()

    Dim sht As Worksheet
    Dim lastRow As Range, rng As Range
    
    Set Http = CreateObject("MSXML2.ServerXMLHttp")

    Set sht = ActiveSheet
    Set lastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp)
    If lastRow.Row < 2 Then Exit Sub
    
    For Each rng In sht.Range("B2:B" & lastRow.Row)
        If getStat(rng) = -1 Then Exit For
    Next rng
    
    sht.Columns.AutoFit
    sht.Columns("H").ColumnWidth = 100
    sht.Rows.AutoFit
    'Set Html = Nothing
    Set Http = Nothing
    
End Sub

Function getStat(acc As Range) As Integer

    Dim oSht As Worksheet
    Dim sUrl As String
    Dim cID$, Views$, SubCount$, Videos$, Title$, Descr$, Since$
    
    sUrl = "https://www.googleapis.com/youtube/v3/channels?part=snippet,statistics&key=" & API_KEY & "&forHandle=@" & acc.Text
    'sUrl = "https://m.youtube.com/channel/<YOUTUBE_CHANNEL_ID_LONG_STRING>/about"
    'sUrl = "https://m.youtube.com/@USER_ACOUNT_NAME/about"
    'sUrl = "https://www.youtube.com/@" & acc
    
    With Http
        .Open "Get", sUrl, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Linux; Android 6.0;) AppleWebKit/537.36 Chrome/120.0.0.0 Mobile Safari/537.36"
        .setRequestHeader "Content-Type", "application/json"
        .send
        'Html.body.innerHTML = .responseText
        JSON.JSON = .responseText
    End With
    'Debug.Print JSON.JSON
    
    If JSON.Exists("error") Then
        If MsgBox("[ Error!! " & JSON("error")("code") & " ] " & JSON("error")("message") & _
            vbNewLine & vbNewLine & "Stop now?", vbYesNo + vbCritical) = vbYes Then
            getStat = -1
        Else
            getStat = 0
        End If
        Exit Function
    End If
    cID = JSON("items")(1)("id")
    Views = JSON("items")(1)("statistics")("viewCount")
    SubCount = JSON("items")(1)("statistics")("subscriberCount")
    Videos = JSON("items")(1)("statistics")("videoCount")
    'Debug.Print cID, SubCount, Views, Videos
    Title = JSON("items")(1)("snippet")("title")
    Descr = JSON("items")(1)("snippet")("description"): Descr = Replace(Descr, Chr(10), " ")
    Since = JSON("items")(1)("snippet")("publishedAt"): Since = Left(Since, 10)
    
    acc.Offset(, -1) = Title
    Set oSht = acc.Parent
    acc.Offset(, 1) = cID
    oSht.Hyperlinks.Add acc.Offset(, 1), "https://m.youtube.com/channel/" & cID & "/about", , "About"
    acc.Offset(, 2) = Views
    acc.Offset(, 2).NumberFormat = "###,###,###,###"
    acc.Offset(, 3) = SubCount
    acc.Offset(, 3).NumberFormat = "###,###,###,###"
    acc.Offset(, 4) = Videos
    acc.Offset(, 4).NumberFormat = "###,###,###,###"
    acc.Offset(, 5) = Since
    acc.Offset(, 6) = Descr
    getStat = 1
    
End Function

Sub zStartTimer()

    getYoutubeStats
    Application.StatusBar = "Last update: " & Format(Now, "MM/DD(ddd) HH:NN:SS")
        
    '60초마다 갱신
    LastCall = Now + TimeValue("00:01:00")
    Application.OnTime LastCall, "zStartTimer"

End Sub

Sub zStopTimer()

    On Error Resume Next
    With Application
        .OnTime LastCall, "AddTimer", , False
        .StatusBar = Application.StatusBar & " >> Stopped"
        .OnTime Now + TimeValue("00:00:05"), "zStatusOff"
    End With
    
End Sub

Sub zStatusOff(Optional T As Boolean)
    Application.StatusBar = False
End Sub

 

 

핵심 부분은 https://www.googleapis.com/youtube/v3/channels?part=snippet,statistics&key= API_KEY&forHandle=@계정아이디로 조회하면 아래와 같은 JSON 데이터가 돌아오거나 에러메시지가 뜹니다. 이 JSON 정보를 파싱해서서 화면에 보여주고 갱신하는 것입니다.​

    '    {
    '        "kind": "youtube#channelListResponse",
    '        "etag": "vdXkdxBKA5DKypndZTEWUM_tBPQ",
    '        "pageInfo": {
    '            "totalResults": 1,
    '            "resultsPerPage": 5
    '        },
    '        "items": [
    '            {
    '                "kind": "youtube#channel",
    '                "etag": "R5sb6hhxom_vkPLE9izol0WBeVg",
    '                "id": "UC1gi4ynIYAcd_BmFlJsLOWg",
    '                "statistics": {
    '                    "viewCount": "35960",
    '                    "subscriberCount": "242",
    '                    "hiddenSubscriberCount": false,
    '                    "videoCount": "26"
    '                }
    '            }
    '        ]
    '    }
    
    '//error
    '{"error":{"code":400,"message":"API key not valid. Please pass a valid API key.","errors":[{"message":"API key not valid. Please pass a valid API key.","domain":"global","reason":"badRequest"}],"status":"INVALID_ARGUMENT","details":[{"@type":"type.googleapis.com/google.rpc.ErrorInfo","reason":"API_KEY_INVALID","domain":"googleapis.com","metadata":{"service":"youtube.googleapis.com"}}]}}

 

 

4. 현재는 60초마다 갱신합니다. 갱신할 때마다 상태표시줄에 갱신 시간을 보여줍니다.

채널ID 링크를 클릭하면 해당 유투브의 채널 about 페이지로 하이퍼링크됩니다.

​

샘플 첨부합니다.

getYoutubeStat1_sample.xlsm

0.07MB

 

​

※ 첨부한 샘플 파일을 처음 열면 오류가 발생합니다. 반드시 작동하는 API KEY를 입력해야 작동합니다.

​

2024년 2월 15일 현재 조회 결과:

 

 

참고:

 

구글 API는 여러 Method 들을 미리 실행해볼 수 있습니다.

https://developers.google.com/youtube/v3/docs?apix=true&hl=ko

파란 글씨의 여러 Method 를 선택하고 오른쪽 Try this method 창에서 각종 인수를 입력하고 Execute 를 눌러서 돌아오는 JSON 결과값을 확인할 수 있습니다.

 

channel 외에도 특정 영상의 정보를 가져올 수도 있고

가장 많은 조회수, 구독자수를 조회해볼 수도 있고

여러가지 다양한 작업이 가능합니다.

물론 API KEY에 할당된 제한 회수내에서 이용 가능합니다.