The code below has been working, but I'm afraid that with a couple of thousands rows more, it can get laggy and I was wondering what would be the approach to improve it.

It basically compares 2 datasets by a number common to both and update a couple of columns with their status/checkboxes (TRUE, or FALSE):

function onEdit(e) {
if (e.range.getSheet().getName() === 'Todays Tests V2' && e.range.getA1Notation() === 'C3') {
    var formRespSheet = e.source.getSheetByName('Form Responses 1');
    var formRespRng = formRespSheet.getRange(2, 13, formRespSheet.getLastRow() - 1, 4);
    var formRespValues = formRespRng.getValues();

    var todaysTest = e.source.getSheetByName('Todays Tests V2');
    var todaysTestData = todaysTest.getRange(6, 1, todaysTest.getLastRow(), 18).getValues();

    todaysTest.getRange('O6:O').clearContent();
    todaysTest.getRange('Q6:Q').clearContent();
    todaysTest.getRange('R6:R').clearContent();

    for (var i = 0; i < formRespValues.length; i  ) {
      for (var j = 0; j < todaysTestData.length; j  )
        if (formRespValues[i][1] == todaysTestData[j][1]) {
          if (formRespValues[i][0] == 'Yes') {
            todaysTest.getRange('O'   (6   j)).setValue('TRUE')
          } else {
            todaysTest.getRange('O'   (6   j)).setValue('FALSE')
          }
          if (formRespValues[i][2] == 'Yes') {
            todaysTest.getRange('Q'   (6   j)).setValue('TRUE')
          } else {
            todaysTest.getRange('Q'   (6   j)).setValue('FALSE')
          }
          if (formRespValues[i][3] == 'Yes') {
            todaysTest.getRange('R'   (6   j)).setValue('TRUE')
          } else {
            todaysTest.getRange('R'   (6   j)).setValue('FALSE')
          }
        }
    }

CodePudding user response：

I see some things that can be further improved with @MisterJojo's answer, see modifications below:

Modifications:

function onEdit(e) {
  if (e.range.getSheet().getName() === 'Todays Tests V2' && 
      e.range.getA1Notation() === 'C3') {
    let formRespSheet     = e.source.getSheetByName('Form Responses 1'),
        formRespRng       = formRespSheet.getRange(2, 13, formRespSheet.getLastRow() - 1, 4),
        formRespValues    = formRespRng.getValues(),
        todaysTest        = e.source.getSheetByName('Todays Tests V2'),
        todaysTestNumRows = todaysTest.getLastRow() - 5,
        todaysTestData    = todaysTest.getRange(6, 1, todaysTestNumRows, 18).getValues();

    // this can be replaced with getValues if it feels sluggish
    var inputB    = todaysTestData.map(x => x[1]),
        outputO   = todaysTestData.map(x => [x[14]]), 
        outputQR  = todaysTestData.map(x => [x[16], x[17]]);
        
    for (const frv of formRespValues) {
      let frv0test = (frv[0] == 'Yes') ? 'TRUE' : 'FALSE', 
          frv2test = (frv[2] == 'Yes') ? 'TRUE' : 'FALSE',
          frv3test = (frv[3] == 'Yes') ? 'TRUE' : 'FALSE';

      let index = inputB.indexOf(frv[1]);
      if (index > -1) {
        outputO[index]  = [frv0test];
        outputQR[index] = [frv2test, frv3test];
      }
    }
    // write by bulk
    todaysTest.getRange(6, 15, outputO.length, outputO[0].length).setValues(outputO);
    todaysTest.getRange(6, 17, outputQR.length, outputQR[0].length).setValues(outputQR);
  }
}

Summary of changes:

• Fixed the incorrect last row value of todaysTestData by subtracting 5.
• Removed clearContent since you are writing FALSE after a blank cell which basically removes the tick in the checkbox.
• Used map instead to reduce method calls to SpreadsheetApp. These map functions can be replaced by getValues if you see a better getValues performance.
• Instead of looping your todaysTestData, just use indexOf to check what index frv[1] is found on the columnB of todaysTestData (if there is any) and use that index to write data there.
• Write by bulk using setValues instead of looping setValue.
• Combine writing Q and R since they are adjacent columns.

CodePudding user response：

try that...

function onEdit(e)
  {
  if ( e.range.getSheet().getName() === 'Todays Tests V2' 
    && e.range.getA1Notation() === 'C3') 
    {
    let 
      formRespSheet  = e.source.getSheetByName('Form Responses 1')
    , formRespRng    = formRespSheet.getRange(2, 13, formRespSheet.getLastRow() - 1, 4)
    , formRespValues = formRespRng.getValues()
    , todaysTest     = e.source.getSheetByName('Todays Tests V2')
    , todaysTestData = todaysTest.getRange(6, 1, todaysTest.getLastRow(), 18).getValues()
      ;
    todaysTest.getRange('O6:O').clearContent();
    todaysTest.getRange('Q6:Q').clearContent();
    todaysTest.getRange('R6:R').clearContent();

    for (const frv of formRespValues)
      {
      let
        frv0test = (frv[0] == 'Yes') ? 'TRUE' : 'FALSE'
      , frv2test = (frv[2] == 'Yes') ? 'TRUE' : 'FALSE'
      , frv3test = (frv[3] == 'Yes') ? 'TRUE' : 'FALSE'
        ;
      todaysTestData.forEach( (ttd,j) =>
        {
        if (frv[1] == ttd[1])
          {
          let j6 = j 6
          todaysTest.getRange(`O${j6}`).setValue( frv0test )
          todaysTest.getRange(`Q${j6}`).setValue( frv2test )
          todaysTest.getRange(`R${j6}`).setValue( frv3test )
          }
        })
      }
    }
  }