如何从应用程序脚本使用 Google 表格查询或 Google 可视化 api? [英] How to use Google sheets query or Google visualization api from apps script?

问题描述

我正在使用绑定到 Google Sheet 的 Google Script 以编程方式生成以下查询:

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 Scripts 中是否有任何方法可以解析该查询结果的对象表示?我希望能够编写如下代码:

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 结果 = 新查询(查询字符串)；for(var i = 0; i < results.length; i++) {var 结果 = 结果 [i];//做一点事}

据我所知，除非您使用 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 Script 中使用该功能，您可以利用 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*/
", "") // 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屋！