Me gustaría aprender una forma alternativa de ejecutar este ciclo for, porque actualmente establece el valor a través de cada iteración y, dado que esto puede aumentar, es mejor que sea más eficaz.
function calculate() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const settingSheet = ss.getSheetByName('settings'); const dataSheet= ss.getSheetByName('Data'); let settingData = settingSheet.getRange(1, 1, settingSheet.getLastRow(), settingSheet.getLastColumn()).getValues(); let spcData = dataSheet.getRange(1, 1, dataSheet.getLastRow(), dataSheet.getLastColumn()).getValues(); let recordTypesAccepted = ['strategy1', 'goal2']; var settingPar1 = settingData.filter(e => e[0] == 'default').map(e => e[1]); var settingPar2 = settingData.filter(e => e[0] == 'default').map(e => e[2]); for (let a = 0; a < spcData.length; a++) { for (let r = 0; r < settingData .length; r++) { let settingSearchTerm = settingData[r][4]; let spcSearchTerm = spcData[a][10]; let settingMatchType = settingData[r][5]; let spcMatchType = spcData[a][9]; let spcRecordType = spcData[a][8]; if (recordTypesAccepted.indexOf(spcRecordType) > -1 && settingMatchType === spcMatchType) { if (settingSearchTerm == spcSearchTerm) { settingPar1 = settingData[r][7]; settingPar2 = settingData[r][6]; } let spcClicks = spcData[a][10]; let spcOrders = spcData[a][11]; let row = a + 1; if (spcClicks >= settingsPar1 && spcOrders >= settingsPar2) { let newValue = 10; spcSheet.getRange(row, 20).setValue(newBid).setBackground("#D8E271"); } } } } }¡¡¡Gracias!!!
Descripción
Siempre que use setValues() no sobrescriba ninguna fórmula que pueda usar getValues() para obtener una matriz de los valores antiguos, reemplace cualquiera que deba actualizarse y simplemente vuelva a colocar la matriz de valores usando setValues(). Lo mismo para los formatos de fondo y números.
Tenga en cuenta que se supone del OP que spcSheet se define en otro lugar.
Guion
function calculate() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const settingSheet = ss.getSheetByName('settings'); const dataSheet= ss.getSheetByName('Data'); let settingData = settingSheet.getRange(1, 1, settingSheet.getLastRow(), settingSheet.getLastColumn()).getValues(); let spcData = dataSheet.getRange(1, 1, dataSheet.getLastRow(), dataSheet.getLastColumn()).getValues(); let recordTypesAccepted = ['strategy1', 'goal2']; var settingPar1 = settingData.filter(e => e[0] == 'default').map(e => e[1]); var settingPar2 = settingData.filter(e => e[0] == 'default').map(e => e[2]); let spcRange = spcSheet.getRange(1,20,spcSheet.getLastRow(),1) var spcValues = spcRange.getValues(); var backgrounds = spcRange.getBackgrounds(); var numberformats = spcRange.getNumberFormats(); for (let a = 0; a < spcData.length; a++) { for (let r = 0; r < settingData .length; r++) { let settingSearchTerm = settingData[r][4]; let spcSearchTerm = spcData[a][10]; let settingMatchType = settingData[r][5]; let spcMatchType = spcData[a][9]; let spcRecordType = spcData[a][8]; if (recordTypesAccepted.indexOf(spcRecordType) > -1 && settingMatchType === spcMatchType) { if (settingSearchTerm == spcSearchTerm) { settingPar1 = settingData[r][7]; settingPar2 = settingData[r][6]; } let spcClicks = spcData[a][10]; let spcOrders = spcData[a][11]; if (spcClicks >= settingsPar1 && spcOrders >= settingsPar2) { let newValue = 10; spcValues[a][0] = newBid; backgrounds[a][0] = "#D8E271"; numberformats[a][0] = "€##0.00"; } } } } spcRange.setValues(spcValues); spcRange.setBackgrounds(backgrounds); spcRange.setNumberFormats(numberformats); }Referencia