时间戳

首页 > 解决方案 > setValues() 函数问题

google-apps-script - setValues() 函数问题

问题描述

我想在工作表末尾插入选项卡内容。函数 appendRows() 工作得很好，但想在特定范围内添加数据。

我用 setValues 尝试了很多东西，但没有任何效果。

这是我的代码的一部分。

// File used
var ss = SpreadsheetApp.getActiveSpreadsheet();

// Sheet used
var sheet = ss.getActiveSheet();

// Data range
var sheetDataRange = sheet.getDataRange();

// The last sheet row with data in
var sheetLastRow = sheet.getLastRow();

// The last sheet column with data in
var sheetLastColumn = sheet.getLastColumn();    

function processFormAddCustomer(formObject) {
    
      var rowsValues = [
          formObject.customer,
          formObject.contractScope]
          .concat(
            fillCell(formObject.acType),
            formObject.classrooms,
            formObject.license,
            formObject.licenseExpiration,
            formObject.indexDeliveredInitial,
            formObject.shipmentDateInitial,
            formObject.implementationDateInitial
          );
      //SpreadsheetApp.getUi().alert(rowsValues);
      //sheet.appendRow(rowsValues);
      var test = sheet.getRange(sheetLastRow+1, 1, 1, rowsValues.length).getA1Notation(); // Already tried without A1Notation
      test.setValues(rowsValues);
      SpreadsheetApp.getUi().alert("The customer was successfully added");
    ... //Nothing important
    }

rowValues var 包含我想要的数据。它们来自一个 html 表单。

更新：

我有两个按钮与两个不同的脚本相关联。如果您单击这些按钮，则会为每个按钮显示一个不同的 html 表单。用户可以插入值并单击提交。有文本输入，下拉菜单，......我想在最后一行和前十列中插入第一个表单的数据。我想在第 11 到 13 列的同一行中插入其他表单数据。

“formObject.id”是用户在表单中录制的数据。

这是代码的另一部分。

<!-- JavaScriptAddCustomer.html -->

<script>
    
  /*
   * The window will not close when the form is submited
   */
    function preventFormSubmitAddCustomer() {

    var formsAddCustomer = document.querySelectorAll('FormAddCustomer');

    for (var i; i < formsAddCustomer.length; i++) {
            formsAddCustomer[i].addEventListener('submit', function(event) {
                event.preventDefault();
            });
        }
    }
  // The page is not refreshed when the button submit is pressed
    window.addEventListener('load', preventFormSubmit);

  /*
   * Calls the form
   */
    function handleFormSubmitAddCustomer(formObject) {
        google.script.run.processFormAddCustomer(formObject);
    document.getElementById("addCustomerForm").reset();
    }

  // ????
  function formSubmit() {
    var customerDropdown = document.getElementById("customer");
    var selectedCustomer = e.value;

    var acTypeDropdown = document.getElementById("acType"); // SI bu essayer AcType
    var selectedAcType = e.value;

    google.script.run.getSelectDatas(document.forms[0]);
  }
</script>


<!-- FormAddCustomer.html -->

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>

<!-- form -->
<form id="addCustomerForm" onsubmit="handleFormSubmitAddCustomer(this)">
<!-- // Mettre ; pour appeler 2 fctns dans onSubmit ? -->

  <label for="customer">Customer *</label><br/>
  <input type="text" id="customer" name="customer" placeholder="Required"><br/><br/>

  <label for="contractScope">Contract Scope *</label><br/>
  <select name="contractScope" id="contractScope" > <!-- onEdit="moreLicencesOption(this);" -->
    <option value=""> </option><br/>
    <option value="ACE"> ACE</option><br/>
    <option value="ACT Suite"> ACT Suite</option><br/>
    <option value="ACT for Academy"> ACT for Academy</option><br/>
    <option value="AKM"> AKM</option><br/>
    <option value="ATC Maintenance"> ATC Maintenance </option><br/>
    <option value="ATC Flight"> ATC Flight </option><br/>
    <option value="ATC Cabin"> ATC Cabin </option><br/>
    <option value="CKM"> CKM</option><br/>
    <option value="CTS"> CTS</option><br/>
    <option value="FTS"> FTS</option><br/>
    <option value="MTS3"> MTS3</option><br/>
    <option value="PA"> PA</option><br/>
    <option value="SKM"> SKM</option>
  </select><br/><br/>

  <label for="acType">AC Type *</label><br/>
  <select multiple id="acType" name="acType">
    <option value="A380"> A380 </option><br/>
    <option value="A350"> A350 </option><br/>
    <option value="A340"> A340 </option><br/>
    <option value="A330"> A330 </option><br/>
    <option value="A320"> A320 </option><br/>
    <option value="A220"> A220 </option><br/>
    <option value="Common (MPP Tools Menu)"> Common (MPP Tools Menu)</option>
  </select><br/><br/>

  <label for="classrooms">Classrooms</label><br/>
  <input type="text" id="classrooms" name="classrooms"><br/><br/>
  //...

在此先感谢您的帮助。

标签： google-apps-scriptgoogle-sheets

解决方案

Issues:

  • setValues(values) accepts a 2D array, and you are providing a simple array (rowsValues) instead.
  • getA1Notation() returns a String, not a Range. There's no point in using that, and it will cause an error for sure, since a String doesn't have a method setValues.

Solution:

Since your desired Range has a single row and several columns, you should provide a 2D array whose outer array contains a single element corresponding to the simple array of data you want to append (rowsValues):

var test = sheet.getRange(sheetLastRow+1, 1, 1, rowsValues.length);
test.setValues([rowsValues]);

推荐阅读