Specific plain English keywords returning specific numerical values using google sheets app script

user_17985936 0
Viewed 53

I'm creating a spreadsheet for developing new cocktails and cocktail ingredients for bars to generate loads of info quickly.

I have made a script based on Anthony Grant's here using switch case to help with calculating the pH of any given solution that picks up the plain English text in the ingredients column that returns the concentration of that substance. For example, the word "Citric" returns the numeric value 192.124 (as that is the molecular weight of citric acid) and so forth depending on the acid. It works great and fits in perfectly. From there I can calculate pH. like this:

/**
 * This function determines the concentration of hydronium ions to be used to calculate pH.
 * To finish the calculation, combine the concentrations of all acids then add into -log10(concentration)
 * @constructor
 * @param {input} amount of acid in grams
 * @param {acid} type of acid in plain English
 * @param {volume} total volume of solution
 * @return The amount of acid divided by the molar mass of acid then divided by the total volume
 * @customfunction
 */

function pHCALC( input, acid, volume) {
  
 var rate = 0 ;
 switch (acid) {
 case 'Citric':
 rate = 192.124;
 break;
 case 'Malic':
 rate = 137.087;
 break;
 case 'Tartaric':
 rate = 150.087;
 break;
 case 'Ascorbic':
 rate = 176.120;
 break;
 case 'Lactic':
 rate = 90.08;
 break;
 case 'Phosphoric':
 rate = 97.994;
 break;
 case 'Acetic':
 rate = 60.052;
 default:
 rate = 0;
 }
 return ((input / rate)/ volume);
}

I was hoping to do the same with the sucrose levels in different syrups, so I just copied the code and changed what I needed to, but for some reason, it doesn't work at all:

/**
 * This function determines sucrose content of common syrups
 * @constructor
 * @param {input} type of sugar/syrup in English
 * @return The amount of sucrose in 1g of noted substance
 * @customfunction
 */

function sugarcalc( input,sugar) {

 var rate = 0;
 switch (sugar) {
 case 'Caster Sugar': rate = 1.0;
 break;
 case 'Honey': rate = 0.82;
 break;
 case '2:1 Honey Syrup': rate = 0.55;
 break;
 case '1:1 Honey Syrup': rate = 0.41;
 break;
 case 'Invert Syrup': rate = 0.70;
 break;
 case '2:1 Sugar Syrup': rate = 0.67;
 break;
 case '3:2 Sugar Syrup': rate = 0.60;
 break;
 case '1:1 Sugar Syrup': rate = 0.50;
 break;
 case 'Agave Syrup': rate = 0.68;
 break;
 case 'Maple Syrup': rate = 0.68;
 break;
 case 'Rice Malt Syrup': rate = 0.55;
 break;
 case 'Gomme': rate = 0.68;
 default:rate = 0;
 }
 return (input * sugar);
}

I want to reference plain English so if the words mentioned in each case get mentioned, then the function will output the correct value depending on the syrup. I get a yellow lightbulb next to "var rate = 0" to begin with, but on the pH function, I don't.

Thanks in advance

EDIT:Sorted the problem with the last line, plus rate2 - Thanks!

Now if I wanted to expand the terms of the case so that it would catch more phrases but still return the same result. for example:

 case 'Caster Sugar','caster sugar','sugar','granulated sugar': rate2 = 1.0;
 break;

That seems to break it. Do I need a different separator?

google-apps-script google-sheets javascript
2 Answers

Change the variable to a unique name on your second function. Such as:

var rate2 = 0;

rate has already been defined in the first function.

user_75020290

I think you need to change sugar in this line:

return (input * sugar);

to rate:

return (input * rate);

Update

As for 'expand the terms', probably switch/case is not the best choice. If you want to ignore a case ('Caster Sugar' == 'caster sugar'). You can convert everything in lower case before you do comparison:

...
switch (sugar.toLowerCase()) {
  // it will catch 'caster sugar' and 'Caster Sugar' and 'CASTER SUGAR', etc
  case 'caster sugar': rate = 1.0; 
  break;
...

But if you want to check a list of possible values ('caster sugar','sugar','granulated sugar') it needs a more tricky code. I suppose for your case wiser just to add the several lines in the 'switch/case' statement instead.

Let me know if you really want to try the more complicated solution.

Update 2

I'd propose to use if statement within a dedicated function instead of switch/case. It works about the same way but there is the way to add more complicated conditions. The code looks like this:

function sugarcalc(input, sugar) {
    var sugar = sugar.toLowerCase() // convert to lower case 
        .trim()                     // remove spaces at the start and at end
        .replace(/ +/g, ' ');       // replaces double spaces with single spaces
    return input * get_rate(sugar);


    function get_rate(sugar) {

        // multi conditions
        var texts = ['caster sugar', 'sugar', 'granulated sugar'];
        if (texts.includes(sugar))      return 1.00;

        var texts = ['gomme', 'agave syrup', 'maple syrup'];
        if (texts.includes(sugar))      return 0.68;

        // single conditions
        if (sugar == 'honey')           return 0.82;
        if (sugar == '2:1 honey syrup') return 0.55;
        if (sugar == '1:1 honey syrup') return 0.41;
        if (sugar == 'invert syrup')    return 0.70;
        if (sugar == '2:1 sugar syrup') return 0.67;
        if (sugar == '3:2 sugar syrup') return 0.60;
        if (sugar == '1:1 sugar syrup') return 0.50;
        if (sugar == 'rice malt syrup') return 0.55;

        return 0; // default rate
    }
}

// tests
console.log(sugarcalc(2, 'Sugar'));               // --> 2
console.log(sugarcalc(2, '  Sugar'));             // --> 2
console.log(sugarcalc(1, '2:1 Honey Syrup'));     // --> 0.55
console.log(sugarcalc(1, '2:1  HONEY   Syrup'));  // --> 0.55
console.log(sugarcalc(3, 'Granulated Sugar'));    // --> 3
console.log(sugarcalc(3, 'Granulated sugar  '));  // --> 3
console.log(sugarcalc(3, 'Gomme'));               // --> 2.04

user_142654690
Related