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')
}
}
}
I see some things that can be further improved with @MisterJojo's answer, see modifications below:
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 by subtracting 5.clearContent since you are writing FALSE after a blank cell which basically removes the tick in the checkbox.map instead to reduce method calls to SpreadsheetApp. These map functions can be replaced by getValues if you see a better getValues performance.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.setValues instead of looping setValue.Q and R since they are adjacent columns.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 )
}
})
}
}
}