Google可视化API(gviz)查询和从电子表格中获取数据 [英] Google visualization API (gviz) query and fetching data from a spreadsheet

问题描述

我正在使用Google Visualization API查询电子表格.如果我将URL粘贴到浏览器中:

I am using the Google Visualization API to query a spreadsheet. If I paste the URL in the browser:

https://docs.google.com/spreadsheets/d/14dIMLkVwHRo-bkvdIhaDwC-rGBHLefftlC6CCCs5YrWSc/gviz/tq?sheet=customers&tq=select+*+Where+A='27938'

A是客户ID"列.我得到此JSON回溯文本: google.visualization.Query.setResponse({"version":"0.6"，"reqId":"0"，"status":"ok"，"sig":"7671558882"，"table":{"cols:[{" id:" A，" label:" ID，" type:" string}，{" id:" B，" label:"名字，.....

A is the customer ID column. I get this JSON looking text back: google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"7671558882","table":{"cols":[{"id":"A","label":"ID","type":"string"},{"id":"B","label":"FirstName",.....

1)我该如何在google脚本功能(而不是从客户端javascript功能)中执行相同的操作?

1) How can I do the same within a google script function (not from the client side javascript function)?

我尝试使用URLFetchApp.fetc():

I tried using the URLFetchApp.fetc():

var url = 'https://docs.google.com/spreadsheets/d/14dIMLkVwHRo-bkvdIhaDwC-rGBHLefftlC6CCCs5YrWSc/gviz/tq?sheet=customers&tq=select+*+Where+A=27938'
var result = URLFetchApp.fetch(url);
var out = JSON.parse(result.getContentText());
Logger.log(out);

记录器显示一长串CSS样式定义和HTML标记.如果我可以获取并解析JSON响应，则打算在该函数中执行一些业务逻辑，以将值返回给客户端.

The logger shows a long list of CSS style definitions and HTML tags. If I can get the JSON response and parse it, I was planning to do some business logic within the function return a value to the client.

推荐答案

我也发现了Google表格/gvis查询的问题-出于未知原因，Google表格返回的不是纯JSON，而是向其中添加了一些s ***，因此在使用前JSON.parse()我们需要将其切掉(或裁剪掉响应的有用部分):

I found problems with google sheets/gvis query too - for unnown reason Google Sheets returns not pure JSON but add some s*** to it, so before use JSON.parse() we need to cut it of (or crop the usefull part of the response):

var httpresponse = UrlFetchApp.fetch(urlrequest, options).getContentText();

var from = httpresponse.indexOf("{");
var to   = httpresponse.lastIndexOf("}")+1;

var jsonText = httpresponse.slice(from, to);

var parsedText = JSON.parse(jsonText);

这篇关于Google可视化API(gviz)查询和从电子表格中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！