Google Script .push() to duplicate rows and add ticket numbers

Question

So I am currently in the process of generating ticket numbers based on uniqueIDs. For each person have some data, the most important of which is [uniqueID, ticketNumber (currently emplty), numberOfEntries,...]. What I want to generate is a list where if someone has 3 entries, they would have three rows, where the only difference is the ticket number. I would like the ticket number to be of the form UniqueID-1, UniqueID-2, UniqueID-3 etc.

So far I have this:

function autoDup() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var data = sheet.getDataRange().getValues();
  var newData = [];
  var temp = [];
  
  for(var n in data){
    var unID = data[n][0]; //get uniqueID from column 1
    if(!Number(data[n][2])){continue};// if column 3 is not a number then do nothing
    for(var c=0 ; c < Number(data[n][2]) ; c++){ //loop through the entries
      temp[c] = data[n];
      temp[c][1] = String(unID) + "-" + String(c+1);
      Logger.log(temp[c]);
      newData.push(temp[c]);//store values
      Logger.log(newData);
    }
  }
  
  sheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);// write new data to sheet, overwriting old data
}

In the execution log, I get that temp[c] produces the data that I want, however newData.push(temp[c]) ends up overriding the previous pushes and I get the same ticket number duplicated for a particular entry. This is not what I expected, and I don't understand where I am going wrong?

Please let me know if I haven't explained something well, and I will clarify any points.

I would advice you to add the input and the expected output as an example. You don't need the actual data or even the full data, some mock data that replicates your sheet and structure. — Marios
– Marios, Commented Jan 25, 2021 at 13:28

Iamblichus · Accepted Answer · 2021-01-25 13:54:55Z

Issue:

You are assigning an array (data[n]) to your variable (temp[c]). When you do that, if data[n] is subsequently modified, temp[c] will be modified too, and vice versa.

That's because arrays, unlike primitive types, are reference types. This means when you assign an array to a variable, you are assigning a memory address to your variable, and not the actual array.

So, in order to solve this, you have to copy the contents of one array to the other, not simply assign it. For example, you could replace this:

temp[c] = data[n];

With this:

temp[c] = Array.from(data[n]);

There are other issues that could lead to problems, like declaring your array temp outside both your loops. Because of this, I'd suggest using the following sample instead:

Code sample:

function autoDup() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  const data = sheet.getDataRange().getValues();
  let newData = [];
  data.forEach(row => {
    if (!isNaN(row[2])) { 
      const unID = row[0];
      for (var c = 0; c < Number(row[2]); c++) {
        let currentRow = Array.from(row);
        currentRow[1] = String(unID) + "-" + String(c+1);
        newData.push(currentRow);
      }
    }
  })
  sheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);
}

2 Comments

Iamblichus Over a year ago

I was one minute late :/. In any case, take a look at my answer for a more in depth explanation, an alternative modification and a reworked code. I hope this is useful.

Becky Whittingham Over a year ago

@lamblichus Haha sorry :P Thank you, this explanation was very useful.

Collectives™ on Stack Overflow

Google Script .push() to duplicate rows and add ticket numbers

2 Answers 2

Issue:

Code sample:

Related:

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Issue:

Code sample:

Related:

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related