Sum only numbers given a string

Question

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"));

Answer 1

Google Sheets Native

While this formula is less clean than having a single formula defined in Apps Script, this will load every time without any hiccups.

=ArrayFormula(SUM(IFNA(REGEXEXTRACT(to_text(split(substitute(H1831,"$","~$"),"~")), "\$([0-9.,]+)")*1)))

Apps Script

UPDATE: This implementation works but will sometimes get in a loading Error.

While I genuinely appreciate all the input and suggestions I received, it indeed steered me in the right direction; I was able to solve my case with the following.

This provides a solution for my case and the preview and autosuggestion for using it in Google Sheets.

/**
 * Adds only numbers in a cell.
 * 
 * @param {string} cell The cell of text selected.
 * @return The sum of only numbers of a given cell.
 * @customfunction
 */

function ADDNUMBERS(cell) {  
  var regex = /\d+([\.]\d+)?/g;

  const matches = cell.match(regex);
  
  nums = matches.map(Number);

  sum = nums.reduce((a,b) => {
    return a + b;
  });
  
  return sum
}

function main(){
  test = "Neighbor Notification Shipping Fees $7.50 + Building Permit Final Fees $308.78 + CC Fees";
  console.log(ADDNUMBERS(test))
}

Execution log
11:27:58 PM Notice  Execution started
11:27:59 PM Info    316.28
11:27:59 PM Notice  Execution completed

This function parses my string and creates an array that matches a regex for only floats. Later all elements in the array are cast into floats and, lastly, will return the sum.

The bonus is creating the JSDoc comment above the formula.

Again, I truly appreciate all the quick input.

Answer 2

The code you provided is working, you just have to create another function that will call console.log(sumAmounts("foo5bar6cat1")); as Apps Script can only execute code within a function.

Your code should look like this:

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]));
    }
  }
  for (let i = 0; i < nums.length; i++) {
    sum += nums[i];
  }
  return sum;
}


function main(){
  console.log(sumAmounts("foo5bar6cat1"));
}

Output:

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 Custom Function in your Google Sheet to test the value.

Example:

Answer 3

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

Regular Expressions

regex syntax for GAS