The URL of https://spreadsheets.google.com/feeds/list/###/od6/public/values?alt=json is for Sheets API v3. When the Sheets API v4 is used, an API key is required to be used like https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}?key=[YOUR_API_KEY].
But, fortunately, when I saw your Spreadsheet, it seems that the Spreadsheet is published as the Web publish. I thought that in your situation, this might be used without using the API key. In this answer, I would like to propose this method. In this method, the values can be retrieved by the query language. When your script is modified, it becomes as follows.
Modified script:
From:
jQuery.getJSON(
//"https://spreadsheets.google.com/feeds/list/1iebtJB4FxhDVztxydXT85SNpgQNhOgJEp_F1qpTSkMI/od6/public/values?alt=json",
"https://sheets.googleapis.com/v4/spreadsheets/1nt-5-zruTuJPOAfRbCgy9sJA2CCVvzo0sCPEPx0uxEk/values/Section-A/?alt=json&key=AIzaSyCJuQ_bsRn1SVyfI8rkeq0RUnSpecP7IYk",
function(data) {
var sheetData = data.feed.entry;
var i;
for (i = 0; i < sheetData.length; i++) {
var perc = data.feed.entry[i]['gsx$changepct']['$t'];
var cap = data.feed.entry[i]['gsx$marketcap']['$t'];
var date = data.feed.entry[i]['gsx$tradetime']['$t'];
var price = data.feed.entry[i]['gsx$price']['$t'];
document.getElementById('perc').innerHTML += (perc);
document.getElementById('capitalizzazione').innerHTML += (cap);
document.getElementById('dataTrade').innerHTML += (date);
document.getElementById('price').innerHTML += (price);
}
});
To:
jQuery.ajax({
type: "GET",
url: "https://docs.google.com/spreadsheets/d/1nt-5-zruTuJPOAfRbCgy9sJA2CCVvzo0sCPEPx0uxEk/pub?output=csv"
}).done(csv => {
let [header, ...values] = Papa.parse(csv).data;
values = values.map(r => r.map(c => {
const t = c.trim().replace(",", ".");
return isNaN(t) ? t : Number(t);
}));
const obj = values.map(r => r.reduce((o, c, j) => Object.assign(o, {[header[j] && header[j].trim()]: c}), {}));
obj.forEach(data => {
var perc = data['changepct'];
var cap = data['marketcap'];
var date = data['tradetime'];
var price = data['price'];
document.getElementById('perc').innerHTML += (perc);
document.getElementById('capitalizzazione').innerHTML += (cap);
document.getElementById('dataTrade').innerHTML += (date);
document.getElementById('price').innerHTML += (price);
});
});
- In this modification, please load
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script> for parsing the CSV data to an array.
- I think that the URL of
https://docs.google.com/spreadsheets/d/1nt-5-zruTuJPOAfRbCgy9sJA2CCVvzo0sCPEPx0uxEk/pub?output=csv can be also used instead of https://docs.google.com/spreadsheets/d/1nt-5-zruTuJPOAfRbCgy9sJA2CCVvzo0sCPEPx0uxEk/pub?output=csv.
References:
