I’m trying to find a way to automatically transfer a table from Google Sheets into a Google Doc while maintaining the link between them using Google Apps Script.
My current manual process looks like this:
Highlight the data range in the spreadsheet
Copy it with Ctrl + C
Open the target document
Paste with Ctrl + V
Choose the “Paste as Linked Object” option
I’ve attempted to recreate this workflow using Apps Script methods like addTable() and insertTableFromRange(), but these functions only transfer the raw data without any formatting. More importantly, they don’t create the dynamic link back to the source spreadsheet.
Does anyone know if there’s a built-in Apps Script method to achieve this linked table functionality? Alternatively, is there any way to simulate user interface interactions in Apps Script similar to how PyAutoGUI works in Python?
I ran into this exact problem building automated client reports last year. Apps Script’s native methods don’t support the linked object feature you get in the UI - it’s just not there. But I found a workaround that might help. Instead of trying to recreate linked paste, I built a custom function that watches the source sheet for changes using onChange triggers. When data updates, the script instantly refreshes the table in your document. It’s not technically a linked object, but it syncs in near real-time. The trick is using getRange().getDisplayValues() to keep your number and date formatting from the sheet, then applying that same formatting to document table cells with setAttributes(). This has worked great for my automated reports, though it takes more setup than just pasting manually.
I’ve dealt with this same issue! Apps Script can’t do the “paste as linked object” thing - that’s only available through the UI. My workaround uses onEdit() triggers on the source sheet to push updates automatically. When someone changes the data, the trigger fires and updates specific cells in the doc using replaceText() with placeholders. It’s not as clean as real linking, but it keeps everything synced without any manual updates.
Unfortunately, Apps Script can’t create linked tables like the manual paste option. I hit this same wall six months ago while automating weekly reports. The insertTableFromRange() method you mentioned only does static data transfer. My workaround was setting up a time-based trigger that runs every few hours to refresh the static table with new data from the source sheet. It’s not truly linked, but works for most situations. You can use replaceText() or rebuild the whole table section when data changes. Another option I’ve seen is using the Sheets API inside the script to grab data, then format it directly in the doc with paragraph and table styling methods. This gives you better control over formatting while keeping data current through scheduled updates.