CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kenny Meyer
Kenny Meyer

Reputation: 13

Custom function used in a google sheets arrayfunction does not correctly update values

I'm new to google script. I am attempting to write a custom function that works with an arrayformula in google sheets. My implementation does not return the calculated value. My goal is to provide an updated calculation for data collected in a google form.

In a nutshell, I want to multiply a number by an amount based on a threshold based on type. e.g. the multiplier would only be applied for amount larger than a threshold.

Using the following google sheet

count, category, amount 
2, a_type,=arrayformula(amount_calc(a2:a, b2:b, 25)) 
4, b_type, 
7, a_type, 
5, c_type,

I want to obtain the following result using an arrayformula

2, a_type, 0  //threshold: count>2
4, b_type, 75 //threshold: count>1
4, a_type, 50 //threshold: count>2
5, c_type, 25 //threshold: count>4

I've written the "amount_calc" function

function amount_calc(count, cat, multiplier) {
  var amount=0
  if(cat=="a_type" && count > 2) {
    amount=(count - 2) * multiplier   
  } else if(cat=="a_type" && count <= 2) {
    amount=0
  }  
  if(cat=="b_type" && count > 1) {
    amount=(count - 2) * multiplier   
  } else if(cat=="b_type" && count <= 1) {
    amount=0
  } 
  if(cat=="c_type" && count > 4) {
    amount=(count - 4) * multiplier   
  } else if(cat=="b_type" && count <= 4) {
    amount=0
  } 
  return amount
}

The custom function as written produces the following result in the google sheet 

2, a_type, 0  
4, b_type, 0 
4, a_type, 0 
5, c_type, 0

From what I can the assignments in the if statements don't update the global variable "amount"

Alternately, I've unsuccessful tried accumulating the results in an amount array as follows

function amount_calc(count, cat, multiplier) {
  var amount=[]
  for (var i=0; i<count.length; i++ {
       if(cat=="a_type" && num > 2) {
    amount.push[i]((count - 2) * multiplier)    
  } else if(cat=="a_type" && count <= 2) {
    amount.push[i](0)
  }  
  if(cat=="b_type" && num > 1) {
    amount.push[i]((count - 1) * multiplier)    
  } else if(cat=="b_type" && count <= 1 {
            amount.push[i](0)
    } 
    if(cat=="c_type" && num > 4) {
      amount.push[i](count - 4) * multiplier) 
    } else if(cat=="b_type" && count <= 4 {
              amount.push[i](0)
      } 
      return amount
}

The result is a "Exceeded maximum execution time (line 0)" error.

Clearly I don't understand the function of the arrayformula. Am I mistaken to think it is iterating the function on each row? Why isn't the global variable 'amount' updated by the if statements. Any help or clarification would be appreciated. Thanks, Kenny

Upvotes: 1

Views: 120

Answers (1)

TheMaster
TheMaster

Reputation: 50565

Flow:

  • Create a Object map of types: {type: threshold}
  • Use Array.map to iterate rows and columns1

Sample script:

/**
 * @param {number[][]} counts
 * @param {string[][]} types
 * @param {number} multiplier
 */
function amountcl(counts, types, multiplier) {
  const typeCfg = { a_type: 2, b_type: 1, c_type: 4 };
  return counts.map((row, i) =>
    row.map((count, j) => {
      let diff = count - typeCfg[types[i][j]];
      return diff > 0 ? diff * multiplier : 0;
    })
  );
}

Upvotes: 1

Related Questions