Google Apps Script to Export Youtube Channel Subscribers List in Google Sheets Automatically

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

const SPREADSHEET_ID = "1FjjVHtazctE31qG1O73eF5vBeZVLTVO6Fg2ChA4w9Iw"
const SHEET_NAME = "sheet1"

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.

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")
}

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