Trouble with ImportXML on Google Sheets

Question

I have filled a google spreadsheet with around 500 URLs and Xpaths. After discovering that ImportXML has some drawbacks (it is getting perpetual loading errors, even when there are only 10 or so functions running). I am looking for another way to populate the sheet. My first attempt was an iterative script that simply wrote an ImportXML function into a working cell then wrote in the value for each URL. I thought that by just having one ImportXML running at a time it would work fine but it still gets perpetual loading errors.

Sample sheet: https://docs.google.com/spreadsheets/d/1QgW4LVkB_oraO9gdS5DsnNta3GVlqsH0_uC1QP0iE7w/edit?usp=sharing

(note the sample sheet actually works OK with the iterative ImportXML script, still returns some errors, but I think there must be some limit on historical ImportXML functions not just current ones on sheet because my main sheet has real problems handling just a few now)

Is there a simple script that will work? I have tried variations using URLFetch, xml.evaluate, xmlService, but with my limited knowledge I can't get it to work.

Any guidance much appreciated. Thanks!

Answer 1

Here's a working method - I tested for you :

add this function in above the function you currently have in your apps script.

function importprice(url) {
  var found, html, content = '';
  var response = UrlFetchApp.fetch(url);
  if (response) {
    html = response.getContentText();
    if (html) content = html.match(/<span id="product_price" itemprop="price">(.*)<\/span>/gi)[0].match(/<span id="product_price" itemprop="price">(.*)<\/span>/i)[1];
  }
  return content;
}

and then replace your importxml function that currently looks like this:

 var cellFunction1 = '=IMPORTXML("' + sheet.getRange(row,4).getValue() + '?' + queryString + '","' + sheet.getRange(row,5).getValue() + '")';

with this:

var cellFunction1 = importprice(sheet.getRange(row,4).getValue());