0

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)

Tanaike
  • 181,128
  • 11
  • 97
  • 165
dexi
  • 1
  • 1
  • 1
    What strange result are you getting? – CodeCamper Oct 27 '22 at 21:02
  • MLS Area {{data.thisMonth}} {{data.lastMonth}} {{data.lastYear}} "{{data.thisMonth}}- {{data.lastYear}} % Chg" Year-to-Date "Map Image" {{data.year}} "% Chg Year Ago" {{row.name}} {{row[selected.trend.key].now | number}} {{row[selected.trend.key].lastMonth | number}} {{row[selected.trend.key].yearAgo | number}} {{row[selected.trend.key].changeYOY | number : 1}} {{row[selected.trend.key].totalYTD | number}} {{row[selected.trend.key].changeYTD | number : 1}} Link – dexi Oct 27 '22 at 21:53
  • Check the source code for any json/api calls. If you find any, use `importjson` – TheMaster Oct 27 '22 at 22:59

1 Answers1

2

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 1
    @dexi About `This was perfect, thank you!`, welcome. Thank you for letting me know. I'm glad your issue was resolved. If your question was solved, please push an accept button. Other people who have the same issue with you can also base your question as a question that can be solved. And I think that your issue and solution will be useful for them. If you don't find the button, feel free to tell me. https://stackoverflow.com/help/accepted-answer – Tanaike Oct 28 '22 at 22:22