Home > Blockchain >  Sum only numbers given a string: Google Sheets Apps Script
Sum only numbers given a string: Google Sheets Apps Script

Time:04-19

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

enter image description here

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 enter image description here

  • Related