Google-sheets – Is it possible to use IMPORTHTML to return only certain cells

google sheetsimporthtmlworksheet-function

I'm currently using IMPORTHTML to import tables from a website that have five columns and three rows. But, I only need three cells from each of these tables. Is there a way to import just these three cells into my spreadsheet, rather than the entire table?

I could use INDEX(IMPORTHTML, but due to the number of IMPORHTML functions my spreadsheet is using, I fear it would slow it down even further than it already is if I triple that amount.

Best Answer

Pretty much possible:

=INDEX(IMPORTHTML("https://geizhals.de/?fs="&A1&"&in=";"table";0);2;3)

This one shall search for a phrase located in cell A1 and it will return data from a table 0, but only from a row 2 of that table and from a cell 3 of that row. In your case:

=INDEX(IMPORTHTML("siteurl";"TABLE";number);rownumber;cellnumber)
_________________________________________________________________

=INDEX(IMPORTHTML("siteurl";"TABLE";number);2;4)
=INDEX(IMPORTHTML("siteurl";"TABLE";number);2;5)
=INDEX(IMPORTHTML("siteurl";"TABLE";number);3;1)

If you are afraid of slowdown then import it into a new spreadsheet and then use IMPORTRANGE in the final spreadsheet to import it from "Google side".

Related Solutions

Google-sheets – Can a Google Spreadsheet be recalculated if it’s not manually opened

If you are using the new (Q2 2014) spreadsheets you can try the Recalculation option found under [File] » [Spreadsheet Settings ...]

I believe it will work in your case. It depends what you mean by "on an alternate page". If you mean a 2nd sheet in the same spreadsheet you may be in luck. If you mean an alternate workbook, I think you lose.

In our case, we have several users with workbooks having ImportRange() calls to a master workbook, which is updated by Google Apps Script activity. The new Recalculation feature does NOT cause the remote users' workbooks to pull from the master the way a manual edit would.

Google Sheets – Using IMPORTXML Function to Download Multiple Columns

It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service.

Example:

Adaptation of the code taken from the answer by Vidar S. Ramdal

function parseFeedXml(url) {
   var result = [];
   var xml = UrlFetchApp.fetch(url).getContentText();
   var document = XmlService.parse(xml);
   var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
   var feed = document.getRootElement();
   var entry = feed.getChildren('entry',atom);
   for (var i = 0; i < entry.length; i++) { 
     var content = entry[i].getChildren('content',atom);
     var contentElement = '';
     var resultRow = [];
     for (var j = 0; j < content.length; j++) { 
       contentElement = content[j];
       evaluateText(contentElement.getChild('filing-date',atom), resultRow);
       evaluateText(contentElement.getChild('filing-type',atom), resultRow);
       evaluateText(contentElement.getChild('filing-href',atom), resultRow);
       evaluateText(contentElement.getChild('form-name',atom), resultRow);
       evaluateText(contentElement.getChild('size',atom), resultRow);
       evaluateText(contentElement.getChild('amend',atom), resultRow);
       result.push(resultRow);                
     }
   }
   return result;
}

function evaluateText(element, resultRow) {
  if (element) {
    resultRow.push(element.getText());
  } else {
    resultRow.push('');
  }
}

Best Answer

Related Solutions

Google-sheets – Can a Google Spreadsheet be recalculated if it’s not manually opened

Google Sheets – Using IMPORTXML Function to Download Multiple Columns

Related Topic