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?

drive.google.com/file/d/1Y-OLP8lFbvB8fCy2z4OTqVTJOk_sOwDx/… The first sheet are the 290 items I want to duplicate. The second sheets are the departments that I need to add. The third sheet is the expected result — Thorsten Liewald
– Thorsten Liewald, Commented Mar 7, 2021 at 12:00
So you bascially want to take the data B:E from Deliverables and paste it where exactly? It is not very clear from your sample Spreadsheet as the desired vs actual sheets are very different from one another. — Mateo Randwolf
– Mateo Randwolf, Commented Mar 8, 2021 at 8:54
Hi, I want to take the 290 deliverables from page one. Then run throug each department in page two and post the total list in table 3. The list 3 contains the following information: Column 1: Index Column2,3,4,5; the information as written in sheet 1 Column6: The current Group of the outer for loop Column7: the current department of the inner for loop So after 290 lines of deliverables the next 290 lines start with another department and group. Does this clairfy my needs? — Thorsten Liewald
– Thorsten Liewald, Commented Mar 8, 2021 at 12:55
Hi ! I still have some doubts regarding what you are trying to achieve. So, you basically want to take those 290 records from shet 1 and for each department in the second sheet paste them on sheet 3 right? On sheet 3 you take the data of Deliverable,Task, Description and SPD Milestone from sheet1 right? And where exactly on sheet 3 are you intending to indicate the information regarding department and group? — Mateo Randwolf
– Mateo Randwolf, Commented Mar 10, 2021 at 8:28

sebsall · Accepted Answer · 2021-03-11 12:31:23Z

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)
 }

Hi @sebastian ! Could you please provide a brief explanation or comment the code of the changes you made to solve the user's problem so that other users with similar problems can easily understand your modifications? Thanks !
Yes, of course @MateoRandwolf, i will comment the code, I believe this is more helpfull!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related