Author(s: Warrie Usenobong Warrie This article was originally published by , the World’s Most Reputable AI and Technology News and Media Company. We invite you to become an AI sponsor if you’re working on an AI product or service. helps technology and AI startups scale. We can help you bring your technology to everyone. Image of Data Visualization by author, inspired by Pranay_design. Digital data collection is great fun. How about collecting data on the things you enjoy and watch? You might want to track details on your YouTube channel. You might want to keep track of details such as subscribers and views. You might also want these details saved somewhere, possibly in a text file. Additionally, they should be updated at specific intervals. How do you accomplish this? This article will show you how to use Google Apps Script for extracting data from YouTube Channels to Google Sheets and then using Tableau to display the data. Google App Script Google App Scripts are a low-code cloud service platform which allows you to easily extend functionality and interact across Google products. App Script is built using popular programming languages like HTML, JavaScript and CSS. The app script has many other functions, but we will focus on using app scripts for programmatically interfacing with Google Sheets. This is a way to automatically enter data from YouTube. Tableau Tableau is a visually interactive platform that has a large and diverse community. Most data analysts have heard about Tableau. This post will use Tableau Public which is a free online platform for data visualization. Tableau will connect to Google Sheets to display our final data. Let’s start by defining what we need. To access Google Sheets and Apps Script, you must have a Google account. If you don’t already have one, create one. JavaScript knowledge is not required for beginners. Google Cloud Platform account. Let’s now get to the practical steps. A list of YouTube channel IDs is required before you can start building. The ID will allow you to retrieve the details of a channel from the API. It is unique for each channel. This is done manually in a Google Sheet. The channel name does not need to be included in this information. This is the essence of populating these channels’ names. First, take a top-level view and then search for the url_id for each channel. Make a google sheet named “Favorite Youtube Comedy” and add the following: Get Channel ID. This section provides information about how to get the unique ID for your account. Every channel is assigned a unique ID in the URL. On YouTube, search for the populated channel name, click on the channel name in one of the returned video lists and copy the ID from the URL (https://www.youtube.com/channel/UCfvmvOEGw6Nqd6E9qMryE3A). Next, search for the Channel Name. Copy the YouTube ID (image by the author). Then create a column called Channel ID. After this, paste the ID in the appropriate cell. This should be done for every channel. You’ll see that certain URLs differ from traditional ID-based URLs. The URLs of these channels use Youtube-legacy usernames. They do not reveal their IDs within the URL. This means that extracting the channel’s ID requires a new technique. Image by the author: Channel URL with legacy username (image from author). To fix this problem, use Youtube Data API. Copy the username of the channel URL. For example, in the URL- https://www.youtube.com/user/MarkAngelComedy, the username is MarkAngelComedy. To retrieve your channel ID, navigate to Youtube API using your browser. You must be signed in with your Google account. On the API website, click on “List by YouTube username”. In the panel entitled “Try this API”, replace GoogleDevelopers by the channel username. Image by the author: Channel ID extraction from Youtube API To perform this action, you will need to authorize your Google account to access the YouTube API. After access has been granted, you will see the API response as seen above. From this, you can find the channel ID in the JSON value items. You can use this same scenario for subsequent channels. Simply replace your username in the textbox forUsername and run the EXECUTE command. After that, copy and paste the ID into your Google Sheet. You will now see the complete list of channel IDs within a single column. Image by the author: Complete Channel IDs. Step 1 of Google AppScript Setup. Access Google App Script. Once you have the channel IDs, it is time to get the script bound to the google sheet. The editor allows data to be exchanged between Youtube and sheet. In the menu bar of the sheet, click on Extension>Apps script editor. The script will be created and the file code.gs is instantiated using the interface for app script editor. Although the interface is identical to regular code editors, this one runs on a cloud platform. It can also be instantiated using google sheets. It is possible to write your code directly in the function editor and save it as a separate file. Change the Project Name. Change the project name at the top of the script editor from “Untitled Project”, to “Favorite Youtube Channels”. Step 2: Install Youtube Service Apps. Since the YouTube Data API is required to pull in data from the sheet, the YouTube Data API will be needed. You can enable the YouTube service by clicking on the Services < > link in the left pane. Click on YouTube Data API version 3 and then click Add. You will now be able to access the Youtube Data API v3 as indicated in the interface. The next section will show you how to use this interface for extracting data directly from Youtube API and putting it into a sheet. Data extraction You will need to follow the below cause of action to get these items. First, you need to create nine headers that specify the columns of details you want to extract from the sheet. Image by the author: Header for channel details. This is an example of the steps involved in extracting this information. Read the Channel IDs sheet. Extract Data From Youtube
