How to copy Google sheet Table to Google doc with same text color and formatting?

Question

I am trying to import table from google sheet to Google doc using google apps script. So far I have been able to import data table to doc with partial formatting. The issue is Font Style and text color is not preserved when table is imported to google doc.

Here is the code:

function appendTable() {
  
  // Replace these values with your Sheet ID, Document ID, and Sheet Name
  
  let ssId = '<Spreadsheet Id>' // REPLACE
  let docId = '<Google doc Id>' // REPLACE
  let sheetName = '<Sheet Name>' // REPLACE
  
  // Sheet
  let range = SpreadsheetApp.openById(ssId).getSheetByName(sheetName).getDataRange()
  let values = range.getValues();
  let backgroundColors = range.getBackgrounds();
  let styles = range.getTextStyles();
  
  
 // Document
  let body = DocumentApp.openById(docId).getBody();
  let table = body.appendTable(values);
 
  for (let i=0; i<table.getNumRows(); i++) {
    for (let j=0; j<table.getRow(i).getNumCells(); j++) {
      
      let docStyles = {};
      
      docStyles[DocumentApp.Attribute.BACKGROUND_COLOR] = backgroundColors[i][j];
      docStyles[DocumentApp.Attribute.FONT_SIZE] = styles[i][j].getFontSize();
      docStyles[DocumentApp.Attribute.BOLD] = styles[i][j].isBold();
     // docStyles[DocumentApp.Attribute.FOREGROUND_COLOR] = colors[i][j];
      
      table.getRow(i).getCell(j).setAttributes(docStyles);
    }
  }
} 

When script runs, it imports the following table from the sheet:

But the imported table in Google doc lose its formatting and looks like this:

Can you please guide me on what am I missing here, why the Font style of the table in Google docs and the text color not the same as the sheet table? Here is the

link

to the sample sheet. Thank you

Answer 1

When I saw your showing images, I thought that it is required to include DocumentApp.Attribute.FOREGROUND_COLOR in docStyles. So, how about the following modification?

From:

 let docStyles = {};
 
 docStyles[DocumentApp.Attribute.BACKGROUND_COLOR] = backgroundColors[i][j];
 docStyles[DocumentApp.Attribute.FONT_SIZE] = styles[i][j].getFontSize();
 docStyles[DocumentApp.Attribute.BOLD] = styles[i][j].isBold();
// docStyles[DocumentApp.Attribute.FOREGROUND_COLOR] = colors[i][j];
 
 table.getRow(i).getCell(j).setAttributes(docStyles);

To:

let docStyles = {};
docStyles[DocumentApp.Attribute.BACKGROUND_COLOR] = backgroundColors[i][j];
docStyles[DocumentApp.Attribute.FONT_SIZE] = styles[i][j].getFontSize();
docStyles[DocumentApp.Attribute.BOLD] = styles[i][j].isBold();
docStyles[DocumentApp.Attribute.FOREGROUND_COLOR] = styles[i][j].getForegroundColorObject().asRgbColor().asHexString(); // Added
table.getRow(i).getCell(j).setAttributes(docStyles);

Reference:

• getForegroundColorObject()