Trouble updating a batch of cells in Google Sheets App Scripts

38 views Asked by At

So, I am running a program that sorts through a large spreadsheet and organizes each row based into other sheets based on properties of individual cells in that row. I got the macro working, but it was extremely slow so I went back and started from scratch to try to get it working faster using block execution instead of reading and writing every cell in a loop repeatedly. I'm running into a problem here with my If statement below. If I am interpreting the error message correctly, it is not reading the second portion of the 2D array. The code I have is:

range = portSheet.getRange(2, 1, portSheet.getLastRow(), portSheet.getLastColumn());
  var cellValues = range.getValues();
  for (i = 0; i <= cellValues.length; i++){
    if (cellValues[i][7] == "Confirmed"){
      cellValues[i][7] = "X-Confirmed";      
    }
  }
  range.setValues(cellValues);

The error I receive is

"TypeError: Cannot read property '6' of undefined (line 20)".

Line 20, specifically is:

if (cellValues[i][7] == "Confirmed"){

I added some test lines to make sure the information is read and processed properly and that I was pulling the right cell. That looked like this:

range = portSheet.getRange(2, 1, portSheet.getLastRow(), portSheet.getLastColumn());
  var cellValues = range.getValues();
  range = sipSheet.getRange(2, 1);
  range.setValue(cellValues[0][6]);
  range = sipSheet.getRange(2, 2);
  range.setValue(cellValues[0][7]);
  for (i = 0; i <= cellValues.length; i++){
    if (cellValues[i][7] == "Confirmed"){
      cellValues[i][7] = "X-Confirmed";      
    }
  }
  range.setValues(cellValues);

Which operated as expected until the line in question. I tested with a cell to the left and the cell I was aiming to get to make sure I was counting cells correctly. The cells printed as expected with the test lines so I know the array is in fact 7 columns wide and the values exist in the array before the if statement.

I have spent several days now researching this and I cannot find anything that pertains to my situation online. I'm pretty new to coding, so it may be something obvious but I would appreciate any guidance available.

0

There are 0 answers