您可以使用该端点,然后从包含感兴趣数据的响应中提取 javascript 对象,并使用 jsonconverter.bas 进行解析。
Json 库:
我使用 jsonconverter.bas。从下载原始代码here https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas并添加到名为 JsonConverter 的标准模块。然后,您需要转到 VBE > 工具 > 引用 > 添加对 Microsoft 脚本运行时的引用。从复制的代码中删除顶部的属性行。
VBA:
Option Explicit
Public Sub GetYouTubeViews()
Dim s As String, ws As Worksheet, body As String
body = "inputType=1&stringInput=https://www.youtube.com/channel/UC43lrLHl4EhxaKQn2HrcJPQ&limit=100&keyType=default"
Set ws = ThisWorkbook.Worksheets("Sheet1")
With CreateObject("MSXML2.XMLHTTP")
.Open "POST", "https://youtube-playlist-analyzer.appspot.com/submit", False
.setRequestHeader "User-Agent", "Mozilla/5.0"
.send body
s = .responseText
End With
Dim results(), r As Long, jsonSource As String
Dim json As Object, item As Object, headers()
jsonSource = GetString(s, "json_items = ", ";")
If jsonSource = "No match" Then Exit Sub
Set json = JsonConverter.ParseJson(jsonSource)
headers = Array("Title", "ViewCount")
ReDim results(1 To json.Count, 1 To UBound(headers) + 1)
For Each item In json
r = r + 1
results(r, 1) = item("title")
results(r, 2) = item("viewCount")
Next
With ws
.Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
.Cells(2, 1).Resize(UBound(results, 1), UBound(results, 2)) = results
End With
End Sub
Public Function GetString(ByVal inputString As String, ByVal startPhrase As String, ByVal endPhrase As String) As String
Dim s As Long, e As Long
s = InStr(inputString, startPhrase)
If Not s > 0 Then
GetString = "No match"
Exit Function
End If
e = InStr(s + Len(startPhrase) - 1, inputString, endPhrase)
If Not e > 0 Then
GetString = "No match"
Exit Function
End If
GetString = Mid$(inputString, s + Len(startPhrase), e - (s + Len(startPhrase)))
End Function
结果示例:
Py:
使用 python 更加简洁
import requests, re, json ,csv
data = {
'inputType': '1',
'stringInput': 'https://www.youtube.com/channel/UC43lrLHl4EhxaKQn2HrcJPQ',
'limit': '100',
'keyType': 'default'
}
r = requests.post('https://youtube-playlist-analyzer.appspot.com/submit', data=data)
p = re.compile(r'json_items = (.*?);', re.DOTALL)
results = json.loads(p.findall(r.text)[0])
with open("data.csv", "w", encoding="utf-8-sig", newline='') as csv_file:
w = csv.writer(csv_file, delimiter = ",", quoting=csv.QUOTE_MINIMAL) #change this for locale
w.writerow(['Title','ViewCount'])
for item in results:
w.writerow([item['title'], item['viewCount']])