How can one create an Apps Script function to sum only numbers given a string. I've been able to do this using this regular formula within Google Sheets:
=SUM(SPLIT( LOWER(A1) , "abcdefghijklmnopqrstuvwxyz $ " ))
but I haven't been able to understand how to do something like this in Google Apps Script. While I know that it works similar to Javascript, this example in Javascript will not work in Apps Script for Google Sheets.
function sumAmounts(str) {
let nums = [];
let sum = 0;
for (let i = 0; i < str.length; i ) {
if (!isNaN(Number(str[i]))) {
nums.push(Number(str[i]));
}
}
// console.log(nums);
for (let i = 0; i < nums.length; i ) {
sum = nums[i];
}
return sum;
}
// Test it
console.log(sumAmounts("foo5bar6cat1"));
CodePudding user response:
Sum of numbers in a String
function sumOnlyNumbersInAString() {
const s ="A123N789Kyxtu9x6".split("");
let sum = s.reduce((a,c) => {
if(c.match(/\d/)) {//matches [0-9]
a = Number(c);
}
return a;
},0);
Logger.log(sum);
}
Execution log
4:59:47 PM Notice Execution started
4:59:47 PM Info 45.0
4:59:48 PM Notice Execution completed
Another way is to assume that consecutive numbers are multiple digit numbers
function sumOnlyNumbersInAString() {
const m ="A123N789Kyxtu9x6".match(/\d /g);
let sum = m.reduce((a,c) => {
a = Number(c);
return a;
},0);
Logger.log(sum);
}
Execution log
5:08:14 PM Notice Execution started
5:08:15 PM Info 927.0
5:08:16 PM Notice Execution completed
In the latter case we are adding 123 789 9 6
Note: Make sure to change the function to run in your Apps Script. There is a drop down beside the Debug button where you can choose which function to run.
Also, since you have a return statement in your sumAmounts(str)
function, you can use it as