How can I create a function in Apps Script that takes a String as a parameter and then return inform-CodePudding

Here is the issue I am facing. I am currently making an expense tracker on Google Sheets:

I would like to implement a feature where the user could put in a String as a parameter in the function, and then have the percentage of their clothing expenses in regards to that particular String... if that makes sense. For example myFunction("Zara") would return 33.7%, because the sum of all clothing expenses is 2433, and the client spent 821 on Zara clothing. Therefore, (821/2433)*100 = 33.7.

So far, I believe the function should be declared like this function myFunction(string, values). Then, I would initialize a variable sum to 0, and loop through every value in the sheet. I'm really confused as to how I would approach this, and I apologize if I made little sense, as I am new to JavaScript and programming in general.

Thanks in advance!

CodePudding user response：

solution#1

You don't need a custom function, try

=sumif($A$2:$A,D2,$B$2:$B)/sum($B$2:$B)

solution#2

If you want a custom function, try

=perCent("B")

with the following script

function perCent(categ){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var values = sh.getRange(2,1,sh.getLastRow()-1,2).getValues()
  var total = 0
  var sum = 0
  values.forEach(function(row){
    total =row[1]
    if (row[0]==categ){sum =row[1]}
  })
  return sum/total
}