如何通过Google Apps Script实现Spreadsheet表格以link to spreadsheet方式复制到Google Doc？

If you’ve ever wanted to embed a live-updating table from Google Sheets into Google Docs (mirroring the native "link to spreadsheet" option but with more control), this solution is for you. It lets you insert a table that stays connected to your source Sheet, and you can refresh it with one click whenever your data changes—no re-copying or re-linking required.

Step 1: Set Up the Script #

Open your target Google Doc, go to Extensions > Apps Script to launch the script editor. Delete the default myFunction code and paste in the following full script:

// Adds a custom menu to the Doc for easy access
function onOpen() {
  const ui = DocumentApp.getUi();
  ui.createMenu("🔗 Linked Tables")
    .addItem("Insert New Linked Table", "insertLinkedSpreadsheetTable")
    .addItem("Refresh All Linked Tables", "refreshLinkedTables")
    .addToUi();
}

// Inserts a linked table from a Google Sheet into the Doc
function insertLinkedSpreadsheetTable() {
  const ui = DocumentApp.getUi();
  const doc = DocumentApp.getActiveDocument();
  const body = doc.getBody();
  
  // Prompt user for Spreadsheet ID and range (no code editing needed!)
  const spreadsheetIdPrompt = ui.prompt(
    "Enter Spreadsheet ID", 
    "Find this in your Sheet's URL (between /d/ and /edit):", 
    ui.ButtonSet.OK_CANCEL
  );
  if (spreadsheetIdPrompt.getSelectedButton() !== ui.Button.OK) return;
  const spreadsheetId = spreadsheetIdPrompt.getResponseText().trim();
  
  const rangePrompt = ui.prompt(
    "Enter Range A1 Notation", 
    "Example: Sheet1!A1:D10", 
    ui.ButtonSet.OK_CANCEL
  );
  if (rangePrompt.getSelectedButton() !== ui.Button.OK) return;
  const rangeA1 = rangePrompt.getResponseText().trim();
  
  try {
    // Fetch data from the source Sheet
    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const range = spreadsheet.getRange(rangeA1);
    const data = range.getValues();
    
    // Insert the table into the Doc
    const table = body.appendTable(data);
    
    // Store the source connection details directly in the table's attributes
    table.setAttributes({
      spreadsheetId: spreadsheetId,
      rangeA1: rangeA1
    });
    
    // Add a clear caption to indicate it's a linked table
    body.appendParagraph("🔗 Linked table (use the 'Linked Tables' menu to refresh)")
      .setAlignment(DocumentApp.HorizontalAlignment.CENTER)
      .setFontSize(10);
    
    ui.alert("Linked table inserted successfully!");
  } catch (e) {
    ui.alert(`Error: ${e.message}\nPlease check your Spreadsheet ID and range are correct.`);
  }
}

// Refreshes all linked tables in the Doc to match the latest Sheet data
function refreshLinkedTables() {
  const ui = DocumentApp.getUi();
  const doc = DocumentApp.getActiveDocument();
  const body = doc.getBody();
  const tables = body.getTables();
  let updatedCount = 0;
  
  tables.forEach(table => {
    const attributes = table.getAttributes();
    // Check if the table is a linked one
    if (attributes.spreadsheetId && attributes.rangeA1) {
      try {
        const spreadsheetId = attributes.spreadsheetId;
        const rangeA1 = attributes.rangeA1;
        
        // Fetch the latest data from the Sheet
        const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
        const range = spreadsheet.getRange(rangeA1);
        const data = range.getValues();
        
        // Clear existing rows to replace with updated data
        const numRows = table.getNumRows();
        for (let i = numRows - 1; i >= 0; i--) {
          table.removeRow(i);
        }
        
        // Add the updated rows to the table
        data.forEach(row => {
          const tableRow = table.appendTableRow();
          row.forEach(cellValue => {
            tableRow.appendTableCell(cellValue);
          });
        });
        
        updatedCount++;
      } catch (e) {
        ui.alert(`Error refreshing a linked table: ${e.message}`);
      }
    }
  });
  
  ui.alert(`Done! Refreshed ${updatedCount} linked table(s).`);
}

Step 2: Use the Script #

1. Insert a Linked Table:

   • Open your Doc, look for the new 🔗 Linked Tables menu in the top bar.
   • Select Insert New Linked Table, then follow the prompts to enter your Sheet ID and target range.
   • The table will be inserted automatically, with a caption noting it’s linked.

2. Refresh Data:

   • Whenever your Sheet data changes, go back to the 🔗 Linked Tables menu and select Refresh All Linked Tables.
   • All linked tables in the Doc will update to match the latest Sheet data.

Key Features & Tips #

• No Code Editing Required: The script uses prompts to get your Sheet details, so you don’t need to tweak the code every time.
• Multiple Linked Tables: You can insert as many linked tables as you want (each pointing to different Sheet ranges), and the refresh function will update all of them.
• Error Handling: The script will alert you if there’s an issue (like an invalid Sheet ID or range) so you can fix it quickly.
• Authorization: The first time you run the script, you’ll need to authorize it to access your Docs and Sheets (this is standard for Google Apps Script and safe to approve).

内容的提问来源于stack exchange，提问作者Sudhanshu Patel