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, Jan 25 '21 at 13:28

score 0 · Answer 1 · answered Jan 25 '21 at 13:53

0

I worked it out, the line temp[c] = data[n]; passes values by reference rather than vale, so when pushing, it was pushing the reference, which was obviously wrong. So by changing this to temp[c] = [...data[n]]; I fixed the problem.

answered Jan 25 '21 at 13:53

Becky Whittingham

13
2

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. – Iamblichus Jan 25 '21 at 13:55
@lamblichus Haha sorry :P Thank you, this explanation was very useful. – Becky Whittingham Jan 25 '21 at 15:29

score 0 · Accepted Answer · answered Jan 25 '21 at 13:54

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

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

2 Answers2

Issue:

Code sample:

Related: