I have a script that populates with today's date column J when column A is filled.

function Populate() {

  var sheetNameToWatch = "MASTER";
  var columnNumberToWatch = /* column A */ 1;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();
  var val=sheet.getActiveCell().getValue()

  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && val!= ""  ) {
    var targetCell = sheet.getRange(range.getRow(), range.getColumn() 9
                               );
    targetCell.setValue(""   Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd"));
  }
}

I think it's quite slow and I also would like to fill more columns at once, aside from the date on Column J:

• Column I: "No payment"
• Column L: "PENDING"

In order to fill multiple columns and try to make it work faster, I've also tested another version:

function Populate2(e) 
{
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== 'MASTER'|| e.range.getColumn() !== 1)
  {
    return;
  }
  for(var i=0;i<e.range.getNumRows();i  )
  {
    var offset=e.range.getRow()   i;
    sheet.getRange('I'  offset).setValue("No Payment");
    sheet.getRange('J'  offset).setValue(new Date());
    sheet.getRange('L'  offset).setValue("PENDING");
  }
}

The last version has the problem that even if I clean the column A, the values are filled.

Couldn't figure out which version - if any - would be the best approach to improve regarding efficiency, and how. Can anyone give me a hand? Thank you in advance.

CodePudding user response：

I believe your goal is as follows.

• You want to reduce the process cost of your script.
• When the values of column "A" are removed, you want to clear the columns "I", "J", and "L".

In this case, how about the following modification?

Modified script:

function Populate2(e) {
  var range = e.range;
  var sheet = e.source.getActiveSheet();
  if (sheet.getSheetName() !== 'MASTER' || range.columnStart !== 1) {
    return;
  }
  var values = range.getDisplayValues();
  var { noPayment, date, pending, clear } = values.reduce((o, [a], i) => {
    var row = range.rowStart   i;
    if (a == "") {
      o.clear.push(...["I", "J", "L"].map(e => e   row));
    } else {
      o.noPayment.push("I"   row);
      o.date.push("J"   row);
      o.pending.push("L"   row);
    }
    return o;
  }, { noPayment: [], date: [], pending: [], clear: [] });
  if (noPayment.length > 0) {
    sheet.getRangeList(noPayment).setValue("No Payment");
    sheet.getRangeList(date).setValue(new Date());
    sheet.getRangeList(pending).setValue("PENDING");
  }
  if (clear.length > 0) {
    sheet.getRangeList(clear).clearContent();
  }
}

• In this modification, the values are put to the cells using the range list. And also, the cells are cleared using the range list.

Note:

• From your question, this modified script supposes that your function Populate2 is installed as OnEdit trigger. Please be careful about this.
• I think that in your script, onEdit simple trigger might be also used. But, I'm not sure about your actual situation. So I used Populate2 in your script.

References:

• getRangeList(a1Notations)
• Class RangeList

CodePudding user response：

Try this:

function Populate2(e) {
  //e.source.toast('Entry');
  var sh = e.range.getSheet();
  if (sh.getName() !== 'Master' || e.range.columnStart !== 1) { return; }
  //e.source.toast('flag1')
  let n = e.range.rowEnd - e.range.rowStart   1;
  let dt = new Date();
  let i = [...Array.from(new Array(n).keys(),x => ["No Payment"])];
  let j = [...Array.from(new Array(n).keys(),x => [dt])];
  let l = [...Array.from(new Array(n).keys(),x => ["PENDING"])];
  sh.getRange(e.range.rowStart, 9, n).setValues(i);
  sh.getRange(e.range.rowStart, 10, n).setValues(j);
  sh.getRange(e.range.rowStart, 12, n).setValues(l);
}

or

function Populate2(e) {
  //e.source.toast('Entry');
  var sh = e.range.getSheet();
  if (sh.getName() !== 'Master' || e.range.columnStart !== 1) { return; }
  //e.source.toast('flag1')
  let n = e.range.rowEnd - e.range.rowStart   1;
  let dt = new Date();
  let i = [...Array.from(new Array(n).keys(),x => ["No Payment",dt])];
  let l = [...Array.from(new Array(n).keys(),x => ["PENDING"])];
  sh.getRange(e.range.rowStart, 9, n, 2).setValues(i);
  sh.getRange(e.range.rowStart, 12, n).setValues(l);
}

Array.from()

Array Constructor