Record YouTube views in a spreadsheet using Lambda

There are times when you just want to record the number of YouTube views, right? I was addicted to a dance video, and I was wondering if the number of views was tied to media exposure, so I decided to get it, and I just recorded the number using the Youtube Data API, Lambda, Cloud Watch, and Google Sheets. I did it.

Python 3.7, environment is Windows. For Mac Please read the command etc.

reference

I made it with reference to the following https://qiita.com/akabei/items/0eac37cb852ad476c6b9 https://qiita.com/masaha03/items/fab8c8411a020ff2bd42

Enable API

Enable APIs for Youtube, Google Drive and Google Sheets.

Register to use GCP Even if you use only the API, you need to register the project on GCP. The project name is arbitrary.
Enable API from API library Search for API from the "API and Services" library. Search for and enable the Youtube Data API, Google Drive API, and Google Sheets API.
Get an authentication key Create a key from "Credentials". Youtube Data API authenticates with API key, Google Drive API, Google Sheets API with service account. Save the service account as a JSON file.

Spreadsheet preparation

Prepare a spreadsheet. (This time, the sheet name is created by default)

Spreadsheet settings Share your spreadsheet. From "Share" of the created spreadsheet, enter the email address of "client_email" described in the JSON obtained by the service account.

Get Youtube views and write to spreadsheets

Write the number of views and comments to the spreadsheet. Since the difference between the number of playbacks and the number of comments is set on the spreadsheet side, leave it blank when writing.

import gspread
import json
import const
from datetime import datetime
from googleapiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials

def youtube_count(request, context):
    YOUTUBE_API_KEY = const.getYoutubeApiKey()
    #Youtube video ID you want to get
    YOUTUBE_MOVIE_ID = 'Video ID'

　　#Authenticate with API key
    youtube = build('youtube', 'v3', developerKey=YOUTUBE_API_KEY)

    #Get the number of views and the number of comments (In addition to this, you can also get the number of likes)
    statistics = youtube.videos().list(part = 'statistics', id = YOUTUBE_MOVIE_ID).execute()['items'][0]['statistics']

    #It seems that you have to keep issuing refresh tokens if you do not describe as follows
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

    #Set the downloaded json file name
    credentials = ServiceAccountCredentials.from_json_keyfile_name('file name', scope)

    #Log in to the Google API using your OAuth2 credentials.
    gc = gspread.authorize(credentials)

    #Variables for shared spreadsheet keys[SPREADSHEET_KEY]Store in.
    SPREADSHEET_KEY = const.getSpleadsheetKey()

    #Open Sheet 1 of a shared spreadsheet
    worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1

    #Current time
    now_date = datetime.now().strftime("%Y/%m/%d %H:%M")

    #write
    worksheet.append_row([now_date,statistics['viewCount'], '', statistics['commentCount']])

In addition to the main file, place the JSON file of the service account, the const.py file that describes the spreadsheet key to be filled in and the API key of the Youtube Data API in the same hierarchy.

Set to Lambda

Set the created Python file to Lambda.

Set library to Lambda Layers It's quite packed, so I'm writing another article. https://qiita.com/chr36/items/eb6e98f81c8d358ae64c
Set up Lambda (AWS account creation is omitted) Select "New Function" from Lambda and create a function. Deploy the code created above and the configuration file, and link the registered Layers.
Time zone setting It is necessary to set the time zone to output the date and time. This time, the time zone is changed to "Asia / Tokyo" in the environment variable, but it is officially ** deprecated **. http://blog.serverworks.co.jp/tech/2019/10/30/lambda-timezone-2/
Other settings It seems that it may take some time to get the Youtube API, and if the timeout is set to 3 seconds, the acquisition may fail. Also, since the default setting is to re-execute when a failure occurs, the same content may be recorded twice if the default setting is used. Therefore, ** set the timeout time to about 10 seconds **, and if it is not necessary to obtain it, ** re-execute once or less **.

CloudWatch Events settings

Triggered by CloudWatch Events. This time, I thought that I should get it once an hour for the time being, so I will get it once an hour with a schedule formula.

This will automatically fill the spreadsheet. After being written to the spreadsheet, I try to automatically calculate the difference in formatting and views using GAS, but this will be done at a later date.

Finally

Click here for the addicted video. This is Snow Man's "Crazy F-R-E-S-H Beat". https://www.youtube.com/watch?v=lfVfBqkk2Vo