我可以使用Google Visualization API在apps脚本中查询电子表格吗？ [英] Can I use Google Visualization API to query a spreadsheet in apps script?

问题描述

我有一个包含大约3000行数据的Google Spreadsheet（大约30列）。数据以标准方式布置，列标题和每个数据项一行。这些数据代表了各种学术课程中使用的案例研究和文章，并且数据集一直在不断增长。

我需要根据指定的参数搜索/查询数据。例如，我想运行这个查询：select * where course_no = 123 and instructor = Smith and year = 2012.我想获得一系列数据作为结果，然后我可以简单地显示为raw新工作表中的数据，或使用GAS操纵某种报告，或者只是将原始数据发送给收件人。

它看起来像Google Visualization API提供了很好的查询选项（我正在查看google.visualization.Query类的setQuery方法），但它有可能使用它而不将其附加到图表或可视化？我可以在脚本中调用它，然后管理它自己返回的范围吗？当我尝试这样做时，我在这一行收到一个错误ReferenceError：google is not defined：

  var query = new google .visualization.Query（my_data_source_url）; 
  

我也不确定在上面的代码行中使用电子表格url作为data_source_url是正确的做法，虽然这就是我想要的。

我正在尝试这个，因为我假设使用这种查询方法比逐个搜索单元格（如此处所示如何搜索Google Spreadsheets？），但我很欣赏所有的反馈。谢谢！ Carrie

解决方案

不幸的是，目前无法直接将Google Visualization API加载到Google Apps脚本项目中。不过，您的确有几个选择：

1. 使用Google Apps脚本（ScriptDb）中的noSQL存储构建 rel =nofollow>将电子表格读入数据库之前查询回复。




2. 忘记查询Apps Script中的数据，并在表单中使用 QUERY公式使用与 Google可视化API查询语言相同的语法




3. 如果使用旧表格，您可以使用文件>发布到网页，然后使用网址提取服务并通过设置查询数据源网址< - 这种方式的问题是，您可能需要做大量的工作来处理返回的数据，可能是html，csv或json。此简单工具可帮助您构建数据源网址。

就个人而言，根据场景，我会尝试＃2，但如果不符合＃1

I have a Google Spreadsheet with about 3000 rows of data (and about 30 columns). The data is laid out in standard fashion with column headers and one row per data item. The data represent case studies and articles used in various academic courses, and the dataset is always growing.

I need to search/query the data based on specified parameters. For example, I'd like to run this query : select * where course_no = 123 and instructor = Smith and year = 2012. I'd like to get a range of data as the result, which I can then either simply display as raw data on a new Sheet, or manipulate into some kind of report using GAS, or perhaps just email raw data to a recipient.

It looks like the Google Visualization API offers nice query options (I'm looking at the setQuery method of the google.visualization.Query class), but is it possible to use this without attaching it to a Chart or visualization? Can I just call it in script and then manage the range it returns myself? When I try this, I get an error "ReferenceError: google is not defined" at this line :

var query = new google.visualization.Query(my_data_source_url);

I'm also not sure that using the spreadsheet url as the data_source_url in the line of code above is the right thing to do, though that's what I'm trying.

I'm trying this because I'm assuming that using this sort of query approach is more efficient than searching through the cells one by one (like is suggested here How do I search Google Spreadsheets?) but I appreciate all feedback. Thank you! Carrie

解决方案

Unfortunately it's not currently possible to directly load Google Visualisation API in a Google Apps Script project. You do have a couple of options though:

1. Use the noSQL storage build into Google Apps Script (ScriptDb) to read the spreadsheet into a database before querying the response.

2. Forget querying the data in Apps Script and use the QUERY formula in a sheet (this uses the same syntax as the Google Visualization API Query Language

3. If using old Sheets you can File > Publish to the web and then query the data in your script using the URL Fetch Service and calling the data by Setting the Query in the Data Source URL <- the problem with this way is you've potentially got a lot of work to process the returned data which can be html, csv or json. This simple tool helps you build the datasource url.

Personally, depending on the scenario, I'd try #2 but failing that go with #1

这篇关于我可以使用Google Visualization API在apps脚本中查询电子表格吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！