0

I am trying to find the first occurrence of a date, happens to be in 'MMM dd" format, within a coloumn, by using findIndex method.

The following code isnt able to achieve it

function copyInvoiceDetailsToDB() {

var sss = SpreadsheetApp.getActiveSpreadsheet();
var ss = sss.getSheetByName('Import MT Order Sheet'); //Source Sheet
var compareDate = ss.getRange(1,4).getValue();        // Search item
///var mmm = Utilities.formatDate(compareDate,"IST", 'MMM dd');


var reldata = ss.getRange('A3:AA'+ a).getValues();

var tss = SpreadsheetApp.openById("1xhPD6tlJiU33_tdnC82p-
e9rWA8mmMtI0g9jDLkk6s0");                              // sheet being searched
var ss = tss.getSheetByName('DB');     
var ssdata = ss.getRange('A:A').getValues(); // Range containing the values

var a = ssdata.indexOf(compareDate);

Logger.log(a);
Logger.log(compareDate);
Logger.log(ssdata);
return;

Generates the following log.

Please help me understand where I must be going wrong.

[17-11-22 02:40:11:568 IST] -1.0
[17-11-22 02:40:11:569 IST] Nov 22
[17-11-22 02:40:11:572 IST] [[],  [Sun Nov 19 00:00:00 GMT+05:30 2017], [Mon Nov 20 00:00:00 GMT+05:30 2017], [Mon Nov 20 00:00:00 GMT+05:30 2017], [Mon Nov 20 00:00:00 GMT+05:30 2017], [Tue Nov 21 00:00:00 GMT+05:30 2017], [Tue Nov 21 00:00:00 GMT+05:30 2017], [Wed Nov 22 00:00:00 GMT+05:30 2017], [Wed Nov 22 00:00:00 GMT+05:30 2017], [Wed Nov 22 00:00:00 GMT+05:30 2017], [Wed Nov 22 00:00:00 GMT+05:30 2017], [Wed Nov 22 00:00:00 GMT+05:30 2017], [Wed Nov 22 00:00:00 GMT+05:30 2017], [Wed Nov 22 00:00:00 GMT+05:30 2017], [Wed Nov 22 00:00:00 GMT+05:30 2017], [Wed Nov 22 00:00:00
Neil
  • 14,063
  • 3
  • 30
  • 51
Sujeeth
  • 11
  • 2
  • The variable `ssdata` is a 2D array. Even though each inner array only has one value in it, there is still an inner array for every cell value. You can convert the 2D array to a 1D array with: `ssdata = ssdata.toString().split(",");` So try doing that and then looking for the match of the value in the 1D array. If that doesn't work, then the date value being searched for might not be in the same format as in the array, or not present. For testing purposes, you might want to get less data, and make sure there is a match in the data. – Alan Wells Nov 21 '17 at 23:03

1 Answers1

0

How about this answer? From your question, I understood as follows.

  • You want to retrieve the first occurrence (the row number?) of a date using a string 'MMM dd".
  • You want to understand for retrieving values using indexOf().

About retrieving values from array using indexOf()

  • When you want to retrieve a value from an array using indexOf(), the array has to be 1 dimensional array. This is pointed out by Sandy Good.
  • When a value is retrieved from an 1 dimensional array using indexOf(), it doesn't retrieve the value included in each element, it retrieves the same value for each element.
    • ["foo", "bar", "baz"].indexOf("ba") is -1.
      • This means that there is no "ba" in each element in the array.
    • ["foo", "bar", "baz"].indexOf("bar") is 1.
      • This means that there is "bar" at index 1 of the array.
  • When it retrieves the value included in each element, each element has to be used as a string.
    • ["foo", "bar", "baz"].indexOf("ba") is -1.
    • "bar".indexOf("ba") is 0.
      • This means that the string of "ba" is found at 1st string in "bar". So in the case of "bbbar".indexOf("ba"), the result is 2.

The modification point reflected them is as follows.

Modification point :

From :

var a = ssdata.indexOf(compareDate);
Logger.log(a);

To :

var a;
for (var i in ssdata) {
  if (ssdata[i][0].indexOf(compareDate) > -1) {
    a = i;
    break;
  }
}
Logger.log(a);
  • a is the index with the value included "Nov 22" in the array of ssdata. If you want to the row number, please add 1 to it.
    • When your result shown at the log is used, a is 7.

References :

If I misunderstand your question, I'm sorry.

Tanaike
  • 181,128
  • 11
  • 97
  • 165