Youtube data scrape locally, and upload csv to sheet

<p> <span style="background-color: #1f1f1f; color: #c586c0; font-family: Menlo, Monaco, "Courier New", monospace; font-size: 12px; white-space: pre;">import</span><span style="background-color: #1f1f1f; color: #cccccc; font-family: Menlo, Monaco, "Courier New", monospace; font-size: 12px; white-space: pre;"> </span><span style="background-color: #1f1f1f; color: #4ec9b0; font-family: Menlo, Monaco, "Courier New", monospace; font-size: 12px; white-space: pre;">re</span></p><div style="background-color: #1f1f1f; color: #cccccc; font-family: Menlo, Monaco, "Courier New", monospace; font-size: 12px; line-height: 18px; white-space: pre;"><div><span style="color: #c586c0;">import</span> <span style="color: #4ec9b0;">gspread</span></div><div><span style="color: #c586c0;">import</span> <span style="color: #4ec9b0;">datetime</span></div><div><span style="color: #c586c0;">from</span> <span style="color: #4ec9b0;">googleapiclient</span>.<span style="color: #4ec9b0;">discovery</span> <span style="color: #c586c0;">import</span> <span style="color: #dcdcaa;">build</span></div><div><span style="color: #c586c0;">import</span> <span style="color: #4ec9b0;">time</span> <span style="color: #c586c0;">as</span> <span style="color: #4ec9b0;">tm</span></div><div><span style="color: #c586c0;">import</span> <span style="color: #4ec9b0;">csv</span></div><div><span style="color: #c586c0;">import</span> <span style="color: #4ec9b0;">os</span></div><div><span style="color: #c586c0;">import</span> <span style="color: #4ec9b0;">pandas</span> <span style="color: #c586c0;">as</span> <span style="color: #4ec9b0;">pd</span></div><br /><br /><div><span style="color: #9cdcfe;">sa</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">gspread</span>.<span style="color: #dcdcaa;">service_account</span>(<span style="color: #9cdcfe;">filename</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"/Users/jitendersingh/Documents/sheetsapi.json"</span>)</div><div><span style="color: #9cdcfe;">sh</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sa</span>.<span style="color: #dcdcaa;">open</span>(<span style="color: #ce9178;">'YT_Channel_views'</span>)</div><div><span style="color: #9cdcfe;">sheet</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sh</span>.<span style="color: #dcdcaa;">worksheet</span>(<span style="color: #ce9178;">'Sheet1'</span>)</div><br /><br /><div><span style="color: #6a9955;"># Replace with your API key</span></div><div><span style="color: #4fc1ff;">API_KEY</span> <span style="color: #d4d4d4;">=</span> <span style="color: #ce9178;">""</span> <span style="color: #6a9955;">#jin634</span></div><br /><div><span style="color: #6a9955;"># API_KEY = "" #Jitenders634</span></div><br /><div><span style="color: #6a9955;"># Create a YouTube Data API client</span></div><div><span style="color: #9cdcfe;">youtube</span> <span style="color: #d4d4d4;">=</span> <span style="color: #dcdcaa;">build</span>(<span style="color: #ce9178;">"youtube"</span>, <span style="color: #ce9178;">"v3"</span>, <span style="color: #9cdcfe;">developerKey</span><span style="color: #d4d4d4;">=</span><span style="color: #4fc1ff;">API_KEY</span>)</div><br /><div><span style="color: #6a9955;">#Function to get youtube videos title, views, link</span></div><div><span style="color: #569cd6;">def</span> <span style="color: #dcdcaa;">get_channel_videos</span>(<span style="color: #9cdcfe;">channel_id</span>):</div><div> <span style="color: #9cdcfe;">videos</span> <span style="color: #d4d4d4;">=</span> []</div><div> <span style="color: #9cdcfe;">next_page_token</span> <span style="color: #d4d4d4;">=</span> <span style="color: #569cd6;">None</span></div><br /><div> <span style="color: #6a9955;"># Get the "uploads" playlist ID for the channel</span></div><div> <span style="color: #9cdcfe;">channels_response</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">youtube</span>.channels().list(</div><div> <span style="color: #9cdcfe;">id</span><span style="color: #d4d4d4;">=</span><span style="color: #9cdcfe;">channel_id</span>,</div><div> <span style="color: #9cdcfe;">part</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">'contentDetails'</span></div><div> ).execute()</div><br /><div> <span style="color: #9cdcfe;">uploads_playlist_id</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">channels_response</span>[<span style="color: #ce9178;">'items'</span>][<span style="color: #b5cea8;">0</span>][<span style="color: #ce9178;">'contentDetails'</span>][<span style="color: #ce9178;">'relatedPlaylists'</span>][<span style="color: #ce9178;">'uploads'</span>]</div><br /><div> <span style="color: #c586c0;">while</span> <span style="color: #569cd6;">True</span>:</div><div> <span style="color: #6a9955;"># Fetch the videos in the "uploads" playlist</span></div><div> <span style="color: #9cdcfe;">playlist_items_request</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">youtube</span>.playlistItems().list(</div><div> <span style="color: #9cdcfe;">playlistId</span><span style="color: #d4d4d4;">=</span><span style="color: #9cdcfe;">uploads_playlist_id</span>,</div><div> <span style="color: #9cdcfe;">part</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">'snippet'</span>,</div><div> <span style="color: #6a9955;"># maxResults=50,</span></div><div> <span style="color: #9cdcfe;">pageToken</span><span style="color: #d4d4d4;">=</span><span style="color: #9cdcfe;">next_page_token</span></div><div> )</div><div> <span style="color: #9cdcfe;">playlist_items_response</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">playlist_items_request</span>.execute()</div><br /><div> <span style="color: #6a9955;"># Extract video IDs</span></div><div> <span style="color: #9cdcfe;">video_ids</span> <span style="color: #d4d4d4;">=</span> [<span style="color: #9cdcfe;">item</span>[<span style="color: #ce9178;">'snippet'</span>][<span style="color: #ce9178;">'resourceId'</span>][<span style="color: #ce9178;">'videoId'</span>] <span style="color: #c586c0;">for</span> <span style="color: #9cdcfe;">item</span> <span style="color: #c586c0;">in</span> <span style="color: #9cdcfe;">playlist_items_response</span>[<span style="color: #ce9178;">'items'</span>]]</div><br /><div> <span style="color: #6a9955;"># Fetch video details and statistics for each video ID</span></div><div> <span style="color: #9cdcfe;">videos_request</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">youtube</span>.videos().list(</div><div> <span style="color: #9cdcfe;">part</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"snippet,statistics"</span>,</div><div> <span style="color: #9cdcfe;">id</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">','</span>.<span style="color: #dcdcaa;">join</span>(<span style="color: #9cdcfe;">video_ids</span>)</div><div> )</div><div> <span style="color: #9cdcfe;">videos_response</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">videos_request</span>.execute()</div><br /><div> <span style="color: #6a9955;"># Process each video</span></div><div> <span style="color: #c586c0;">for</span> <span style="color: #9cdcfe;">item</span> <span style="color: #c586c0;">in</span> <span style="color: #9cdcfe;">videos_response</span>[<span style="color: #ce9178;">'items'</span>]:</div><div> <span style="color: #9cdcfe;">video</span> <span style="color: #d4d4d4;">=</span> {</div><div> <span style="color: #ce9178;">"title"</span>: <span style="color: #9cdcfe;">item</span>[<span style="color: #ce9178;">'snippet'</span>][<span style="color: #ce9178;">'title'</span>],</div><div> <span style="color: #ce9178;">"views"</span>: <span style="color: #9cdcfe;">item</span>[<span style="color: #ce9178;">'statistics'</span>][<span style="color: #ce9178;">'viewCount'</span>],</div><div> <span style="color: #ce9178;">"video_link"</span>: <span style="color: #569cd6;">f</span><span style="color: #ce9178;">"https://www.youtube.com/watch?v=</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">item</span>[<span style="color: #ce9178;">'id'</span>]<span style="color: #569cd6;">}</span><span style="color: #ce9178;">"</span></div><div> }</div><div> <span style="color: #9cdcfe;">videos</span>.<span style="color: #dcdcaa;">append</span>(<span style="color: #9cdcfe;">video</span>)</div><br /><div> <span style="color: #6a9955;"># Check if there are more pages of results</span></div><div> <span style="color: #9cdcfe;">next_page_token</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">playlist_items_response</span>.get(<span style="color: #ce9178;">"nextPageToken"</span>)</div><div> <span style="color: #c586c0;">if</span> <span style="color: #569cd6;">not</span> <span style="color: #9cdcfe;">next_page_token</span>:</div><div> <span style="color: #c586c0;">break</span></div><br /><div> <span style="color: #c586c0;">return</span> <span style="color: #9cdcfe;">videos</span></div><br /><br /><div><span style="color: #6a9955;">#Function to get youtube channel id using url / username (sometime username wont work)</span></div><div><span style="color: #569cd6;">def</span> <span style="color: #dcdcaa;">get_channel_info_using_api</span>(<span style="color: #9cdcfe;">url</span>):</div><div> <span style="color: #ce9178;">"""</span></div><div><span style="color: #ce9178;"> Extracts channel ID or username and uses Youtube Data API to get channel name and ID.</span></div><br /><div><span style="color: #ce9178;"> Args:</span></div><div><span style="color: #ce9178;"> url: The URL of the YouTube channel.</span></div><br /><div><span style="color: #ce9178;"> Returns:</span></div><div><span style="color: #ce9178;"> A dictionary containing 'id' and 'name' keys, or None if parsing fails.</span></div><div><span style="color: #ce9178;"> """</span></div><div> <span style="color: #6a9955;"># Regex patterns for URL formats</span></div><div> <span style="color: #9cdcfe;">channel_id_pattern</span> <span style="color: #d4d4d4;">=</span> <span style="color: #569cd6;">r</span><span style="color: #d16969;">"channel/</span><span style="color: #ce9178;">([^</span><span style="color: #d16969;">/</span><span style="color: #ce9178;">]</span><span style="color: #d7ba7d;">+</span><span style="color: #ce9178;">)</span><span style="color: #d16969;">"</span></div><div> <span style="color: #9cdcfe;">username_pattern</span> <span style="color: #d4d4d4;">=</span> <span style="color: #569cd6;">r</span><span style="color: #d16969;">"@</span><span style="color: #ce9178;">([^</span><span style="color: #d16969;">/</span><span style="color: #ce9178;">]</span><span style="color: #d7ba7d;">+</span><span style="color: #ce9178;">)</span><span style="color: #d16969;">"</span></div><br /><div> <span style="color: #6a9955;"># Extract channel ID or username based on URL format</span></div><div> <span style="color: #9cdcfe;">match</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">re</span>.<span style="color: #dcdcaa;">search</span>(<span style="color: #9cdcfe;">channel_id_pattern</span>, <span style="color: #9cdcfe;">url</span>)</div><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">match</span>:</div><div> <span style="color: #9cdcfe;">channel_id</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">match</span>.<span style="color: #dcdcaa;">group</span>(<span style="color: #b5cea8;">1</span>)</div><div> <span style="color: #c586c0;">else</span>:</div><div> <span style="color: #9cdcfe;">match</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">re</span>.<span style="color: #dcdcaa;">search</span>(<span style="color: #9cdcfe;">username_pattern</span>, <span style="color: #9cdcfe;">url</span>)</div><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">match</span>:</div><div> <span style="color: #9cdcfe;">channel_username</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">match</span>.<span style="color: #dcdcaa;">group</span>(<span style="color: #b5cea8;">1</span>)</div><div> <span style="color: #9cdcfe;">channel_id</span> <span style="color: #d4d4d4;">=</span> <span style="color: #569cd6;">None</span> <span style="color: #6a9955;"># Initialize channel_id here to avoid the error</span></div><div> <span style="color: #c586c0;">else</span>:</div><div> <span style="color: #c586c0;">return</span> <span style="color: #569cd6;">None</span> <span style="color: #6a9955;"># URL format not recognized</span></div><br /><div> <span style="color: #6a9955;"># Use Youtube Data API to get channel information based on ID or username</span></div><div> <span style="color: #c586c0;">from</span> <span style="color: #4ec9b0;">googleapiclient</span>.<span style="color: #4ec9b0;">discovery</span> <span style="color: #c586c0;">import</span> <span style="color: #dcdcaa;">build</span> <span style="color: #6a9955;"># Import library for Youtube Data API</span></div><br /><div> <span style="color: #9cdcfe;">youtube</span> <span style="color: #d4d4d4;">=</span> <span style="color: #dcdcaa;">build</span>(<span style="color: #ce9178;">"youtube"</span>, <span style="color: #ce9178;">"v3"</span>, <span style="color: #9cdcfe;">developerKey</span><span style="color: #d4d4d4;">=</span><span style="color: #4fc1ff;">API_KEY</span>)</div><br /><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">channel_id</span>:</div><div> <span style="color: #6a9955;"># Get channel information by ID</span></div><div> <span style="color: #c586c0;">try</span>:</div><div> <span style="color: #9cdcfe;">response</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">youtube</span>.channels().list(</div><div> <span style="color: #9cdcfe;">part</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"snippet"</span>, <span style="color: #9cdcfe;">id</span><span style="color: #d4d4d4;">=</span><span style="color: #9cdcfe;">channel_id</span></div><div> ).execute()</div><div> <span style="color: #9cdcfe;">channel_name</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">response</span>[<span style="color: #ce9178;">"items"</span>][<span style="color: #b5cea8;">0</span>][<span style="color: #ce9178;">"snippet"</span>][<span style="color: #ce9178;">"title"</span>]</div><div> <span style="color: #c586c0;">return</span> {<span style="color: #ce9178;">"id"</span>: <span style="color: #9cdcfe;">channel_id</span>, <span style="color: #ce9178;">"name"</span>: <span style="color: #9cdcfe;">channel_name</span>}</div><div> <span style="color: #c586c0;">except</span>:</div><div> <span style="color: #c586c0;">return</span> <span style="color: #569cd6;">None</span> <span style="color: #6a9955;"># Handle potential errors during API call</span></div><div> <span style="color: #c586c0;">else</span>:</div><div> <span style="color: #6a9955;"># Get channel information by username (search by channel name)</span></div><div> <span style="color: #9cdcfe;">search_results</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">youtube</span>.search().list(</div><div> <span style="color: #9cdcfe;">q</span><span style="color: #d4d4d4;">=</span><span style="color: #9cdcfe;">channel_username</span>, <span style="color: #9cdcfe;">part</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"snippet"</span>, <span style="color: #9cdcfe;">type</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"CHANNEL"</span></div><div> ).execute()</div><div> <span style="color: #c586c0;">for</span> <span style="color: #9cdcfe;">result</span> <span style="color: #c586c0;">in</span> <span style="color: #9cdcfe;">search_results</span>[<span style="color: #ce9178;">"items"</span>]:</div><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">result</span>[<span style="color: #ce9178;">"snippet"</span>][<span style="color: #ce9178;">"title"</span>] <span style="color: #d4d4d4;">==</span> <span style="color: #9cdcfe;">channel_username</span>:</div><div> <span style="color: #c586c0;">try</span>:</div><div> <span style="color: #9cdcfe;">channel_id</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">result</span>[<span style="color: #ce9178;">"snippet"</span>][<span style="color: #ce9178;">"channelId"</span>] <span style="color: #6a9955;"># Assign channel_id here</span></div><div> <span style="color: #9cdcfe;">response</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">youtube</span>.channels().list(</div><div> <span style="color: #9cdcfe;">part</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"snippet"</span>, <span style="color: #9cdcfe;">id</span><span style="color: #d4d4d4;">=</span><span style="color: #9cdcfe;">channel_id</span></div><div> ).execute()</div><div> <span style="color: #9cdcfe;">channel_name</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">response</span>[<span style="color: #ce9178;">"items"</span>][<span style="color: #b5cea8;">0</span>][<span style="color: #ce9178;">"snippet"</span>][<span style="color: #ce9178;">"title"</span>]</div><div> <span style="color: #c586c0;">return</span> {<span style="color: #ce9178;">"id"</span>: <span style="color: #9cdcfe;">channel_id</span>, <span style="color: #ce9178;">"name"</span>: <span style="color: #9cdcfe;">channel_name</span>}</div><div> <span style="color: #c586c0;">except</span>:</div><div> <span style="color: #c586c0;">return</span> <span style="color: #569cd6;">None</span> <span style="color: #6a9955;"># Handle potential errors during API call</span></div><div> <span style="color: #c586c0;">return</span> <span style="color: #569cd6;">None</span> <span style="color: #6a9955;"># Channel not found by username</span></div><br /><br /><br /><br /><div><span style="color: #6a9955;">#Function to create before 24 hours sheet using channel name.</span></div><div><span style="color: #569cd6;">def</span> <span style="color: #dcdcaa;">create_worksheet_for_channel</span>():</div><div> <span style="color: #6a9955;"># Get all non-empty cells in the first column of Sheet1</span></div><div> <span style="color: #9cdcfe;">first_column_cells</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">findall</span>(<span style="color: #4ec9b0;">re</span>.<span style="color: #dcdcaa;">compile</span>(<span style="color: #569cd6;">r</span><span style="color: #d16969;">'^https</span><span style="color: #d7ba7d;">?</span><span style="color: #d16969;">://'</span>))</div><div> </div><div> <span style="color: #c586c0;">for</span> <span style="color: #9cdcfe;">cell</span> <span style="color: #c586c0;">in</span> <span style="color: #9cdcfe;">first_column_cells</span>:</div><div> <span style="color: #9cdcfe;">url</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">cell</span>.value</div><div> <span style="color: #6a9955;"># Check if the corresponding cell in the second column is empty</span></div><div> <span style="color: #9cdcfe;">second_column_value</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">cell</span>(<span style="color: #9cdcfe;">cell</span>.row, <span style="color: #b5cea8;">2</span>).<span style="color: #9cdcfe;">value</span></div><div> <span style="color: #c586c0;">if</span> <span style="color: #569cd6;">not</span> <span style="color: #9cdcfe;">second_column_value</span>:</div><div> <span style="color: #6a9955;"># Get channel details</span></div><div> <span style="color: #9cdcfe;">channel_info</span> <span style="color: #d4d4d4;">=</span> <span style="color: #dcdcaa;">get_channel_info_using_api</span>(<span style="color: #9cdcfe;">url</span>)</div><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">channel_info</span>:</div><div> <span style="color: #9cdcfe;">channel_name</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">channel_info</span>[<span style="color: #ce9178;">"name"</span>]</div><div> <span style="color: #9cdcfe;">channel_id</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">channel_info</span>[<span style="color: #ce9178;">"id"</span>]</div><div> <span style="color: #6a9955;"># Check if a worksheet with the same name already exists</span></div><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">channel_name</span> <span style="color: #569cd6;">in</span> <span style="color: #9cdcfe;">sh</span>.<span style="color: #dcdcaa;">worksheets</span>():</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Worksheet '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">' already available."</span>)</div><div> <span style="color: #c586c0;">else</span>:</div><div> <span style="color: #6a9955;"># Create a new worksheet with the channel name</span></div><div> <span style="color: #9cdcfe;">new_worksheet</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sh</span>.<span style="color: #dcdcaa;">add_worksheet</span>(<span style="color: #9cdcfe;">title</span><span style="color: #d4d4d4;">=</span><span style="color: #9cdcfe;">channel_name</span>, <span style="color: #9cdcfe;">cols</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"9"</span>, <span style="color: #9cdcfe;">rows</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"18000"</span>)</div><div> <span style="color: #6a9955;"># Add headers to the new worksheet</span></div><div> <span style="color: #9cdcfe;">headers</span> <span style="color: #d4d4d4;">=</span> [<span style="color: #ce9178;">"Title"</span>, <span style="color: #ce9178;">"Views Earlier"</span>, <span style="color: #ce9178;">"Link"</span>, <span style="color: #ce9178;">"Title"</span>, <span style="color: #ce9178;">"Views Later"</span>, <span style="color: #ce9178;">"Link"</span>]</div><div> <span style="color: #9cdcfe;">new_worksheet</span>.<span style="color: #dcdcaa;">insert_row</span>(<span style="color: #9cdcfe;">headers</span>, <span style="color: #9cdcfe;">index</span><span style="color: #d4d4d4;">=</span><span style="color: #b5cea8;">1</span>)</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Worksheet '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">' created."</span>)</div><br /><div> <span style="color: #6a9955;">#get videos using channel id</span></div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Getting the videos of '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">'."</span>)</div><div> <span style="color: #9cdcfe;">videos</span> <span style="color: #d4d4d4;">=</span> <span style="color: #dcdcaa;">get_channel_videos</span>(<span style="color: #9cdcfe;">channel_id</span>)</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Got the videos of '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">'."</span>)</div><br /><div> <span style="color: #9cdcfe;">csv_file_path</span> <span style="color: #d4d4d4;">=</span> <span style="color: #ce9178;">"/Users/jitendersingh/Documents/youtube_csv/"</span><span style="color: #d4d4d4;">+</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #d4d4d4;">+</span><span style="color: #ce9178;">" BEFORE.csv"</span></div><div> <span style="color: #dcdcaa;">save_to_csv</span>(<span style="color: #9cdcfe;">videos</span>, <span style="color: #9cdcfe;">csv_file_path</span>)</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Saved the csv file of '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">'."</span>)</div><br /><div> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">update_cell</span>(<span style="color: #9cdcfe;">cell</span>.row, <span style="color: #b5cea8;">2</span>, <span style="color: #9cdcfe;">channel_name</span>)</div><br /><div> <span style="color: #6a9955;"># Get current date and time</span></div><div> <span style="color: #9cdcfe;">current_date</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">datetime</span>.<span style="color: #4ec9b0;">datetime</span>.<span style="color: #dcdcaa;">now</span>().<span style="color: #dcdcaa;">strftime</span>(<span style="color: #ce9178;">"</span><span style="color: #569cd6;">%d</span><span style="color: #ce9178;"> %B %Y"</span>) <span style="color: #6a9955;"># Format date as dd-mm-yyyy</span></div><div> <span style="color: #9cdcfe;">current_time</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">datetime</span>.<span style="color: #4ec9b0;">datetime</span>.<span style="color: #dcdcaa;">now</span>().<span style="color: #dcdcaa;">strftime</span>(<span style="color: #ce9178;">"%H:%M"</span>) <span style="color: #6a9955;"># Format time in 24-hour format</span></div><br /><div> <span style="color: #6a9955;"># Update cells</span></div><div> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">update_cell</span>(<span style="color: #9cdcfe;">cell</span>.row, <span style="color: #b5cea8;">3</span>, <span style="color: #9cdcfe;">current_date</span>) <span style="color: #6a9955;"># Updating current date in the third column</span></div><div> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">update_cell</span>(<span style="color: #9cdcfe;">cell</span>.row, <span style="color: #b5cea8;">4</span>, <span style="color: #9cdcfe;">current_time</span>) <span style="color: #6a9955;"># Updating current time in the fourth column</span></div><br /><div> <span style="color: #c586c0;">else</span>:</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Failed to get channel info for URL: </span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">url</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">"</span>)</div><div> <span style="color: #c586c0;">else</span>:</div><div> <span style="color: #6a9955;"># Get channel name from the URL</span></div><div> <span style="color: #9cdcfe;">channel_info</span> <span style="color: #d4d4d4;">=</span> <span style="color: #dcdcaa;">get_channel_info_using_api</span>(<span style="color: #9cdcfe;">url</span>)</div><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">channel_info</span>:</div><div> <span style="color: #9cdcfe;">channel_name</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">channel_info</span>[<span style="color: #ce9178;">"name"</span>]</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"First process already done for channel '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">'."</span>)</div><br /><br /><br /><br /><br /><div><span style="color: #6a9955;">#saving videos to csv for backup</span></div><div><span style="color: #569cd6;">def</span> <span style="color: #dcdcaa;">save_to_csv</span>(<span style="color: #9cdcfe;">videos</span>, <span style="color: #9cdcfe;">file_path</span>):</div><div> <span style="color: #6a9955;"># Define the field names (columns) for the CSV file</span></div><div> <span style="color: #9cdcfe;">field_names</span> <span style="color: #d4d4d4;">=</span> [<span style="color: #ce9178;">"Title"</span>, <span style="color: #ce9178;">"Views"</span>, <span style="color: #ce9178;">"Video Link"</span>]</div><br /><div> <span style="color: #6a9955;"># Write the video information to the CSV file</span></div><div> <span style="color: #c586c0;">with</span> <span style="color: #dcdcaa;">open</span>(<span style="color: #9cdcfe;">file_path</span>, <span style="color: #9cdcfe;">mode</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"w"</span>, <span style="color: #9cdcfe;">newline</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">""</span>, <span style="color: #9cdcfe;">encoding</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"utf-8"</span>) <span style="color: #c586c0;">as</span> <span style="color: #9cdcfe;">file</span>:</div><div> <span style="color: #9cdcfe;">writer</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">csv</span>.<span style="color: #4ec9b0;">DictWriter</span>(<span style="color: #9cdcfe;">file</span>, <span style="color: #9cdcfe;">fieldnames</span><span style="color: #d4d4d4;">=</span><span style="color: #9cdcfe;">field_names</span>)</div><div> <span style="color: #9cdcfe;">writer</span>.<span style="color: #dcdcaa;">writeheader</span>()</div><div> <span style="color: #c586c0;">for</span> <span style="color: #9cdcfe;">video</span> <span style="color: #c586c0;">in</span> <span style="color: #9cdcfe;">videos</span>:</div><div> <span style="color: #9cdcfe;">writer</span>.<span style="color: #dcdcaa;">writerow</span>({</div><div> <span style="color: #ce9178;">"Title"</span>: <span style="color: #9cdcfe;">video</span>[<span style="color: #ce9178;">"title"</span>],</div><div> <span style="color: #ce9178;">"Views"</span>: <span style="color: #9cdcfe;">video</span>[<span style="color: #ce9178;">"views"</span>],</div><div> <span style="color: #ce9178;">"Video Link"</span>: <span style="color: #9cdcfe;">video</span>[<span style="color: #ce9178;">"video_link"</span>]</div><div> }) </div><br /><br /><br /><div><span style="color: #6a9955;">#Function to merge csv files, and merge them, and delete extra videos cells</span></div><div><span style="color: #569cd6;">def</span> <span style="color: #dcdcaa;">synchronize_csv_files</span>(<span style="color: #9cdcfe;">old_csv_path</span>, <span style="color: #9cdcfe;">new_csv_path</span>, <span style="color: #9cdcfe;">output_csv_path</span>):</div><div> <span style="color: #6a9955;"># Read the old and new CSV files into DataFrames</span></div><div> <span style="color: #9cdcfe;">old_df</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">pd</span>.<span style="color: #dcdcaa;">read_csv</span>(<span style="color: #9cdcfe;">old_csv_path</span>)</div><div> <span style="color: #9cdcfe;">new_df</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">pd</span>.<span style="color: #dcdcaa;">read_csv</span>(<span style="color: #9cdcfe;">new_csv_path</span>)</div><div> <span style="color: #6a9955;"># Find the matching value in the first column of the second row in the new CSV</span></div><div> <span style="color: #9cdcfe;">matching_value</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">old_df</span>.<span style="color: #9cdcfe;">iloc</span>[<span style="color: #b5cea8;">1</span>, <span style="color: #b5cea8;">0</span>]</div><div> <span style="color: #6a9955;"># Delete rows from the new CSV until the matching value is found in the first column</span></div><div> <span style="color: #c586c0;">while</span> <span style="color: #9cdcfe;">new_df</span>.<span style="color: #9cdcfe;">iloc</span>[<span style="color: #b5cea8;">1</span>, <span style="color: #b5cea8;">0</span>] <span style="color: #d4d4d4;">!=</span> <span style="color: #9cdcfe;">matching_value</span>:</div><div> <span style="color: #9cdcfe;">new_df</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">new_df</span>.<span style="color: #9cdcfe;">iloc</span>[<span style="color: #b5cea8;">1</span>:]</div><div> <span style="color: #6a9955;"># Reset the index of the new DataFrame</span></div><div> <span style="color: #9cdcfe;">new_df</span>.<span style="color: #dcdcaa;">reset_index</span>(<span style="color: #9cdcfe;">drop</span><span style="color: #d4d4d4;">=</span><span style="color: #569cd6;">True</span>, <span style="color: #9cdcfe;">inplace</span><span style="color: #d4d4d4;">=</span><span style="color: #569cd6;">True</span>)</div><div> <span style="color: #6a9955;"># Combine the rows from both CSV files into a single DataFrame</span></div><div> <span style="color: #9cdcfe;">combined_df</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">pd</span>.<span style="color: #dcdcaa;">concat</span>([<span style="color: #9cdcfe;">old_df</span>, <span style="color: #9cdcfe;">new_df</span>], <span style="color: #9cdcfe;">axis</span><span style="color: #d4d4d4;">=</span><span style="color: #b5cea8;">1</span>)</div><div> <span style="color: #6a9955;"># Save the synchronized CSV to the output file</span></div><div> <span style="color: #9cdcfe;">combined_df</span>.<span style="color: #dcdcaa;">to_csv</span>(<span style="color: #9cdcfe;">output_csv_path</span>, <span style="color: #9cdcfe;">index</span><span style="color: #d4d4d4;">=</span><span style="color: #569cd6;">False</span>)</div><br /><div><span style="color: #6a9955;">#count video views difference</span></div><div><span style="color: #569cd6;">def</span> <span style="color: #dcdcaa;">calculate_numbers</span>(<span style="color: #9cdcfe;">input_csv_path</span>, <span style="color: #9cdcfe;">output_csv_path</span>):</div><div> <span style="color: #6a9955;"># Read the synchronized CSV file into a DataFrame</span></div><div> <span style="color: #9cdcfe;">df</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">pd</span>.<span style="color: #dcdcaa;">read_csv</span>(<span style="color: #9cdcfe;">input_csv_path</span>)</div><div> <span style="color: #6a9955;"># Calculate the result in the 6th column based on the values in the 2nd and 5th columns</span></div><div> <span style="color: #9cdcfe;">df</span>[<span style="color: #ce9178;">'GAP'</span>] <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">df</span>[<span style="color: #ce9178;">'Views.1'</span>] <span style="color: #dcdcaa;">-</span> <span style="color: #9cdcfe;">df</span>[<span style="color: #ce9178;">'Views'</span>]</div><div> <span style="color: #6a9955;"># Save the modified CSV to the output file with a new name</span></div><div> <span style="color: #9cdcfe;">df</span>.<span style="color: #dcdcaa;">to_csv</span>(<span style="color: #9cdcfe;">output_csv_path</span>, <span style="color: #9cdcfe;">index</span><span style="color: #d4d4d4;">=</span><span style="color: #569cd6;">False</span>)</div><br /><br /><br /><div><span style="color: #6a9955;">#after 24 hours worksheet update, and all the last function work in this function.</span></div><div><span style="color: #569cd6;">def</span> <span style="color: #dcdcaa;">worksheet_after</span>():</div><div> <span style="color: #6a9955;"># Get all non-empty cells in the first column of Sheet1</span></div><div> <span style="color: #9cdcfe;">first_column_cells</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">findall</span>(<span style="color: #4ec9b0;">re</span>.<span style="color: #dcdcaa;">compile</span>(<span style="color: #569cd6;">r</span><span style="color: #d16969;">'^https</span><span style="color: #d7ba7d;">?</span><span style="color: #d16969;">://'</span>))</div><div> </div><div> <span style="color: #c586c0;">for</span> <span style="color: #9cdcfe;">cell</span> <span style="color: #c586c0;">in</span> <span style="color: #9cdcfe;">first_column_cells</span>:</div><div> <span style="color: #9cdcfe;">url</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">cell</span>.value</div><div> <span style="color: #6a9955;"># Check if the corresponding cell in the fifth column is empty</span></div><div> <span style="color: #9cdcfe;">fifth_column_value</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">cell</span>(<span style="color: #9cdcfe;">cell</span>.row, <span style="color: #b5cea8;">5</span>).<span style="color: #9cdcfe;">value</span></div><div> <span style="color: #c586c0;">if</span> <span style="color: #569cd6;">not</span> <span style="color: #9cdcfe;">fifth_column_value</span>:</div><div> <span style="color: #6a9955;"># Get channel details</span></div><div> <span style="color: #9cdcfe;">channel_info</span> <span style="color: #d4d4d4;">=</span> <span style="color: #dcdcaa;">get_channel_info_using_api</span>(<span style="color: #9cdcfe;">url</span>)</div><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">channel_info</span>:</div><div> <span style="color: #9cdcfe;">raw_channel_name</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">channel_info</span>[<span style="color: #ce9178;">"name"</span>]</div><div> <span style="color: #9cdcfe;">channel_name</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">channel_info</span>[<span style="color: #ce9178;">"name"</span>].strip().lower() <span style="color: #6a9955;"># Normalize channel name</span></div><div> <span style="color: #9cdcfe;">channel_id</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">channel_info</span>[<span style="color: #ce9178;">"id"</span>]</div><div> <span style="color: #9cdcfe;">sa</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">gspread</span>.<span style="color: #dcdcaa;">service_account</span>(<span style="color: #9cdcfe;">filename</span><span style="color: #d4d4d4;">=</span><span style="color: #ce9178;">"/Users/jitendersingh/Documents/sheetsapi.json"</span>)</div><div> <span style="color: #9cdcfe;">sh</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sa</span>.<span style="color: #dcdcaa;">open</span>(<span style="color: #ce9178;">'YT_Channel_views'</span>)</div><div> <span style="color: #9cdcfe;">worksheets</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sh</span>.<span style="color: #dcdcaa;">worksheets</span>()</div><div> <span style="color: #9cdcfe;">worksheet_titles</span> <span style="color: #d4d4d4;">=</span> [<span style="color: #9cdcfe;">worksheet</span>.<span style="color: #9cdcfe;">title</span>.strip().lower() <span style="color: #c586c0;">for</span> <span style="color: #9cdcfe;">worksheet</span> <span style="color: #c586c0;">in</span> <span style="color: #9cdcfe;">worksheets</span>] <span style="color: #6a9955;"># Normalize worksheet titles</span></div><div> <span style="color: #6a9955;"># print(worksheet_titles)</span></div><div> <span style="color: #6a9955;"># Check if a worksheet with the same name already exists</span></div><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">channel_name</span> <span style="color: #569cd6;">in</span> <span style="color: #9cdcfe;">worksheet_titles</span>:</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #9cdcfe;">channel_name</span>)</div><div> <span style="color: #6a9955;"># Get videos using channel id</span></div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Getting the videos of '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">'."</span>)</div><div> <span style="color: #9cdcfe;">videos</span> <span style="color: #d4d4d4;">=</span> <span style="color: #dcdcaa;">get_channel_videos</span>(<span style="color: #9cdcfe;">channel_id</span>)</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Got the videos of '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">'."</span>)</div><br /><div> <span style="color: #9cdcfe;">after_csv_file_path</span> <span style="color: #d4d4d4;">=</span> <span style="color: #ce9178;">"/Users/jitendersingh/Documents/youtube_csv/"</span><span style="color: #d4d4d4;">+</span><span style="color: #9cdcfe;">raw_channel_name</span><span style="color: #d4d4d4;">+</span><span style="color: #ce9178;">" AFTER.csv"</span></div><div> <span style="color: #dcdcaa;">save_to_csv</span>(<span style="color: #9cdcfe;">videos</span>, <span style="color: #9cdcfe;">after_csv_file_path</span>)</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Saved the csv file of '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">'."</span>)</div><br /><br /><div> <span style="color: #9cdcfe;">old_csv_path</span> <span style="color: #d4d4d4;">=</span> <span style="color: #ce9178;">"/Users/jitendersingh/Documents/youtube_csv/"</span><span style="color: #d4d4d4;">+</span><span style="color: #9cdcfe;">raw_channel_name</span><span style="color: #d4d4d4;">+</span><span style="color: #ce9178;">" BEFORE.csv"</span></div><div> <span style="color: #9cdcfe;">output_csv_path</span> <span style="color: #d4d4d4;">=</span> <span style="color: #ce9178;">"/Users/jitendersingh/Documents/youtube_csv/temp_csv.csv"</span></div><div> <span style="color: #dcdcaa;">synchronize_csv_files</span>(<span style="color: #9cdcfe;">old_csv_path</span>, <span style="color: #9cdcfe;">after_csv_file_path</span>, <span style="color: #9cdcfe;">output_csv_path</span>)</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"csv file of '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">raw_channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">' synchronize csv file."</span>)</div><div> <span style="color: #9cdcfe;">input_csv_path</span> <span style="color: #d4d4d4;">=</span> <span style="color: #ce9178;">"/Users/jitendersingh/Documents/youtube_csv/temp_csv.csv"</span></div><div> <span style="color: #9cdcfe;">output_csv_path</span> <span style="color: #d4d4d4;">=</span> <span style="color: #ce9178;">"/Users/jitendersingh/Documents/youtube_csv/"</span><span style="color: #d4d4d4;">+</span><span style="color: #9cdcfe;">raw_channel_name</span><span style="color: #d4d4d4;">+</span><span style="color: #ce9178;">" 24 HOURS.csv"</span></div><div> <span style="color: #dcdcaa;">calculate_numbers</span>(<span style="color: #9cdcfe;">input_csv_path</span>, <span style="color: #9cdcfe;">output_csv_path</span>)</div><div> <span style="color: #4ec9b0;">tm</span>.<span style="color: #dcdcaa;">sleep</span>(<span style="color: #b5cea8;">1</span>)</div><div> <span style="color: #4ec9b0;">os</span>.<span style="color: #dcdcaa;">remove</span>(<span style="color: #ce9178;">"/Users/jitendersingh/Documents/youtube_csv/temp_csv.csv"</span>)</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"csv file of '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">raw_channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">' synchronize csv file done."</span>)</div><br /><br /><div> <span style="color: #9cdcfe;">worksheet</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">sh</span>.<span style="color: #dcdcaa;">worksheet</span>(<span style="color: #9cdcfe;">raw_channel_name</span>)</div><br /><div> <span style="color: #6a9955;"># Read the CSV file</span></div><div> <span style="color: #c586c0;">with</span> <span style="color: #dcdcaa;">open</span>(<span style="color: #9cdcfe;">output_csv_path</span>, <span style="color: #ce9178;">'r'</span>) <span style="color: #c586c0;">as</span> <span style="color: #9cdcfe;">file</span>:</div><div> <span style="color: #9cdcfe;">csv_data</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">list</span>(<span style="color: #4ec9b0;">csv</span>.<span style="color: #dcdcaa;">reader</span>(<span style="color: #9cdcfe;">file</span>))</div><br /><div> <span style="color: #6a9955;"># Update the worksheet with the CSV data</span></div><div> <span style="color: #9cdcfe;">worksheet</span>.<span style="color: #dcdcaa;">update</span>(<span style="color: #ce9178;">'A1'</span>, <span style="color: #9cdcfe;">csv_data</span>)</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"csv file of '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">' Sheet update done."</span>)</div><br /><div> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">update_cell</span>(<span style="color: #9cdcfe;">cell</span>.row, <span style="color: #b5cea8;">5</span>, <span style="color: #ce9178;">"24 hours Process Done"</span>)</div><br /><div> <span style="color: #6a9955;"># Get current date and time</span></div><div> <span style="color: #9cdcfe;">current_date</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">datetime</span>.<span style="color: #4ec9b0;">datetime</span>.<span style="color: #dcdcaa;">now</span>().<span style="color: #dcdcaa;">strftime</span>(<span style="color: #ce9178;">"</span><span style="color: #569cd6;">%d</span><span style="color: #ce9178;"> %B %Y"</span>) <span style="color: #6a9955;"># Format date as dd-mm-yyyy</span></div><div> <span style="color: #9cdcfe;">current_time</span> <span style="color: #d4d4d4;">=</span> <span style="color: #4ec9b0;">datetime</span>.<span style="color: #4ec9b0;">datetime</span>.<span style="color: #dcdcaa;">now</span>().<span style="color: #dcdcaa;">strftime</span>(<span style="color: #ce9178;">"%H:%M"</span>) <span style="color: #6a9955;"># Format time in 24-hour format</span></div><div> <span style="color: #6a9955;"># Update cells</span></div><div> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">update_cell</span>(<span style="color: #9cdcfe;">cell</span>.row, <span style="color: #b5cea8;">6</span>, <span style="color: #9cdcfe;">current_date</span>) <span style="color: #6a9955;"># Updating current date in the third column</span></div><div> <span style="color: #9cdcfe;">sheet</span>.<span style="color: #dcdcaa;">update_cell</span>(<span style="color: #9cdcfe;">cell</span>.row, <span style="color: #b5cea8;">7</span>, <span style="color: #9cdcfe;">current_time</span>) <span style="color: #6a9955;"># Updating current time in the fourth column</span></div><br /><div> <span style="color: #c586c0;">else</span>:</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"Failed to get channel info for URL: </span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">url</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">"</span>)</div><div> <span style="color: #c586c0;">else</span>:</div><div> <span style="color: #6a9955;"># Get channel name from the URL</span></div><div> <span style="color: #9cdcfe;">channel_info</span> <span style="color: #d4d4d4;">=</span> <span style="color: #dcdcaa;">get_channel_info_using_api</span>(<span style="color: #9cdcfe;">url</span>)</div><div> <span style="color: #c586c0;">if</span> <span style="color: #9cdcfe;">channel_info</span>:</div><div> <span style="color: #9cdcfe;">channel_name</span> <span style="color: #d4d4d4;">=</span> <span style="color: #9cdcfe;">channel_info</span>[<span style="color: #ce9178;">"name"</span>]</div><div> <span style="color: #dcdcaa;">print</span>(<span style="color: #569cd6;">f</span><span style="color: #ce9178;">"24 hours process already done for channel '</span><span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">channel_name</span><span style="color: #569cd6;">}</span><span style="color: #ce9178;">'."</span>)</div><br /><br /><br /><div><span style="color: #6a9955;"># create_worksheet_for_channel()</span></div><div><span style="color: #6a9955;"># print("first process complete---------------------")</span></div><div><span style="color: #dcdcaa;">worksheet_after</span>()</div><br /><br /><br /></div>

Youtube data scrape locally, and upload csv to sheet

  import re import gspread import datetime from googleapiclient . discovery import build import time as tm import csv import os i...

Read more »