如何通过Google Sheets ImportXML导入指定网页的Major Market Sectors表格

I’ve run into similar issues with dynamically rendered tables on financial sites before—Fidelity’s pages often use JavaScript to load content, which is why IMPORTHTML or raw XPath queries might not work out of the box. Here are two reliable methods to get that sector table into your sheet:

Method 1: Google Apps Script (For Automated/Updatable Imports) #

This is the best approach if you want the data to refresh automatically or if the table is loaded dynamically. Here’s how to set it up:

1. Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
2. Delete the default myFunction() code, then paste the script below.
3. Add the Cheerio library (for easy HTML parsing):
   • Click Libraries > + in the left sidebar.
   • Paste this library ID: 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0
   • Select the latest version from the dropdown, then click "Add".
4. Run the importFidelitySectors function. You’ll need to authorize the script (follow the prompts—Google will warn you it’s an unverified app, but you can proceed safely by selecting "Advanced" > "Go to [Your Script Name]").
5. The table data will populate starting at cell A1 in your active sheet.

function importFidelitySectors() {
  const url = "https://fundresearch.fidelity.com/mutual-funds/composition/316389303";
  const response = UrlFetchApp.fetch(url, {
    headers: {
      "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
    }
  });
  const html = response.getContentText();
  const $ = Cheerio.load(html);
  
  // Target the exact sector breakdown table using its data-test-id attribute
  const sectorTable = $('div[data-test-id="sector-breakdown"] table');
  const rows = sectorTable.find('tr');
  
  const tableData = [];
  rows.each((index, row) => {
    const rowContent = [];
    // Extract text from both header and data cells
    $(row).find('th, td').each((cellIndex, cell) => {
      rowContent.push($(cell).text().trim());
    });
    // Skip empty rows
    if (rowContent.length > 0) {
      tableData.push(rowContent);
    }
  });
  
  // Write data to the sheet
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  activeSheet.clearContents();
  activeSheet.getRange(1, 1, tableData.length, tableData[0].length).setValues(tableData);
}

Why this works: #

• We use UrlFetchApp to fetch the page HTML, with a browser-like User-Agent header to avoid being blocked by Fidelity’s servers.
• Cheerio acts like jQuery to parse the HTML, letting us target the table using the data-test-id="sector-breakdown" attribute—this is more reliable than raw XPath because it’s tied to the page’s intended structure, not just element positions.
• The script extracts all rows and cells, cleans up whitespace, and writes the data directly to your sheet.

You can also set up a time-driven trigger (under Triggers > Add trigger) to refresh the data daily/weekly automatically.

Method 2: Manual Copy-Paste (Quick One-Time Import) #

If you only need the data once, this is faster:

• Open the Fidelity page, right-click anywhere in the "Major Market Sectors" table.
• Select Inspect to open Chrome DevTools. In the Elements tab, find the <table> tag nested inside the div[data-test-id="sector-breakdown"] container.
• Right-click the <table> tag, choose Copy > Copy element.
• Switch back to Google Sheets, select cell A1, right-click, and choose Paste. The table will paste perfectly with all formatting intact.

Why your initial XPath attempt might have failed: #

Fidelity’s pages sometimes load content dynamically after the initial HTML is fetched. If you tried to use XPath on the raw HTML (like what IMPORTHTML sees), the table might not exist yet. The inspector shows the fully rendered page, so your XPath worked there—but Google Sheets can’t wait for JavaScript to run before parsing the HTML.

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