更新/编辑html输入中的Google表格行值 [英] Update/edit google sheets row values from html input

问题描述

我有一个html表单，其中插入了客户的数据，并将带有值的行附加到了Google表格上.

I have an html form where the client's data is inserted in and it appends row with the values on to a google sheet.

表单中有一个字段，用于在搜索特定值(标识号)时搜索并返回客户数据.

In the form, there's a field that searches and returns the clients data when searching for a specific value (id number).

function getID(IDsearch){
  var ws = SpreadsheetApp.getActiveSheet();
  var data = ws.getRange(3, 1, ws.getLastRow(), 36).getValues();

  var dataInput = data.map(function(r){return r[7];});  
  var position = dataInput.indexOf(IDsearch);
  var dataArray = ws.getRange(position+3, 1, 1, 36).getValues();

  if(position > -1){
    return dataArray;
  } else {
    return position;
  }

} 

运行此命令后，将使用该行中的数据填充表单中的所有输入字段. 我需要编辑表单中的值，提交时应覆盖/更新具有该ID号的现有行.

After this runs, all the input fields in the form are populated with the data from that row. I need to edit the values in the form and when submit it should overwrite/update the existing row with that id number.

在Google表格文档中，我找到了电子表格sheets.values.update方法，但我无法弄清楚.我在这方面还很陌生，任何帮助将不胜感激.

In google sheets documentation, I've found the spreadsheets.values.update method, but I cannot figure this out. I'm pretty new in this and any help would be appreciated.

谢谢大家！

推荐答案

• 您要实现以下流程.
  • You want to achieve the following flow.
    1. 在id="insertID"中输入"ID"，然后单击按ID搜索".
    2. 通过搜索"ID"显示电子表格中的值.
    3. 编辑id="name"和id="ID"的值.
    4. 单击保存数据"时，您要更新电子表格上的值.
    1. Input "ID" to id="insertID" and click "Search by ID".
    2. Show the values from Spreadsheet by searching "ID".
    3. Edit the values of id="name" and id="ID".
    4. When "Save data" is clicked, you want to update the values on the Spreadsheet.

  从您的回复，共享的电子表格和脚本中，我可以像上面那样理解.如果我的理解是正确的，那么修改之后又如何呢?请认为这只是几个可能的答案之一.

  From your replying, shared Spreadsheet and script, I could understand like above. If my understanding is correct, how about ths following modification? Please think of this as just one of several possible answers.

  • 对于您而言，需要修改Google Apps脚本一侧的processForm.
    • 使用formObject搜索该行并覆盖单元格的值.
    • In your case, processForm at Google Apps Script side is required to be modified.
      • Search the row using formObject and overwrite the values of cells.

      修改脚本后，请按以下说明在Google Apps脚本一侧修改processForm.我从网址中删除了电子表格ID.因此，请在测试脚本之前进行设置.

      When your script is modified, please modify processForm at Google Apps Script side as follows. I remove the Spreadsheet ID from the URL. So please set it, before you test the script.

      function processForm(formObject) {
        var url = "https://docs.google.com/spreadsheets/d/###/edit#gid=0";
        var ss = SpreadsheetApp.openByUrl(url);
        var ws = ss.getSheetByName("Database");
      
        // I added and modified below script.
        var ranges = ws.getRange(4, 2, ws.getLastRow() - 3, 1).createTextFinder(formObject.ID).findAll();
        if (ranges.length > 0) {
          for (var i = 0; i < ranges.length; i++) {
            ranges[i].offset(0, -1, 1, 2).setValues([[formObject.name, formObject.ID]]);
          }
        } else {
          ws.appendRow([formObject.name, formObject.ID]);
        }
      }
      

      • 在此修改中，当存在相同的ID时，相同ID的所有行都将被覆盖.例如，如果要修改第一个，请修改为ranges[0].offset(0, -1, 1, 2).setValues([[formObject.name, formObject.ID]]);.
      • In this modification, when the same IDs are existing, all rows of the same IDs are overwritten. For example, if you want to modify the 1st one, please modify to ranges[0].offset(0, -1, 1, 2).setValues([[formObject.name, formObject.ID]]);.
      • Class TextFinder

      这篇关于更新/编辑html输入中的Google表格行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！