ImportXML not loading due to number of requests

Question

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?

Answer 1

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:

• https://stackoverflow.com/a/48483734/5632629
• https://stackoverflow.com/a/41275946/5632629

Answer 2

First thing to do is to remove the formulas that are unnecessary.

Your spreadsheet has a lot of formulas that are using the GOOGLEFINANCE built-in function that aren't necessary because they are referring cells from column E that are blanks, so they are returnin #N/A.

This will help to reduce the recalculation time of your spreadsheet and could help to reduce the frequency of the issue.

GOOGLEFINANCE and IMPORTXML both make calls to external services. The direct way to reduce the number of calls is to reduce the number of cells that use those functions.

Another approach is instead of doing the job that is done on a cell basis, do that by batch, meaning instead of importing on value at a time import all or the most that you can like a table or column on a single call.

If the source has offers an API and you are able to use it, you could use Google Apps Script or the Google Sheets API to import the values to a Google Sheets Spreadsheet.