Google Sheets: How do I list rows of data copied from another sheet consecutively, in order, and wit-CodePudding

I'm kind of new to working with Google Sheets, and I've stumbled across an issue while creating buttons for a ticket system. I've been having trouble getting the copied data to just fill the other sheet from the second row and down without gaps or overwriting existing data.

The script basically checks if a checkbox of a certain row is ticked, copies the data in that row, moves that data to another sheet, and then resets that row to it's original state. Meaning it doesn't completely delete the row or format, but only copies the contents.

Here's the code I've been working with so far:

function MigrateInProgress() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();  
    var s = ss.getSheetByName('Copy of New Tickets');
    var s2 = ss.getSheetByName('In Progress');
    var r = s.getRange('G:G');    
    var v = r.getValues();  
    var dataset = [];
for(var i=v.length-1;i>=0;i--) 
    if(v[0,i]=='true') { 
        var data = s.getRange(i 1,1,1,6).getValues(); 
        s2.getRange(i 1,s2.getLastColumn()-6,data.length,6).setValues(data); 
        var datasetrange = s.getRange(i 1,1,1,6);
        datasetrange.clear({contentsOnly: true});
        datasetrange.clear({formatOnly: false});
        s.getRange(i 1,7).setValue(false);    
  }
}

Here's some screenshots to help contextualize what I mean:

Screenshot 1

Screenshot 2

Screenshot 3

Any help would be greatly appreciated. Thank you!

CodePudding user response：

I believe your goal is as follows.

You want to copy the rows of columns "A" to "F" of the source sheet to the destination sheet when the checkbox of column "G" is checked.
At that time, you want to clear the columns "A" to "F" and uncheck the column "G". And, you want to copy the values to the destination sheet without including the empty rows.

In this case, how about the following modification?

Modified script:

function MigrateInProgress() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Copy of New Tickets');
  var s2 = ss.getSheetByName('In Progress');

  var v = s.getRange('A2:G'   s.getLastRow()).getValues();
  var { values, clear, uncheck } = v.reduce((o, r, i) => {
    if (r[6] === true) {
      var row = i   2;
      o.values.push(r.splice(0, 6));
      o.clear.push(`A${row}:F${row}`);
      o.uncheck.push(`G${row}`);
    }
    return o;
  }, { values: [], clear: [], uncheck: [] });
  console.log(values)
  if (values.length > 0) {
    s2.getRange(s2.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
    s.getRangeList(clear).clearContent();
    s.getRangeList(uncheck).uncheck();
  }
}

In this modification, at first, all values are retrieved from the source sheet. And create the values for copying to the destination sheet, and also create the range list for clearing the cells and unchecking the checkboxes.
By the way, the values retrieved by getValues is 2 dimensional array, and in this case the checkbox returns the boolea type. So when you want to use v[0,i]=='true', you can use v[i][0] === true.

About some unclear points, I guessed as follows.

I couldn't understand whether where you want to put the values when the values are copied to the destination sheet. So in this modification, the values are appended to the destination sheet. When you don't want to append the values, please modify s2.getRange(s2.getLastRow() 1, 1, values.length, values[0].length).setValues(values); to s2.getRange(2, 1, values.length, values[0].length).setValues(values);. By this, the values are always put from the row 2.

Modified script:

About some unclear points, I guessed as follows.

References: