使用诺言对Google表格进行多种查询 [英] Using promises for mulitple queries to Google sheets
本文介绍了使用诺言对Google表格进行多种查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我是一名初学者，希望对加快Google表格查询速度有所帮助.我到处走走了很多，找不到解决方法.我正在尝试快速对Google表格运行25个查询，这就是我的尝试方式:
I'm a beginner coder, and would like some help with speeding up my Google sheet queries. I have looked around a lot and cannot find a solution. I'm trying to run 25 queries to a Google sheet quickly, and this is how I'm trying it:
- 对Google表格运行查询.
- 将结果推入数组.
- 运行其他查询.
- 将结果推送到同一数组.
- 运行下一个查询... x25.
- 绘制最终结果表.
问题是，如果我逐一运行查询，则需要25秒才能完成.如果我在for循环中运行quires，结果将在2秒后返回，但异步返回，并且数据全部丢失.也许，我应该使用诺言，或者其他.请你帮忙.
The problem is that if I run the quires one by one they take 25 seconds to complete. If I run the quires in a for loop the results come back in 2 second, but asynchronously, and the data is all out of wack. Perhaps, I should use a promise, or something else. Please can you help.
以下是慢速版本的代码:
Here is the code with the slow version:
var resultData;
var allData = [];
var i = 0;
function startQuery() {
i = 0;
allData.length = 0;
runOnecode();
}
function runOnecode() {
var str = "";
str += $(".google-visualization-controls-rangefilter-thumblabel").text();
var until = str.substring(10);
var from = str.substring(0,10);
var center = $("#centerSelect option:selected").text();
var query = [
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND G =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND H =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND I =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND J =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND K =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND N =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND O =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND P =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND Q =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND R =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND S =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND T =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND U =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND V =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND W =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND X =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND Y =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND Z =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AA =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AB =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AC =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AD =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AE =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AF =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AG =\"No\" ")
];
getData(query[i]);
}
</script>
<script>
function getData(incomingQuery) {
// Prepare the query
var queryString = encodeURIComponent(incomingQuery);
var query = new google.visualization.Query(
//Collect the data from this spreadsheet
'MySpreadSheet URL' + queryString);
query.send(handleSampleDataQueryResponse);
//Handle any errors from the Google server
function handleSampleDataQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' +
response.getDetailedMessage());
return;
}
// get look into the resulting data and find the detail.
var datas = response.getDataTable();
if (datas.Gf.length === 0) {
resultData = 0;
} else resultData = datas.Gf[0].c[0].v;
allData.push(resultData);
// console.log(allData);
if (i < 25) {
runOnecode();
} else {
drawTable();
}
i++;
}
}
</script>
这里是快速返回异步结果的快速版本:
Here is the fast version that returns the results asynchronously:
<script>
function runOnecode() {
var str = "";
str += $(".google-visualization-controls-rangefilter-thumblabel").text();
var until = str.substring(10);
var from = str.substring(0,10);
var center = $("#centerSelect option:selected").text();
var query = [
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND G =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND H =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND I =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND J =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND K =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND N =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND O =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND P =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND Q =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND R =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND S =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND T =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND U =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND V =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND W =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND X =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND Y =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND Z =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AA =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AB =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AC =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AD =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AE =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AF =\"No\" "),
("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A) >=date \'" + from + "\' AND todate(A) <=date \'" + until + "\' AND AG =\"No\" ")
];
for(var i=0; i<25; i++){
getData(query[i]);
// When all are done I will draw the table.
}
}
</script>
<script>
function getData(incomingQuery) {
// Prepare the query
var queryString = encodeURIComponent(incomingQuery);
var query = new google.visualization.Query(
//Collect the data from this spreadsheet
'MySpreadSheet URL' + queryString);
query.send(handleSampleDataQueryResponse);
//Handle any errors from the Google server
function handleSampleDataQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' +
response.getDetailedMessage());
return;
}
// get look into the resulting data and find the detail.
var datas = response.getDataTable();
console.log(datas);
if (datas.Gf.length === 0) {
resultData = 0;
} else resultData = datas.Gf[0].c[0].v;
allData.push(resultData);
console.log(allData);
}
}
</script>
推荐答案
使用诺言，您可以做到
function runOnecode() {
var str = "";
str += $(".google-visualization-controls-rangefilter-thumblabel").text();
var until = str.substring(10);
var from = str.substring(0,10);
var center = $("#centerSelect option:selected").text();
var query = [
// your query data, removed for brevity ...
];
Promise.all(query.map(getData))
.then(function(results) {
// results is an array of results in the same order as query
})
.catch(function(err) {
// err is FIRST error - no other information about success/faill of other queries will be available
});
}
function getData(incomingQuery) {
return new Promise(function(fulfill, reject) {
// Prepare the query
var queryString = encodeURIComponent(incomingQuery);
var query = new google.visualization.Query(
//Collect the data from this spreadsheet
'MySpreadSheet URL' + queryString);
query.send(handleSampleDataQueryResponse);
//Handle any errors from the Google server
function handleSampleDataQueryResponse(response) {
if (response.isError()) {
throw('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
}
// get look into the resulting data and find the detail.
var datas = response.getDataTable();
console.log(datas);
if (datas.Gf.length === 0) {
resultData = 0;
} else {
resultData = datas.Gf[0].c[0].v;
}
fulfill(resultData);
}
});
}
如果需要获取所有查询成功/失败的状态，则可以使用类似Q Promise的东西，该方法具有allSettled方法(而不是Promise.all)，该方法将返回所有Promise的结果，而不管成功/失败
if you need to get the state of all queries success/failure, you could use something like Q promises which has a allSettled method (instead of Promise.all), which returns the result of all promises regardless of success/failure
或者，这是我自己翻滚的人
Or, here's one I rolled myself
Promise.allSettled = function(arr) {
return Promise.all([].map.call(arr,
function(v) {
return Promise.resolve(v).then(
function fulfilled (x) { return {fulfilled: true, value: x}; },
function rejected (e) { return {fulfilled: false, value: e}; }
);
})
);
};
然后
runOnecode看起来像
function runOnecode() {
var str = "";
str += $(".google-visualization-controls-rangefilter-thumblabel").text();
var until = str.substring(10);
var from = str.substring(0,10);
var center = $("#centerSelect option:selected").text();
var query = [
// your query data, removed for brevity ...
];
Promise.allSettled(query.map(getData))
.then(function(results) {
// results is an array of results in the same order as query
results.forEach(function(result) {
if (result.fulfilled) {
// successful
console.log(result.value);
}
else {
// unsuccessful
console.log(result.value); // will show the thrown error
}
});
});
}
这篇关于使用诺言对Google表格进行多种查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
