Issues reading cells using map() in a range in Google Sheets

Question

I have a function that works perfectly when assigning it on a per-cell basis, but when I try to create an array using the map() function, cell values are not being read properly. Can anyone help on this?

I've tried to read up on this and it might have something to do with Google Sheets map() function not being able to handle certain scenarios because all calcs are being done server-side? Not sure.

Anyway, I'm trying to read dates from one column, and see whether there was a discount in another column, and based on the date and discount token "DSC", return a percent discount value: 25% for or 30% if between 2 dates.

Picture of no 30% values in 3rd column

Below is the code for the working function (assigned to each cell) and non-working map() function assigned to a range then a Google Sheets link:

// ==================================== Using .map 
/**
@customfunction
*/
function Disc_Perc_Map (input1, input2){ // input1 is product ID column, input2 is Date
  var Date_1_a = Date.parse(String("1/3/2017")) ;// put start date of discount change here
  var Date_1_b = Date.parse(String("1/4/2017")) ;// put end date of discount change here
  var Disc_Def = .25;
  var Disc_1 = .30;
  if (input1.map){ // Checks if array 
    return input1.map(Disc_Perc_Map);   // added along with added brace at end
  } else { 

    // Main part of code 
    if (String(input1).indexOf(",") >-1) { // if there are commas
      Cell_Array = input1.split(",");
      Cell_Len = input1.split(',').length;
      var Date_Num = Date.parse(String(input2));
      if (Date_Num >= Date_1_a && Date_Num <= Date_1_b){
        return Disc_1;
      } else {
        return Disc_Def;
      }
    } else {                          // if there are NO commas
      return "";
      //Cell_Len = 1;
    }
  }
}


// ==================================== without using .map
/**
@customfunction
*/
function Disc_Perc_No_Map(input1, input2) { // input1 is product ID column, input2 is Date
  var Date_1_a = Date.parse(String("1/3/2017")) ;// put start date of discount change here
  var Date_1_b = Date.parse(String("1/4/2017")) ;// put end date of discount change here
  var Disc_Def = .25;
  var Disc_1 = .30;

  // Main part of code 
  if (String(input1).indexOf(",") > -1) { // if there are commas
    Cell_Array = input1.split(",");
    Cell_Len = input1.split(',').length;
    var Date_Num = Date.parse(String(input2));
    if (Date_Num >= Date_1_a && Date_Num <= Date_1_b){
      return Disc_1;
    } else {
      return Disc_Def;
    }
  } else {   // if there are NO commas
    return "";
    //Cell_Len = 1;
  }         
}

Link to example Google Sheets:

Any help great appreciated.

Answer 1

I think that the reason of issue is as follows.

• When Disc_Perc_Map() do the callback, input2 becomes the index of input1.map().
  • So the values of input2 inputted from the custom function is removed from the running script.

In order to avoid this issue, how about this modification?

Modification points :

• Backup input2 and use in the callback.
• In order to use the backed up input2, add a counter.
  • Retrieve the date value using the counter.

Modified script :

var cnt = -1; // Added
var input2bk; // Added

function Disc_Perc_Map (input1, input2){ // input1 is product ID column, input2 is Date

  if (isNaN(input2)) input2bk = input2; // Added

  var Date_1_a = Date.parse(String("1/3/2017")) ;// put start date of discount change here
  var Date_1_b = Date.parse(String("1/4/2017")) ;// put end date of discount change here
  var Disc_Def = .25;
  var Disc_1 = .30;

  if (input1.map){ // Checks if array 
    return input1.map(Disc_Perc_Map);   // added along with added brace at end
  } else {

    cnt += 1; // Added

    // Main part of code 
    if (String(input1).indexOf(",") > -1) { // if there are commas
      Cell_Array = input1.split(",");
      Cell_Len = input1.split(',').length;
      var Date_Num = Date.parse(String(input2bk[cnt][0])); // Modified
      if (Date_Num >= Date_1_a && Date_Num <= Date_1_b){
        return Disc_1;
      } else {
        return Disc_Def;
      }
    } else {   // if there are NO commas
      return "";
      //Cell_Len = 1;
    }
  }
}

Another pattern :

As an another pattern, how about this modification? This is also the same result with above.

function Disc_Perc_Map (input1, input2) { // input1 is product ID column, input2 is Date
  var Date_1_a = Date.parse(String("1/3/2017")) ;// put start date of discount change here
  var Date_1_b = Date.parse(String("1/4/2017")) ;// put end date of discount change here
  var Disc_Def = .25;
  var Disc_1 = .30;
  return input1.map(function(e, i) {
    // Main part of code 
    if (String(e[0]).indexOf(",") > -1) { // if there are commas
      Cell_Array = e[0].split(",");
      Cell_Len = e[0].split(',').length;
      var Date_Num = Date.parse(String(input2[i][0])); // Modified
      if (Date_Num >= Date_1_a && Date_Num <= Date_1_b) {
        return [Disc_1];
      } else {
        return [Disc_Def];
      }
    } else {   // if there are NO commas
      return [""];
      //Cell_Len = 1;
    }
  });
}

If I misunderstand your question, I'm sorry.