Converting formatted Text from Google sheets to Google docs

Question

I am trying to copy formatted texts from google sheets to google docs using google scripts. I have successfully converted text from sheets to docs however I am unable to carry over the relevant formatting/markdowns like bold, italics, colour, underlined & etc. Does anyone have any idea as to what I am doing wrong or what functions I can use in the google scripting library which allows me to copy over the formatting as well?

Currently, I have an existing google doc that acts as the template. All future google docs created will follow a similar template. I have created a sheet named 'doc Builder' and have used ,for loops and switch statements to choose which cell within the sheet to be copied over to the word doc.

function createDocument() {

  var docbuilder = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('doc Builder');

  //active data range
  var range = docbuilder.getRange(4, 1, docbuilder.getLastRow() - 3, docbuilder.getLastColumn() - 1).getDisplayValues();

  var templateId = 'myworddocIDwhichihaveremoved'; //the word doc

  //Make a copy of the template file
  var documentId = DriveApp.getFileById(templateId).makeCopy().getId();

  //Rename the copied file
  DriveApp.getFileById(documentId).setName('name of new doc');

  //Get the document body as a variable
  var body = DocumentApp.openById(documentId).getBody();

  //copies texts from cell to word doc 
  //i = row, j = column
  for(var i = 0; i < range.length; i++){
    for(var j = 0; j < range[i].length; j++){
      var cells = [];
      switch(j) {
      case 0:
      body.appendParagraph(range[i][j]);
      break;
      case 1: 
      body.appendParagraph(range[i][j]);
      break;
      case 2: 
        if(range[i][j] != ""){
          body.appendParagraph('PARAGRAPH 1:' + range[i][j]);
        }
      break;
      case 3:
        body.appendParagraph('PARAGRAPH 2:' + range[i][j]);
      break;
      }
    }
  }
}

I have tried copyTo() and it copies the formatting from sheet to sheet successfully however am unable to do the same for sheet to doc. I am also aware of the attributes which I can add to my word doc like BACKGROUND_COLOR, BOLD and etc from the documentation however the data I am handling often only has some parts of the cell formatted for example : sally is a girl instead of sally is a girl. Thus making it difficult to hard code when the number of cells increases.

Simply put I am trying to bring over the formatting from the sheet to the doc so I don't have to handle each cell individually.

I am working with more cases but I have removed them to simplify the code, also every cell within the active data range is formatted but when the new google doc is created the formatting disappears.

I hope someone has a solution to this haha :""D

Answer 1

Copying values from Sheets to Docs with formatting

There is no native method that you can use to copy formatted text from Sheets to Docs. They don't use the same classes to handle formatted text.

Sheets has RichTextValue that contains all the information for a cell. For example, when you call:

const range = sheet.getRange("A2")
const richTextValue = range.getRichTextValue()

You then can obtain all the information about the text formatting within the cell. For example, if you have a cell like this:

If you get the rich text value from this cell and then call the getRuns() method on this value you will get a series of new RichTextValue:

wherein each run is the longest possible substring having a consistent text style.

So for the example you will get a new object for:

• "Hello"
• "bold"
• "italic"
• ... etc

You may also get individual object for the spaces between words.

For each of these objects, you can call a series of methods to get the individual components of its format:

• getFontFamily()
• getFontSize()
• getForegroundColor()
• isBold()
• isItalic()
• isStrikethrough()
• isUnderline()

NOTE: getBackgroundColor() is not used in this example because background color in sheets cannot apply to single text runs, but the whole cell.

There is no equivalent class in DocumentApp. You can't append a RichTextValue to any element in a document. So this means that you need to match up the corresponding methods that you need. For example, you could use the Text class which has all the corresponding methods, you would just need a go-between to link up the methods and sequence them in the right way.

Example implementation

This would most likely need to be adapted to your exact needs, I don't know what the logic of the switch statements are and I don't have sample data to test it with, but this should give you a good idea of how it might work. You may also be able to use the custom class as-is in your script.

Ideally you would be able to call some simple methods from the main script, something like this:

function main() {
  // Getting the rich text value
  const sheet = SpreadsheetApp.getActive();.getSheetByName("Sheet1");
  const range = sheet.getRange("A2");
  const value = range.getRichTextValue();

  // Creating an instance of a custom class that will be implemented
  const textToExport = new SheetRichText(value)

  // Setting the target document
  const doc = DocumentApp.openById("[YOUR DOCUMENT ID]")
  const body = doc.getBody()

  // Calling a method of the custom class
  textToExport.appendAsNewParagraph(body)
}

NOTE: Replace [YOUR DOCUMENT ID] with the correct document ID.

Remember that in my example my sheet has this:

The custom class I have implemented in my example is:

class SheetRichText{
  // To initialize it you pass it a RichTextValue object
  constructor(RichTextValue){
    // It gets all the runs and creates an object that contains all the information
    // needed to call the corresponding methods in the document Text class.
    this.runs = RichTextValue.getRuns().map(run => {
      const style = run.getTextStyle()
      return {
        "style" : {
            "fontFamily" : style.getFontFamily(),
            "fontSize" : style.getFontSize(),
            "foregroundColor" : style.getForegroundColor(),
            "bold" : style.isBold(),
            "italic" : style.isItalic(),
            "strikethrough" : style.isStrikethrough(),
            "underline" : style.isUnderline()
          },
        "text" : run.getText(),
        "start" : run.getStartIndex(),
        "end" : run.getEndIndex()
      }
    })
  }

  // This takes as an argument the body of a document and adds the RichTextValue
  // to the document as a new paragraph
  appendAsNewParagraph(body){

    // Initializing the new blank paragraph
    const paragraph = body.appendParagraph("")

    // For each run, copy the text and then set all the formatting
    // making sure that the start and end indices are called.
    this.runs.forEach(run => {
      const textElement = paragraph.asText().appendText(run.text)
      const [start, end] = [run.start, run.end -1]

      textElement.setFontFamily(start, end, run.style.fontFamily)
      textElement.setFontSize(start, end, run.style.fontSize)
      textElement.setForegroundColor(start, end, run.style.foregroundColor)
      textElement.setBold(start, end, run.style.bold)
      textElement.setItalic(start, end, run.style.italic)
      textElement.setStrikethrough(start, end, run.style.strikethrough)
      textElement.setUnderline(start, end, run.style.underline)
    })
  }
}

Which results in:

References

• Sheets RichTextValue
• Docs Text