javascript - 将HTML Table中的信息拆分并与 "header"信息合并

Question

我有一个 HTML 表单，其中包含一个 HTML 表，用户可以在其中添加/删除行。我将此数据发送到 Google 表格。我想将“标题”信息与表中的每一行一起存储在单独的行上。例如，如果用户填写表单并提交表中的两行数据，则会有两行发送到 Google 表格。表中每行的 A 列到 T、X 和 Y 列将相同，但 U、V 和 W 列将不同。

我遇到的问题是我当前的代码除了第一行之外没有收集表中的任何信息。在我能够收集该数据后，我想确保表格的每一行都在电子表格中创建一个新行。

我已经使用现已弃用的 UI 服务来完成此工作，但由于 UI 服务已弃用，我希望使用 HTML 服务。另外，它更漂亮。

我是根据 here 的说明进行此操作的。

这是我的 Apps 脚本

    ////////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
   /////////////                                          \\\\\\\\\\\\\\\\\\
  /////////////         BASE APPS SCRIPT CODE.GS           \\\\\\\\\\\\\\\\\\
 /////////////                                              \\\\\\\\\\\\\\\\\\
////////////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

/**
 * Serves HTML of the application for HTTP GET requests.
 * If folderId is provided as a URL parameter, the web app will list
 * the contents of that folder (if permissions allow). Otherwise
 * the web app will list the contents of the root folder.
 *
 * @param {Object} e event parameter that can contain information
 *     about any URL parameters provided.
 */
function doGet(e) {
  var template = HtmlService.createTemplateFromFile('Index');
  var document = HtmlService.createTemplateFromFile('Index');
  // Retrieve and process any URL parameters, as necessary.

  // Build and return HTML in IFRAME sandbox mode.
  return template.evaluate()
      .setTitle('Carrier RA Authorization Request Form')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);

}

//////////////////////////////////////////////////////////////////////////////////////////////////


    ////////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
   /////////////                                          \\\\\\\\\\\\\\\\\\
  /////////////  BLOG CODE TO MOVE DATA TO GOOGLE SHEET    \\\\\\\\\\\\\\\\\\
 /////////////                                              \\\\\\\\\\\\\\\\\\
////////////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

//  1. Enter sheet name where data is to be written below
        var SHEET_NAME = "CarrierData";

//  2. Run > setup
//
//  3. Publish > Deploy as web app
//    - enter Project Version name and click 'Save New Version'
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
//  4. Copy the 'Current web app URL' and post this in your form/script action
//
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function

/*
function doGet(e){
  var template = HtmlService.createTemplateFromFile('Index');
  var document = HtmlService.createTemplateFromFile('Index');
  return template.evaluate();
      template.setTitle('RA Request Form - Retail');
      template.setSandboxMode(HtmlService.SandboxMode.IFRAME);
  // return handleResponse(e);
}
*/

function doPost(e){
  return handleResponse(e);
}


function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.

  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);

    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [];
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

代码片段文件如下

1. CSS
2. HTML

<!-- This CSS package applies Google styling;
 it should always be included. --> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css"> <style> .result-display {
  margin-left: 15px;
  font-size: 125%;
}
.error {
  color: #FF0000;
}
.hidden {
  display: none;
}
<!--------------- ADD ROWS CSS ---------------------------> .entry:not(:first-of-type) {
  margin-top: 10px;
}
.glyphicon {
  font-size: 12px;
}
<!--------------------------------------------------------> input[type=checkbox] {
  -webkit-appearance: checkbox;
}
</style>

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!=H tmlService.createHtmlOutputFromFile( 'Stylesheet').getContent(); ?>

  <head>

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>

    <!--------------- BOOTSTRAP CSS --------------------------->
    <!-- Latest compiled and minified CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">

    <!-- Optional theme -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap-theme.min.css">

    <!-- Latest compiled and minified JavaScript -->
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
    <!-------------------------------------------------------->
    <script>
      /**
       * Run initializations on web app load.
       */
      $(function() {
        // Call the server here to retrieve any information needed to build the page.
        google.script.run
          .withSuccessHandler(function(contents) {
            // Respond to success conditions here.
            updateDisplay(contents);
          })
          .withFailureHandler(function(msg) {
            // Respond to failure conditions here.
            $('#main-heading').text(msg);
            $('#main-heading').addClass("error");
            $('#error-message').show();
          })
          .getFolderContents(folderId);
      });

       /// -- table add/remove row --- ////
      $(window).load(function() {
        var i = 1;
        var table_tag = 1;
        $("#add_row").click(function() {
          $('#addr' + i).html("<td>" + (i + 1) + "</td><td><input  name='packNumber" + i + "' type='text' placeholder='WHR Packing Slip Model Number'  class='form-control input-md'></td><td><input  name='qty" + i + "' type='text' placeholder='Quantity'  class='form-control input-md'></td><td><select name='reason" + i + "' class='form-control'><option>Defective/Damaged Return</option><option>Good Product Return</option></select></td>");

          $('#tab_logic').append('<tr id="addr' + (i + 1) + '"></tr>');
          i++;
          table_tag++;
        });
        $("#delete_row").click(function() {
          if (i > 1) {
            $("#addr" + (i - 1)).html('');
            i--;
            table_tag--;
          }
        });

      });
    </script>
  </head>

  <html>

  <body>
    <div class="container">
      <!-- Form Name -->
      <h1>Greenville Return Authorization Request Form for Carrier</h1>

      <!----------------------------------->
      <!--------PRIVACY INFORMATION-------->
      <!----------------------------------->

      <form action="https://script.google.com/a/macros/whirlpool.com/s/AKfycbyf9PgQxWaqFG7Tt7RJOgAh_LomVpW0jqT_KR4bA6uxFq-iBx8/exec" method="post">
        <br>
        <div>
          <p><span class="text-danger"> * </span> Indicates a required field.</p>
          <p>Please read and accept the <a href="http://whirlpoolcorp.com/privacy-united-states/">Whirlpool Privacy Statement</a> prior to filling out this form.</p>
          <label>
            <input type="checkbox" required="">I accept and acknowledge the Privacy and Security Policy above<span class="text-danger"> * </span>
          </label>
          <br>
        </div>
        <br>
        <p>If you have any questions regarding the form or return process, please send an email to <a href="mailto:KASA_Returns@whirlpool.com">KASA_Returns@whirlpool.com</a>. Thank you.</p>
        <br>




        <!----------------------------------->
        <!--COMPANY NAME AND SOLD TO NUMBER-->
        <!----------------------------------->
        <div class="row">
          <div class="col-md-6">
            <!--Company Name -->
            <div class="form-group">
              <label for="companyName">Retail Company Name returning the product<span class="text-danger"> * </span>
              </label>
              <input name="companyName" id="companyName" type="text" placeholder="Acme, Inc." class="form-control" required="">
            </div>
          </div>
          <!--Soldto Number -->
          <div class="col-md-6">
            <div class="form-group">
              <label for="soldtoNumber">Customer Sold-to Number</label>
              <input name="soldtoNumber" id="soldtoNumber" type="text" placeholder="123456" class="form-control">
            </div>
          </div>
        </div>

        <!----------------------------------->
        <!-----CUSTOMER SHIP TO ADDRESS------>
        <!----------------------------------->
        <h3> Customer Ship to Address (shipping point returning product to Whirlpool)</h3>
        <!-- Ship To Company Name -->
        <div class="form-group">
          <label for="shipToCompanyName">Company<span class="text-danger"> * </span>
          </label>
          <input name="shipToCompanyName" id="shipToCompanyName" type="text" placeholder="Company Name" class="form-control" required="">
        </div>
        <!-- Ship To Street Address 1 -->
        <div class="form-group">
          <label for="shipToStreetOne">Street Address 1<span class="text-danger"> * </span>
          </label>
          <input name="shipToStreetOne" id="shipToStreetOne" type="text" placeholder="Street Address 1" class="form-control" required="">
        </div>
        <!-- Ship To Street Address 2 -->
        <div class="form-group">
          <label for="shipToStreetTwo">Street Address 2</label>
          <input name="shipToStreetTwo" id="shipToStreetTwo" type="text" placeholder="Street Address 2" class="form-control">
        </div>
        <div class="row">
          <!-- Ship To City -->
          <div class="col-md-6 form-group">
            <label for="shipToCity">City<span class="text-danger"> * </span>
            </label>
            <input name="shipToCity" id="shipToCity" type="text" placeholder="City" class="form-control" required="">
          </div>
          <!-- Ship To State -->
          <div class="col-md-2 form-group">
            <label>State<span class="text-danger"> * </span>
            </label>
            <br/>
            <select id="shipToState" name="shipToState" class="form-control" required="">
              <option value="AL">AL</option>
              <option value="AK">AK</option>
              <option value="AZ">AZ</option>
              <option value="AR">AR</option>
              <option value="CA">CA</option>
              <option value="CO">CO</option>
              <option value="CT">CT</option>
              <option value="DE">DE</option>
              <option value="DC">DC</option>
              <option value="FL">FL</option>
              <option value="GA">GA</option>
              <option value="HI">HI</option>
              <option value="ID">ID</option>
              <option value="IL">IL</option>
              <option value="IN">IN</option>
              <option value="IA">IA</option>
              <option value="KS">KS</option>
              <option value="KY">KY</option>
              <option value="LA">LA</option>
              <option value="ME">ME</option>
              <option value="MD">MD</option>
              <option value="MA">MA</option>
              <option value="MI">MI</option>
              <option value="MN">MN</option>
              <option value="MS">MS</option>
              <option value="MO">MO</option>
              <option value="MT">MT</option>
              <option value="NE">NE</option>
              <option value="NV">NV</option>
              <option value="NH">NH</option>
              <option value="NJ">NJ</option>
              <option value="NM">NM</option>
              <option value="NY">NY</option>
              <option value="NC">NC</option>
              <option value="ND">ND</option>
              <option value="OH">OH</option>
              <option value="OK">OK</option>
              <option value="OR">OR</option>
              <option value="PA">PA</option>
              <option value="RI">RI</option>
              <option value="SC">SC</option>
              <option value="SD">SD</option>
              <option value="TN">TN</option>
              <option value="TX">TX</option>
              <option value="UT">UT</option>
              <option value="VT">VT</option>
              <option value="VA">VA</option>
              <option value="WA">WA</option>
              <option value="WV">WV</option>
              <option value="WI">WI</option>
              <option value="WY">WY</option>
            </select>
          </div>


          <!-- Ship To Zip -->
          <div class="col-md-4 form-group">
            <label for="shipToZip">Zip Code<span class="text-danger"> * </span>
            </label>
            <input name="shipToZip" id="shipToZip" type="number" pattern="\d{5}(?:[-\s]\d{4})?" placeholder="Zip" class="form-control" required="">
          </div>
        </div>


        <!----------------------------------->
        <!--------CARRIER INFORMATION-------->
        <!----------------------------------->

        <!-- Carrier Name -->
        <div class="form-group">
          <label for="carrierName">Carrier Company Name<span class="text-danger"> * </span>
          </label>
          <input name="carrierName" id="carrierName" type="text" placeholder="Sam's Trucking Company" class="form-control" required="">
        </div>

        <div class="row">

          <!-- Carrier Outbound Tracking Number -->
          <div class="col-md-4 form-group">
            <label for="carrierOBNumber">Carrier Outbound Tracking Number</label>
            <input name="carrierOBNumber" id="carrierOBNumber" type="text" placeholder="06459827" class="form-control">
          </div>

          <!-- Carrier Return Tracking Number -->
          <div class=" col-md-4 form-group">
            <label for="carrierReturnNumber">Carrier Return Tracking Number</label>
            <input name="carrierReturnNumber" id="carrierReturnNumber" type="text" placeholder="06459827A" class="form-control">
          </div>

          <!-- Date of Original Shipment -->
          <div class=" col-md-4 form-group">
            <label for="shipDate">Date of Original Shipment</label>
            <input name="shipDate" id="shipDate" type="date" placeholder="01/01/2015" class="form-control">
          </div>

        </div>

        <div class="row">

          <!-- RA Requster Name -->
          <div class="col-md-4 form-group">
            <label for="requesterName">First &amp; Last Name of RA Requester<span class="text-danger"> * </span>
            </label>
            <input name="requesterName" id="requesterName" type="text" placeholder="John Doe" class="form-control" required="">
          </div>

          <!-- Carrier Phone Number -->

          <div class="col-md-4 form-group">
            <label for="phoneNumber">Phone Number</label>
            <input name="phoneNumber" id="phoneNumber" type="tel" placeholder="(555) 123 - 4567 x301" class="form-control">
          </div>

          <!-- Carrier Email Address -->
          <div class="col-md-4 form-group">
            <label for="email">Email address</label>
            <input name="emailAddress" id="emailAddress" type="email" placeholder="John_L_Doe@email.com" class="form-control">
          </div>

        </div>


        <!----------------------------------->
        <!--------RETURN INFORMATION--------->
        <!----------------------------------->
        <h4>To help speed the processing and accuracy of your return credit, please provide as much of the following information as possible.</h4>
        <h5 class="text-danger"><strong>Delivery SID is required</strong></h5>


        <div class="row">

          <!-- WHR Shipment Number-->
          <div class="col-md-3 form-group">
            <label for="shipNumber">Shipment Number</label>
            <input name="shipNumber" id="shipNumber" type="text" placeholder="Whirlpool Shipment Number" class="form-control">
          </div>

          <!-- Delivery SID Number -->
          <div class="col-md-3 form-group">
            <label for="sidNumber" class="text-danger">Delivery SID Number<span class="text-danger"> * </span>
            </label>
            <input name="sidNumber" id="sidNumber" type="text" placeholder="Delivery SID Number" class="form-control">
          </div>

          <!-- Whirlpool Order Number-->
          <div class="col-md-3 form-group">
            <label for="orderNumber">Whirlpool Order Number</label>
            <input name="orderNumber" id="orderNumber" type="text" placeholder="Whirlpool Order Number" class="form-control">
          </div>

          <!-- BOL Number-->
          <div class="col-md-3 form-group">
            <label for="bolNumber">BOL Number</label>
            <input name="bolNumber" id="bolNumber" type="text" placeholder="BOL Number" class="form-control">
          </div>

        </div>

        <!----------------------------------------->
        <!-------PRODUCT INFORMATION TABLE--------->
        <!----------------------------------------->
        <h4 class="text-center">Product being returned</h4>
        <h5 class="text-center text-danger"><strong>WHR Packing Slip Model Number and Quantity are required fields</strong></h5>
        <div class="container">
          <div class="row clearfix">
            <div class="col-md-12 column">
              <table class="table table-bordered table-hover" id="tab_logic">
                <thead>
                  <tr>
                    <th class="text-center">
                      Row
                    </th>
                    <th class="text-center" required="">
                      WHRPackingSlipModelNumber<span class="text-danger"> * </span>
                    </th>
                    <th class="text-center" required="">
                      Quantity<span class="text-danger"> * </span>
                    </th>
                    <th class="text-center">
                      ReasonforReturningProduct
                    </th>
                  </tr>
                </thead>
                <tbody>
                  <tr id='addr0'>
                    <td>
                      1
                    </td>
                    <td>
                      <input type="text" name='slipNumber' placeholder='WHR Packing Slip Model Number' class="form-control" required="" />
                    </td>
                    <td>
                      <input type="text" name='quant' placeholder='Quantity' class="form-control" required="" />
                    </td>
                    <td>
                      <select id="reason" name="reason" class="form-control" required="">
                        <option>Defective/Damaged Return</option>
                        <option>Good Product Return</option>
                      </select>
                    </td>
                  </tr>
                  <tr id='addr1'></tr>
                </tbody>
              </table>
            </div>
          </div>
          <a id='delete_row' class="pull-right btn btn-default">Delete Last Row</a><a id="add_row" class="pull-right btn btn-default ">Add Row</a>
        </div>

        <!----------------------------------------->
<!--------EXTRA RETURN INFORMATION--------->
<!----------------------------------------->
<div class="row">

<div class="col-md-6 form-group">
  <label  for="skids">How many skids or pallets will be returned?</label>
  <input name="skids" id="skids" type="text" placeholder="Skid/Pallet Quantity" class="form-control">
</div>

<div class="col-md-6 form-group">
  <label  for="weight">Weight of return, if known</label>
  <input name="weight" id="weight" type="text" placeholder="Weight (in pounds)" class="form-control">
</div>

</div>

<!--- submit button -->                    
<div class="row">
  
  <div class="col-md-6 col-md-offset-3">
      <button  name="submit" value="Submit" id="ss-submit"type="submit" class="btn-primary btn-block" action="https://script.google.com/a/macros/whirlpool.com/s/AKfycbw6HV4X1lAa64lzIC18YSrvRZS2_aV8hVRMVL953_ICqIvdtqc/exec" method="post">Submit</button>
  </div>

</div>
<br>
<br>
<!----------------------------->
</form>
</div>
</body>
</html>

Answer 1

如果您提供了完整的 HTML，则没有结尾 </form>标签。但是，您可能无意中遗漏了这一点。我确实找到了一些可以添加/删除的东西。

<a id='delete_row' class="pull-right btn btn-default">Delete Last Row</a><a id="add_row" class="pull-right btn btn-default ">Add Row</a>

但我没有看到任何触发任何代码来添加或删除任何内容的内容。

您可能需要将 HTML 精简为仅一个输入，使其正常工作，然后从那里开始。如果您有添加新行的代码，并且 name属性未设置新的且唯一的名称，这会导致问题。另外，我不确定是否使用 action属性和 post表单中的方法:

<form action="https://script.google.com/a/macros/whirlpool.com/s/ID Here/exec" method="post">

我不确定应用程序脚本如何处理这个问题。提交表单的文档使用 google.script.run 。与google.script.run Apps 脚本获取表单对象，并将其转换为其他内容，只有使用 name 属性才能取回数据。可能值得尝试使用 google.script.run而不是表单的发布请求。

此外，我没有看到代码在哪里处理表单对象。你有一行:

var headRow = e.parameter.header_row || 1;

然后headRow没有在任何地方使用。据我所见，您的代码根本没有使用表单中的任何数据。我不确定您是否获得了第一行。