0
413
ViewsHow can I create a function in Apps Script that takes a String as a parameter and then return information specific to that String?
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!
1 answers
Answer question0
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
}
