How do I make this Google Sheets Script faster

Question

function getCellValue(){
var x=2;
var y=2;
var targetSheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var targetSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
var tempNumber = targetSheet1.getRange(y,4).getValue();
var compareNumber = targetSheet2.getRange(x,1).getValue();

 for (j = 0; j<49; j++)
 {
  x=2;
  var i= 0;
  for (i; i<234; i++)
  {
    if (tempNumber == compareNumber)
    {
      targetSheet2.getRange(x,58).setValue(.5);
      x=x+1;
      compareNumber = targetSheet2.getRange(x,1).getValue();
    }
    else
    {
      x=x+1;
      compareNumber = targetSheet2.getRange(x,1).getValue();
    }
  }
   y=y+1;
   tempNumber = targetSheet1.getRange(y,4).getValue();
}
}

Answer 1

API calls, such as getRange and getValue are expensive, so you should minimize the number of times you get data from your sheet. But there's no apparent cost of getting a lot of data in one call. You could extract all the values from your sheet, store it in a JavaScript array, and operate on that.

So instead of calling getRange etc from within a for loop, rewrite so that you get all the data you need beforehand:

var SHEET_1_STUDENT_ID_COL_IDX = 3;
var SHEET_2_STUDENT_ID_COL_IDX = 0;

var SHEET_2_UPDATE_COL_IDX = 5;

function faster_getCellValue() {
  var x=2;
  var y=1;

  var targetSheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var targetSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');


  function findSheet2RowByStudentId(studentId) {
    for (var i = 0; i < sheet2Values.length; i++) {
      var row = sheet2Values[i];
      if (row[SHEET_2_STUDENT_ID_COL_IDX] === studentId) {
        return i;
      }
    }
  }

  function updateSheet2RowWithValue(rowIdx, colIdx, value) {
    targetSheet2.getRange(rowIdx + 1, colIdx + 1).setValue(value);
  }

  var sheet1Values = targetSheet1.getDataRange().getValues();
  var sheet2Values = targetSheet2.getDataRange().getValues();

  var studentId = sheet1Values[y - 1][SHEET_1_STUDENT_ID_COL_IDX];

  var sheet2RowIdx = findSheet2RowByStudentId(studentId);

  if (sheet2RowIdx === undefined) {
    return; // Student ID not found in Sheet 2
  }

  updateSheet2RowWithValue(sheet2RowIdx, SHEET_2_UPDATE_COL_IDX, .5);

}

Note that I have not tested the above script, but hopefully you will get the general idea so that you're able to implement it on your own:

1. First, copy the sheet's values to a JavaScript array by using var yourValues = sheet.getDataRange().getValues()
2. Update the JavaScript array with new values
3. Write the values back to the sheet by using sheet.getDataRange().setValues(yourValues);