Return index of matched array not working - Javascript Google Apps Script

Question

I have a spreadsheet and row #1 has dates in each cell going across

I want to return the column number whenever that column matches today's date. First header starts in cell B1.

I am using the following and I can get it to work, but when instead I do 'return i', it always returns '0'.

function getColumnIndex() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  var data = sheet.getRange(1,2,1,lastColumn).getValues(); //create an array of data from row 1
  for (var i = 0; i <= data.length; i++) {
    var dateToday = Utilities.formatDate(new Date(), "EST", "MM/dd/yyyy")
    if (data[i] == dateToday) {break};
    {  
      return i;
    }
  }
}

Now if I switch the last line 'return i' to 'return dateToday' the function will work and it will return the correct date so I know it's matching properly (and if I change row cells to other values it will return those values if it matches). I just can't get it to spit out the index number when I put 'return i'.

Is this a date format issue somehow, so your comparison isn't matching? To test this, instead of "return i", could you try return "data[2]", for example, to see whether that format matches what you get when you do "return dateToday"? Just an idea... Also, I haven't done much coding recently - is there any chance that the value of i is lost (set to zero) after you break out of the FOR loop? — kirkg13
– kirkg13, Commented Oct 15, 2020 at 0:04

Marios · Accepted Answer · 2020-10-15 08:10:50Z

3

Issues / Explanation:

var data = sheet.getRange(1,2,1,lastColumn).getValues(); returns a 2D array.

As a result, data[i] returns a 1D array which is actually referring to the row. To solve this issue, flatten the array to convert it to 1D:

var data = sheet.getRange(1,2,1,lastColumn).getDisplayValues().flat();

Your if condition is executed at the first iteration i=0 because you put a semicolon ; right after it. Also, break is not needed because nothing will be executed after the return statement:

Replace:

if (data[i] == dateToday) {break};
{  
  return i;
}

with

if (data[i] == dateToday) 
{  
  return i;
}

When you are working with date comparisons, you need to use getDisplayValues() to be sure that you are comparing the the displayed values and not the value of the date.

Solution:

function getColumnIndex() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  var data = sheet.getRange(1,2,1,lastColumn).getDisplayValues().flat(); //create an array of data from row 1
  
  for (var i = 0; i <= data.length; i++) {
    var dateToday = Utilities.formatDate(new Date(), "EST", "MM/dd/yyyy")    
    if (data[i] == dateToday) 
    {  
      return i;
      // return i+2; // if you want to get the column number instead.
    }
  }
}

Keep in mind, i refers to the position of the array. In JavaScript, the indexes in the arrays start from 0. Also, your data starts from the second column. If you want your script to return the number of column, then change return i to return i+2.

edited Oct 15, 2020 at 8:10

answered Oct 15, 2020 at 0:14

Marios

27.5k9 gold badges40 silver badges59 bronze badges

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

3 Comments

823g4n8901 Over a year ago

Brilliant, I read something about it producing a 2D array but didn't know what to do with that info - and that's why I kept getting an index return of 0 whenever it matched - because the first row is 0 so it was giving the correct return index, I just didn't know what I was doing. Also thanks for the pointer on removing the semicolon and moving the break. I was researching for hours today how to do this on my own having no knowledge of GAS but it's funny how someone who knows what they're doing can look at it instantly and figure it out

TheMaster Over a year ago

1.break statement is unreachable. Once the return executes, break is useless. 2. You should mention why you used getDisplayValues()

Marios Over a year ago

@TheMaster true, my bad :) Thanks

Cooper · Accepted Answer · 2020-10-15 00:40:38Z

0

function getColumnIndexForToday() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const shsc=2;
  const offset=0;//0 if you want the index from column B 1 if you want the index from ColumnA
  const data=sh.getRange(1,shsc,1,sh.getLastColumn()-shsc+1).getDisplayValues()[0];//assuming format is "MM/dd/yyyy"
  var dObj={};
  data.forEach(function(h,i){dObj[h]=i+offset;});//You really can just do this once and then use it repeatedly
  var dateToday = Utilities.formatDate(new Date(), "EST", "MM/dd/yyyy")
  return dObj[Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy")];
}

answered Oct 15, 2020 at 0:40

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Collectives™ on Stack Overflow

Return index of matched array not working - Javascript Google Apps Script

2 Answers 2

Issues / Explanation:

Solution:

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Issues / Explanation:

Solution:

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related