Search code examples
javascripthtmlgoogle-apps-scriptgoogle-sheetsuserform

google sheets script submit button not working

Hay!

im new to javascript and building userforms and ive been following tutorials and reading up where i can to achieve what im wanting to create.

Ive got a mate who has made these and hes had no issues following a tutorial he sent me and ive followed it to the dot on multiple attempts and it all works up until i create the script in the html file and send across through the java script google.script.run

for some reason, it just will not send accross and im not the greatest at debugging. i understand how to do it on the .gs side but the html side doesnt seem to have any and ive been told to use the dev tools from the browser but i still am not sure how to get it to fully work.

Ill post the code below and hopefully someone can spot where im going wrong. also if you can explain why it didnt work and why your way is the solution i would greatly appreciate it as itll help more on my journey to understand everything. Cheers.

funcs.gs

function addNewRow(data){

 const date = new Date();
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const ws = ss.getSheetByName("Compensations");
 
 ws.appendRow([date,data.rubio,data.staff,data.steam,data.steamID,]);
}

AddForm.html

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

    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-BmbxuPwQa2lc/FVzBcNJ7UAyJxM6wuqIj61tLrc4wSX0szH/Ev+nYRRuWlolflfl" crossorigin="anonymous">

    <title>Hello, world!</title>
  </head>
  <body>
    <h1>Compensation Form</h1>

      <div class="input-group mb-3">
        <span class="input-group-text" id="staffName">Staff Name</span>
        <input type="text" class="form-control" placeholder="Big Cahoonas" aria-label="staffName" aria-describedby="staffName">
      </div> 

      <div class="input-group mb-3">
        <span class="input-group-text" id="steamName"> Compensated Steam Name</span>
        <input type="text" class="form-control" placeholder="GetRiggityRekt" aria-label="steamName" aria-describedby="steamName">
      </div>

      <div class="input-group mb-3">
        <span class="input-group-text" id="steamID">SteamID64</span>
        <input type="number" class="form-control" placeholder="76938475322" aria-label="steamID" aria-describedby="steamID">
      </div>

      <div class="input-group mb-3">
        <span class="input-group-text" id="rubioLink">Rubio Link</span>
        <input type="text" class="form-control" id="rubioLink" placeholder="fivem.ozzy.life/id/1563423" aria-describedby="rubioLink">
      </div>

      <div class="d-grid gap-2">
        <button class="btn btn-primary" id="submit">Submit</button>
      </div>







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

    <!-- Option 1: Bootstrap Bundle with Popper -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/js/bootstrap.bundle.min.js" integrity="sha384-b5kHyXgcpbZJO/tY9Ul7kGkf1S0CWuKcCD38l8YkeH8z8QjE0GmW1gYU5S9FOnJ0" crossorigin="anonymous"></script>

<script>

document.getElementById("submit").addEventListener("click",addRecord);

function addRecord(){

var staff = document.getElementById("staffName");
var steam = document.getElementById("steamName");
var steamID = document.getElementById("steamID");
var rubio = document.getElementById("rubioLink");

var data = {
  staff: staff.value,
  steam: steam.value,
  steamID: steamID.value,
  rubio: rubio.value
  };

google.script.run.addNewRow(data);

loadUserForm.gs

function loadForm() {

  const showPop = HtmlService.createTemplateFromFile("AddForm");
  const htmlOutput = showPop.evaluate();

 
  var ui = SpreadsheetApp.getUi();
  ui.showModalDialog(htmlOutput, "Compensation V1");
  
}

function createMenu(){

const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu("Compensations");
menu.addItem("Add Record","loadForm");
menu.addToUi();

}

function onOpen(){

createMenu();

}
Solution

  • I think that in your HTML, the ID for each input tag is not set. By this, each value of data is undefined. I thought that this might be the reason of your issue. So, for example, how about the following modification?

    From:

    <div class="input-group mb-3">
  <span class="input-group-text" id="staffName">Staff Name</span>
  <input type="text" class="form-control" placeholder="Big Cahoonas" aria-label="staffName" aria-describedby="staffName">
</div> 

<div class="input-group mb-3">
  <span class="input-group-text" id="steamName"> Compensated Steam Name</span>
  <input type="text" class="form-control" placeholder="GetRiggityRekt" aria-label="steamName" aria-describedby="steamName">
</div>

<div class="input-group mb-3">
  <span class="input-group-text" id="steamID">SteamID64</span>
  <input type="number" class="form-control" placeholder="76938475322" aria-label="steamID" aria-describedby="steamID">
</div>

<div class="input-group mb-3">
  <span class="input-group-text" id="rubioLink">Rubio Link</span>
  <input type="text" class="form-control" id="rubioLink" placeholder="fivem.ozzy.life/id/1563423" aria-describedby="rubioLink">
</div>

    To:

    <div class="input-group mb-3">
  <span class="input-group-text">Staff Name</span>
  <input type="text" id="staffName" class="form-control" placeholder="Big Cahoonas" aria-label="staffName" aria-describedby="staffName">
</div> 

<div class="input-group mb-3">
  <span class="input-group-text"> Compensated Steam Name</span>
  <input type="text" id="steamName" class="form-control" placeholder="GetRiggityRekt" aria-label="steamName" aria-describedby="steamName">
</div>

<div class="input-group mb-3">
  <span class="input-group-text">SteamID64</span>
  <input type="number" id="steamID" class="form-control" placeholder="76938475322" aria-label="steamID" aria-describedby="steamID">
</div>

<div class="input-group mb-3">
  <span class="input-group-text">Rubio Link</span>
  <input type="text" id="rubioLink" class="form-control" id="rubioLink" placeholder="fivem.ozzy.life/id/1563423" aria-describedby="rubioLink">
</div>
    • In this modification, as a sample modification, in order to use the IDs of your Javascript addRecord(), the IDs of span were moved to input.
    • By above modification, when the values are inputted to each input tags and the button is clicked, data has the values and your Google Apps Script appends new row with the inputted values to the Spreadsheet.