unable to get index of date in array with apps script-CodePudding

using apps script, I try to get the index of the top of column date within an array of month / year values. The code triggers on editing any cell below those date columns.

Index keeps returning -1 instead of the index position in the array.

What am I doing wrong ??

Link to the spreadsheet : https://docs.google.com/spreadsheets/d/121HEP0EDRcGsxX72n_ga6-_pAgLnlAGWukGc5WmXdfo/edit?usp=sharing

Here's the code :

function onEdit(e) {
  testIndexOf(e)
}

function testIndexOf(e) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let month = ["January","February","March","April","May","June","July","August","September","October","November","December"];
  let months = ss.getRange('A2:A14').getDisplayValues() // 2D array
  let months1D = months.map(function(r) { return r[0] }) // 1D array
    
  let sheet = e.range.getSheet();
  //let currentDate = sheet.getRange(1, e.range.columnStart).getValue()
  let currentDate = sheet.getRange(1, e.range.columnStart).getDisplayValue()
  let currentYear = currentDate.getFullYear()
  let currentMonth = month[currentDate.getMonth() ]
  let monthYear = currentMonth   " "   currentYear 

  // get index of month/year in column A
  let index = months1D.indexOf(monthYear)
  Logger.log("monthYear : "   monthYear   "\n")
  Logger.log("index : "   index)
}

When using .getDisplayValue() :

LOG :

Jan 31, 2022, 10:53:12 AM Error TypeError: currentDate.getFullYear is not a function at testIndexOf(Code:14:33) at onEdit(Code:2:3)

CodePudding user response：

When I saw your sample Spreadsheet, the values of "A2:A14" has A0 as the space. On the other hand, you are using 20 as the space at let monthYear = currentMonth " " currentYear. I thought that this might be the reason for your current issue of Index keeps returning -1 instead of the index position in the array.. In order to return the index from the array of month using monthYear, how about the following modification?

Pattern 1:

When you want to use the current values of "A2:A14", please modify your script as follows.

From:

let monthYear = currentMonth   " "   currentYear

To:

let monthYear = currentMonth   String.fromCharCode(0xA0)   currentYear;

Or,

From:

let months1D = months.map(function(r) { return r[0] })

To:

let months1D = months.map(function (r) { return r[0].replace(String.fromCharCode(0xA0), " ") });

And also, please modify let currentDate = sheet.getRange(1, e.range.columnStart).getDisplayValue() to let currentDate = sheet.getRange(1, e.range.columnStart).getValue().

Pattern 2:

When you want to modify the values of "A2:A14", please modify the space from A0 to 20 (Normal space.). In this case, you are not required to modify let monthYear = currentMonth " " currentYear. In this case, you can do this using the following simple script.

SpreadsheetApp.getActiveSheet().getRange("A2:A14").createTextFinder(String.fromCharCode(0xA0)).replaceAllWith(" ");

And also, please modify let currentDate = sheet.getRange(1, e.range.columnStart).getDisplayValue() to let currentDate = sheet.getRange(1, e.range.columnStart).getValue().