I’m exploring how to use Google Apps Script to copy table information from a Google Sheet to a Google Doc. The problem I’m encountering is that all my text formatting disappears during the transfer.
I found a workaround that helps me retain some basic formatting, but this solution only applies if the entire cell has the same style. In my case, some cells have different formatting for different parts of the text, like bold or italic styles.
For instance, I may have a cell containing “Regular text bold textitalic text” where various segments have distinct formatting.
When I try to copy the data using standard methods, the mixed formats within the cells don’t make it over to the Google Doc correctly. While the text appears, all the special formatting is lost.
Is there a method to keep all the varied text formatting when copying tables from Sheets to Docs with Google Apps Script? I want to ensure that aspects like partial bold text, colored segments, and italicized parts stay intact within the same cell.
Been dealing with this exact headache for years. Apps Script’s formatting preservation is honestly painful to work with manually.
I ended up automating the whole thing instead of wrestling with getRichTextValue() and those character-by-character loops. Too much maintenance when you’ve got multiple sheets and docs to sync regularly.
I set up automation that pulls rich text from Sheets, processes all the formatting (bold, italic, colors, etc.), and pushes it to Docs with formatting intact. Best part? It runs automatically when your source data changes, so no babysitting.
Handles mixed formatting within cells perfectly and scales way better than manual scripting. You can add error handling and retry logic without cluttering your Apps Script code.
This issue with maintaining mixed formatting during the transfer of data from Sheets to Docs is indeed a common challenge with Apps Script. The conventional methods typically strip away any formatting, so your idea of utilizing the TextStyle and TextRange classes sounds effective. By iterating through each character of the cell, you’re able to capture the formatting changes accurately, allowing you to recreate them in your document. While this approach does require more effort than a simple copy-paste, it is ultimately the best solution for preserving the intricate formatting you need.
Just dealt with this myself. The Document service’s appendTable() method basically breaks when you try to bring over formatted content from Sheets. Here’s what worked for me: build a custom mapping function that actually keeps the formatting intact during transfer. I used a two-stage approach - grab all the rich text data with formatting first, then rebuild it in the document using proper text styling. The tricky bit is nested formatting. When you’ve got bold italic text, you need both attributes hitting the same text range or it won’t work. Font size and color threw me for a loop too. You have to explicitly handle these with setFontSize() and setForegroundColor() on the destination range. Skip this and the document’s default styling will override everything, even with rich text methods.
The getValues() method strips everything - that’s your problem. Use getDisplayValues() with the rich text methods people mentioned. I had the same issue last month and this combo preserved mixed formatting without breaking anything.
Apps Script and mixed cell formatting is tricky - there’s a gotcha that gets everyone. The formatting actually gets lost during table insertion, not just when copying. I ditched appendTable() completely and built each cell manually instead. Use insertTable() with empty cells, then populate them one by one with your formatted content. You’ll need to loop through each cell’s rich text runs and apply formatting directly to the document cell using the Paragraph API. Here’s the key: Docs handles inline formatting totally differently than Sheets. You can’t just transfer formatting wholesale - you have to rebuild it for each text segment. This saved all my bold, italic, and colored text without losing anything.
yeah, totally get it! using getRichTextValue() for Sheets is key, and then setRichText() for Docs. normal copy just messes everything up, but those rich text functions keep ur formats intact!
I’ve dealt with this before - the RichText API works differently than most people think. You can’t just apply formatting to the whole thing. You need to grab the TextStyle objects for each text run in the cell. Here’s what worked for me: build a helper function that loops through each text run using getRichTextValue().getRuns(), then rebuild each piece in the destination with its original TextStyle properties. This keeps all the bold, italic, colors, and other formatting intact for cells with mixed content. The annoying part is line breaks and special characters in formatted segments - they need special handling when you transfer them. Test it with cells that have lots of formatting changes to make sure everything comes through properly.
Manual scripting with getRichTextValue() loops gets messy fast, especially with multiple sheets or scheduled runs.
I’ve watched teams waste weeks building custom TextStyle mapping functions, then spend more time fixing bugs with nested formatting and special characters.
Maintenance is the real killer. Scripts break when Google updates their API, you need new formatting support, or want conditional logic for different templates.
I switched to automation workflows that handle everything - pull formatted data from Sheets, keep all rich text styling, push to Docs without those painful character loops.
Runs automatically when data changes, handles errors well, scales across multiple workflows without duplicate code.
You also get retry logic built-in and can easily add validation or data transformation steps.
Latenode handles this exact workflow without any custom scripting: https://latenode.com