Google-sheets – ImportXML not loading due to number of requests

formulasgoogle sheetsgoogle-apps-scriptimportxmlxpath

I am working on this sheet and it has a lot of ImportXML requests between columns N-Q.
And because of the high number of requests, it seems like the ImportXML function is not working properly (it keeps loading forever on the majority of cells).

So as a beginner I was wondering if is there any way to import XML data from different URLs (based on data in column E) without making so many requests? What's the best approach to solving this problem since I really need to import a lot of data from different URLs?

Best Answer

The easiest solution would be to divide your formulas in 16 spreadsheets each with 50 formulas and then just use IMPORTRANGE 16 times in your Master Spreadsheet.

Also, no need to calculate the "nothing" (eg. cells like E420). Use IF function to check for value first:

=IF(LEN(E2); 
 IMPORTXML(CONCATENATE("https://br.financas.yahoo.com/quote/"; E2; ".SA/financials"); $A$3); )

Or you can try one of the scripted solutions like:

Related Topic