CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
823g4n8901
823g4n8901

Reputation: 139

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

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'.

Upvotes: 1

Views: 862

Answers (2)

Marios
Marios

Reputation: 27390

Issues / Explanation:

  1. 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();
  1. 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;
}
  1. 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.

Upvotes: 3

Cooper
Cooper

Reputation: 64110

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")];
}

Upvotes: 0

Related Questions