Google Apps script to move rows with comments to another sheet

Question

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

Please consider asking a subset of the question and from the answer you can derive the rest. Otherwise were just doing all your work for you. I'm not interested. I could do it. I just don't wish to. — Cooper, Mar 30 '23 at 19:35
I would suggest using a different strategy. Looping through and getting each row individually is not very efficient. You could, for example, get the entire range values using range.getValues() and notes using range.getNotes() then loop through the array of values and construct a new value array and note array and then set the value and note arrays in the desired new ranges. — JLMosher, Mar 30 '23 at 19:44
@JLMosher I am currently following your suggestions and for the paste function you suggest copyTo() instead of moveTo()? — Mozart75, Mar 31 '23 at 23:21
I would avoid the .moveTo() and .copyTo() methods for numerous reasons and handle everything with .getValues() and .getNotes(), loop through the array of values with .forEach(), constructing value and note arrays and then use ,setValues().setNotes() to update the target destination cells. this may be more efficient and would not interfere with your conditional formatting. — JLMosher, Apr 03 '23 at 14:01
@JLMosher But Note and Comment are different in Google Sheets.. I need to copy/paste comments — Mozart75, Apr 03 '23 at 14:14
ah, yes you are correct. that's why i never use comments for anything that needs to be programmatically interacted with. you won't be able to do what you are attempting without breaking the conditional formatting as well as any formula cell references in your sheet with the move and copy methods. — JLMosher, Apr 03 '23 at 14:20
@JLMosher I think I found a solution, you can see it upstairs — Mozart75, Apr 03 '23 at 16:04

SputnikDrunk2 · Accepted Answer · 2023-04-01T01:16:40.400

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

Thanks SputnikDrunk2, this script works very well and is much faster. I was looking for an alternative to moveTo() because this function works very well but has a drawback, it completely disables the conditional formatting of the target sheet (MASTER). — Mozart75, Apr 01 '23 at 18:32

Google Apps script to move rows with comments to another sheet

1 Answers1

SUGGESTION

Script

Demo

References