Upon reviewing your script, I have identified several issues and made the necessary adjustments:
- I renamed
rangeSource to accurately reflect its purpose of retrieving values rather than the actual range itself.
I introduced new arrays for storing destination values and cell backgrounds. This step ensures that existing values and background colors are not unintentionally overwritten when the script is executed. Additionally, iterating through an array of a range's values proves to be significantly faster than directly iterating through the range itself. Referencing best practices supports this optimization approach.
In place of the for..in loop you previously used, a sequential for loop is more appropriate in this context. Detailed rationale behind this recommendation can be found in this informative answer. Also, it's essential to note that looping through a Google Range object using the Array notation like [][] might result in performance degradation due to reliance on the slow getCell() method.
I isolated sourceValues[i][17] into a separate variable for clarity. Similarly, consider adopting this practice for other elements within sourceValues to enhance code readability and maintainability.
Your use of a switch statement for handling </> comparisons is incorrect. Alternatives to this misapplication with detailed explanations can be explored elsewhere; one such discussion can be accessed here.
To expedite processing speed, I leveraged batch operations for efficiently writing destination values and setting cell background colors. The usage of batch operations facilitates quicker execution compared to individual value assignments and background color configurations.
Subsequent to these modifications, the revised script appears as follows:
function getValuesFromModal(form) {
var IdeasCounter = 0;
const IDEA = 10,
PROD = 26,
PROM = 20,
CLIENT = 4,
ORANGE = "#e69138",
GREEN = "#6aa84f",
RED = "#e06666";
var sourceValues = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Records").getRange("A2:V").getValues();
var destRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Board").getRange("A2:A"); // Focus on column A exclusively
var destNewValues = destRange.getValues();
var destBackgrounds = destRange.getBackgrounds();
for (var i=0; i<sourceValues.length; i++) {
var ideaValue = sourceValues[i][17];
if (sourceValues[i][0] == "Ideas" && (!isNaN(parseInt(ideaValue)))) {
var destValue = "( " + sourceValues[i][2] + " )\n" + sourceValues[i][3] + "\n\n[ " + ideaValue + " ]";
destNewValues[i][0] = destValue;
if (ideaValue < (IDEA - 2)) { // Excluding ideaValue == 10 currently
destBackgrounds[i] = [GREEN];
} else if (ideaValue > IDEA) {
destBackgrounds[i] = [RED];
} else if (ideaValue < IDEA) {
destBackgrounds[i] = [ORANGE];
}
}
}
destRange.setValues(destNewValues).setBackgrounds(destBackgrounds);
}