Embed Google Spreadsheet table Withing Google Document when data contains dates

Question

I found a great script here that will add a Google Sheet table to my Google Doc.

function onOpen() {
  DocumentApp.getUi()
             .createMenu('Custom')
             .addItem('Update Data', 'updateData')
             .addToUi();
}

function updateData() {
  var ssUrl = ' spreadsheet url here ';
  var sheetName = 'Sheet2';   // name of sheet to use
  var rangeName = 'A1:C3';    // range of values to include 

  var values = SpreadsheetApp.openByUrl(ssUrl)
                             .getSheetByName(sheetName)
                             .getRange(rangeName)
                             .getValues();
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();
  var ranges = doc.getNamedRanges('embeddedSheet-range');
  if (ranges.length == 0) {
    var table = body.appendTable(values);
  }
  else {
    tableRange = ranges[0];
    table = tableRange.getRange().getRangeElements()[0].getElement();
    var ind = body.getChildIndex(table);
    tableRange.remove();
    body.removeChild(table);
    table = body.insertTable(ind, values);
  }
  var rangeBuilder = doc.newRange();
  rangeBuilder.addElement(table);
  doc.addNamedRange('embeddedSheet-range', rangeBuilder.build());
}

This works fine with regular numbers and text, but if a cell in Google sheet is formatted as a date, for example with the value "30/01/2020 10:58:41" it prevents the script form working and I get the message:

The parameters (number[]) don't match the method signature for
DocumentApp.Body.appendTable

Any ideas?

Answer 1

Replace

 var values = SpreadsheetApp.openByUrl(ssUrl)
                             .getSheetByName(sheetName)
                             .getRange(rangeName)
                             .getValues();

by

 var values = SpreadsheetApp.openByUrl(ssUrl)
                             .getSheetByName(sheetName)
                             .getRange(rangeName)
                             .getDisplayValues(); 

The above should work because getDisplayValues() returns a 2D array having the text representation of the cell values while getValues() returns strings, numbers and Date objects.