I made a script for YouTube search results. I placed my keywords in column A2 & A3. Script is working fine as I am getting the results of 1st & 2nd Keyword (you can see in log pic) but I am only able to retrieve the results of first keyword on my sheet.

function youTubeSearchResults() {

let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let lastRow = sheet.getLastRow();

 for (let i = 0; i < lastRow-1; i  ){

 let keywords = sheet.getRange(2 i,1).getValue();

// Do Search On YouTube
 let searchResults = YouTube.Search.list("id, snippet", {q:keywords, maxResults: 10, type: "video", order: "viewCount", videoDuration: "short", order: "date"});

// Filter Search Results By Kind From searchResults Variable
 let fSearchResults = searchResults.items.filter(function(sr) {return sr.id.kind === "youtube#video"});

// Map Search Results In An Array From fSearchResults Variable
 let modifyResults = fSearchResults.map(function(sr) { return [sr.id.videoId, sr.snippet.title, sr.snippet.publishedAt, sr.snippet.channelTitle, sr.snippet.channelId, sr.snippet.thumbnails.high.url] } );

// Parse modifyResults Variable Values in Sheet
  sheet.getRange(2 i, 2, modifyResults.length, modifyResults[0].length).setValues(modifyResults);
     
 }
}

Apps Script log of both keywords

output data on google sheets

can you please help me how to get the results of second keyword on my sheet too, or if I add more keywords then all the results simultaneously.

I have no coding background so please make it simple for me. Thanks in advance.

CodePudding user response：

Modification points:

• In your script, i of sheet.getRange(2 i, 2, modifyResults.length, modifyResults[0].length).setValues(modifyResults); is the row number. But, the length of modifyResults is different from i. I thought that this is the reason of your issue of but I am only able to retrieve the results of first keyword on my sheet.
• When getValue and setValues are used in a loop, the process cost becomes high. Ref

When these points are reflected in your script, how about the following modification?

Modified script:

function youTubeSearchResults() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getRange("A2:A"   sheet.getLastRow()).getValues();
  const modifyResults = values.flatMap(([keywords]) => {
    const searchResults = YouTube.Search.list("id, snippet", { q: keywords, maxResults: 10, type: "video", order: "viewCount", videoDuration: "short", order: "date" });
    const fSearchResults = searchResults.items.filter(function (sr) { return sr.id.kind === "youtube#video" });
    return fSearchResults.map(function (sr) { return [sr.id.videoId, sr.snippet.title, sr.snippet.publishedAt, sr.snippet.channelTitle, sr.snippet.channelId, sr.snippet.thumbnails.high.url] });
  });
  sheet.getRange(2, 2, modifyResults.length, modifyResults[0].length).setValues(modifyResults);
}

Reference:

• map()