I have a sheet that tracks what products needs to be delivered to the factory during the week.

I want to have values of two columns (D & H) copied to column A (from sheet: WEEKPLANNING) in first empty cell on another sheet (DELIVERED) when the checkbox in column (G & K)

Now I managed to copy to values from sheet “WEEKPLANNING” column D & H to column A on the same sheet and then copy to column A in sheet “DELIVERED” with the function below.

My biggest issue is the values are not directly copied to the sheet “DELIVERED” and the sheet “WEEKPLANNING” only has 57 rows. So when the rows are filled he stops copying the values and when I delete the values from column A in “WEEKPLANNING”, check a new box, he overwrites what’s in column A in “DELIVERED”

I know my explanation is crap, but do ask if you have questions. I need help

function onEdit(e) {
// Ref: /a/44563639
Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
const range = this.getRange(offsetRow, columnNumber, 11&6);
const values = range.getDisplayValues();
if (values[0][0] && values[1][0]) {
return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
} else if (values[0][0] && !values[1][0]) {
return offsetRow + 1;
}
return offsetRow;
};

var sheetName = "WEEKPLANNING";
var { range } = e;
var sheet = range.getSheet();
if (sheet.getSheetName() != sheetName || range.columnStart != 11&6 || !range.isChecked()) return;
range.offset(0, -3).copyTo(sheet.getRange(sheet.get1stEmptyRowFromTop(1), 1), { contentsOnly: true });

const sourceAndDestinationSheet = ["WEEKPLANNING", "Blad4"]; // Please set the source sheet name and destination sheet name to the 1st and 2nd element.

const dstHeader = [“LOSNR.”]; // This is the header of the destination values.

// 1. Retrieve values from the source sheet.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const [srcSheet, dstSheet] = sourceAndDestinationSheet.map(s => ss.getSheetByName(s));
const [header, …values] = srcSheet.getDataRange().getValues();

// 2. Create an array of destination values.
const colIndexes = dstHeader.map(h => header.indexOf(h));
const dstValues = [dstHeader, …values.map(r => colIndexes.map(i => r[i]))];

// 3. Put the destination values to the destination sheet.
dstSheet.getRange(1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

spreadsheet.getRangeList([‘G4:G55’, ‘K4:K55’]).uncheck();

}