Creating a Google Doc from spreadsheet with the right formatString GAS

Question

With this script I can create a Google Doc from a template and the values in a Google Spreadsheet.

It takes the fields from the active row in the Google Spreadsheet (the one with a highlighted cell or row) and using a Google Doc template (identified by TEMPLATE_ID) creates a doc with these fields replacing the placeholders in the template.

Now, the problem is the format of the cells.
I would to pull in the Google Doc exactly what it appears in the spreadsheet.
With the actual code, if I have:

• in a spreadsheet cell € 18.532,78 ----> in the Google Doc appears 18532,7762
  
• in a spreadsheet cell 300,06 -----> in the Google Doc appears 300.06
  

I would to pull in the Google Doc exactly what it appears in the spreadsheet. How could I solve?
This is the code:

function myFubction() {
  var tableColumn = [3,5]; // Table header is in column C to E
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var TEMPLATE_ID = 'xxx';
  var ui = SpreadsheetApp.getUi();
  if (TEMPLATE_ID === '') {        
    ui.alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }
  var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy();
  var copyId = copyFile.getId();
  var copyDoc = DocumentApp.openById(copyId);
  var FILE_NAME = ui.prompt('Insert the name of Google Doc', ui.ButtonSet.OK);
  copyDoc.setName(FILE_NAME.getResponseText());
  var copyBody = copyDoc.getBody();
  var lastColumn = sheet.getLastColumn();
  var activeRowIndex = sheet.getActiveRange().getRowIndex();
  var activeRow = sheet.getRange(activeRowIndex, 1, 1, lastColumn).getValues()[0];
  var headerRow = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  for (var columnIndex = 0; columnIndex < headerRow.length; columnIndex++) {
    if (columnIndex === tableColumn[0] - 1) { // Check if column corresponds to table data
      try {
        var tableValues = sheet.getRange(2, tableColumn[0], sheet.getLastRow()-1,tableColumn[1]-tableColumn[0]+1).getValues();
        var placeholder = `%${headerRow[tableColumn[0]-1]}%`;
        var rangeElement = copyBody.findText(placeholder);
        var element = rangeElement.getElement();
        console.log(element.asText().getText())
        var childIndex = copyBody.getChildIndex(element.getParent());
        console.log(childIndex)
        const elementText = copyBody.getChild(childIndex).asText().getText();
        const [beforeText, afterText] = elementText.split(placeholder);
        copyBody.getChild(childIndex).asText().setText('');
        copyBody.insertParagraph(childIndex, beforeText);
        copyBody.insertTable(childIndex+1, tableValues);
        copyBody.insertParagraph(childIndex+2, afterText);
      } catch(err) {
        continue;
      }
    } else if (columnIndex < tableColumn[0] - 1 || columnIndex > tableColumn[1] - 1) {
      var nextValue = formatString(activeRow[columnIndex]);
      copyBody.replaceText('%' + headerRow[columnIndex] + '%', nextValue);
    }                     
  }
  copyDoc.saveAndClose();
  SpreadsheetApp.getUi().alert('Google Doc created!!')     
}

and this is my menu.gs for formatString;

function formatString(value, options) {

  var formattedValue = value
  options = options || {}

  if (typeof value === 'number') {

    formattedValue = value.toLocaleString('de-DE', {currency: 'EUR', style: 'currency'});
    
  } else if (value instanceof Date) {
  
    var timeZone = Session.getScriptTimeZone()
    var dateFormat = 'dd/MM/yyyy'
    
    if (options.dateFormat !== undefined) {
      dateFormat = options.dateFormat 
    }
    
    formattedValue = Utilities.formatDate(value, timeZone, dateFormat)
    
  } else if (typeof value === 'object') {
  
    formattedValue = JSON.stringify(value)
  }
  
  return formattedValue
}

Answer 1

Specifically for those cases, you might be able to fix it using getDisplayValues instead of getValues. This will in return gets the values as how it is shown in the sheets.

But this may interfere with your other cells since you are reformatting other values, so be careful.