CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsweb-scrapinggoogle-sheets-formula
dexi
dexi

Reputation: 1

Is there a way to import this table?

I'm looking to do a simple import into Google sheets. My goal is to use an ImportHTML for this table. However, i'm getting a very strange result, and was wondering if anyone had any solutions.

Thanks!

Current Code:

=IMPORTHTML(A1, "table", 0)

Where Cell A1 is simply the link provided above (https://www.recenter.tamu.edu/data/housing-activity/#!/activity/State/Texas)

Upvotes: 0

Views: 616

Answers (1)

Tanaike
Tanaike

Reputation: 201713

Issue and workaround:

Unfortunately, when I saw the HTML, it seems that no table values are included in the HTML and the table is created by Javascript. In this case, IMPORTHTML and IMPORTXML cannot be used. This has already been mentioned in the existing answer. But, fortunately, when I saw the HTML, it seems that there is an endpoint for directly retrieving the values of the table as JSON data. In this answer, as a workaround, the table is retrieved using this endpoint.

Sample script:

In this answer, in order to retrieve the values of the table, Google Apps Script is used. So, please copy and paste the following script to the script editor of Spreadsheet, and save the script.

When you use this script, please put a custom function of =SAMPLE("https://assets.recenter.tamu.edu/WebData/mls/sta/geo_state_id_26.data") to a cell.

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (res.getResponseCode() != 200) return "Values couldn't be retrieved.";
  const header = ["date", "sales", "volume", "averagePrice", "medianPrice", "active", "inventory"];
  const obj = JSON.parse(res.getContentText());
  return [header, ...obj.monthlyData.map(o => header.map(h => h == "date" ? new Date(o[h]) : o[h] || null))];
}

Testing:

When this script is run, the following result is obtained.

enter image description here

Note:

  • This sample script is for your showing URL of https://assets.recenter.tamu.edu/WebData/mls/sta/geo_state_id_26.data. When you change the URL, this script might not be able to be used. Please be careful about this.

  • In the current stage, it seems that this script can be used. But, when the specification of the site and/or the data structure are changed, this might not be able to be used. Please be careful about this.

References:

Upvotes: 2

Related Questions