Function getYouTubeComments

YouTube_Comments.js:3–44

()

//"oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.currentonly", "https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/cloud-platform", "https://www.googleapis.com/auth/documents"]

function getYouTubeComments() {
  const apiKey = 'ENTER YOUTUBE API KEY'; // Replace with your API key
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Read and trim the video ID from cell B3
  let videoId = sheet.getRange('B3').getValue().trim(); // Remove any whitespace and ensure no extra characters
  
  // Ensure videoId does not contain any extra characters like backticks or quotes
  videoId = videoId.replace(/[`'"]/g, ""); // Removes any backticks, single or double quotes if present
  
  const maxResults = 100; // Number of comments to fetch in one API call (max 100)
  let nextPageToken = ''; // Used for pagination
  let comments = [];
  
  // Clear content from row 5 downwards
  sheet.getRange('4:5000').clearContent(); // Adjust the range as needed
  
  do {
    // Construct the API URL
    let url = `https://www.googleapis.com/youtube/v3/commentThreads?part=snippet&videoId=${videoId}&maxResults=${maxResults}&key=${apiKey}&pageToken=${nextPageToken}`;
    
    // Fetch the data from YouTube API
    let response = UrlFetchApp.fetch(url);
    let result = JSON.parse(response.getContentText());
    
    // Extract comments and push to the array
    result.items.forEach(item => {
      let comment = item.snippet.topLevelComment.snippet.textDisplay;
      let author = item.snippet.topLevelComment.snippet.authorDisplayName;
      let publishedAt = item.snippet.topLevelComment.snippet.publishedAt;
      comments.push([author, comment, publishedAt]);
    });
    
    // Set nextPageToken for pagination
    nextPageToken = result.nextPageToken;
    
  } while (nextPageToken);

  // Log the comments or insert into Google Sheets
  Logger.log(comments);
  insertCommentsToSheet(comments);
}

// Helper function to insert comments into Google Sheets
function insertCommentsToSheet(comments) {