0

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?

0

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.

| improve this answer | |
  • Thanks Ruben <br/> You solution worked as charm :( 2 more questions 1- Is there a way to to format to the table data that I'm Adding? Specifically , Bold the text in the header row, change font size of rows under the header row, generate the table in RTL direction* 2- Is there a way to add the table in a specific page of the Google doc ? For example page 3? – Azriel Lider Feb 19 at 10:05
  • @AzrielLider The short answer for both question is yes, there is a way. Please bear in mind that follow-up questions should be posted as new questions, but before doing that, please read developers.google.com/apps-script/guides/docs – Rubén Feb 19 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.