Google Sheets IMPORTXML抓取URL报错求助（仅需打开时触发脚本）

I’ve run into similar issues with Google Sheets’ built-in scraping functions like IMPORTHTML—many sites use anti-scraping measures or have dynamic content that breaks these tools. Here’s a reliable solution using Google Apps Script that triggers automatically when you open your sheet, and pulls the exact Achain price data you need into columns D, E, and F starting at row 3:

Step 1: The Apps Script Code #

First, open your Google Sheet, navigate to Extensions > Apps Script to launch the script editor. Replace the default code with this:

function onOpen() {
  // Runs automatically when the sheet is opened
  fetchAchainPrices();
}

function fetchAchainPrices() {
  const targetUrl = "https://walletinvestor.com/forecast/achain-prediction";
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  try {
    // Fetch webpage with browser-like headers to avoid anti-scraping blocks
    const response = UrlFetchApp.fetch(targetUrl, {
      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 rawHtml = response.getContentText();
    
    // Extract prices using regex (adjust patterns if site structure changes)
    const currentPrice = extractPrice(rawHtml, "Current Price");
    const oneYearForecast = extractPrice(rawHtml, "1-Year Forecast");
    const fiveYearForecast = extractPrice(rawHtml, "5-Year Forecast");
    
    // Write values to specified cells
    activeSheet.getRange("D3").setValue(currentPrice);
    activeSheet.getRange("E3").setValue(oneYearForecast);
    activeSheet.getRange("F3").setValue(fiveYearForecast);
    
  } catch (error) {
    activeSheet.getRange("D3:F3").setValue("Error: " + error.message);
    console.error("Scraping failed:", error);
  }
}

// Helper function to extract price text from HTML
function extractPrice(html, label) {
  const regexPattern = new RegExp(`${label}.*?([\\d\\.]+)`, "i");
  const match = html.match(regexPattern);
  return match ? match[1] : "N/A";
}

Step 2: How It Works #

• Auto-Trigger: The onOpen() function runs as soon as you open the sheet, so your data updates without manual intervention.
• Browser Emulation: The User-Agent header makes your request look like it’s coming from a real browser, which bypasses most basic anti-scraping blocks that break IMPORTHTML.
• Regex Parsing: The helper function uses flexible regex to pull out prices next to your target labels. If the site’s HTML structure changes later, you can tweak the regex pattern to match the new layout.
• Error Handling: If the site is down, or the structure changes unexpectedly, it will display a clear error message in cells D3:F3 instead of crashing silently.

Step 3: Setup & Troubleshooting #

1. Save & Authorize: Click the save icon (💾) in the script editor, name your project (e.g., "Achain Price Scraper"), then run the fetchAchainPrices function once. You’ll need to authorize the script to access your sheet and fetch web data—follow the prompts (click "Advanced" > "Go to [Project Name]" to proceed through the security warning).
2. Test the Trigger: Close and reopen your sheet. The prices should populate in D3, E3, and F3 automatically.
3. Fixing "N/A" Values: If you see "N/A", the site’s HTML structure has changed. Right-click the price text on the WalletInvestor page, select "Inspect", and update the regex pattern in the extractPrice function to match the new surrounding HTML. For example, if the price is wrapped in a <div class="price-value"> tag, adjust the regex to target that.

Common Fixes for Issues #

• 403 Forbidden Error: If you get this, update the User-Agent string to match your current browser (search "what's my user agent" online to get a valid one).
• Dynamic Content: If the site loads prices with JavaScript (unlikely for WalletInvestor), the static HTML fetch won’t work. In that case, you’d need to use a cloud-based headless browser tool like Puppeteer, but that requires extra setup outside of Google Apps Script.

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