如何让已发布Google Sheets链接的单元格内跳转在网页iframe中正常生效？

I get it, it's frustrating when Google Sheets links break out of your iframe even after tweaking sandbox settings. The core issue here is twofold: Google's pubhtml embed forces links to open in new tabs by default, and the edit URLs you were using in Apps Script point to the full Sheets interface instead of the embedded view. Here's how to fix it:

Step 1: Replace Pubhtml Embed with a Custom Apps Script Web App #

Instead of using Google's built-in pubhtml embed, we'll create a custom web app that serves your sheet data with fully controllable links. This lets us set links to open in the same iframe.

Create the Web App Script #

1. Open your Google Sheet, go to Extensions > Apps Script.
2. Replace the default code with this:

function doGet(e) {
  // Replace with your Sheet ID (found in the sheet's URL)
  const sheetId = "1Aks5t1b08U4mA_-A1r6Js66tPkI24iQj-fYNNSyH1Ck";
  // Replace with the name of your sheet (e.g., "+")
  const sheetName = "+";
  
  const ss = SpreadsheetApp.openById(sheetId);
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const targetRange = e.parameter.range || "A1"; // Default to top-left if no range specified

  // Define your cell-to-cell link mappings here
  const linkMap = {
    "L9": "M10", // Click L9 to jump to M10
    "M10": "L9", // Click M10 to jump back to L9
    // Add more links like "A1": "B5" as needed
  };

  // Build the HTML table
  let html = `
  <html>
    <head>
      <style>
        /* Style to match Google Sheets look (customize as needed) */
        table { border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; }
        td { border: 1px solid #ccc; padding: 8px; text-align: left; }
        a { color: #1a73e8; text-decoration: none; }
        a:hover { text-decoration: underline; }
      </style>
    </head>
    <body>
      <table>`;

  // Loop through rows and cells to build the table
  data.forEach((row, rowIndex) => {
    html += "<tr>";
    row.forEach((cell, colIndex) => {
      const cellAddress = sheet.getRange(rowIndex + 1, colIndex + 1).getA1Notation();
      let cellContent = cell || ""; // Handle empty cells

      // Add link if cell is in our linkMap
      if (linkMap[cellAddress]) {
        cellContent = `<a href="?range=${linkMap[cellAddress]}" target="_self">${cellContent}</a>`;
      }

      html += `<td data-range="${cellAddress}">${cellContent}</td>`;
    });
    html += "</tr>";
  });

  // Add script to scroll to the target range
  html += `
      </table>
      <script>
        window.onload = () => {
          const targetCell = document.querySelector('td[data-range="${targetRange}"]');
          if (targetCell) {
            targetCell.scrollIntoView({ behavior: 'smooth', block: 'center' });
          }
        };
      </script>
    </body>
  </html>`;

  return HtmlService.createHtmlOutput(html);
}

3. Save the script (name it something like "SheetEmbedLinks").

Deploy the Web App #

1. Click Deploy > New deployment.
2. Under Type, select Web app.
3. Set:
   • Execute as: Me (your email)
   • Who has access: Anyone, even anonymous (adjust if you need restricted access)
4. Click Deploy, authorize permissions when prompted, then copy the web app URL.

Step 2: Embed the Web App in Your WordPress Site #

Replace your existing iframe code with this (using the web app URL you copied):

<iframe title="table" 
        src="YOUR_WEB_APP_URL" 
        width="900" 
        height="900" 
        sandbox="allow-same-origin allow-scripts">
</iframe>

• The sandbox attributes are minimal and correct (no pipes—spaces separate values).
• allow-same-origin lets the web app run scripts, and allow-scripts enables the scroll functionality.

Why This Works #

• Controlled Links: We explicitly set target="_self" on all links, so they open in the same iframe instead of a new tab.
• Internal Navigation: The web app uses URL parameters (?range=XXX) to navigate between cells, and a small script scrolls the table to the target cell.
• No External Redirects: Unlike the pubhtml embed or edit URLs, this stays within your custom web app hosted on Google's servers, keeping navigation locked to the iframe.

Bonus: Customize the Styling #

You can tweak the CSS in the web app script to match your WordPress site's design—adjust fonts, colors, borders, etc., to make the table blend in seamlessly.

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