Formatting Numbers Before Importing to Google Sheets

[daniel30]

Hello Community,

I need some assistance with a workflow issue I’m facing.

My process involves taking invoices from Gmail, transferring them to Google Drive, and then uploading them to Google Sheets. This workflow functions well with compressed files; it successfully uncompresses the files, saves them to Drive, and sends the data to Google Sheets.

However, the problem arises because the invoices use different decimal separators depending on the vendor. Some invoices use a period (.) for decimals, while others use a comma (,). Additionally, for miles, they may use a comma or a period, again depending on the decimal separator.

Any advice on how to standardize these formats before importing them into Google Sheets would be greatly appreciated! @solomon.

This is how it looks Google Sheets:

[rbreen]

Hello @daniel30 ,

You can use the system prompt to convert everything to json, then upload to sheets. Here’s a workflow.

Best,

Robert

[daniel30]

Hello @rbreen, thank you for your response. I followed your instructions, but it still isn’t working. The input number is $909,900.00, and the output remains the same at $909,900.00.

The last five lines are the result of different tests when changing the code options; it did not transform the number.

[solomon]

Hey @daniel30

You can use the replaceAll() function in your Sheets node.

For every field you need to replace the commas, write like this;

{{ $json['Valor total'].replaceAll(",”, ".") }}

[daniel30]

Wow, @solomon, thank you for expanding my perspective! I was feeling stuck, but thanks to your help, I was able to work through all the scenarios related to number formatting in my country. Now, it’s functioning perfectly. Here’s the code I used:

{{ 
  // Obtener el valor original
  (function() {
    let valor = $json['Valor total'];
    
    // Eliminar el símbolo de peso $ y espacios
    valor = valor.replace(/\$/g, '').trim();
    
    // Para determinar el formato, necesitamos analizar la estructura
    // Si tiene coma, asumimos que es el separador decimal
    // Si solo tiene punto y está a 3 posiciones del final, es separador de miles
    
    let tieneComaSeparador = /,\d{1,2}$/.test(valor); // Coma seguida de 1 o 2 dígitos al final
    let tienePuntoMiles = /\.\d{3}$/.test(valor); // Punto seguido de 3 dígitos al final
    
    // Caso 1: Si tiene coma como separador decimal (ej: "1.234,56")
    if (tieneComaSeparador) {
      // Quitar todos los puntos (separadores de miles)
      valor = valor.replace(/\./g, '');
      // Reemplazar coma por punto (para formato internacional)
      valor = valor.replace(',', '.');
    }
    // Caso 2: Si solo tiene punto como posible separador de miles (ej: "4.200")
    else if (tienePuntoMiles) {
      // Quitar todos los puntos (son separadores de miles)
      valor = valor.replace(/\./g, '');
    }
    // Caso 3: Si tiene punto como separador decimal (ej: "1,234.56")
    else if (valor.includes('.')) {
      // Quitar todas las comas (separadores de miles)
      valor = valor.replace(/,/g, '');
    }
    // Caso 4: Si tiene coma pero no como decimal (ej: "1,234")
    else if (valor.includes(',')) {
      // Quitar todas las comas (son separadores de miles)
      valor = valor.replace(/,/g, '');
    }
    
    // Convertir a número
    return Number(valor);
  })()
}}