In the current stage, it seems that your expected values are included in the HTML data as a JSON object for Javascript. In this case, when the JSON object is retrieved with Google Apps Script, the value can be retrieved. When this is reflected in a sample Google Apps Script, how about the following sample script?
Sample script:
Please copy and paste the following script to the script editor of Google Spreadsheet and save the script. When you use this script, please put a custom function of =SAMPLE("https://sg.finance.yahoo.com/quote/SPY/history?p=SPY") to a cell. By this, the script is run.
function SAMPLE(url) {
const html = UrlFetchApp.fetch(url).getContentText().match(/root.App.main = ([\s\S\w]+?);\n/);
if (!html || html.length == 1) return "No data";
const tempObj = JSON.parse(html[1].trim());
const obj = tempObj.context.dispatcher.stores;
const header = ["date", "amount", "open", "high", "low", "close", "adjclose", "volume"];
return [header, ...obj.HistoricalPriceStore.prices
.map(o => header.map(h => {
if (h == "date") {
return new Date(o[h] * 1000)
} else if (h == "amount" && o[h]) {
return `${o[h]} ${o.type}`;
}
return o[h];
}))];
}
Testing:
When this script is run with =SAMPLE("https://sg.finance.yahoo.com/quote/SPY/history?p=SPY"), the following result is obtained.
Note:
The above script is for a custom function. If you want to use this script with the script editor, you can also the following sample script.
function myFunction() {
const url = "https://sg.finance.yahoo.com/quote/SPY/history?p=SPY"; // This URL is from your question.
const html = UrlFetchApp.fetch(url).getContentText().match(/root.App.main = ([\s\S\w]+?);\n/);
if (!html || html.length == 1) return;
const tempObj = JSON.parse(html[1].trim());
const obj = tempObj.context.dispatcher.stores;
const header = ["date", "amount", "open", "high", "low", "close", "adjclose", "volume"];
const values = [header, ...obj.HistoricalPriceStore.prices
.map(o => header.map(h => {
if (h == "date") {
return new Date(o[h] * 1000)
} else if (h == "amount" && o[h]) {
return `${o[h]} ${o.type}`;
}
return o[h];
}))];
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}
Note:
- If
const obj = tempObj.context.dispatcher.stores is the salted base64 data, please check this answer.
References:
