Google sheets handling async loads

importing JavaScript elements is not supported in google sheets:

I believe your goal is as follows.

• You want to retrieve the table in the site of the URL https://www.cboe.com/delayed_quotes/vix/future_quotes/.

Issue and workaround:

When I saw the HTML data of the URL, it seems that your expected table is not included. But, I notice that the table is created by Javascript using the values retrieved from the API. So, in this answer, I would like to propose directly retrieving your expected values from the API. In this case, the retrieved value is JSON data. So, I would like to propose using a Google Apps Script as a custom function.

Sample script:

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() to a cell. By this, the values are returned.

function SAMPLE() {
  const url = "https://www.cboe.com/us/futures/api/get_quotes_combined/?symbol=AMT1&rootsymbol=null";
  const res = UrlFetchApp.fetch(url);
  const obj = JSON.parse(res.getContentText());
  const header = ["symbol", "expiration", "last_price", "change", "high", "low", "settlement", "volume"];
  return obj.data.map(o => header.map(h => o[h]));
}

Testing:

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

Note:

• This answer is the current answer. When the specification of the site and the API is changed, this script might not be able to be used. So, please be careful about this.

References:

• Custom Functions in Google Sheets
• fetch(url)