CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docsgoogle-workspace
Sudhanshu Patel
Sudhanshu Patel

Reputation: 799

How to copy Table from a Spreadsheet to a Google Doc as `link to Spreadsheet`?

When users copy a range manually from a spreadsheet and paste it into a Google Doc, it prompts the option to "link to spreadsheet" and "paste normally".

I needed to do the functionality of "linked to a spreadsheet using google apps script"

link to spreadsheet while pasting manually

Use case context

We have a spreadsheet with a table we are trying to copy to our final document where the user can add additional stuff.

So if for any reason some value needs to change in the spreadsheet, our users just want to simply refresh the table data.

Upvotes: 3

Views: 1154

Answers (1)

iansedano
iansedano

Reputation: 6481

Answer

This is not possible without a workaround.

In the table object in the documentation there is no method for inserting a table with a "link to spreadsheet" as there is in the UI. Seeking in the table object in the Docs API there is nothing exposed there either. You could file a feature request for this in the Issue Tracker, at the moment, I can see no feature requests for this.

A potential avenue for a workaround

Any workaround will not be as seamless as the UI, though if you want to automate the inserting of a table from a specific source, here is an example of that.

Please note that this is only a starting point. You should experiment with this and then if you need more features and run into problems, you should ask new questions about the specific problems your are having.

Introduction and initial steps

This script will take a table from a Sheet. This table should be the only thing in the sheet. The way the script is set up, is that it uses getDataRange which automatically selects all the data in a Sheet. You can modify this for your use case depending on how your spreadsheet is set up.

![enter image description here

Then it will append the table to the end of the document. You can modify this depending on your needs. It will keep most of the formatting.

enter image description here

The styles will not match exactly, though again, this is something you can iron out the details depending on your use case.

Instructions

  1. Get the id number of the spreadsheet and document
  2. Create a script file
  3. Copy this function:
function appendTable() {
  
  // Replace these values with your Sheet ID, Document ID, and Sheet Name
  
  let ssId = '<your spreadsheet id>' // REPLACE
  let docId = '<your document id>' // REPLACE
  let sheetName = '<your 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()
      
      table.getRow(i).getCell(j).setAttributes(docStyles);
    }
  }
}
  1. Run the script!
  2. Design how you want the user to run this and maybe set this up an add-on. Though this is out of the scope of this question.

References

Upvotes: 4

Related Questions