如何通过应用脚本使用Google表格查询或Google可视化api? [英] How to use Google sheets query or Google visualization api from apps script?
问题描述
我正在使用绑定到Google表格的Google脚本以编程方式生成以下查询:
I am using a Google Script bound to a Google Sheet to programatically generate the following query:
=query('16 Jul - 20 Jul Responses'!A1:I31, "SELECT C WHERE D = 'Available'", 0)
Google脚本中是否可以解析该查询结果的对象表示形式?我希望能够编写如下代码:
Is there any way in Google Scripts to parse an object representation of that query's results? I'd like to be able to code something like:
var queryString = '=query('16 Jul - 20 Jul Responses'!A1:I31, "SELECT C WHERE D = 'Available'", 0)';
var results = new Query(queryString);
for(var i = 0; i < results.length; i++) {
var result = results[i];
// do something
}
var queryString = '=query('16 Jul - 20 Jul Responses'!A1:I31, "SELECT C WHERE D = 'Available'", 0)';
var results = new Query(queryString);
for(var i = 0; i < results.length; i++) {
var result = results[i];
// do something
}
据我所知,除非您使用的是Google Web App,否则Query对象是不存在的.这是真的?还是有办法实现这个想法?
As far as I can tell, the Query object doesn't exist unless you are working with a Google Web App. Is this true? Or is there a way to implement this idea?
推荐答案
= QUERY 是电子表格功能.如果您想在Apps脚本中使用该功能,则可以使用 Google的可视化API和查询语言.我在GAS中为此编写了一个自定义模块.这是摘录:
=QUERY is a spreadsheet function. If you want that functionality in Apps Script you can leverage Google's Visualization API and Query Language. I wrote a custom module for it in GAS. Here's an extract:
(function(context) {
const Utils = (context.Utils || (context.Utils = {}));
/**
* Queries a spreadsheet using Google Visualization API's Datasoure Url.
*
* @param {String} ssId Spreadsheet ID.
* @param {String} query Query string.
* @param {String|Number} sheetId Sheet Id (gid if number, name if string). [OPTIONAL]
* @param {String} range Range [OPTIONAL]
* @param {Number} headers Header rows. [OPTIONAL]
*/
Utils.gvizQuery = function(ssId, query, sheetId, range, headers) {
var response = JSON.parse( UrlFetchApp
.fetch(
Utilities.formatString(
"https://docs.google.com/spreadsheets/d/%s/gviz/tq?tq=%s%s%s%s",
ssId,
encodeURIComponent(query),
(typeof sheetId === "number") ? "&gid=" + sheetId :
(typeof sheetId === "string") ? "&sheet=" + sheetId :
"",
(typeof range === "string") ? "&range=" + range :
"",
"&headers=" + ((typeof headers === "number" && headers > 0) ? headers : "0")
),
{
"headers":{
"Authorization":"Bearer " + ScriptApp.getOAuthToken()
}
}
)
.getContentText()
.replace("/*O_o*/\n", "") // remove JSONP wrapper
.replace(/(google\.visualization\.Query\.setResponse\()|(\);)/gm, "") // remove JSONP wrapper
),
table = response.table,
rows;
if (typeof headers === "number") {
rows = table.rows.map(function(row) {
return table.cols.reduce(
function(acc, col, colIndex) {
acc[col.label] = row.c[colIndex] && row.c[colIndex].v;
return acc;
},
{}
);
});
} else {
rows = table.rows.map(function(row) {
return row.c.reduce(
function(acc, col) {
acc.push(col && col.v);
return acc;
},
[]
);
});
}
return rows;
};
Object.freeze(Utils);
})(this);
只需在您的GAS编辑器中将该模块放入其自己的文件中,即可按以下方式调用它:
Just drop that module into its own file in your GAS editor then you can call it as follows:
// result is an array of objects if header row is specified, otherwise it is an array of arrays
var result = Utils.gvizQuery(
"<YOUR_SPREADSHEET_ID>",
"<YOUR_QUERY_STRING>",
<SHEET_ID_IF_NEEDED>, // can be a number (the sheetId), or the name of the sheet; if not needed, but headers are, pass in undefined
<RANGE>, // specify range, ex: `A2:O`
<HEADER_ROW_INDEX_IF_NEEDED> // always a number
);
这篇关于如何通过应用脚本使用Google表格查询或Google可视化api?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!