1
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();
}
}
  • 1
    Welcome! Your innermost code runs [49×234=] 11,466 times. I hope obvious, the answer is likely going to involve minimizing what's in those nested loops. No example code, but consider getValues and operating on that array rather than constantly one-off polling and setting cells. – Joel Reid Apr 25 at 15:11
  • Hi! If it's possible for you to share a spreadsheet demonstrating your problem, it would be a bit easier to write a fully correct answer. – Vidar S. Ramdal Apr 26 at 7:39
  • This question is related: webapps.stackexchange.com/a/99307/21583 – Vidar S. Ramdal Apr 26 at 8:14
  • Please add context and don't just dump code. What are you doing here? – user55949 Apr 26 at 11:42
0

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);
  • Hi, thank you for the response. I’m relatively new to both JS and arrays. When running the program it says “TypeError: Cannot Read Property “3” from undefined” referencing line 34 “tempNumber= sheet1Values[y-1][3]”. Would you know how to fix this error? – Danish Bajwa Apr 28 at 16:52
  • So if you're only updating one row, we can simplify a bit. See updated answer. – Vidar S. Ramdal Apr 29 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.