Public data
Fetch publicly available data for as many YouTube channels as you desire.
# python
import os
import time
import gspread
import schedule
import pandas as pd
from isodate import parse_duration
from googleapiclient.discovery import build
from gspread_dataframe import set_with_dataframeGoogle sheet Automation
# Creating and cleaning a Google sheet to store data
GSHEET_NAME = 'Youtube Analytics'
gc = gspread.service_account(filename=modular_filepath)
def clear_sheet(GSHEET_NAME):
sh = gc.open(GSHEET_NAME)
tab_name = "Sheet0"
try:
worksheet = sh.worksheet(tab_name)
worksheet.clear()
except gspread.WorksheetNotFound:
# Create a new worksheet if it doesn't exist
worksheet = sh.add_worksheet(title=tab_name, rows=1000, cols=26)
clear_sheet(GSHEET_NAME)
Getting Publicly Available Data
data fetching
Saving to Google Sheets
Writing to the Google sheet.
Private data
Accessing private data using YouTube analytics
User Authentication is required to fetch private data, I'll be using Access and Refresh tokens to ensure hassle less authentication.
# google auth process
def get_service():
credentials = None
if os.path.exists("token.json"):
credentials = Credentials.from_authorized_user_file("token.json", Scopes)
if not credentials or not credentials.valid:
if credentials and credentials.expired and credentials.refresh_token:
credentials.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRETS_FILE, Scopes)
credentials = flow.run_local_server(port=0)
with open("token.json", "w") as token:
token.write(credentials.to_json())
return build(API_SERVICE_NAME, API_VERSION, credentials = credentials)
def execute_api_request(client_library_function, **kwargs):
response = client_library_function(
**kwargs
).execute()
print(response)
return response
fetching data
Automation
Finally uploading, the created data frames to the sheet
# uploading on a Google sheet
GSHEET_NAME1 = 'Youtube Analytics'
TAB_NAME1 = 'Private Data'
range_to_clear = 'Private Data!A1:Z2000'
gc = gspread.service_account(filename=modular_filepath)
def write_df_to_sheet(GSHEET_NAME, TAB_NAME, df):
sh = gc.open(GSHEET_NAME)
worksheet = sh.worksheet(TAB_NAME)
sh.values_clear(range_to_clear)
set_with_dataframe(worksheet, df)
write_df_to_sheet(GSHEET_NAME1, TAB_NAME1, private_data_df)
