VLOOKUP vs IMPORTHTML

Question

I am having a Google Apps Script issue using VLOOKUP on some data generated by the IMPORTHTML function.

For normal user interactions this does not seem to be an issue but scripts accessing the VLOOKUP cell occasionally return #N/A even though you can plainly see the data in the cell and in the VLOOKUP data. This seems to occur after using the sheet for more than about 20 minutes. I'd say it happens about 20% of the time.

Forcing the IMPORTHTML to refresh has about a 50-50 chance of fixing the issue and sometimes you need to do it twice. When that does not work we close and reopen the sheet.

I am pulling the data from a very simple "theSheet.getRange(theRow,10).getDisplayValue()" script call and am sure that theRow is properly set to a real row.

Has anyone else seen this and can you advise on a possible solution?

The [RawData] tab is hidden with the data being derived from "IMPORTHTML("http://www.foobar.org/Ajax.php?Act=7", "table", 2)" with a named range of CustData and ends up looking like:

The vlookup is simply "=vlookup(A7,RawData!K:L,2,true)" with A7 being a Data Validation dropdown.

The code that is getting the #N/A is the CustomerSSID: attribute in the below snippet that is called from an OnEdit trigger:

var theData =
{
  DSAmClean: PropertiesService.getUserProperties().getProperty('DSAmClean'),
  ServerIP: theSheet.getRange('ServerIP').getDisplayValue(),
  ServerPort: theSheet.getRange('ServerPort').getDisplayValue(),
  SheetName: theSheet.getName(),
  Row: theRow, 
  Col: theCol,
  Notes: theNotes,
  colTitle: theSheet.getRange(2,theCol).getValue(),
  cellValue: theSheet.getRange(theRow,theCol).getDisplayValue(),
  DeliveryDate: theSheet.getRange(theRow,1).getDisplayValue(),
  CustomerName: theSheet.getRange(theRow,2).getDisplayValue(),
  CustomerSSID: theSheet.getRange(theRow,3).getValue(),
  ProdSSID: theSheet.getRange(theRow,4).getDisplayValue(),
  ProductShipped: theSheet.getRange(theRow,5).getDisplayValue().toString(),
  ContractSales: theSheet.getRange(theRow,6).getDisplayValue(),
  OriginPoint: theSheet.getRange(theRow,7).getDisplayValue(),
  VendorName: theSheet.getRange(theRow,8).getDisplayValue(),
  VendorSSID: getSSIDFromVendorName(theSheet.getRange(theRow,8).getDisplayValue()),
  Margin: theSheet.getRange(theRow,9).getValue(),
  FOBPrice: theSheet.getRange(theRow,10).getValue(),
  PurchaseContract: theSheet.getRange(theRow,11).getDisplayValue(),
  LoadNumber: theSheet.getRange(theRow,12).getDisplayValue(),
  TruckingCompany: theSheet.getRange(theRow,13).getDisplayValue(),
  TruckingRate: theSheet.getRange(theRow,14).getValue(),
  ShippingWeight: theSheet.getRange(theRow,15).getValue(),
  FreightTotal: theSheet.getRange(theRow,16).getValue()
}

Of course column 3 contains the Vlookup.

Answer 1

I think that the most reliable approach in cases like yours is to avoid using functions like IMPORTHTML, IMPORTRANGE, etc. because they don't refresh on every recalculation or to force the "refresh" of the corresponding formulas before the line code that depends on th results of such formulas.