I have made a custom function on Google Sheets that allows the user to see how much they have spent on an expense.

Here is the example output:

The HTML sidebar:

Here is the JavaScript function:

function perCentBrand(brand){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
  var total = 0;
  var sum = 0;
  values.forEach(function(row){
    total =row[1];
    if (row[6]==brand){sum =row[1]}
  })
  var val = "You spent a total of "   sum   " on "   brand   " out of "   total   " ."   " Additionally, "   (sum/total)*100   "%"    " of your income has been spent on "   brand; 
  var ui = SpreadsheetApp.getUi();
  ui.alert(val)
}

And here is the HTML form code:

    <form onsubmit="runFunc()">
      <input class = "u-full-width " id="brand" type = "text" placeholder="Enter brand name">

    <div  style="display:flex; justify-content: center">
      <button type="submit" >Submit</button>
    </div>
    </form>

I have read about the toLowerCase() and the toUpperCase() methods but I am unsure if these should be included to make the user input case insensitive.

Thanks in advance.

CodePudding user response：

function perCentBrand(brand){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
  var total = 0;
  var sum = 0;
  values.forEach(function(row){
    total =row[1];
    if (row[6].toLowerCase() == brand.toLowerCase()){sum =row[1]}
  })
  var val = "You spent a total of "   sum   " on "   brand   " out of "   total   " ."   " Additionally, "   (sum/total)*100   "%"    " of your income has been spent on "   brand; 
  var ui = SpreadsheetApp.getUi();
  ui.alert(val)
}

CodePudding user response：

I'm assuming you mean "make user input case insensitive" regarding your if-statement, so I would control for case there if (row[6].toLowerCase()==brand.toLowerCase()){sum =row[1]}.