如何优化Google Sheets中同一网站的多IMPORTXML调用？

Great question—let's break this down step by step, since I've run into exactly this issue with Google Sheets and IMPORTXML before.

1. Why do some cells stay stuck on "Loading..."? Is there a Google limit? #

Absolutely, this is almost always tied to Google Sheets' external function quotas and rate limits. Here's what's happening:

• Each IMPORTXML call counts as a separate external request. When you run 10 calls per row across dozens/hundreds of rows, you're hitting Google's limits for how many external requests can be processed at once or within a certain timeframe.
• Google caps the number of external function calls (like IMPORTXML, IMPORTHTML) per sheet, per user, per day. Exceeding these limits means some requests get queued indefinitely or dropped, leaving you with "Loading..." cells.
• On top of that, repeated calls to the same URL can also trigger anti-scraping measures on the target website, which might block or throttle requests, adding to the loading issues.

2. Should I adjust the function to reduce IMPORTXML calls? #

100% yes—reducing the number of IMPORTXML calls is the single most effective way to fix the loading problem and improve performance. Every time you call IMPORTXML on the same URL, you're re-downloading the entire page, which is a massive waste of resources and a surefire way to hit limits fast. Combining calls will cut down on redundant requests drastically.

3. Are there more efficient solutions than manual copy-pasting? #

Definitely—here are a few practical options:

• Google Apps Script: Write a simple script that fetches the page once per URL, parses all the needed data with XPath (or DOM methods), and writes the results directly to the sheet. This way, you only make one request per URL, and you avoid hitting the IMPORTXML limits entirely.
• Batch import with array formulas: Once you've combined your XPath queries into a single IMPORTXML call (see question 4), use array formulas like INDEX or QUERY to spread the results across columns without extra calls.
• Cache results: Use Google Sheets' built-in caching (though it's limited) or a script to store fetched data in a hidden sheet, so you don't re-scrape the same URL every time you open the sheet.

4. Can I use a single IMPORTXML call with a predefined list of elements? #

Yes! You can modify your XPath to match multiple values in one go using the or operator, then split the results into columns. Here's how:

First, update your XPath to target all the fields you need at once:

//table[@class='info-table']/tr[th/text()[contains(.,'Glass') or contains(.,'Color') or contains(.,'Type')]]/td

Then, use this in a single IMPORTXML call:

=IMPORTXML(D3,"//table[@class='info-table']/tr[th/text()[contains(.,'Glass') or contains(.,'Color') or contains(.,'Type')]]/td")

This will return a vertical array of results (one per field). To split them into separate columns, use INDEX to pull each value:

• For Glass: =INDEX(IMPORTXML(D3,"your-combined-xpath"),1)
• For Color: =INDEX(IMPORTXML(D3,"your-combined-xpath"),2)
• For Type: =INDEX(IMPORTXML(D3,"your-combined-xpath"),3)

If you want to make it even cleaner, define your field list in a range (say, G1:I1 with "Glass", "Color", "Type") and use an array formula to generate all the INDEX calls automatically. For example, in cell E3 (assuming your first field column is E):

=ARRAYFORMULA(INDEX(IMPORTXML(D3,"//table[@class='info-table']/tr[th/text()[contains(.,'"&JOIN("') or contains(.,'",G1:I1)&"')]]/td"),ROW(G1:I1)))

This way, you only have one IMPORTXML call per row, and you can easily add/remove fields by updating the G1:I1 range.

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