按钮操作，使用Google App脚本从电子表格中检索数据 [英] Button Action to retrieve data from spreadsheet using google app script
问题描述
如何基于诸如单击按钮之类的操作的过滤器从电子表格中检索完整的行.
How to retrieve a complete row from a spreadsheet based on a filter on an action such as a click of a button.
我读到GAS是服务器端脚本，要访问电子表格很复杂.
I read that GAS is server-side scripting and it is complex to gain access to a spreadsheet.
是这样.请引导我.
我已经做完了:
$("#form-action")
.button()
.click(function() {
var ss = SpreadsheetApp.openById("");
var sheet = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Test'));
SpreadsheetApp.getActiveSheet().getRange("D1").setFormula('Query(A:C,"SELECT A,B,C WHERE B="' + "mydata'" + ',1)');
SpreadsheetApp.getActiveSheet().getRange("E:J").getValues();
});
推荐答案
完全可以访问电子表格.您必须记住，虽然Google Apps脚本在Google服务器上运行，但是客户端代码(例如，您在UI模板中使用的HTML和JavaScript代码)将被发送到浏览器进行渲染，因此您无法真正将两者混为一谈并在GAS(.gs)文件中编写jQuery代码，反之亦然.
Gaining access to the spreadsheet is not difficult at all. You have to remember that while Google Apps Script runs on Google servers, the client-side code (e.g. HTML and JavaScript code you use in your UI templates) will be sent to your browser for rendering, so you can't really mix the two and write jQuery code in GAS(.gs) files or vice versa.
为澄清起见，类似
var ss = SpreadsheetApp.openById("");
必须保存在.gs文件中.要使用客户端HTML和JavaScript，必须在项目中创建单独的HTML文件(转到文件-新建-HTML文件).这是有关在GAS中提供HTML的更多信息 https://developers.google.com/apps -script/guides/html/
must be kept in .gs files. To use client-side HTML and JavaScript, you must create separate HTML files in your project (go to File - New - HTML file). Here's more information on serving HTML in GAS https://developers.google.com/apps-script/guides/html/
幸运的是，Google提供了API，通过调用"google.script.run"，您可以在客户端和服务器端之间进行通信.后跟".gs"文件中函数的名称.
Luckily, Google provides the API that allows you to communicate between client and server sides by calling 'google.script.run.' followed by the name of the function in '.gs' file.
.gs文件中的示例功能
Example function in '.gs' file
function addRow() {
var sheet = SpreadsheetApp.getActive()
.getSheets()[0];
sheet.appendRow(['Calling', 'server', 'function']);
}
在HTML模板文件中，这是调用此函数的方式
In your HTML template file, here's how you would call this function
<script>
google.script.run.addRow();
</script>
考虑与您的情况更相关的示例.在我的电子表格中，QUERY公式根据用户输入的值动态变化.带有输入字段的表单显示在侧栏中.
Consider the example that is more relevant to your situation. In my spreadsheet, the QUERY formula changes dynamically based on the value entered by the user. The form with input field is displayed in the sidebar.
项目结构
"sidebar.html"的代码如下.注意，必须使用< input>元素的'name'属性.在提交表单时，属性的值('filterBy')将转换为表单对象的属性，我们可以在服务器功能中引用该属性以获取用户输入.
Code for 'sidebar.html' is below. Note that using the 'name' attribute of the <input> element is mandatory. On form submit, the value of the attribute ('filterBy') will be transformed into propetry of the form object that we can reference in our server function to get user input.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js">
</script>
</head>
<body>
<form id="myForm">
<input type="text" name="filterBy">
<input type="submit" value="submit">
</form>
<table id="myTable"></table>
<script>
$('document').ready(function(){
var form = $('#myForm');
var table = $('#myTable');
var runner = google.script.run;
form.on('submit', function(event){
event.preventDefault(); //prevents <form> redirecting to another page on submit
table.empty(); // clear the table
runner.withSuccessHandler(function(array){ //this callback function will be invoked after the 'retriveValues()' function below
for (var i = 0; i < array.length; i++) {
var item = '<tr><td>' + array[i] +'</td></tr>';
table.append(item);
}
})
.retrieveValues(this); //the function that will be called first. Here, 'this' refers to the form element
});
});
</script>
</body>
</html>
.gs"文件中的代码:
Code in '.gs' file:
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheets()[0];
function onOpen() {
var ui = SpreadsheetApp.getUi();
var htmlOutput = HtmlService.createTemplateFromFile('sidebar')
.evaluate();
ui.showSidebar(htmlOutput);
}
function retrieveValues(req) {
var res = [];
var filterBy = req.filterBy; //getting the value of the input field.
sheet.getRange(1, 2, 1, 1)
.setFormula("QUERY(A1:A, \"SELECT A WHERE A > " + filterBy + "\")");
sheet.getRange(1, 2, sheet.getLastRow(), 1)
.getValues()
.map(function(value){
if (value[0] != "") res = res.concat(value[0]); // get only the values that are not empty strings.
});
return res;
}
这是输入值并提交表单的结果.服务器端函数返回值大于5的数组.我们作为参数传递给'withSuccessHandler'的回调函数然后接收该数组并在边栏中填充表.
Here's the result of entering the value and submitting the form. The server-side function returns the array of values greater than 5. The callback function that we passed as parameter to 'withSuccessHandler' then receives this array and populates the table in the sidebar.
最后，我不确定您为什么使用QUERY公式.您只需修改目标范围中的值，然后在GAS中对其进行过滤即可，而无需修改"SELECT"语句.
Finally, I'm not sure why you are using the QUERY formula. Instead of modifying 'SELECT' statement, you could simply take the values from the target range an filter them in GAS.
这篇关于按钮操作，使用Google App脚本从电子表格中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
