Google spreadsheet - script throws "Exceeded maximum execution time" error

Question

I have the following google script that will run against 500+ rows of data in the "InputFromHospital" sheet and in the middle of execution, getting the "Exceeded maximum execution time" error.

I was having few more lines of code but after reading the similar questions in StackOverflow, I removed and kept only the needed lines/source code. I am not sure how I could further optimize.

Looking for your suggestions/expertise to optimize and to reduce the execution time to under 5 minutes?

function feedToMasterAndPolice() {
  var ssbook = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ssbook.getSheetByName('MasterPatientData')
  var sLastRow = srcSheet.getLastRow() + 1
  var inputSheet = ssbook.getSheetByName('InputFromHospital')
  var iLastRow = inputSheet.getLastRow();
  var pSheet = ssbook.getSheetByName('DataToPolice')
  var pLastRow = pSheet.getLastRow() + 1;

  var todayDate = Utilities.formatDate(new Date(), "IST", "dd/MMM/yyyy")

  //Loop thru all rows in "Input.." sheet
  for (var i = 2; i <= iLastRow; i++) {
    //Reading contact number from "Input.." sheet
    var value = inputSheet.getRange(i, 5).getValue();
    var gID = "";
    if (value.toString.length > 0) {
      //Generating unique patient ID for each COVID Patient
      gID = "PID".concat(srcSheet.getLastRow());
      srcSheet.getRange(sLastRow, 1).setValue(gID.toString());
      //Looping thru all 8 colums in "Input" sheet
      for (var colN = 2; colN < 9; colN++) {
        var actCellVal = inputSheet.getRange(i, colN).getValue();
         //Set value for contact# and date values
        if (colN == 5 || colN == 6 || colN == 8) {
          srcSheet.getRange(sLastRow, colN).setValue(actCellVal)
        } else {
         //All String values - upper case
          srcSheet.getRange(sLastRow, colN).setValue(actCellVal.toString().toUpperCase())
        }
      }

      var cR = srcSheet.getLastRow();
      //Adding formula to calculate "DAYS SINCE ADMISSION" - example =if(A2<>"",TODAY()-F2,"")
      srcSheet.getRange(sLastRow, 9).setFormula("=if(A" + cR + "<>\"\",TODAY()-F" + cR + ",\"\")")
      //Adding formula to calculate "FOLLOW UP NEEDED" - example =if(I2<=Admin!$C$2,"YES","NO"
      srcSheet.getRange(sLastRow, 10).setFormula("=if(I" + cR + "<=\'Admin\'!$C$2,\"YES\",\"NO\")");
      //Add current date
      srcSheet.getRange(sLastRow, 11).setValue(todayDate);

      sLastRow = sLastRow + 1

    } else {
      //Above logic same when contact number is blank
      //Not considerd Patient ID #
      var ppID = "NCPID".concat(pSheet.getLastRow());
      //Untested
      pSheet.getRange(pLastRow, 1).clear();
      pSheet.getRange(pLastRow, 1).setValue(ppID.toString());
      //till above
      for (var colN = 2; colN <= 9; colN++) {
        var actCellVal = inputSheet.getRange(i, colN).getValue();
        if (colN == 6 || colN == 8) {
          pSheet.getRange(pLastRow, colN).setValue(actCellVal)
        } else {
          pSheet.getRange(pLastRow, colN).setValue(actCellVal.toString().toUpperCase())
          pSheet.getRange(pLastRow, 9).setValue(todayDate);
        }
      }
      pLastRow = pLastRow + 1
    }
  }
   //this.ClearAnySheet("InputFromHospital")
};

Sample file available here.

Answer 1

I believe your goal as follows.

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

Modified script:

function feedToMasterAndPolice_b() {
  var ssbook = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ssbook.getSheetByName('MasterPatientData');
  var sLastRow = srcSheet.getLastRow() + 1;
  var inputSheet = ssbook.getSheetByName('InputFromHospital');
  var pSheet = ssbook.getSheetByName('DataToPolice');
  var pLastRow = pSheet.getLastRow() + 1;
  var todayDate = Utilities.formatDate(new Date(), "IST", "dd/MMM/yyyy");


  var [, ...values] = inputSheet.getDataRange().getValues();
  var { masterPatientData, dataToPolice } = values.reduce((o, [c1, c2, c3, c4, c5, c6, c7, c8], i) => {
    if (c5.toString() != "") {
      var last = sLastRow - 1 + o.masterPatientData.length;
      o.masterPatientData.push([
        `PID${last}`,
        ...[c2, c3, c4, c5, c6, c7, c8].map((c, j) => [3, 4, 6].includes(j) ? c : c.toString().toUpperCase()),
        `=if(A${last + 1}<>"",TODAY()-F${last + 1},"")`,
        `=if(I${last + 1}<='Admin'!\$C\$2,"YES","NO")`,
        todayDate,
      ]);
    } else {
      var last = pLastRow - 1 + o.dataToPolice.length;
      o.dataToPolice.push([
        `NCPID${last}`,
        ...[c2, c3, c4, c5, c6, c7, c8].map((c, j) => [4, 6].includes(j) ? c : c.toString().toUpperCase()),
        todayDate,
      ]);
    }
    return o;
  }, { masterPatientData: [], dataToPolice: [] });
  if (masterPatientData.length > 0) {
    srcSheet.getRange(sLastRow, 1, masterPatientData.length, masterPatientData[0].length).setValues(masterPatientData);
  }
  if (dataToPolice.length > 0) {
    pSheet.getRange(pLastRow, 1, dataToPolice.length, dataToPolice[0].length).setValues(dataToPolice);
  }
}

Note:

• Unfortunately, I cannot test above modified script with your actual situation. So when my proposed script cannot be used for your actual situation, can you provide the sample Spreadsheet for replicating the issue? By this, I would like to confirm it.

References:

• Benchmark: Reading and Writing Spreadsheet using Google Apps Script
• getValues()
• setValues(values)