Wrong MYSQL format number in Google Sheets via JDBC

Question

The numbers that I import from an sql database on google sheet are interpreted as duration and not as numbers, more specifically as currencies.

colA	colB	colC	colD
449	1234	29521.00	0.00
449	1234	29521.00	0.00

Wrong = 29521.00 (format currently imported)
Correct = € 29.521,00

What am I doing wrong in colC and colD?

function runSql(query, options) {

console.log('query from runSql :' + query);
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheets()[2];
    var cell = sheet.getRange('a1');
    var activeCellRow = cell.getRow();
    var activeCellCol = cell.getColumn();

var rs = [];
    try {
        var fullConnectionString = 'jdbc:' + DB_TYPE + '://' + HOST + ':' + PORT
        var conn = Jdbc.getConnection(fullConnectionString, USERNAME, PASSWORD);
        console.log('query :', query)
        var stmt = conn.createStatement();
        stmt.execute('USE ' + DATABASE);
        var start = new Date();
        var stmt = conn.createStatement();
        //stmt.setMaxRows(MAXROWS);
        var rs = stmt.executeQuery(query);
    } catch (e) {
        console.log(e, e.lineNumber);
        Browser.msgBox(e);
        return false
    }

    var results = [];
    cols = rs.getMetaData();
    console.log("cols", cols)
    var colNames = [];
    var colTypes = {};
    for (i = 1; i <= cols.getColumnCount(); i++) {
        var colName = cols.getColumnLabel(i)
        colTypes[colName] = { type: cols.getColumnTypeName(i), loc: i }
        colNames.push(colName);
    }
    var rowCount = 1;
    results.push(colNames);
    while (rs.next()) {
        curRow = rs.getMetaData();
        rowData = [];
        for (i = 1; i <= curRow.getColumnCount(); i++) {
            rowData.push(rs.getString(i));
        }
        results.push(rowData);
        rowCount++;
    }
    rs.close();
    stmt.close();
    conn.close();
    console.log('results', results)
    var colCount = results[0].length
    var rowCount = results.length
    var comment = "Updated on: " + (new Date()) + "\n" + "Query:\n" + query
    if (options.omitColumnNames) {
        results = results.slice(1)
        rowCount -= 1
    }
    if (options.clearColumns && sheet.getLastRow() > 0) {
        var startCellRange = sheet.getRange(startCell)
        sheet.getRange(startCellRange.getRow(), startCellRange.getColumn(), sheet.getLastRow(), colCount).clearContent();
    }
    if (options.clearSheet) {
        var startCellRange = sheet.getRange(startCell)
        sheet.clear({ contentsOnly: true });
    }
    //sheet.getRange(activeCellRow, activeCellCol, rowCount, colCount).clearContent();
    sheet.getRange(activeCellRow, activeCellCol, rowCount, colCount).setValues(results);
    var cell = sheet.getRange(activeCellRow, activeCellCol)
    cell.clearNote()
    cell.setNote(comment);
    sheet.setActiveRange(sheet.getRange(activeCellRow + rowCount + 1, activeCellCol))
    console.log('query success!, rows = ', rowCount - 1)
}

function runSqlFromSheet() {
   //var doc = SpreadsheetApp.getActiveSpreadsheet();

    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getActiveSheet();
    var sheet = doc.getSheets()[2];
    var sheetName = sheet.getName();
    var cell = doc.getActiveSheet().getActiveCell();



    //var sql2 = '{call P_Totali_per_Azienda(?)}';
    //Logger.log('sql2;', sql2)
      var options = {}
    console.log(sql2);
    runSql(sql2, options)
    
}

Answer 1

The fact that the values get automatically converted to durations suggests that the data is in a format that makes Google Sheets consider them as duration values. This can happen if you read and write the data as text strings and your spreadsheet is in a locale that uses commas as decimal separators (as in € 1,99) and periods as time separators (as in 12.00). If you are reading the values as text strings, you may need to read them as numbers instead to avoid automatic conversion.

Consider using ResultSet.getFloat() instead of ResultSet.getString().

Once you can write the values correctly to the spreadsheet, you can set the final format programmatically, like this:

  SpreadsheetApp.getActive()
    .getRange("Sheet1!C2:D")
    .setNumberFormat("[$ €]#,##0.00");

If you always write the values to the same sheet, and get the data as numbers from the database, you can most likely manually format columns C:D as Format > Number > Currency just once, and have the format stick through future imports.