Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsrow

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

  • 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: enter image description here

    References