Google Sheets使用IMPORTXML与XPath抓取价格报错，求排查及解决方法

Hey there! Let's break down why your formula is throwing an error and how to get that 102.4 value into your sheet.

Why You're Seeing #ERROR! #

1. The Price is Dynamically Loaded (Most Likely) #

IMPORTXML only grabs static HTML that the server sends directly—if that price pops up after the page loads via JavaScript, it won't exist in the raw source code. That means IMPORTXML can't see it at all.

To check this: Open the target website, right-click and select View Page Source (not "Inspect"—Inspect shows the rendered DOM, not the raw code). Press Ctrl+F and search for id11. If it doesn't show up, dynamic loading is your issue, and IMPORTXML won't work here.

2. The Website is Blocking Google Sheets' Request #

Some sites detect and block requests from tools like IMPORTXML because of their default User-Agent. If the site sees it's a bot instead of a real browser, it might return empty or broken content.

3. Your XPath Syntax is Actually Fine #

Quick note: Your formula uses //*[@id=""id11""]—that double-double-quote is correct for escaping in Google Sheets strings, so this isn't the problem.

How to Fix It #

If It's Dynamic Content: Use Google Apps Script with Puppeteer #

Since IMPORTXML can't handle JS-rendered content, we'll use a script to simulate a real browser loading the page:

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

async function fetchDynamicPrice() {
  const targetUrl = "http://www.funder.co.il/fund.aspx?id=5122973";
  // Launch headless Chrome
  const browser = await Puppeteer.launch({ headless: true });
  const page = await browser.newPage();
  // Wait for the page to fully load all dynamic content
  await page.goto(targetUrl, { waitUntil: "networkidle2" });
  // Grab the text from the element with id11
  const priceText = await page.$eval("#id11", el => el.textContent.trim());
  await browser.close();
  // Write the result to cell A1 (change this to your target cell)
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue(priceText);
}

3. Click Services > Add Service and select Puppeteer (you might need to enable advanced services first if it's missing).
4. Run the function—you'll need to grant permissions the first time.

You can even set up a time-driven trigger to auto-update the price regularly.

If It's a Blocked Request: Customize the Request Header #

If the content is static but blocked, use UrlFetchApp to mimic a real browser:

function fetchStaticPrice() {
  const targetUrl = "http://www.funder.co.il/fund.aspx?id=5122973";
  const requestOptions = {
    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 response = UrlFetchApp.fetch(targetUrl, requestOptions);
  const rawHtml = response.getContentText();
  
  // Use regex to extract the price since HTML might be messy for XML parsing
  const priceMatch = rawHtml.match(/<div[^>]*id="id11"[^>]*>([\d.]+)<\/div>/);
  const price = priceMatch ? priceMatch[1] : "Price not found";
  
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue(price);
}

Quick Test to Rule Out Issues #

First, try grabbing a basic element with IMPORTHTML to see if the site allows requests from Google Sheets:

=IMPORTHTML("http://www.funder.co.il/fund.aspx?id=5122973", "list", 1)

If this also returns #ERROR!, the site is either blocking you or all content is dynamic.

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