为表v4 API版本更新脚本时出现问题

Question

我正在尝试更新这个脚本的表v4 API版本，但我没有成功…

jQuery(document).ready(function() {
setTimeout(function() {
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);
}
});
}, 1000);

})

可以帮我一下吗?

提前感谢!

Answer 1

https://spreadsheets.google.com/feeds/list/###/od6/public/values?alt=json的URL是针对Sheets API v3的。当使用Sheets API v4时，需要使用API密钥，如https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}?key=[YOUR_API_KEY]。

但是，幸运的是，当我看到你的电子表格时，它似乎是作为Web发布发布的。我想在你的情况下，这可能是使用不使用API密钥。在这个回答中，我想提出这个方法。在此方法中，可以通过查询语言检索值。当您的脚本被修改后，它变成如下所示:

修改脚本:

:

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);
}
});

:

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);
});
});

• 在此修改中，请加载<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>用于解析CSV数据到数组。
• 我认为https://docs.google.com/spreadsheets/d/1nt-5-zruTuJPOAfRbCgy9sJA2CCVvzo0sCPEPx0uxEk/pub?output=csv的URL也可以代替https://docs.google.com/spreadsheets/d/1nt-5-zruTuJPOAfRbCgy9sJA2CCVvzo0sCPEPx0uxEk/pub?output=csv。

引用:

• 方法:spreadsheets.values.get
查询语言