Home > front end >  How to copy Google sheet Table to Google doc with same text color and formatting?
How to copy Google sheet Table to Google doc with same text color and formatting?

Time:09-22

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:

Sheet Table

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

Doc Table

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

enter image description here

Doc:

enter image description here

  • Related