1

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

Kenny Meyer
  • 13
  • 3

1 Answers1

1

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;
    })
  );
}
Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Note: The above code will not run on deprecated ES5 engine. To use the code, [upgrade to modern faster V8 engine](https://developers.google.com/apps-script/guides/v8-runtime#enabling_the_v8_runtime). – TheMaster Feb 28 '20 at 06:39
  • @Kenny Make sure you understand and practice each of the linked methods individually. – TheMaster Feb 28 '20 at 16:22
  • This is an elegant solution. One I would have never thought of myself. If I understand correctly, the script returns an anonymous function instead of a value. The calculation is done in the sheet and not in the script. The arrayformula iterates the function for each row. Is that a fair description of how this works? – Kenny Meyer Feb 28 '20 at 16:27
  • @Kenny Not even close. It'll work and return a array even without `arrayformula`(because it iterates through all the values and calculation is done on the script: doing it on the sheet would be slow). You need to practice arrays. See https://stackoverflow.com/a/60445654/ and take a online js course. I don't expect you to get it immediately. But if you read the links and practice, you'll eventually see. – TheMaster Feb 28 '20 at 16:32
  • I see. Thanks for the clarification. This helps a lot. Thanks for the referrals. A dim light has winked on. Next time, I'll start from a better grasp of the platform. Much appreciated. – Kenny Meyer Feb 28 '20 at 16:44