I have created the below script to add a timestamp when a cell equals a specific value. This script repeats for each row but it is not efficient. How can I make this simpler and quicker? Below is an extract of the script, it repeats for each row

function MyFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName('Overview')
  var cell = sheet.getRange('H3').getValue()
  var sent = sheet.getRange('p3').getValue()

  if (cell == "Full" && sent == "") {
    sheet.getRange('p3').setValue(new Date())
  }
  
  else if (cell == "Open" && sent == "") {      
    sheet.getRange('p3').setValue("")
  }
}

CodePudding user response：

Try this.

function MyFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Overview');
  // get range H3:P were H=8, P=16, 9 columns total or index 0 to 8
  var data = sheet.getRange(3,8,sheet.getLastRow()-2,9).getValues();  // get range H3:P
  var i = 0;
  var row = null;

  for( i=0; i<data.length; i   ) {
    row = data[i];
    if( ( row[0] === "Full" )  && ( row[8] === "" ) ) {
      row[8] = new Date();
    }
    // Your else doesn't do anything if blank set blank ??
    row.splice(0,8);  // extract only column P
  }
  sheet.getRange(3,16,data.length,1).setValues(data);
}

CodePudding user response：

Try using setvalues() method of class range

function MyFunction() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName('Overview');
  const rg = sh.getDataRange();
  const vs = rg.getValues();
  let vo = vs.map(r => {
    if( r[7] == "Full") r[15]=new Date()
    if( r[7] == "Open") r[15]='';
    return [r[15]];
  });
  sh.getRange(2,16,vo.length,1).setValues(vo);
}