How to send data from HTML form to Google Spreadsheet using JavaScript?

Question

I'm trying to build a webapp that records datas from a form in a Google Spreadsheet. To do this, I have to use JavaScript (JSON or AJAX requests will work as well), but I cannot use Google Apps Script because I need the user to keep using my pages and GAS doesn't allow it.

I'm not so much versed in JSON requests but I tried to make an append one: no surprise, it's not working and no surprise, I don't know why.

I'm not sure the URL I used to make the request and the code are correct, but not knowing very well how to proceed, it's quite difficult to know what's wrong in my code.

That's my form:

    <form name="reqForm" id="reqForm" method="post" action="" accept-charset="UTF-8" enctype="application/json">
            <input type="hidden" name="area" id="area" readonly/>
            <input type="hidden" name="idN" id="idN" readonly/>
            <input type="hidden" name="dataReq" id="dataReq" readonly />
            <label for="nome">* Nome:</label>
            <input type="text" id="nome" name="nome" placeholder="Il tuo nome" />
            <label for="cognome">* Cognome:</label>
            <input type="text"  id="cognome" name="cognome" placeholder="Il tuo cognome" />
            <label for="mat">* Matricola:</label>
            <input type="text" id="mat" name="mat" placeholder="La tua matricola" />
            <label for="mail">* E-mail:</label>
            <input type="text" id="mail" name="mail" placeholder="La tua e-mail" />
            <label for="testo">* Richiesta:</label>
            <textarea id="testo" name="testo" placeholder="Che cosa vuoi chiedere?"></textarea>
            <button type="button" value="Invia" onClick="check()">Invia</button>
        </form>`

The hidden values are set to provide an ID Number and the user's path.

The check() function will check the form and (should) make the request and write in the GSpreadSheet

    function check() {

document.getElementById('errorForm').innerHTML = "";

var a = document.getElementById('area').value;
var idN = document.getElementById('idN').value;
var n = document.getElementById('nome').value;
var c = document.getElementById('cognome').value;
var m = document.getElementById('mat').value;
var em= document.getElementById('mail').value;
var t = document.getElementById('testo').value;

// check the possible errors and set error messages.
    // if msg is not empty, writes the messages in my page.

} else if(msg == "") {
    var xhr = new XMLHttpRequest();
    var key = mySheetKey, sName =  mySheetName, url = "https://sheets.googleapis.com/v4/spreadsheets/"+key+"/values/"+ sName + ":append?valueInputOption=USER_ENTERED";

    xhr.open("POST", url, true);
    xhr.setRequestHeader("Content-type", "application/json");
    xhr.onreadystatechange = function() {
        if(xhr.readyState === 4 && xhr.status === 200) {
            var json = JSON.parse(xhr.responseText);
        }
    // Here I should create the object made of my variables I read 
    // from my form at the beginning of the code and send the request that
    // should append my datas to my Spreadsheet

    xhr.send();
    } 
    }

As I said before, my code look similar to several ones I found online but it's not working and I don't know how to understand what's wrong.

Could you please kindly give me some tips or advice or some example that could help me appending data to a Google Spreadsheet?

Why on EARTH are you using XMLHttpRequest AND $.ajax - that is just silly. Also where is the if to the else? — mplungjan
– mplungjan, Commented Jun 17, 2017 at 5:59
follow the apis used for this purpose developers.google.com/sheets/api/reference/rest/v4/… — Chandan Kumar Thakur
– Chandan Kumar Thakur, Commented Jun 17, 2017 at 6:00
@mplungjan I'm really sorry... This is new to me and I'm not versed in AJAX or XMLHttpRequest... could you please give me some tips or anything that could help me please? — Simone
– Simone, Commented Jun 17, 2017 at 6:01
@ChandanKumarThakur thanks for your reply: I read it several times but there's no samples and I can't understand how to write the code and the URL — Simone
– Simone, Commented Jun 17, 2017 at 6:02
here is a sample of writing into sheets: developers.google.com/sheets/api/samples/writing — Chandan Kumar Thakur
– Chandan Kumar Thakur, Commented Jun 17, 2017 at 6:04

Cooper · Accepted Answer · 2017-06-25 17:10:56Z

5

A simple spreadsheet contained web app example

$(function() {
        $('#btn1').click(validate);
        $('#txt4').val('');
        $('#txt3').val('');
        $('#txt2').val('');
        $('#txt1').val('')
      });
function validate()
{
    var txt1 = document.getElementById('txt1').value || ' ';
    var txt2 = document.getElementById('txt2').value || ' ';
    var txt3 = document.getElementById('txt3').value || ' ';
    var txt4 = document.getElementById('txt4').value || ' ';
    var a = [txt1,txt2,txt3,txt4];
    if(txt1 && txt2 && txt3 && txt4)
    {
      google.script.run
        .withSuccessHandler(setResponse)
        .getData(a);
        return true;
    }
    else
    {
      alert('All fields must be completed.');
    }
}

The entire example:

The HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
  <div id="data">
    <br />Text 1<input type="text" size="15" id="txt1" />
    <br />Text 2<input type="text" size="15" id="txt2" />
    <br />Text 3<input type="text" size="15" id="txt3" />
    <br />Text 4<input type="text" size="15" id="txt4" />
    <br /><input type="button" value="submit" id="btn1" />
  </div>
  <div id="resp" style="display:none;">
    <h1>Response</h1>
    <p>Your data has been received.</p>
  </div>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
      $(function() {
        $('#btn1').click(validate);
        $('#txt4').val('');
        $('#txt3').val('');
        $('#txt2').val('');
        $('#txt1').val('')
      });

      function setResponse(a)
      {
        if(a)
        {
          $('#data').css('display','none');
          $('#resp').css('display','block');
        }
      }

      function validate()
      {
        var txt1 = document.getElementById('txt1').value || ' ';
        var txt2 = document.getElementById('txt2').value || ' ';
        var txt3 = document.getElementById('txt3').value || ' ';
        var txt4 = document.getElementById('txt4').value || ' ';
        var a = [txt1,txt2,txt3,txt4];
        if(txt1 && txt2 && txt3 && txt4)
        {
          google.script.run
            .withSuccessHandler(setResponse)
            .getData(a);
            return true;
        }
        else
        {
          alert('All fields must be completed.');
        }
      }

      function loadTxt(from,to)
      {
          document.getElementById(to).value = document.getElementById(from).value;
      }

     console.log('My Code');
   </script>
  </body>
</html>

The Apps Script:

function getData(a)
{
  var ts = Utilities.formatDate(new Date(), "GMT-6", "yyyy-MM-dd' 'HH:mm:ss");
  a.push(ts);
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Login').appendRow(a);
  return true;
}

function doGet()
{
  var html = HtmlService.createHtmlOutputFromFile('index');
  return html.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)

}

My simple spreadsheet:

The good news is that you're probably a lot better at using the Google Chrome debugger now than before you started.

edited Jun 25, 2017 at 17:10

answered Jun 17, 2017 at 18:18

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Simone Over a year ago

Correct me if I'm wrong: your code will send my data to a Google App Script that writes in my Spreadsheet?

Cooper Over a year ago

The function getData(a) is in a gs file on the server. This code comes from a simple web app. It's sending an array of text values.

Cooper Over a year ago

Yes it will send data from a web app to a script that is bound to my spreadsheet and it will append a line of new data each time. Note: the entire html file has not been included. It's true I'm not using a '<form' per se but the end result is that data moves from client to server and then back to client via the set response.

Cooper Over a year ago

Do you want the entire example?

Cooper Over a year ago

Sometimes we who serve here try too hard to seem intelligent and often not hard enough just to be helpful.

|

Collectives™ on Stack Overflow

How to send data from HTML form to Google Spreadsheet using JavaScript?

1 Answer 1

A simple spreadsheet contained web app example

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

A simple spreadsheet contained web app example

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related