Convert decimal points to commas in Google Sheets column using Apps Script

Other Questions Google Sheets
1

I’m working with a Google Sheets document that gets data from my online store through an automation tool. The sheet tracks various sales information including different price columns like net amounts and totals.

The problem I’m facing is that all price values come in as text strings with decimal points like 15.99 instead of numbers. I need these converted to proper numbers using commas as decimal separators so I can perform calculations on them.

I put together this Google Apps Script code that mostly works:

function convertDotsToCommas() {
  var workbook = SpreadsheetApp.openById("MY_SHEET_ID");
  var worksheet = workbook.getSheetByName("MY_SHEET_NAME");
  var dataRange = worksheet.getRange("S2:S");
  dataRange.setValues(dataRange.getValues().map(function(cell) {
    return [cell[0].replace(".", ",")];
  }));
}

The issue is that this script only works when all values in column S contain dots. Once some cells already have commas from previous runs, I get an error saying it cannot find the replace function. How can I modify this to handle both dot and comma values without breaking?

2

Another approach that worked well for me is using parseFloat() combined with toLocaleString() to handle the conversion more reliably. The main issue with your current script is that it assumes all values are strings, but Google Sheets automatically converts numeric-looking strings to actual numbers in some cases. Instead of just doing string replacement, you could parse the values first and then format them properly. Something like checking if the value is already a number, and if so, converting it to string with comma decimal separator using toLocaleString(‘de-DE’) which uses comma as decimal separator. This handles both scenarios - when your data comes in as text with dots and when it’s already been converted to numbers by Sheets.

3

ah yeah i had this same headache with my accounting sheets! the trick is wrapping it in a try-catch block so it dosnt crash when hitting numbers. try adding try { return [cell[0].replace(".", ",")]; } catch(e) { return [cell[0]]; } instead of the direct replace - works like a charm for mixed data types

4

You’re getting that error because once your values are converted to numbers, they lose the string methods like replace(). The script works fine on the first run when everything is text, but fails afterwards when some cells contain actual numeric values.

I had a similar issue with financial data imports. What you need is to check the data type before attempting any string operations. Here’s how I solved it:

function convertDotsToCommas() {
  var workbook = SpreadsheetApp.openById("MY_SHEET_ID");
  var worksheet = workbook.getSheetByName("MY_SHEET_NAME");
  var dataRange = worksheet.getRange("S2:S");
  
  dataRange.setValues(dataRange.getValues().map(function(cell) {
    if (typeof cell[0] === 'string' && cell[0].indexOf('.') !== -1) {
      return [cell[0].replace(".", ",")];
    }
    return [cell[0]];
  }));
}

This version checks if the value is actually a string and contains a dot before attempting the replacement. Already converted values pass through unchanged, preventing the error you’re experiencing.