Welcome folks today in this blog post we will be exporting the list of all the youtube channel subscribers list in google sheets automatically. All the full source code of the application is shown below.
Get Started
In order to get started you need to make a new google sheet
project and just rename it to youtube subscribers and then you need to go to tools and select app script
from the dropdown.
And now you need to make the code.gs
file and copy paste the below code
code.gs
1 2 |
const SPREADSHEET_ID = "1FjjVHtazctE31qG1O73eF5vBeZVLTVO6Fg2ChA4w9Iw" const SHEET_NAME = "sheet1" |
As you can see in the above code we are getting the spreadsheet
url and also the name of the current sheet. You need to replace with your google sheet url and the sheet name.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
const getPublicSubscribers = () => { let result = YouTube.Subscriptions.list("subscriberSnippet,snippet,contentDetails",{ "mySubscribers":true, "maxResults":50 }) console.log(result) let nextPageToken = result.nextPageToken const subs = [...result.items] while(nextPageToken){ result = YouTube.Subscriptions.list("subscriberSnippet,snippet,contentDetails",{ "mySubscribers":true, "maxResults":50, pageToken:nextPageToken }) nextPageToken = result.nextPageToken subs.push(...result.items) } const ss = SpreadsheetApp.openById(SPREADSHEET_ID) const sheet1 = ss.getSheetByName(SHEET_NAME) const dataRange = sheet1.getDataRange() const subIdsRange = sheet1.getRange(1,2,dataRange.getNumRows(),1) const values = subs.map((sub) => { const snippet = sub.subscriberSnippet return [snippet.title,snippet.channelId,`https://www.youtube.com/channel/${snippet.channelId}`,sub.snippet.publishedAt] }) if(values.length > 0){ const rowStartIdx = (dataRange.getNumRows() == 1) ? 1 : dataRange.getNumRows() + 1 const insertionRange = sheet1.getRange(rowStartIdx,1,values.length,4) insertionRange.setValues(values) sheet1.autoResizeColumns(1,4) }else{ console.log("Script Ended") } |
As you can see in the above javascript code we are using the Youtube Subscription API and inside that we are using the list()
method to get the actual public subscribers list of the youtube channel. We are passing the maxResults to be 50. For one iteration only 50 results will be returned and then the pageToken will be returned. With the help of pageToken we will be calling once again the same subscription api to get the next 50 subscribers of the channel using the simple While loop until the nextPageToken is null. And we are storing all the subscribers into an array. And after that we are storing the list of subscribers inside the google sheet.
Now you need to add the Youtube API Service as shown below
Now you need to add the scopes
of the youtube api and spreadsheet api inside the appsscript.json file as shown below. First of all you need to go to settings and check mark the below option to show the appsscript.json file as shown below
And now you need to run the code.gs
file and then grant the permission by pressing the allow button as shown below
And now after it is done executing you will see your google sheets
is filled with list of subscribers with name, channel url and date of subscription as shown below