如何让Google表单检索电子表格数据并将其显示在Google网站上? [英] How to have a Google Form retrieve spreadsheet data and display it on a Google Site?

问题描述

期望的结果:能够在Google表单中输入搜索词(大概但不是必需；可以是标准网页中的表单)，并能够从Google表格中检索相关数据并显示在Google Site网站中应用程序.

我学习了如何从参数化的URL检索数据并在Google网站中显示此问题: 从在线表单中提取数据时，我不知道从哪里开始 而不是网址.也许在提交时，以某种方式读取表单值，创建参数化的URL并转到该页面以显示数据?

解决方案

该示例如何?这是一个非常简单的示例脚本.请根据您的环境进行修改.此示例使用搜索文本在Spreadsheet上检索数据，并显示匹配的行.为了使用此示例，请执行以下操作.

1. 将以下脚本复制并粘贴到脚本编辑器中.
2. 输入用于搜索数据的电子表格ID和工作表名称.
3. 部署Web Apps并运行脚本.
4. 输入搜索文字并按确定"按钮.

脚本:

Google Apps脚本:code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate();
}

function getData(e) {
  var id = "### Spreadsheet ID ###";
  var sheetname = "### Sheet name ###";
  var data = SpreadsheetApp.openById(id).getSheetByName(sheetname).getDataRange().getValues();
  var ar = [];
  data.forEach(function(f) {
    if (~f.indexOf(e.searchtext)) {
      ar.push(f);
    }
  });
  return ar;
}

HTML:index.html

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<form>
<input type="text" name="searchtext">
<input type="button" value="ok" onClick="getData(this.parentNode)" />
</form>
<pre id="disp"></pre>

<script>
function dispData(e) {
  $('#disp').text(JSON.stringify(e));
}
function getData(e) {
  google.script.run.withSuccessHandler(dispData).getData(e);
}
</script>

电子表格示例:

结果:

如果我误解了你的问题，对不起.

Desired Outcome: To be able to enter a search term in a Google Form (presumably but not necessarily; could be a form in a standard web page) and have the relevant data retrieved from a Google Sheet and displayed in Google Site web app.

I learnt how to retrieve data from a parameterized URL and display in a Google Site in this question: How to include data in a URL for a Google Apps Script web app to read?

So the "tech" for retrieving and displaying spreadsheet data is there but I don't know where to start when it comes to pulling the data from a online form rather than a URL. Perhaps on submit, read the form values somehow, create a parameterized URL and go to that page to display the data?

解决方案

How about this sample? This is a very simple sample script. Please modify it to your environment. This sample retrieves data on Spreadsheet using the search text, and displays the matched row. In order to use this sample, please carry out as follows.

1. Copy and paste the following scripts to your script editor.
2. Input spreadsheet ID and sheet name which is used for searching data.
3. Deploy Web Apps and run script.
4. Input search text and push "ok" button.

Script :

Google Apps Script : code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate();
}

function getData(e) {
  var id = "### Spreadsheet ID ###";
  var sheetname = "### Sheet name ###";
  var data = SpreadsheetApp.openById(id).getSheetByName(sheetname).getDataRange().getValues();
  var ar = [];
  data.forEach(function(f) {
    if (~f.indexOf(e.searchtext)) {
      ar.push(f);
    }
  });
  return ar;
}

HTML : index.html

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<form>
<input type="text" name="searchtext">
<input type="button" value="ok" onClick="getData(this.parentNode)" />
</form>
<pre id="disp"></pre>

<script>
function dispData(e) {
  $('#disp').text(JSON.stringify(e));
}
function getData(e) {
  google.script.run.withSuccessHandler(dispData).getData(e);
}
</script>

Sample spreadsheet :

Result :

If I misunderstand your question, I'm sorry.

这篇关于如何让Google表单检索电子表格数据并将其显示在Google网站上?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！