Google Apps Script Copying certain cells from a sheet into another sheet without repeating data-CodePudding

Good day!

The code below is working well for me in terms of using a trigger word "Cancelled" in locating rows that have been labelled cancelled by the user in the spreadsheet utilizing this to get the values of 2 cells from the row and is copied into a separate sheet.

function moveToCancelled() {
  var triggerCancel = "Cancelled";
  var hospitalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SPMC OVP-DOH MAIPP");
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cancelled Requests");
  var values = hospitalSheet.getRange(2, 18, hospitalSheet.getLastRow()).getValues();
  Logger.log(daysUntilEnd);

  for (var i = 0; i < (hospitalSheet.getLastRow()); i  ) {
    if (values[i][0] == triggerCancel ) {
      var targetLocation = targetSheet.getRange(targetSheet.getDataRange().getLastRow()   1, 1);
      var targetSheetRange = hospitalSheet.getRange(i   2, 6);
      var targetLocation1 = targetSheet.getRange(targetSheet.getDataRange().getLastRow()   1, 2);
      var targetSheetRange1 = hospitalSheet.getRange(i   2, 13);

      targetSheetRange.copyTo(targetLocation);
      targetSheetRange1.copyTo(targetLocation1);
    }    
  }
}

My problem is that I would like to avoid replicating data copied into the new sheet. Any way you guys can help me with this? I just got back coding using Apps Script and would like to see the possibilities in doing this method, Thanks.

CodePudding user response：

In your situation, how about using the value of "Cancelled" as follows?

In this sample, after the row was copied, the value of "Cancelled" is changed to "Cancelled_copied". By this, when the script is run again, only the rows with "Cancelled" are copied. By this, I thought that your goal might be able to be achieved.

And, in your script, copyTo is used in a loop. In this case, the process cost becomes high.

When these points are reflected in your script, how about the following modification?

Modified script:

function moveToCancelled() {
  var triggerCancel = "Cancelled";
  var newTriggerCancel = triggerCancel   "_copied";

  var hospitalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SPMC OVP-DOH MAIPP");
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cancelled Requests");
  var values = hospitalSheet.getRange(2, 1, hospitalSheet.getLastRow(), 18).getValues();
  var { v, cells } = values.reduce((o, r, i) => {
    if (r[17] == triggerCancel) {
      o.v.push([r[5], r[12]]);
      o.cells.push(`R${i   2}`);
    }
    return o;
  }, { v: [], cells: [] });
  if (v.length == 0) return;
  targetSheet.getRange(targetSheet.getLastRow()   1, 1, v.length, v[0].length).setValues(v);
  hospitalSheet.getRangeList(cells).setValue(newTriggerCancel);
}

When this script is run, when the column "R" is "Cancelled", the values of columns "F" and "M" are put into the target sheet. And, "Cancelled" is changed to "Cancelled_copied". By this, when the script is run again, the copied row is skipped.

Note:

In this modification, your goal is achieved by changing the value of "Cancelled" to "Cancelled_copied". This is a sample modification. So, for example, I think that you can put "copied" in a new column and you can use it for checking. In this case, please modify the above script.

Modified script:

Note:

References: