setValues() function issue

1
votes

I want to insert the tab content at the end of the sheet. The function appendRows() works perfectly but wanted to add the data in a specific range.

I tried a lot of things with the setValues but nothing works.

Here's a part of my code.

// 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
    }

The rowValues var contains the data I want. They come from an html form.

Update:

I have two buttons associated to two different scripts. If you click on these buttons, a different html form appears for each. The user can insert values and click submit. There are text inputs, dropdowns, ... I want to insert the data of the first form in the last row and the first ten columns. I want to insert the other form data in the same row in the column 11 to 13.

The "formObject.id" is the data that the user taped in the form.

Here's another part of the code.

<!-- 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/>
  //...

Thanks in advance for your help.

1
google-apps-scriptgoogle-sheets
Your question isn't clear. Can you show an example of the result you expect? - trinalbadger587
You should simplify your question to just focusing on rowvalues into your sheet. Your issue is almost certainly related to a single dimensional array vs. a two-dimensional array. If you post exactly what rowValues is, it won't be hard to resolve. - pgSystemTester
Hi, was this answer useful to you? If that's the case, please consider accepting&upvoting it stackoverflow.com/help/someone-answers. - Iamblichus

1 Answers

1
votes

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]);