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
}

score 1 · Accepted Answer · answered Feb 04 '22 at 21:40

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.

answered Feb 04 '22 at 21:40

NightEye

10,634
2
5
24

And so, what are the edits to the code? – Gavis Feb 04 '22 at 21:42
@Gavis, replace all `getValues` with `getDisplayValues`. If that does mess up your other values, please provide a sample sheet with values. I recommended you use `getDisplayValues` but it may interfere with your other values since you have been trying to format them. – NightEye Feb 04 '22 at 21:44
The possible issue you are going to encounter is that any value you pass to `formatString` would be treated as string. You will have to modify your `formatString` most likely if you proceed with this fix. That should still be doable since what you are formatting are just dates, and numbers. You can use these references: [date check](https://stackoverflow.com/questions/7445328/check-if-a-string-is-a-date-value) and [number check](https://stackoverflow.com/questions/175739/how-can-i-check-if-a-string-is-a-valid-number) – NightEye Feb 04 '22 at 21:46
1

Ok. Perfect. Last question. How could I set for the table created, the width of the first column? – Gavis Feb 04 '22 at 21:49
Hi @Gavis, I think you would need [setColumnWidth](https://developers.google.com/apps-script/reference/document/table#setcolumnwidthcolumnindex,-width). – NightEye Feb 04 '22 at 21:52
In my code, how to set the width of the first column with setColumnWidth ? – Gavis Feb 04 '22 at 21:55
Hi @Gavis, you can try and change `copyBody.insertTable(childIndex+1, tableValues);` to `copyBody.insertTable(childIndex+1, tableValues).setColumnWidth(0, 100);`, this will set the 1st column to 100 points. I'd like to assist you more but this question is actually already unrelated to the original post. If you have other questions or followup ones, kindly post it as another question. Thank you. – NightEye Feb 04 '22 at 21:59
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/241742/discussion-between-gavis-and-octavia-sima). – Gavis Feb 04 '22 at 22:18

Creating a Google Doc from spreadsheet with the right formatString GAS

1 Answers1