Google App Script Concating Arrays change the values for the whole array

Question

I have a list of 290 items with 4 columns, which I need to duplicate. I have in a Spreadsheet Row some departements and under it a lot of systems. for each system I need to duplicated the 290 values and add one index column at the front, the department in column 6 and the system in column 7.

I am using the following code:

const ssOrg = SS.getSheetByName("OrgStructure");

function myFunction() {

  var afinal = [];
  var aDevs = ssDeliverables.getDataRange().getValues();
  aDevs.shift();

  var lastRow = ssOrg.getLastRow();
  var lastColum = ssOrg.getLastColumn();

  var count = 1

  for (var spalte = 1; spalte <lastColum; spalte++){

    var squad = ssOrg.getRange(3,spalte).getValue();

    for (var reihe=5; reihe <lastRow; reihe++) {

      var system = ssOrg.getRange(reihe,spalte).getValue();
      if (system !== ""){
        
          aDevs.map(function(row){
            row[0] = count;
            row[5] = squad;
            row[6] = system; 
            count ++
            return row
            })
        Logger.log(system);
        afinal = afinal.concat(aDevs);
        
      }
    }
  }
    var lastDataRow = ssAssessmentLogic.getLastRow();
    ssAssessmentLogic.getRange(2,1,lastDataRow-1,10).clearContent();
    var rngResult = ssAssessmentLogic.getRange(2,1,afinal.length,7);
    rngResult.setValues(afinal);

}

The problem is that the array at the end (16000 rows) has the same value for each row in column 6 and 7. It is allways the last system & department combination that appears in all 16000 rows.

Where am I wrong?

Answer 1

The question was a little confusing for me but I followed your specifics in the comments section where you explain what exactly info to copy and how and where to paste it. This gets the job done:

const ss = SpreadsheetApp.getActive();

// this gets the "deliverables" and the "departments", this last ones in a list 
// and for each department runs the function to add the complete new modified array to the spreadsheet
function fillsNewSheet(){
   var newSheet = ss.getSheetByName('List_DeliverablesALL');
   // hardcoded the titles
   newSheet.getRange(1, 1, 1, 6).setValues([['taskHasDeliverableNumber',    'Deliverable Description',  'SDP Task', 'SDP Milestone', 'Group', 'Department']])
   var deliverables = getDeliverables();
   var departments = getDepartments();
   for(i=0;i<departments.length;i++){
      var departmentsGroup = departments[i];
      for(j=0;j<departmentsGroup.length;j++){
        addsNewSection(deliverables, departmentsGroup[j])
      }
    }
}


// this just gets de array of values we are gonna paste for each department in the structure sheet.
function getDeliverables(){
  var deliSheet =ss.getSheetByName('Deliverables1');
  var deliValues = deliSheet.getRange(2, 2, deliSheet.getLastRow()-1, 4).getValues();
  return deliValues;
}

// As the departments are in different columns with different row counts,
// I go over the whole columns and rows and create a single list with all "department" and "group" pairs
function getDepartments(){
  var structureSheet = ss.getSheetByName('OrgStructure');
  var cols = structureSheet.getLastColumn();
  var groups = structureSheet.getRange(3, 1, 1, cols).getValues()[0]
  var departments = [];
  for(i=1;i<=cols;i++){
    var group = groups[i-1];
    var groupDeps = structureSheet.getRange(5, i, structureSheet.getLastRow(), 1).getValues();
    var subDeps = []
    for(j=0;j<groupDeps.length;j++){
      subDeps.push([group, groupDeps[j][0]])
    }
    var filtered = subDeps.filter( function (data) { return data[1] != "" });
    departments.push(filtered);
  }
    return departments;
 }
    
 // finally this gets the complete list of "deliverables" from the first sheet, and one specific department.
  function addsNewSection(deliverables, department){
      var newSheet = ss.getSheetByName('List_DeliverablesALL');
      // and in every row of the deliverables list we add the corresponding department/group pair to get the new modified array.
      var newSection = []
      for(k=0;k<deliverables.length;k++){
        var newRow = deliverables[k].concat(department)
        newSection.push(newRow)
      }
      // when this is complete I paste the whole new array in the third sheet.
      newSheet.getRange(newSheet.getLastRow()+1, 1, newSection.length, 6).setValues(newSection)
 }