如何优化来自同一网站的许多importxml? [英] How to optimize many importxml from the same website?

问题描述

我有一个Google表格，其中行是产品，列是有关这些产品的信息(材料，颜色，价格等).

I have a Google Sheet where rows are products and columns are information about these products (Material, Color, Price, ...).

使用函数IMPORTXML和相应的xpath查询可以检索这些信息.在以下示例中，列D是此产品的URL.这些函数在第3行检索产品的信息(玻璃，颜色和类型):

These information are retrieved using the function IMPORTXML with the corresponding xpath query. In the following example, the column D is the URL of this product. These functions retrieve information (Glass, Color and Type) for the product on row 3:

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

对于每种产品，大约有10列会被检索，因此对于同一URL，有10种不同的功能IMPORTXML.

For each product, there are around 10 columns that are retrieved and thus 10 different functions IMPORTXML for the same URL.

当产品很少时，它正常工作，但是当产品太多时，仅检索到一些信息，而其他单元格保持冻结，其值为正在加载...".

It works properly when there are few products but when there are too many products only some information is retrieved and the other cells stay frozen with the value "Loading...".

我发现的唯一解决方法是手动复制/粘贴检索到的值(Ctrl + C，Ctrl + Maj + v)，然后取消阻止其他一些单元格并开始导入值.最后，我需要对所有单元格执行此操作，这需要很多时间.

The only workaround I found is manually copy/pasting the retrieved values (Ctrl+C, Ctrl+Maj+v) and then some other cells are unblocked and start import the values. In the end, I need to do this for all cells and it takes a lot of time.

1. 为什么我的细胞在正在加载..."中保持冻结状态?Google是否有限制?
2. 我是否应该调整功能以优化流程，并在可能的情况下减少IMPORTXML函数的数量?
3. 您有比我耗时的解决方案更好的解决方案吗?
4. xpath查询始终非常相似.是否可以使用预定义元素的列表.然后使用其他Google Sheet函数获得相同的结果，但仅使用一个IMPORTXML函数

例如:

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

推荐答案

可以通过调整xpath查询并结合使用不同的Google Spreadsheet公式来解决此问题.

This issue can be fixed by adapting the xpath query and using a combination of different Google Spreadsheet formulas.

1. 可以使用字符|
组合多个xpath查询
2. 要仅获取第一个结果，可以在(your_xpath)[1]周围加上xpath查询.
3. TRANSPOSE函数会将多个结果移到同一行

1. It is possible to combine several xpath queries using the character |
2. To only get the first result, you can surround the xpath query by (YOUR_XPATH)[1]
3. The TRANSPOSE function will move the several results on the same row

例如:

=TRANSPOSE(IMPORTXML(D2,"
(//table[@class='info-table']/tr[th/text()[contains(.,'Diameter')]]/td)[1] | 
(//table[@class='info-table']/tr[th/text()[contains(.,'Material')]]/td)[1] | 
(//table[@class='info-table']/tr[th/text()[contains(.,'Glass')]]/td)[1] | 
(//table[@class='info-table']/tr[th/text()[contains(.,'W/R')]]/td)[1] | 
(//table[@class='info-table']/tr[th/text()[contains(.,'Caliber')]]/td)[1] | 
(//table[@class='info-table']/tr[th/text()[contains(.,'Type')]]/td)[1]"))

这样做，每个产品只有一个importXML.

By doing so, there is only one importXML per product.

这篇关于如何优化来自同一网站的许多importxml?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！