Google Apps script to move rows with comments to another sheet

I have a script to move rows to another sheet, it works but not completely as I would like.

I have a SpreadSheet with 2 tabs, one "A PLANIFIER" and one "MASTER".
In tab "A PLANIFIER", in column B the value can be OUI or NON, the rows to move are the ones with the value OUI.
Columns B, D, F to U, V to AB and AE from "A PLANIFIER" to H, J, L to AA, AM to AS and BL from "MASTER" respectively.

I modified the script in SputnikDrunk2's reply with the moveTo() function, which works great because it also transfers comments but the downside is that it messes up the conditional formatting of the target sheet. So I used the copyTo() function, which also works very well but the disadvantage here is that the comments are not imported.

I tried several solutions with setComment() and getComment() but I really can't do it.

Could someone please help me understand my mistake?

My Sheet
In script project, the name is : Transfert Client Planifié.gs

My Script:

function transferData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("A PLANIFIER");
var targetSheet = ss.getSheetByName("MASTER");
// Retrieve the sheet row numbers with 'OUI' data & place in an array
var ouiData = sourceSheet.getDataRange().getValues().map((x, i) => [i + 1, x[1]]).filter(x => x[1] == 'OUI');
// Get the next empty row from the target sheet [reference: https://stackoverflow.com/a/56080850/15384825]
var targetSheetNextRow = targetSheet.getRange(1, 8).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;
ouiData.forEach(y => {
var row = y[0];
sourceSheet.getRange("B" + row).copyTo(targetSheet.getRange("H" + targetSheetNextRow), {contentsOnly:true});
sourceSheet.getRange("D" + row).copyTo(targetSheet.getRange("J" + targetSheetNextRow), {contentsOnly:true});
sourceSheet.getRange("F" + row + ":U" + row).copyTo(targetSheet.getRange("L" + targetSheetNextRow + ":AA" + targetSheetNextRow), {contentsOnly:true});
sourceSheet.getRange("V" + row + ":AB" + row).copyTo(targetSheet.getRange("AM" + targetSheetNextRow + ":AS" + targetSheetNextRow), {contentsOnly:true});
sourceSheet.getRange("AE" + row).copyTo(targetSheet.getRange("BL" + targetSheetNextRow), {contentsOnly:true});
// Delete transferred values from source sheet
sourceSheet.getRange("B" + row + ":AE" + row).clearContent();
// Update the next empty row on the target sheet
targetSheetNextRow = targetSheetNextRow + 1;
}
)
sortM();
sortAP();
}

I found this solution :

function transferData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("A PLANIFIER");
var targetSheet = ss.getSheetByName("MASTER");
// Retrieve the sheet row numbers with 'OUI' data & place in an array
var ouiData = sourceSheet.getDataRange().getValues().map((x, i) => [i + 1, x[1]]).filter(x => x[1] == 'OUI');
// Get the next empty row from the target sheet [reference: https://stackoverflow.com/a/56080850/15384825]
var targetSheetNextRow = targetSheet.getRange(1, 8).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;
ouiData.forEach(y => {
var row = y[0];
sourceSheet.getRange("B" + row).moveTo(targetSheet.getRange("H" + targetSheetNextRow));
sourceSheet.getRange("D" + row).moveTo(targetSheet.getRange("J" + targetSheetNextRow));
sourceSheet.getRange("F" + row + ":U" + row).moveTo(targetSheet.getRange("L" + targetSheetNextRow + ":AA" + targetSheetNextRow));
sourceSheet.getRange("V" + row + ":AB" + row).moveTo(targetSheet.getRange("AM" + targetSheetNextRow + ":AS" + targetSheetNextRow));
sourceSheet.getRange("AE" + row).moveTo(targetSheet.getRange("BL" + targetSheetNextRow));
// Clear format of the target sheet
targetSheet.getRange("H" + targetSheetNextRow + ":BL" + targetSheetNextRow).clearFormat();
// Update the next empty row on the target sheet
targetSheetNextRow = targetSheetNextRow + 1;
// Keep the format of the target line
var rng = targetSheet.getRange("A7:CL7")
rng.copyTo(targetSheet.getRange("A6:CL200" + row), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
rng.copyTo(targetSheet.getRange("A6:CL200" + row), SpreadsheetApp.CopyPasteType.PASTE_CONDITIONAL_FORMATTING, false);
// Keep the format of the source line
var rng = sourceSheet.getRange("A50:AG50")
rng.copyTo(sourceSheet.getRange("A6:AG50" + row), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
rng.copyTo(sourceSheet.getRange("A6:AG50" + row), SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);
}
)
sortM();
sortAP();
}

SUGGESTION

> Note: Please be advised that we do not code for you, but you could use this tweaked script below to guide you in your actual project.

Upon checking on your script, your loop breaks after the first row of data, thus it only moves one row. Perhaps you can test this tweaked script below where it specifically gathers the sheet rows on A PLANIFIER with "OUI" data into an array & loop through it via forEach(). I added some comments on the script for more context.

I still used your original logic as it seems it would break your whole setup & sheet's formatting if a different method is used (also given you've already built multiple script files). Lastly, it is easier to copy the notes with moveTo() for less lines of code though the running speed gets compromised.

Script

function transferData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("A PLANIFIER");
var targetSheet = ss.getSheetByName("MASTER");
// Retrieve the sheet row numbers with 'OUI' data & place in an array
var ouiData = sourceSheet.getDataRange().getValues().map((x, i) => [i + 1, x[1]]).filter(x => x[1] == 'OUI');
// Get the next empty row from the target sheet [reference: https://stackoverflow.com/a/56080850/15384825]
var targetSheetNextRow = targetSheet.getRange(1, 8).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;
ouiData.forEach(y => {
var row = y[0];
sourceSheet.getRange("B" + row).moveTo(targetSheet.getRange("H" + targetSheetNextRow));
sourceSheet.getRange("D" + row).moveTo(targetSheet.getRange("J" + targetSheetNextRow));
sourceSheet.getRange("F" + row + ":U" + row).moveTo(targetSheet.getRange("L" + targetSheetNextRow + ":AA" + targetSheetNextRow));
sourceSheet.getRange("V" + row + ":AB" + row).moveTo(targetSheet.getRange("AM" + targetSheetNextRow + ":AS" + targetSheetNextRow));
sourceSheet.getRange("AE" + row).moveTo(targetSheet.getRange("BL" + targetSheetNextRow));
// Update the next empty row on the target sheet
targetSheetNextRow = targetSheetNextRow + 1;
// Keep the format of the source line
var rng = sourceSheet.getRange("A50:AG50")
rng.copyTo(sourceSheet.getRange("A6:AG50" + row), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
}
)
sortM();
sortAP();
}

Demo

After running the script:

References

• JavaScript Array forEach()
• JavaScript Array map()
• JavaScript Array filter()