unable to get index of date in array with apps script

Question

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)

Edit your question to show latest updated script and logs. console.log({months,monthYear}) — TheMaster
– TheMaster, Commented Jan 31, 2022 at 9:24
You have to use getValue() for currentDate. If you need Date type, use getValue(). If you need String type, use getDisplayValue(). Also provide console.log({months,monthYear}) — TheMaster
– TheMaster, Commented Jan 31, 2022 at 10:46

Tanaike · Accepted Answer · 2022-01-31 12:22:49Z

2

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().

edited Jan 31, 2022 at 12:22

answered Jan 31, 2022 at 12:15

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michel Over a year ago

Wow, I never would have find that by myself ! First time I hear about that bizarre quirk. Thank you very much for going at great length with your detailed explanation !

Tanaike Over a year ago

@Michel Thank you for replying. I'm glad your issue was resolved. Thank you, too.

Collectives™ on Stack Overflow

unable to get index of date in array with apps script

1 Answer 1

Pattern 1:

From:

To:

From:

To:

Pattern 2:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Pattern 1:

From:

To:

From:

To:

Pattern 2:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related