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:
Any help would be greatly appreciated. Thank you!
I believe your goal is as follows.
In this case, how about the following modification?
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.
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.