Why do the arrays in my google apps script get overwritten?

Question

I have a big array with all data from a spreadsheet from which I derive smaller arrays containing parts of the initial array.

Now, with every step the previous array changes and becomes equal to the derived array until at the end all the arrays are the same.

Any idea what I am doing wrong?

(I am not a pro and still in the process of constructing the script, so please bear with me regarding ugly/inefficient code :)

function dataTables() {


  /* All spreadsheet data to tables */


  var mastersheet = SpreadsheetApp.openById("xyz");


  // Data array of whole sheet with values in "Artikelbezeichnung" (field 6)
  var completeTable = mastersheet.getSheetByName("Mastersheet").getDataRange().getValues();
  completeTable.splice(0,2); // Remove header lines 1 & 2
  for ( i = completeTable.length; i > 0 ; i-- ) {
    if (completeTable[i-1][6-1] == "") { completeTable.splice(i-1,1); } // Remove lines with empty "Artikelbezeichnung" (field 6) if necessary
  }

  Logger.log(completeTable); // Log 1

  // Indexeded data array of whole sheet
  var completeTableIndexed = completeTable;
  for ( i = completeTableIndexed.length; i > 0; i-- ) {
    completeTableIndexed[i-1].unshift(i+2);
  }

  Logger.log(completeTableIndexed); // Log 2
  Logger.log(completeTable);

  // Data array of all lines eligible for RTN sheet with Indexed
  var rtnTableIndexed = completeTableIndexed;
  for ( i = rtnTableIndexed.length; i > 0 ; i-- ) {
    if ( rtnTableIndexed[i-1][17] == "Nein" ) { rtnTableIndexed.splice(i-1,1); } // Remove lines with "Reparaturcontroller" (field 17) = "Nein"  if necessary
    else if ( rtnTableIndexed[i-1][28] != "" ) { rtnTableIndexed.splice(i-1,1); } // Remove lines with "Versanddatum G4G -> Hersteller" (tracker, field 28) if necessary
    else if ( rtnTableIndexed[i-1][23] == "Ja" && rtnTableIndexed[i-1][25] == "" ) { rtnTableIndexed.splice(i-1,1); } // Remove lines with "KVA (J/N)" (field 23) = "Ja" and KVA is not yet confirmed ("KVA bestätigt am", field 25) if necessary
  }


  // Data array of all lines eligible for KVA sendings with Indexed
  var kvaTableIndexed = completeTableIndexed;
  for ( i = kvaTableIndexed.length; i > 0 ; i-- ) {
    if ( kvaTableIndexed[i-1][23] != "Ja" && kvaTableIndexed[i-1][24] != "" ) { kvaTableIndexed.splice(i-1,1); } // Remove lines with "KVA (J/N)" (field 23) not "Ja" and "KVA versendet am" (field 24) not empty if necessary
  }


  var results = [completeTableIndexed,rtnTableIndexed, kvaTableIndexed, completeTable]; // Log 3

  Logger.log(completeTableIndexed); // Log 4
  Logger.log(rtnTableIndexed);
  Logger.log(kvaTableIndexed);
  Logger.log(completeTable);




}

Expected output of Log 1 is the complete spreadsheet data without some empty lines - that works just fine and spits out about 15 lines.

Expected output of Log 2 is an indexed array of step one as well as the original array. Instead it outputs the indexed array twice.

Expected output of Log 3 and 4 would be the original array, the indexed original array, and two broken down arrays with each about two lines. Instead, all of the logged arrays are the broken down arrays containing two lines. They are all identical.

In the process the original complete array seems to get overwritten multiple times.

Before posting any further questions I'd recommend that you take this opportunity to take the [tour] and learn how to [ask] and how to write [mcve]. As your question is quite broad and your example is rather involved. — Cooper, Oct 25 '19 at 20:03
As I said in my comment below your question challenged everything I thought I knew about slice and so I felt compelled to ask a question on Stack Overflow however as I was writing the question I asked myself the question I wonder if it only works on single dimensional arrays. And again like so many times in the past when I tried to write a minimal comprehensive question it has led me to the solution. Thus negating the need to ask the question at all. — Cooper, Oct 26 '19 at 13:33

score 1 · Accepted Answer · answered Oct 25 '19 at 20:00

1

Instead of using this var completeTableIndexed = completeTable;

try this:

var completeTableIndexed=[];
completeTable.forEach(function(r){completeTableIndexed.push(r.slice();});

and that will return a copy of the entire array and not a reference to it. While researching this I found that slice doesn't appear to work on multidimensional arrays. I don't know if this will fix all of your problems because your question is quite broad. But it may help get you a little further down the road.

answered Oct 25 '19 at 20:00

Cooper

59,616
6
23
54

Thanks a lot, your solution works great. Indeed that it creates a reference instead of a copy when arrays are handled was the problem. This Stackoverflow thread gives the comprehensive answer on what ways exist to clone an array and which ones are fastest, although not all of them seem to work for apps script: [Fastest way to duplicate an array in JavaScript](https://stackoverflow.com/questions/3978492/fastest-way-to-duplicate-an-array-in-javascript-slice-vs-for-loop) – Dimitrj Oct 26 '19 at 10:25
Thanks for the link I wanted to learn more about that. I admit I found it challenging working on your problem because I thought slice() would work on the 2 dimensional arrays but I couldn’t get it to work. I had to break them up into flattened arrays. For A while it challenged all I thought I knew about this slice(). – Cooper Oct 26 '19 at 13:24
I'm happy I could give a little something back. As far as I understand the article, your solution even is the fastest/best way to handle array copying. Great job and thanks again! – Dimitrj Oct 29 '19 at 12:56

Why do the arrays in my google apps script get overwritten?

1 Answers1