Creating One to N rows

Question

I am struggling with creating a script that takes each value from col A and maps them based on values in col C and D.

Image below for visual explanation:

Answer 1

Try this. Notice that I use a while loop for each column in case they are different heights. And if they are all the same height getDataRange() would return the last filled row so I need to add an empty row to stop the while loops.

function mapper() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("Sheet3");
    // assuming output isn't there yet range=A1:D4
    var data = sh.getDataRange().getValues();
    data.push(["","","",""]); // push an empty row to be safe
    var i = 1;
    var j = 1;
    var k = 1;
    var rows = [];
    while( data[i][0] !== "" ) {
      j = 1;
      while( data[j][2] !== "" ) {
        k = 1;
        while( data[k][3] !== "" ) {
          rows.push([data[i][0],data[j][2],data[k][3]]);
          k++;
        }
        j++;
      }
      i++;
    }
    // put results in F2
    sh.getRange(2,6,rows.length,rows[0].length).setValues(rows);
  }
  catch(err) {
    Logger.log(err);
  }
}

Answer 2

I believe what you are trying to find is the Cartesian Product of columns A, C, and D.

Here I implemented rsp answer on Cartesian product of multiple arrays in JavaScript to your issue.

Try this:

Code:

function myFunction() {
  var sh = SpreadsheetApp.getActiveSheet();
  var nameRangeLastRow = sh.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow();
  var nameVal = sh.getRange(2, 1, nameRangeLastRow - 1, 1).getValues().flat();
  var regionRangeLastRow = sh.getRange("C1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow();
  var regionVal = sh.getRange(2, 3, regionRangeLastRow - 1, 1).getValues().flat();
  var positionRangeLastRow = sh.getRange("D1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow();
  var positionVal = sh.getRange(2, 4, positionRangeLastRow - 1, 1).getValues().flat();
  const cartesian = (...a) => a.reduce((a, b) => a.flatMap(d => b.map(e => [d, e].flat())));
  var result = cartesian(nameVal, regionVal, positionVal);
  sh.getRange(2, 6, result.length, 3).setValues(result);
}

Output: