根据用户在Google Site(Web App)中的选择更改Google Sheet数据 [英] Change Google Sheet data based on user selection in Google Site (Web App)

查看：134
本文介绍了根据用户在Google Site(Web App)中的选择更改Google Sheet数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在制作一个Google网站，该网站根据来自Google表格的数据显示下拉列表.有用.

下一步，我希望当用户从列表中选择一个下拉选项时，所选的值将写入Google表格单元格中(在下面的示例中，所选值将写入到Sheet中:仪表板，单元格:B92).

例如，假定下拉列表具有来自工作表的以下值:项目1"，项目2"，项目3".

当用户从网站上选择项目1"时，脚本应在Google表格单元格B92中写项目1".同样，如果用户选择项目2"，则脚本将在单元格B92中设置项目2".

我尝试使用下面的代码，但是我认为这有问题:

    在HTML文件中
  • 获取
  • doPost(e)，因为我不确定如何传递参数

(我删除了整个代码，以专注于不正确的部分.如有需要，我可以将其重新添加)

HTML中的

HTML file:

解决方案

I believe your goal as follows.

  • You want to put the selected value at select标签，指向工作表Dashboard中的单元格"B92".
  • 您要使用POST方法将值发送到Web Apps.

为此，这个答案如何?

修改点:

  • 在Google Apps脚本方面，
    • 当您要使用POST方法时，请求正文包含在e.postData.contents中.
    • sh.getRange(92,2).setValues(e.parameter);不正确.在这种情况下，请使用setValue/
    • 在您的doPost中，不会返回任何值.在这种情况下，发送值时会在Javascript端发生错误.
  • 在JavaScript方面，
      返回
    • lastIndex.对于When the user select "Item 1" from the web site, the script should write "Item 1" in Google sheet cell B92. Similarly, if the user select "Item 2", the script will set "Item 2" in cell B92.，需要检索并返回所选值.

当上述修改反映到您的脚本时，它如下所示.

修改后的脚本:

Google Apps脚本方面:

function doPost(e) {
  var value = JSON.parse(e.postData.contents).value;
  var ss = SpreadsheetApp.openById('XXXXXXXX');
  var sh = ss.getSheetByName('Dashboard');
  sh.getRange(92, 2).setValue(value);
  return ContentService.createTextOutput(JSON.stringify({message: "ok"})).setMimeType(ContentService.MimeType.JSON);
}

HTML和Javascript方面:

根据您的问题，我无法理解您的选择.因此，我使用了以下示例选项.请根据您的实际情况替换它.

<select id="sel1">
  <option value="sample1">sample1</option>
  <option value="sample2">sample2</option>
  <option value="sample3">sample3</option>
</select>


<script>
  function listQ() {
    const index = this.selectedIndex;
    if (index > 0) {
      const e = document.getElementById("sel1");
      const value = e.options[index].value;
      const url = "https://script.google.com/macros/s/###/exec";  // Please replace this for your Web Apps.
      fetch(url, {
        method: "POST",
        body: JSON.stringify({index: index, value: value}),
      })
      .then(function (response) {
        return response.json();
      })
      .then(function (data) {
        console.log(data);
      })
    }
  } 

  document.getElementById("sel1").addEventListener("change", listQ);
</script>

  • 在本变形例中，当选项和被选择，该值被发送到Web应用程序.然后，在Web Apps上，将检索到的值放入单元格"B92".

注意:

  • 修改Web应用程序的脚本后，请将其重新部署为新版本.这样，最新脚本将反映到Web Apps.请注意这一点.

参考文献:

I am making a google site which shows a drop down based on data coming from a Google Sheet. It works.

As next step, I want that when the user select a drop down choice from the list, the value which is selected is written in Google Sheet cell (in the example below, the selected value will be written in Sheet: "Dashboard", cell: B92).

For example, assume that the drop down list has the following values coming from the Sheet: "Item 1", "Item 2", "Item 3".

When the user select "Item 1" from the web site, the script should write "Item 1" in Google sheet cell B92. Similarly, if the user select "Item 2", the script will set "Item 2" in cell B92.

I tried with the code below, but I think something is wrong with:

  • fetch in the HTML file
  • doPost(e) because I am not sure how to pass the parameter

(I removed the entire code to focus on the pieces which are incorrect. I can add it back if needed)

function doPost(e) {
          var ss=SpreadsheetApp.openById('XXXXXXXX');
          var sh=ss.getSheetByName('Dashboard');
          sh.getRange(92,2).setValues(e.parameter);

        }

HTML file:

<script type="text/javascript">
      var lastIndex = "";
      const url = "https://script.google.com/a/google.com/macros/s/AKfycbxHX7cthji076OrqfY9ZpGa7jNDxKHUMf_ib7Ekmoo0Ir5DQF1Y/exec";
    function listQ(){
        var e = document.getElementById("sel1");
        if(e.selectedIndex > 0){
                lastIndex = e.selectedIndex;
                console.log(lastIndex);
                fetch(url, {
                    method: "POST"
                    , body: lastIndex
                }).then(function (response) {
                    return response.json()
                }).then(function (data) {
                    console.log(data);            
            })
         }
    }

document.getElementById("sel1").addEventListener("click",listQ);

解决方案

I believe your goal as follows.

  • You want to put the selected value at select tab in HTML to the cell "B92" in the sheet Dashboard.
  • You want to send the value to Web Apps with the POST method.

For this, how about this answer?

Modification points:

  • At Google Apps Script side,
    • When you want to use the POST method, the request body is included in e.postData.contents.
    • sh.getRange(92,2).setValues(e.parameter); is not correct. In this case, please use setValue/
    • In your doPost, no values are returned. In this case, an error occurs at Javascript side when the value is sent.
  • At Javascript side,
    • lastIndex is returned. In the case of When the user select "Item 1" from the web site, the script should write "Item 1" in Google sheet cell B92. Similarly, if the user select "Item 2", the script will set "Item 2" in cell B92., the selected value is required to be retrieved and returned.

When above modification is reflected to your script, it becomes as follows.

Modified script:

Google Apps Script side:

function doPost(e) {
  var value = JSON.parse(e.postData.contents).value;
  var ss = SpreadsheetApp.openById('XXXXXXXX');
  var sh = ss.getSheetByName('Dashboard');
  sh.getRange(92, 2).setValue(value);
  return ContentService.createTextOutput(JSON.stringify({message: "ok"})).setMimeType(ContentService.MimeType.JSON);
}

HTML and Javascript side:

From your question, I cannot understand about your options. So I used a sample options like below. Please replace this for your actual situation.

<select id="sel1">
  <option value="sample1">sample1</option>
  <option value="sample2">sample2</option>
  <option value="sample3">sample3</option>
</select>


<script>
  function listQ() {
    const index = this.selectedIndex;
    if (index > 0) {
      const e = document.getElementById("sel1");
      const value = e.options[index].value;
      const url = "https://script.google.com/macros/s/###/exec";  // Please replace this for your Web Apps.
      fetch(url, {
        method: "POST",
        body: JSON.stringify({index: index, value: value}),
      })
      .then(function (response) {
        return response.json();
      })
      .then(function (data) {
        console.log(data);
      })
    }
  } 

  document.getElementById("sel1").addEventListener("change", listQ);
</script>

  • In this modification, when the options of sample2 and sample3 are selected, the value is sent to Web Apps. And then, at the Web Apps, the retrieved value is put to the cell "B92".

Note:

  • When you modified the script of Web Apps, please redeploy it as new version. By this, the latest script is reflected to Web Apps. Please be careful this.

References:

这篇关于根据用户在Google Site(Web App)中的选择更改Google Sheet数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
前端开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆