I have a script that fetches column index numbers from column values. It is working perfectly fine for the column values that are text but the values that are date and time, it doesn't even recognize them. don't know why. Any help will be highly appreciated.

Here is the code.

function columnindex(){
  const  ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("sheetname");
  selected_columns = ["Keywords", "Wed Feb 02 2022 05:55:18 GMT 0530 (India Standard Time)"].toString()

  var columns = sheet1.getRange(1, 1, 1, sheet1.getLastColumn()).getValues().toString();
  Logger.log(columns)
  for (i = 0; i < selected_columns.length; i  ) {
    let column = columns[0].indexOf(selected_columns[i]);
    if (column != -1) {
      Logger.log(column 1)
    }
  }
}

I tried it with and without string property but it doesn't seem working.

CodePudding user response：

In your situation, how about the following modified script? I thought that in your situation, getDisplayValues() might be suitable.

Modified script:

function columnindex() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  selected_columns = ["Keywords", "2/2/2022"];
  var columns = sheet1.getRange(1, 1, 1, sheet1.getLastColumn()).getDisplayValues()[0];
  var columnIndexes = selected_columns.map(e => columns.indexOf(e));
  console.log(columnIndexes);
}

• From I have a script that fetches column index numbers from column values. in your question, in this modification, columnIndexes returns the column indexes that the 1st number is 0. If you want to the coulmn number, please modify var columnIndexes = selected_columns.map(e => columns.indexOf(e)); to var columnIndexes = selected_columns.map(e => columns.indexOf(e) 1);.

Reference:

• getDisplayValues()