将HTML表传递给Google Spreadsheet.从动态表中获取单元格值 [英] Pass HTML Table to Google Spreadsheet. Get Cell Values out of a Dynamic Table

问题描述

我的表格中的表格如下所示:

I have a table within a form that looks like this:

<form>
<table border="1" id="tblSample">
  <tr>
    <th>Col_one</th>
    <th>Col_two</th>
    <th>Col_three</th>
  </tr>
  <tr>
    <td><input type="text" name="txtRowa1"
     id="txtRowa1" size="5" /></td>
    <td>
    <input type="text" name="txtRowb1"
     id="txtRowb1" size="15" />    </td>
    <td>
    <input type="text" name="txtRowc1"
     id="txtRowc1" size="15" />    </td>
  </tr>
   <tr>
    <td><input type="text" name="txtRowa2"
     id="txtRowa2" size="5" /></td>
    <td>
    <input type="text" name="txtRowb2"
     id="txtRowb2" size="15" />    </td>
    <td>
    <input type="text" name="txtRowc2"
     id="txtRowc2" size="15" />    </td>
  </tr>
    <tr>
    <td><input type="text" name="txtRowa3"
     id="txtRowa3" size="5" /></td>
    <td>
    <input type="text" name="txtRowb3"
     id="txtRowb3" size="15" />    </td>
    <td>
    <input type="text" name="txtRowc3"
     id="txtRowc3" size="15" />    </td>
  </tr>
  
</table>
</form>

<p>
<input onclick="formSubmit()" type="button" value="Send data" />
</p>

<script type="text/javascript">
        function formSubmit() {
            google.script.run.getValuesFromForm(document.forms[0]);
        }
    </script>

此表需要放在电子表格中，gs文件为:

This table needs to be put on a spreadsheet, the gs file is:

//getValuesFromForm
function getValuesFromForm(form){
  var tempa1 = form.txtRowa1,
  tempb1 = form.txtRowb1,
  tempc1 = form.txtRowc1,
  tempa2 = form.txtRowa2,
  tempb2 = form.txtRowb2,
  tempc2 = form.txtRowc2,
  tempa3 = form.txtRowa3,
  tempb3 = form.txtRowb3,
  tempc3 = form.txtRowc3,
 sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 sheet.appendRow([tempa1, tempb1, tempc1]);
 sheet.appendRow([tempb1, tempb2, tempc2]);
 sheet.appendRow([tempc1, tempc2, tempc3]);
 }

但是问题是:表是动态的，我不知道要有多少行(在这种情况下，有3行)如何从表中获取值?

But the thing is: the table is dynamic and i dont know how many rows is going to have (in this case there are 3 rows) How do I get the values out of the table?

推荐答案

我将使用DOM方法和属性从客户端从浏览器的表中获取数据.此代码在将表发送到服务器端gs代码之前对其进行处理.它是动态的，因此表的长度或行的长度无关紧要.而且，您无需使用名称或ID来命名输入即可引用它们.这段代码有两个循环.一个FOR循环嵌套在另一个循环内.代码遍历表中的每一行，并为每一行获取该行中的所有输入.然后，它创建一个二维数组以发送到gs代码.您可能会问:为什么不使用gs代码进行处理?"在客户端处理数据使您可以使用方法getElementsByTagName().服务器端代码无法做到这一点.

I would get the data out of the table, in the browser, from the client side using DOM methods and properties. This code processes your table before it is sent to the server side gs code. It's dynamic, so it doesn't matter what the table length or row length is. And you don't need to name your inputs with names or ids in order to reference them. This code has two loops in it. One FOR loop nested inside the other. The code goes through each row in the table, and for each row, gets all the inputs in that row. Then it creates a two dimensional array to send to the gs code. You might ask, "why not do the processing in the gs code?" Processing the data in the client side allows you to use the method getElementsByTagName(). You can't do that with server side code.

您可以在客户端JavaScript或服务器端JavaScript中使用FORM对象作为替代方案，但是Form Object中有很多数据，因此很难弄清并理解该Form Object中的数据如何是结构化的.

You could work with the FORM object in either the client side or server side JavaScript as an alternative, but there is a lot of data in the Form Object, and it's difficult to figure out and understand how the data in that Form Object is structured.

此代码的最终结果是另一个数组内的单元格值数组.

The end result of this code is an array of cell values inside another array.

[[[Row1_Cell1，Row1_Cell2]，[Row2_Cell1，Row2_Cell2]等]

[ [Row1_Cell1, Row1_Cell2], [Row2_Cell1, Row2_Cell2], etc ]

您还需要在gs代码中嵌套一个FOR循环. FOR循环将不再需要为每一行和每个单元格硬编码很多变量名.

You will also need a nested FOR loop in the gs code. The FOR loops will eliminate the need to hard code lots of variable names for each row and cell.

<script type="text/javascript">
  function formSubmit() {
    console.log('formSubmit ran');

    var allTableRows = document.getElementsByTagName("tr");
    console.log('allTableRows: ' + allTableRows);

    var numbrOfRows = allTableRows.length;
    console.log('numbrOfRows: ' + numbrOfRows);

    var thisCellValue = "";

    var arryMaster = [];
    var arryOfTableRowVals = [];
    var thisRowData = "";

    for (var i = 1; i < numbrOfRows; i++) {// START with i = 1

      console.log('row count: ' + i);

      thisRowData = allTableRows[i].getElementsByTagName('input');
      console.log('thisRowData: ' + thisRowData);
      console.log('thisRowData.length: ' + thisRowData.length);

      arryOfTableRowVals = []; //reset the array

      for (var j = 0; j < thisRowData.length; j++) {
        console.log('---- count j: ' + j);
        thisCellValue = thisRowData[j].value;

        arryOfTableRowVals.push(thisCellValue);

        console.log('---- arryOfTableRowVals: ' + arryOfTableRowVals[j]);
      };
      arryMaster.push(arryOfTableRowVals);
    }

    console.log(arryMaster[2][2]);
    google.script.run.getValuesFromForm(arryMaster);

  }
</script>

我在代码中放置了许多console.log()语句，以便您可以在浏览器控制台中查看代码的结果.结果将打印到控制台.您可能想将其注释掉，或将其删除.

I put lots of console.log() statements in the code so that you can see the results of the code in your browsers console. The results will print to the console. You might want to comment those out, or delete them.

这篇关于将HTML表传递给Google Spreadsheet.从动态表中获取单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！