Handling data types in custom Google Sheets functions

Other Questions Google Sheets
1

I’m having trouble with a custom function in Google Sheets. I made a function to add up numbers but it’s not working right. Here’s what’s happening:

I have numbers in column A (1, 2, 3, 4, 5). My function should add them up to 15 but instead it’s giving me 12345. It looks like it’s treating the numbers as text and just putting them together.

Here’s my code:

function addNumbers(range) {
  let total = 0;
  for (let cell of range) {
    total += cell[0];
  }
  return total;
}

I use it like this: =addNumbers(A1:A5)

When I check what’s going on, the first time through the loop total is a number. But after that it becomes text somehow.

Does anyone know how to fix this? Is there a way to make sure Google Sheets treats these as numbers and not text? Thanks for any help!

2

I’ve encountered this issue before, and it’s related to how Google Sheets handles data types in custom functions. The problem is that the cells in the range are being passed as objects, not direct values. To fix this, you need to explicitly access the value of each cell.

Try modifying your function like this:

function addNumbers(range) {
  let total = 0;
  for (let cell of range) {
    total += Number(cell.getValue());
  }
  return total;
}

The getValue() method retrieves the actual cell value, and Number() ensures it’s treated as a number. This should resolve the issue and give you the correct sum. Let me know if you need any further clarification!

3

hey Ethan, i had this problem too. the trick is to use parseFloat() on each cell value. it forces google sheets to treat it as a number. heres how i fixed it:

function addNumbers(range) {
  return range.flat().reduce((sum, cell) => sum + parseFloat(cell), 0);
}

this should work for u. let me kno if u need more help!

4

I’ve run into similar problems with custom functions in Sheets. One trick that’s worked for me is using the Array.map() method to convert all the values to numbers before summing them. Here’s a tweaked version of your function that should do the trick:

function addNumbers(range) {
  return range.map(row => Number(row[0])).reduce((sum, num) => sum + num, 0);
}

This approach first converts all the values to numbers, then uses reduce() to sum them up. It’s more concise and usually performs better for larger ranges. Plus, it avoids the issue of accidentally concatenating strings. Give it a shot and see if it solves your problem!