How to make a nested loop in google spreadsheet?

Question

Google Apps Script

I have a list of buyers and products.
There are 30 buyers and 400 products.
I need each buyer to have their own products. That is, there will be a large canvas, for 1 buyer there will be 400 goods, for 2 buyers also, and so on for each buyer. This is necessary so that I can customize the product separately for each buyer.
I have 2 google spreadsheets. 1 - buyers, 2 - goods. Each table may be updated periodically.
The problem is that the goods are overwritten, instead of having 12,000 lines on the sheet, I get only 400.
I wrote such a script. Help with the correct script. Here I think pure knowledge of JavaScript is needed. I can not build the correct logic.
And I also get an error on the very last line: TypeError: Cannot read property "0" from undefined.
The system points to this line of code. My code is:
sheet.getRange(i, 2).setValue(products[i][0]);

function writeData(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();   

  var buyers = getBuyers();
  var products= getProducts();
  
  sheet.getDataRange().clear();
  
  for (var j = 1; j <= buyers .length; j++)
  {
    var i = 0; 
    while (i <= products.length)
    {
      i++;
      sheet.getRange(i, 1).setValue(buyers [j]);
      sheet.getRange(i, 2).setValue(products[i][0]);
      sheet.getRange(i, 3).setValue(products[i][4]);
    }
  }
}

Answer 1

I solved it this way:
function writeData(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var resultSheet = ss.getSheetByName("Result");
var agentsSheet = ss.getSheetByName("Buyers ");
var productsSheet = ss.getSheetByName("Products");
var buyersNumRows = buyersSheet.getDataRange().getNumRows();
var productsNumRows = productsSheet .getDataRange().getNumRows();
var productsNumColumns = productsSheet.getDataRange().getNumColumns();
var buyersData = agentsSheet.getDataRange().getValues();
var productsData = productsSheet .getDataRange().getValues();
resultSheet.getDataRange().clear();
for(var i=1; i<buyersNumRows ;
var lastrow = resultSheet.getLastRow();
resultSheet.getRange(lastrow+1, 1).setValue(buyersData[i][0]);
resultSheet.getRange(lastrow+1, 2, productsNumRows , productsNumColumns).setValues(products);
}
}
ended up with 24,000 lines. The task is completed, for all buyers they have done their services.

Answer 2

Under no circumstances tease the system setValue(), and even in a nested loop. The recording does not take place on your PC, but on the server. Who likes to be pulled like that?
To work even with "drafts", ie. up to 50,000 rows per sheet, first take ALL data, then form a READY array, then do setValue().
More or less like this:

function writeData(){
  //Не факт, что поможет, но вдруг
  SpreadsheetApp.flush();
  
  //Далее как у всех
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var resultSheet = ss.getSheetByName("Result");
  var agentsSheet = ss.getSheetByName("Buyers");
  var productsSheet = ss.getSheetByName("Products");
  
  var aV = agentsSheet.getDataRange().getValues();
  var pV = productsSheet.getDataRange().getValues();
  
  //срезать шапку
  aV.shift();
  //  pV.shift();
  
  var res = [];
  
  for(var i = 0; i< aV.length; i++)
  {
    res.push([].concat([aV[i][0]], pV[0]));    
    for(var j = 1; j < pV.length; j++){      
      res.push([].concat([''], pV[j]));      
    }
  }
  
  res = res.slice(0, 50000);
  resultSheet.getDataRange().clear(); 
  resultSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

Answer 3

sheet.getRange(i, 1) => sheet.getRange(i + (j * 400), 1)