Public data

# 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_dataframe

Google 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

Saving to Google Sheets

Private data

Accessing private data using YouTube analytics

# 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

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)