我正在创建一个电子表格,用于为酒吧开发新的鸡尾酒和鸡尾酒配料,以快速生成大量信息。
我根据Anthony Grant的使用开关案例编写了一个脚本,以帮助计算任何给定溶液的pH值,该溶液会在成分栏中提取纯英文文本,返回该物质的浓度。例如,单词";柠檬酸";返回数值192.124(因为这是柠檬酸的分子量(,依此类推。它效果很好,非常适合。从那里我可以计算pH值,如下所示:
/**
* 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);
}
我希望对不同糖浆中的蔗糖水平也能做到这一点,所以我只是复制了代码并更改了我需要的内容,但由于某种原因,它根本不起作用:
/**
* 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);
}
我想参考普通英语,所以如果每个案例中提到的单词都被提及,那么函数将根据糖浆输出正确的值。我得到一个黄色灯泡旁边的";var比率=0";一开始,但在pH函数方面,我没有。
提前感谢
编辑:用最后一行解决了问题,加上评分2-谢谢!
现在,如果我想扩展案例的术语,这样它可以捕获更多的短语,但仍然返回相同的结果。例如:
case 'Caster Sugar','caster sugar','sugar','granulated sugar': rate2 = 1.0;
break;
这似乎打破了它。我需要一个不同的分离器吗?
我认为您需要更改此行中的sugar
:
return (input * sugar);
至rate
:
return (input * rate);
更新
至于"扩展术语",switch/case
可能不是最好的选择。如果您想忽略一个案例("Caster Sugar"=="Caster Sugar"(。在进行比较之前,您可以将所有内容转换为小写:
...
switch (sugar.toLowerCase()) {
// it will catch 'caster sugar' and 'Caster Sugar' and 'CASTER SUGAR', etc
case 'caster sugar': rate = 1.0;
break;
...
但是,如果你想检查一个可能的值列表("主糖"、"糖"one_answers"分级糖"(,它需要一个更棘手的代码。我想,对于您的情况,明智的做法是在"switch/case"语句中添加几行。
如果你真的想尝试更复杂的解决方案,请告诉我。
更新2
我建议在专用函数中使用if
语句,而不是switch/case
。它的工作方式大致相同,但有一种方法可以添加更复杂的条件。代码如下:
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
在第二个函数中将变量更改为唯一名称。例如:
var rate2 = 0;
速率已经在第一个函数中定义。