[SOLVED] Is there a way I can make my script more efficient?

Issue

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("")
  }
}

Solution

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

Answered By – TheWizEd

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *