Google App Script: Speed up script - Conditional copy to another tab

Question

There are 2 tabs:

• Alerts
• MASTER

The script:

• In Alerts tab, if A is empty and H >= 45, copies the value from G to A, on the next empty row, in MASTER
• Ads specific values to some columns in tab MASTER, on the recently added row. All new rows will have those same values.

The script was retrieving ocasionally some timeouts and I tried to add some

SpreadsheetApp.flush();

With our without any relation to these additions, the script started to work better but I'm sure it will return more timeouts as the list may grow.

Is it possible to speed it up?

function CopyPendingPayment(){
  var sheet = SpreadsheetApp.getActive();
  var sourceSheet = sheet.getSheetByName("Alerts");
  var destination = sheet.getSheetByName("MASTER");
  var lastRow = sourceSheet.getDataRange().getLastRow();
  var column = destination.getRange('A' + destination.getMaxRows())
  SpreadsheetApp.flush();

  for(var row=3; row<=lastRow; row++){
  if(sourceSheet.getRange(row,1).getValue() == "" & sourceSheet.getRange(row,8).getValue() >= "45"){
      var rangeToCopy = "G"+row+":G"+row;
      var lastFilledRow = parseInt(column.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation().slice(1))
      SpreadsheetApp.flush();
      
      sourceSheet.getRange(rangeToCopy).copyTo(destination.getRange(lastFilledRow+1,1)),{contentsOnly:true};
      destination.getRange(lastFilledRow+1, 9).setValue("No Payment Received");
      destination.getRange(lastFilledRow+1, 10).setValue(new Date());
      destination.getRange(lastFilledRow+1, 12).setValue("PENDING");
      destination.getRange(lastFilledRow+1, 13).setValue("- Reminder sent");
      destination.getRange(lastFilledRow+1, 14).setValue(new Date());
    }
    }
  }

I guess at least the part where it ads the values on the different columns it could be done quicker, right?

Can anyone give me a hand? Thank you in advance

Sample

Answer 1

I believe your goal is as follows.

• You want to reduce the process cost of your script.

In this case, how about the following modification?

Modified script:

function CopyPendingPayment() {
  var sheet = SpreadsheetApp.getActive();
  var sourceSheet = sheet.getSheetByName("Alerts");
  var destination = sheet.getSheetByName("MASTER");
  
  // I modified below script.
  var srcValues = sourceSheet.getRange("A3:H" + sourceSheet.getLastRow()).getValues();
  var lastRow = destination.getLastRow();
  var obj = srcValues.reduce((o, [a,,,,,,g,h], i) => {
    if (a == "" && h >= 45) {
      o.values.push([g]);
      o["No Payment Received"].push(`I${lastRow + i + 1}`);
      o["date"].push(`J${lastRow + i + 1}`, `N${lastRow + i + 1}`);
      o["PENDING"].push(`L${lastRow + i + 1}`);
      o["- Reminder sent"].push(`M${lastRow + i + 1}`);
    }
    return o;
  }, {values: [], "No Payment Received": [], "date": [], "PENDING": [], "- Reminder sent": []});
  destination.getRange(lastRow + 1, 1, obj.values.length).setValues(obj.values);
  ["No Payment Received", "date", "PENDING", "- Reminder sent"].forEach(e => destination.getRangeList(obj[e]).setValue(e == "date" ? new Date() : e));
}

• In this modification, the value of column "A" is put with setValues of Class Range. And, the values of columns "I", "J", "L", "M" and "N" are put with setValue of Class RangeList. Because I thought that you might want to keep other columns.

• As another method, I think that your goal can be also achieved by overwriting the cells of the destination sheet. In that case, how about the following script?

    function CopyPendingPayment() {
      var sheet = SpreadsheetApp.getActive();
      var sourceSheet = sheet.getSheetByName("Alerts");
      var destination = sheet.getSheetByName("MASTER");
      var srcValues = sourceSheet.getRange("A3:H" + sourceSheet.getLastRow()).getValues();
      var lastRow = destination.getLastRow();
      var values = srcValues.reduce((ar, [a,,,,,,g,h], i) => {
        if (a == "" && h >= 45) {
          var date = new Date();
          ar.push([g,,,,,,,,"No Payment Received", date,,"PENDING","- Reminder sent",date]);
        }
        return ar;
      }, []);
      destination.getRange(lastRow + 1, 1, values.length, values[0].length).setValues(values);
    }
  

References:

• setValues(values) of Class Range
• setValue(value) of Class RangeList

Added:

From your following replying,

It seems a lot quicker, indeed. I've tested your versions vs mine. The only problem is that if there is an array in any ot the destination sheet columns, the values are pasted below. The original version takes into consideration that and pastes the values on the 1st blank cell of column A.

How about the following sample script?

Sample script:

function CopyPendingPayment_C() {
  // This is from https://stackoverflow.com/a/44563639
  Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
    const range = this.getRange(offsetRow, columnNumber, 2);
    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 sheet = SpreadsheetApp.getActive();
  var sourceSheet = sheet.getSheetByName("Alerts");
  var destination = sheet.getSheetByName("MASTER");
  
  // I modified below script.
  var srcValues = sourceSheet.getRange("A3:H" + sourceSheet.getLastRow()).getValues();
  var lastRow = destination.get1stEmptyRowFromTop(1) - 1;

  // I modified below part.
  var obj = srcValues.reduce((o, [a,,,,,,g,h]) => {
    if (a == "" && h >= 45) {
      o.offset++;
      o.values.push([g]);
      o["No Payment Received"].push(`I${o.offset}`);
      o["date"].push(`J${o.offset}`, `N${o.offset}`);
      o["PENDING"].push(`L${o.offset}`);
      o["- Reminder sent"].push(`M${o.offset}`);
    }
    return o;
  }, {values: [], "No Payment Received": [], "date": [], "PENDING": [], "- Reminder sent": [], offset: lastRow});

  destination.getRange(lastRow + 1, 1, obj.values.length).setValues(obj.values);
  ["No Payment Received", "date", "PENDING", "- Reminder sent"].forEach(e => destination.getRangeList(obj[e]).setValue(e == "date" ? new Date() : e));
}