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.

Spreadsheet preparation

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

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.

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. image.png

This will automatically fill the spreadsheet. image.png 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

Recommended Posts

Record YouTube views in a spreadsheet using Lambda
How to generate a new loggroup in CloudWatch using python within Lambda
Tweet in Chama Slack Bot ~ How to make a Slack Bot using AWS Lambda ~
Use print in a Python2 lambda expression
How to get a value from a parameter store in lambda (using python)
Get Youtube data in Python using Youtube Data API
Scraping a website using JavaScript in Python
Draw a tree in Python 3 using graphviz
Create a record with attachments in KINTONE using the Python requests module
Create a GIF file using Pillow in Python
View drug reviews using a list in Python
Tips for using ElasticSearch in a good way
Create a MIDI file in Python using pretty_midi
I wrote a Japanese parser in Japanese using pyparsing.
A well-prepared record of data analysis in Python
What is a dog? Django--Volume of using values obtained from URLs in class-based views
Get the file name in a folder using glob
Try face detection in real time using a webcam
Representing Fibonacci sequences using lambda expressions in various languages
How to execute a command using subprocess in Python
I can't send emails from Lambda in a VPC with Boto3 using a VPC endpoint for SES