El siguiente código ha estado funcionando, pero me temo que con un par de miles de filas más, puede retrasarse y me preguntaba cuál sería el enfoque para mejorarlo.
Básicamente, compara 2 conjuntos de datos por un número común a ambos y actualiza un par de columnas con su estado/casillas de verificación (VERDADERO o FALSO):
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') } } }Veo algunas cosas que se pueden mejorar aún más con la respuesta de @MisterJojo, vea las modificaciones a continuación:
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); } }todaysTestData restando 5.clearContent ya que está escribiendo FALSE después de una celda en blanco que básicamente elimina la marca en la casilla de verificación.map usado en su lugar para reducir las llamadas de método a SpreadsheetApp . Estas funciones de map se pueden reemplazar por getValues si ve un mejor rendimiento de getValues .todaysTestData , solo use indexOf para verificar qué índice frv[1] se encuentra en la columna B de todaysTestData (si hay alguno) y use ese índice para escribir datos allí.setValues en lugar de hacer un bucle de setValue .Q y R ya que son columnas adyacentes.trata eso...
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 ) } }) } } }