Array updating two values when it shouldn't

[sben...@bbchs.org]

Hello,

I have written a script to copy every element in an array to a new array.  Then if there is a value 2 in a specific column it pushes it a second time and updates a value in that new "row".  The problem that I am running into is that instead of updating the last row it is updating the last two rows simultaneously. I am sharing an example copy of the sheet.

https://docs.google.com/spreadsheets/d/1WkHts1hheq24FFiN-WcNFUXTHK7DJKODG1C6S2Ww9Jg/edit?usp=sharing

function setup() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var schSheet = ss.getSheetByName('Sheet4');

  var lRow = schSheet.getLastRow();//gets last row of source data

  var data = schSheet.getRange(1, 1, lRow, schSheet.getLastColumn()).getValues(); //gets source data

  var dataLen = data.length;//gets source data length

  var newData = [];//creates a new array to push rows to

  for (var i = 0; i < dataLen; i++) {//iterates through source data rows

    newData.push(data[i]);//pushes row to new array

    if (data[i][8] == 2) {//if has value  of 2

      Logger.log(newData.length);

      newData.push(data[i]); //pushes row to new array a second time

      var nlen = newData.length;//get length of newData array in order to get the last row of the array

      Logger.log(nlen);

      newData[nlen - 1][6] = 9;//updates the last row of the new array.  (Here is where I run into the problem.  It is updating the last two rows.  )

      Logger.log(newData.length);

    }

  }

  ss.getSheetByName('Sheet5').getRange(1, 1, newData.length, newData[0].length).setValues(newData);//pastes new array to sheet5

}

[Ed Sambuco]

You have an indexing problem:  You are including the header row when you should not.

Your cell references should start from row 2 and not row 1 (in range statements.)

Your array references can then stay with a start of 0.

Otherwise you change array start index to 1 and decrement the dataLength by 1

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/77fe8881-092e-40a9-9b15-69d171754fcfn%40googlegroups.com.

[Bennett, Scott]

Here is what I don't understand.

Both rows have a value of 4 at 6th value.  

Then the one line of code later that tells the last row to update a value, both now have a value of 9 at the 6th value.  

But only have code to update the last single row.  

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAANZsGNqE3Ou3KotGP9ET2B_AOT1%3DaSODB6-JGxmn0xFWYQBpA%40mail.gmail.com.

--

Scott Bennett

[cbmserv...@gmail.com]

What you are running into is the difference between making a new copy of an object versus referencing an object from 2 places.

 

Even though you think you created a new row, all you actually did in the code is make 2 references to the same object. So when you update the object from one of the references (to value 9), you can see the changes from both places you reference them.

 

Hope this helps.

 

To change this, try just copying the row rather than pushing the row into the new array. I have struggled with something similar in the past. Attached is something that should work for you:

 

function setup() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var schSheet = ss.getSheetByName('Sheet4');

  ss.getSheetByName('Sheet5').getDataRange().clearContent();//clears sheet5

  SpreadsheetApp.flush()

  var lRow = schSheet.getLastRow();//gets last row of source data

  var data = schSheet.getRange(1, 1, lRow, schSheet.getLastColumn()).getValues(); //gets source data

  var dataLen = data.length;//gets source data length (this is not needed, you already have lRow which is same)

  var nlen =0;

 

  var newData = [];//creates a new array to push rows to

  for (var i = 0; i < dataLen; i++) {//iterates through source data rows

 

    newData.push(data[i]);//pushes row to new array

    if (data[i][8] == 2) {//if has value  of 2

      nlen++; // increment count of additional entries into array

      newData[i+nlen] = [];

      for (var j=0;j<data[i].length;j++)

      {

        newData[i+nlen][j] = data[i][j];

        }

      //newData[i+nlen] = data[i]; //pushes row to new array a second time

      newData[i+nlen][6] = 9;//updates the last row of the new array.  (Here is where I run into the problem.  It is updating the last two rows.  )

    }

  }

  ss.getSheetByName('Sheet5').getRange(1, 1, newData.length, newData[0].length).setValues(newData);//pastes new array to sheet5

 

}

 

 

 

From: 'Bennett, Scott' via Google Apps Script Community <google-apps-sc...@googlegroups.com>
Sent: December 31, 2021 10:02 AM
To: google-apps-sc...@googlegroups.com
Subject: Re: [Apps-Script] Array updating two values when it shouldn't

 

Here is what I don't understand.

Both rows have a value of 4 at 6th value.  

 

Then the one line of code later that tells the last row to update a value, both now have a value of 9 at the 6th value.  

But only have code to update the last single row.  

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANHZS1MMWd%3DTN8uTepJNPJ_77Et7y29ivPiZzUCpM%3Dw6vw%40mail.gmail.com.

[sben...@bbchs.org]

Thank you, I figured it had something to do with the way it was being handled in memory.