Automatically replace dots with commas in a Google Sheets Column with Google Script

Question

I have a WooCommerce store, which is connected with Zapier to a Google spreadsheet. In this file, I keep track of the sales etc. Some of these columns contain -obviously- prices, such as price ex VAT, etc. However, for some reason the pricing vales are stored in my spreadsheet as strings, such as 18.21.

To be able to automatically calculate with these values, I need to convert values in these specific columns to numbers with a comma as divider. I'm new to Google Script, but with reading some other post etc, I managed to "write" the following script, which almost does the job:

function stringIntoNumber() {
  var sheetActive = SpreadsheetApp.openById("SOME_ID");
  var sheet = sheetActive.getSheetByName("SOME_SHEETNAME");
  var range = sheet.getRange("R2:R");
   range.setValues(range.getValues().map(function(row) {
    return [row[0].replace(".", ",")];
}));
}

The script works fine as long as only values with a dot can be found in column R. When values that belong to the range are changed to values with a comma, the script gives the error: TypeError, can't find the function Replace.

I really hope that I made my question clear, and I sincerely hope that someone could help me out.

Cheers,

Hans

Answer 1

The error occurs because .replace is a string method and can't be applied to numbers. A simple workaround would be to ensure the argument is always a string, there is a .toString() method for that.

in your code try

return [row[0].toString().replace(".", ",")];

Answer 2

The locale of your spreadsheet is set to a country that uses commas to seperate decimal places. Zapier however seems to use dots and therefore google sheets interprets the data it gets from Zapier as strings since it can't interpret it as valid numbers.

If you change the locale to United States (under File/Spreadsheet settings) it should work correctly. But you may not want to do that because it can cause other issues.

You got a TypeError because the type was number and not string. You can use an if statement to check the type before calling replace. Also you should convert the type to 'number' to make sure it will work correctly independent of your locale setting.

range.setValues(range.getValues().map(function(row) {
    if(typeof row[0] === "string") return [Number(row[0].replace(",", "."))];
    else return row;
}));

In this case I convert , to . instead of the other way around since the conversion to number requires a ..