admin管理员组

文章数量:1336415

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 = "/"+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?

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

• javascript
• jquery
• json
• google-sheets
• google-apis-explorer

Share Follow edited Feb 2, 2018 at 6:56 Kiquenet 15.1k3636 gold badges151151 silver badges247247 bronze badges asked Jun 17, 2017 at 5:57 SimoneSimone 8722 silver badges1313 bronze badges 11

• Why on EARTH are you using XMLHttpRequest AND $.ajax - that is just silly. Also where is the if to the else? – mplungjan Commented Jun 17, 2017 at 5:59
• 1 follow the apis used for this purpose developers.google./sheets/api/reference/rest/v4/… – 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 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 Commented Jun 17, 2017 at 6:02
• 1 here is a sample of writing into sheets: developers.google./sheets/api/samples/writing – Chandan Kumar Thakur Commented Jun 17, 2017 at 6:04

 |  Show 6 more ments

1 Answer 1

Sorted by: Reset to default 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 pleted.');
    }
}

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./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 pleted.');
        }
      }

      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.

本文标签： jqueryHow to send data from HTML form to Google Spreadsheet using JavaScriptStack Overflow