How to get and set values in different tabs of a google sheet?

Question

I have a sheet with TAB A and TAB B.

I want to loop through TAB A and get a matrix of values which I want to paste in TAB B.

It is throwing Errors, "Cannot read property 1, etc

function getValuesFromModal(form) {
  var IdeasCounter = 0;
  const IDEA = 10,
    PROD = 26,
    PROM = 20,
    CLIENT = 4,
    ORANGE = "#e69138",
    GREEN = "#6aa84f",
    RED = "#e06666";
  var rangeSource = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Records").getRange("A2:V").getValues();
  var rangeDest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Board").getRange("A2:E");


  for (i in rangeSource) {
    if (rangeSource[i][0] == "Ideas" && (!isNaN(parseInt(rangeSource[i][17])))) {
      rangeDest[IdeasCounter][0].setValue("( " + rangeSource[i][2] + " )" + "\n" + rangeSource[i][3] + "\n\n" + "[ " + rangeSource[i][17] + " ]");
      switch (rangeSource[i][17]) {
        case rangeSource[i][17] < (IDEA - 2):
          rangeDest[IdeasCounter][0].setBackground(GREEN);
          break;
        case rangeSource[i][17] > (IDEA):
          rangeDest[IdeasCounter][0].setBackground(RED);
          break;
        case rangeSource[i][17] < (IDEA):
          rangeDest[IdeasCounter][0].setBackground(ORANGE);
          break;
      }
      IdeasCounter++;
    }

  }

}

Answer 1

I found a few issues with your script, and have modified it accordingly:

• I renamed rangeSource as you were pulling the values, not the actual range.

• I then created new arrays for the destination values and the cell backgrounds. This is necessary so that, on running the script, previously existing values and background colors will not be overwritten. Also, iterating through an array of a range's values is much faster than iterating through the range.

• You were using a for..in loop, but you shouldn't do that here. Instead, use a sequential for loop. Please take a look at this great answer to understand why. (Also, you can't loop through a Google Range class using the Array [][] notation. You would have to use the very slow getCell() method on the range, but it is vveeerrryyy ssssllooowwww.)

• I saved sourceValues[i][17] to its own variable, but you should, ideally, do so for the other sourceValues[i][*] values as it will be easier to read and maintain.

• The switch statement is incorrect as it is not really meant to handle </> comparisons. Again, someone else's explanation on both how you could do it, and why you should not do it is better than mine; you can read it here.

• I then used batch operations as they're much faster, to write the destination values and set the cell background colors.

After modification, this is what resulted:

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"); // Looked like you were only writing to column A
  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)) { // Right now excluding ideaValue == 10
        destBackgrounds[i] = [GREEN];
      } else if (ideaValue > IDEA) {
        destBackgrounds[i] = [RED];
      } else if (ideaValue < IDEA) {
        destBackgrounds[i] = [ORANGE];
      }
    }
  }
  destRange.setValues(destNewValues).setBackgrounds(destBackgrounds);
}