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 values 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.

Answer 1

=IF(REGEXMATCH(TO_TEXT(F24);"[.]");REGEXREPLACE(F24;"[.]";",");VALUE(F24))

Works for me

If find dot replace with comma if not, put value

Answer 2

I have different solution.

In my case, I`m getting values from Google Forms and there it is allowed use only numbers with dot as I know. In this case when I capture data from Form and trigger script which is triggered when the form is submited. Than data is placed in specific sheet in a specific cell, but formula in sheet is not calculating, because with my locale settings calculating is possible only with a comma not dot, that is coming from Google Form.

Then I use Number() to convert it to a number even if it is already set as a number in Google Forms. In this case, Google Sheets script is converting number one more time to number, but changes dot to comma because it is checking my locale.

var size = Number(sizeValueFromForm); 

I have not tested this with different locale, so I can`t guarantee that will work for locale where situation is opposite to mine.

I hope this helps someone. I was looking for solution here, but remembered that some time ago I had similar problem, and tried this time too and it works.

Answer 3

An alternative way to replace . with , is to use regex functions and conversion functions in the Sheets cells. Suppose your number is in A1 cell, you can write this function in any new cell:

= IF(REGEXMATCH(TO_TEXT(A1), "."), VALUE(REGEXREPLACE(TO_TEXT(A1), ".", ",")), VALUE(A1)) 

These functions do the following step:

1. Convert the number in the target cell to text. This should be done because REGEXMATCH expects a text as its argument.
2. Check if there is a . in the target cell.
3. If there is a ., replace it with ,, and then convert the result to a number.
4. If there is no ., keep the text in the target cell as is, but convert it to a number.

(Note : the Google Sheets locale setting I used in applying these functions is United States)

Answer 4

Click on Tools > Script Editor. Put this on your macros.gs (create one if you don't have any):

/** @OnlyCurrentDoc */
function ReplaceCommaToDot() {
  var range = SpreadsheetApp.getActiveRange();

  var col = range.getColumn();
  var row = range.getRow();

  function format(str) {
    if(str.length == 0) return str;
    return str.match(/[0-9.,]+/)[0]
    .replace('.','')
    .replace(',','.');
  }

  var log = [range.getRow(), range.getColumn()];
  Logger.log(log);
  var values = range.getValues()
  for(var row = 0; row < range.getNumRows(); row++){
    for(var col = 0; col < range.getNumColumns(); col++){   
      values[row][col] = format(values[row][col]);
    }
  }

  range.setValues(values);
}

Save. Go back to the spreadsheet, import this macro. Once the macro is imported, just select the desired range, click on Tools > Macro and select ReplaceCommaToDot

Note: This script removes the original ., and replaces , by .. Ideal if you are converting from US$ 9.999,99 to 9999.99. Comma , and whatever other text, like the currency symbol US$, were removed since Google Spreadsheet handles it with text formatting. Alternatively one could swap . and ,, like from US$ 9.999,99 to 9,999.99 by using the following code snippet instead:

return str.match(/[0-9.,]+/)[0]
    .replace('.','_')
    .replace(',','.')
    .replace('_',',');

Answer 5

• Select the column you want to change.
• Goto Edit>Find and Replace

• In Find area put "."

• in Replace with area put ","

Answer 6

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 ..

Answer 7

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(".", ",")];