Youtube to Google Sheets: How to import Youtube public data

#beginners #tutorial #productivity

We use a variety of services today to support multiple use cases and so, it becomes increasingly important to be able to connect to different integrations.

Here is a step-by-step guide to fetch YouTube metrics and add it to google sheets every day or every month. Also get a notification while we're at it :P

Let's get started! 🚀

Step 1: Creating necessary sheets to hold our statistics

So the first step is to create two different empty set of google sheets to hold our every day and every month statistics. We're looking to create two sets of sheets like this:

Step 2: Let's start working on those integrations!!
Go to this sample project and click on the top right corner to clone it. This project already has youtube integration for metrics set up. So we can simply clone it and get started.

Step 3: Explore API tab

From left hand navigation bar, move to the API tab. In the left hand panel, you'll find different divisions like - Endpoints, Scheduled triggers, DB triggers, Integration triggers, etc. In the scheduled triggers, you should find two triggers already set up.

dailyReporting - Gets executed daily.
monthlyReporting - Get executed monthly.

Step 4: The dailyReporting flow

Click on the dailyReporting trigger and it'll open up the graph for it. Our flow looks something like this:

Clicking on these individual pills will reveal its settings and configurations.

NOTE: You may need to re-authenticate yourself on each plugin as you've cloned the project.

Step 5: Updating configuration of integrations

Let's start inspecting and correcting any integration settings should we have to. Click on the left most trigger will reveal its settings.

As you can check we've set it up to execute every day around 12:17AM. You can change this schedule should you want to by clicking on the trigger.

Similarly, Youtube integration also has different settings that you can configure. Please move to the auth tab to re-authenticate in case it's required. In the optional parameters, you can define the different variables through which you want to query youtube. In this instance we're using these query parameters:

dimentions: day
endDate: 2021-12-30
ids: channel==INSERT_CHANNEL_ID or MINE
metrics: estimatedMinutesWatched
startDate:2021-12-12

Very similarly to how above settings were configured, we can similarly click on the google sheets to configure it's settings.

Authenticate yourself with the account that owns the recently created Google Sheet
Put in the sheet ID (You can get that in your sheet’s url) and put in range e.g A1:B30
Go to option fields there set valueInputOption to RAW
In values, check static, then click on add item, in there type {{1.rows.[0].[0]} - here 1 refers to 1st webhook in the chain that is Youtube, and the array [0][0] represents the first value returned by youtube webhook.

Finally we move to slack, and update its minimal configuration to trigger a message whenever this workflow is executed. It'll look like this:

and Voila! We have our entire chain set up where

We trigger a workflow every day at X time
Where it fetches metrics from youtube
Adds them to google sheet
Sends a message on slack when done!

Step 6: Creating the flow for monthly Reporting

Similar to how we've created the flow for daily reporting, we'll create on for monthly reporting, where most of the queries will be similar.

We just have to change youtube query parameters, like this

dimentions: day
endDate: 2021-12-30
ids: channel==INSERT_CHANNEL_ID or MINE
metrics: likes,comments,views,estimatedMinutesWatched,averageViewDuration
startDate: 2021-12-12

and change google sheet values like this 👇

With these minor changes, we can modify our trigger to post youtube metrics to google sheet monthly. All of the above mentioned query parameters can be found in the documentation for google sheets and youtube.

Conclusion

Hope this guide turns out to be a good help for you guys. You can clone the sample project directly from here.

You can also check out our other guides here.

Join us on discord to discuss or share with our community. Write to us for any support requests at support@canonic.dev. Check out our website to know more about Canonic.