submission form on google sheets

Question

I followed this tutorial, https://www.youtube.com/watch?v=yZNPkcT4PKA&t=3s trying to create a form on a google spread sheet.

i followed all the steps, form loading script is working ok, form body displays ok, but form data after submission does not appear on spreadsheet as expected.

i must have missed something, but i went through the tutorial several times, and still can't figure it out

so here is the html code:
(the form code is taken from bootsrap)

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-B0vP5xmATw1+K9KRQjQERJvTumQW0nPEzvF6L/Z6nronJ3oUOFUFpCjEUQouq2+l" crossorigin="anonymous">

   <div>
      <div class="form-group">
        <label for="item-name">Item Name</label>
        <input type="text" class="form-control" id="item-name">
      </div>
      <div class="form-group">
        <label for="qty-recieved">Quantity Recieved</label>
        <input type="text" class="form-control" id="qty-recieved">
        </div>
    <button type="submit" class="btn btn-primary" id="mainButton">Submit</button>
    </div>

    <!-- Optional JavaScript; choose one of the two! -->

    <!-- Option 1: jQuery and Bootstrap Bundle (includes Popper) -->
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-Piv4xVNRyMGpqkS2by6br4gNJ7DXjqk09RmUpJ8jgGtD7zP9yug3goQfGII0yAns" crossorigin="anonymous"></script>

    <!-- Option 2: Separate Popper and Bootstrap JS -->
    <!--
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.min.js" integrity="sha384-+YQ4JLhjyBLPDQt//I+STsc9iw4uQqACwlvpslubQzn4u2UU2UFM80nGisd026JF" crossorigin="anonymous"></script>
    -->

    <script>
      function afterButtonClicked(){
      var item = document.getElementById("item-name");
      var qty = document.getElementById("qty-recieved");

      var rowData (item: item.value, qty: qty.value);
      google.script.run.addNewRow(rowData);
      }

      document.getElementById("mainButton").addEventListener("click",addRow);
    </script>
  </body>
</html>

and the addNewRow(rowData) script: (in a separate script file) i did make sure sheet name is "track"

function addNewRow(rowData) {

   
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("track");
  ws.appendRow([rowData.item, rowData.qty]);
}

edited
so i finally figured out what was wrong - the google account i used for the spreadsheet didn't match the gogole account logged in on google chrome browser. that is why the form submission didn't go through hope that helps someone..

Welcome to Stack Overflow. Your HTML is mal formed the head tag was not closed and and body tag is was not opened by the other hand you should include the textual error messages if any are logged in the web browser console on the Google Apps Script execution log. — Rubén
ooops.. sorry about that. how do i include the console errors? — Amir Flax
Could you log what you are getting inside rowData to see if rowData.item exists? — Mateo Randwolf

Cooper Cooper · Accepted Answer · 2021-03-13T13:12:30

I'm guessing that addRow should be something like this

function addNewRow(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("track");
  ws.appendRow([e.namedValues['item'],e.namedValues['qty']]);
}

And you will have to create an installable onFormSubmit trigger for spreadsheet for addNewRow()

onFormSubmit event object

submission form on google sheets

1 Answers