How to exceed the 50 importxml limit in Google Sheets

I created a custom import function that overcomes all limits of IMPORTXML I have a sheet using this in about 800 cells and it works great.

It makes use of Google Sheet’s custom scripts (Tools > Script editor…) and searches through content using regex instead of xpath.

1. function importRegex(url, regexInput) { 
2. var output = ''; 
3. var fetchedUrl = UrlFetchApp.fetch(url, {muteHttpExceptions: true}); 
4. if (fetchedUrl) { 
5. var html = fetchedUrl.getContentText(); 
6. if (html.length && regexInput.length) { 
7. output = html.match(new RegExp(regexInput, 'i'))[1]; 
8. } 
9. } 
10. // Grace period to avoid call limit 
11. Utilities.sleep(1000); 
12. return unescapeHTML(output); 
13. } 

You can then use this function like any function.

=importRegex("https://example.com", "

Of course, you can also reference cells.

=importRegex(A2, "

If you don’t want to see HTML entities in the output, you can use this function.

1. var htmlEntities = { 
2. nbsp: ' ', 
3. cent: '¢', 
4. pound: '£', 
5. yen: '¥', 
6. euro: '€', 
7. copy: '©', 
8. reg: '®', 
9. lt: '<', 
10. gt: '>', 
11. mdash: '–', 
12. ndash: '-', 
13. quot: '"', 
14. amp: '&', 
15. apos: '\'' 
16. }; 
17.  
18. function unescapeHTML(str) { 
19. return str.replace(/\&([^;]+);/g, function (entity, entityCode) { 
20. var match; 
21.  
22. if (entityCode in htmlEntities) { 
23. return htmlEntities[entityCode]; 
24. } else if (match = entityCode.match(/^#x([\da-fA-F]+)$/)) { 
25. return String.fromCharCode(parseInt(match[1], 16)); 
26. } else if (match = entityCode.match(/^#(\d+)$/)) { 
27. return String.fromCharCode(~~match[1]); 
28. } else { 
29. return entity; 
30. } 
31. }); 
32. }; 

All together…

1. function importRegex(url, regexInput) { 
2. var output = ''; 
3. var fetchedUrl = UrlFetchApp.fetch(url, {muteHttpExceptions: true}); 
4. if (fetchedUrl) { 
5. var html = fetchedUrl.getContentText(); 
6. if (html.length && regexInput.length) { 
7. output = html.match(new RegExp(regexInput, 'i'))[1]; 
8. } 
9. } 
10. // Grace period to avoid call limit 
11. Utilities.sleep(1000); 
12. return unescapeHTML(output); 
13. } 
14.  
15. var htmlEntities = { 
16. nbsp: ' ', 
17. cent: '¢', 
18. pound: '£', 
19. yen: '¥', 
20. euro: '€', 
21. copy: '©', 
22. reg: '®', 
23. lt: '<', 
24. gt: '>', 
25. mdash: '–', 
26. ndash: '-', 
27. quot: '"', 
28. amp: '&', 
29. apos: '\'' 
30. }; 
31.  
32. function unescapeHTML(str) { 
33. return str.replace(/\&([^;]+);/g, function (entity, entityCode) { 
34. var match; 
35.  
36. if (entityCode in htmlEntities) { 
37. return htmlEntities[entityCode]; 
38. } else if (match = entityCode.match(/^#x([\da-fA-F]+)$/)) { 
39. return String.fromCharCode(parseInt(match[1], 16)); 
40. } else if (match = entityCode.match(/^#(\d+)$/)) { 
41. return String.fromCharCode(~~match[1]); 
42. } else { 
43. return entity; 
44. } 
45. }); 
46. };