getDisplayValues is not returning a 2-D array

Question

I am writing a script that updates a cell in a Google Sheet based on the intersection of a Row and Column. I find the row by iterating through a list of unique teacher names. When I find the name, I capture its row number in the variable "row". I then iterate through a range of column headers that are dates to find the specific date, and capture its column number as the variable "column". However, when I look at the structure of each object in my code, the "names" object appears as [[Person1], [Person2],..., [PersonX]] whereas the "dates" object appears as [[date1, date2,..., dateX]]. I can iterate through the names object just fine, but the dates object, not so much, and I suspect it is due to the structure.

I understand that the getDisplayValues returns a string and it works fine in another area of my code when I need to grab the date from a cell and name it "dateValue". But when I look for that dateValue in the "dates" object in the code below, that is where my code fails.

Here is a sample of the code:

function updateTracker(){ 
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var teacherName = sheet.getRange('F7').getDisplayValue();
  var dateValue = sheet.getRange('N7').getDisplayValue();
   
  var tracker = SpreadsheetApp.openById('AAAbbbCCCxxxYYYzzz111222333');
  var tab = tracker.getSheetByName('Tracker');
  var names = tab.getRange(1, 4, tab.getLastRow(), 1).getDisplayValues();
  var dates = tab.getRange(1, 1, 1, tab.getLastColumn()).getDisplayValues();
  
  for (var i in names) {
    if (names[i][0] === teacherName) {
      var row = parseInt(i+1);
      }
  }

  for (var j in dates){
    if (dates[0][j] === dateValue) {
      var column = parseInt(j+1);
    }
  }
  
  var cell = tab.getRange(row, column).setValue('x');
}

I get an error on that last line that getRange expects (number, number) but it getting (number, null).

Any suggestions on editing the code?

TheMaster · Accepted Answer · 2020-09-07T04:51:46.533

Logic:

In case of [[Person1], [Person2],..., [PersonX]], You iterate the "outer" array. There are X elements in the outer array. Each element itself is a array("inner") like [Person1] with only 1 primitive element each lime Person1.

Whereas the "dates" object appears as [[date1, date2,..., dateX]]. There is only 1 element in the outer array[date1, date2,..., dateX] and this array contains many elements like date1.

Solution:

You should iterate the inner array in the dates array:

for (var j in dates[0]){//note `[0]`

Using for...in to iterate arrays is also considered bad practice. Use for...of instead:

let column = null, j = 0;
for (const date of dates[0]){
 j++;
 if (date === dateValue) {
   column = parseInt(j+1);//also bad to declare var in a block. Moved declaration outside
 }
}

References:

THANK YOU! This is EXACTLY what I needed. Also, thank you for the best practice recommendation. Being self-taught, I look for any and all suggestions to make my code better. I had never heard of `for ... of` before, but will go through some of my other projects and revise. Many thanks! — Adam Caudell, Sep 07 '20 at 05:45
@AdamCaudell Although in this case, since you're using the counter `j` and counter for-loop: `for(let j=0;j — TheMaster, Sep 07 '20 at 05:54

getDisplayValues is not returning a 2-D array

1 Answers1

Logic:

Solution:

References: