html form not writing to google sheet

Question

I am new to coding and to Stack Overflow so apologies if I am not following protocol or if I am duplicating a question. I did check the questions but didn't find anything close enough with my limited knowledge.

I have copied some code from a webpage Original code is here and amended it to suit my Google sheet & html form but I kinda got stuck. I have opened view access so you can see what I am trying to do but I cannot add another link as I don't have enough reputation yet!

I have created the form and it appears on the site. If I have the code run from the Code.gs page it works in as much as it will post "undefined" in every cell where there is a header. But from the form on the site nothing happens. If I "test web app for your latest code" and F12 I get notified that it "cannot read property of 'getpubliclock'". If I comment out the public lock lines, I get informed "cannot read property of 'createTextOutput'.

My Code.gs file is:

function doGet(e) {

    return HtmlService.createTemplateFromFile('myForm')
    .evaluate()
    .setTitle('eCPC DB v2.0')
    .setSandboxMode(HtmlService.SandboxMode.NATIVE);
};

and myForm.html is:

<div>
    <!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
    <?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>
    <form>                
        <input type="text" name="FIRSTNAME" id="FIRSTNAME" class="form-control" placeholder="First Name" required>
        <input type="text" name="LASTNAME" id="LASTNAME" class="form-control" placeholder="Last Name" required>
        <input type="text" name="DRIVERNUMBER" id="DRIVERNUMBER" class="form-control" placeholder="Driving Licence Number" required>
        <input type="email" name="EMAILADDRESS" id="EMAILADDRESS" class="form-control" placeholder="Email Address">
        <input type="text" name="CONTACTNUMBER" id="CONTACTNUMBER" class="form-control" placeholder="Telephone Number">
        <BR><BR>
        <input type="submit" id="submit-btn" value="send" onclick=doPost() class="blue">
    </form>

    <script>

    function doPost(e){
        return handleResponse(e);
    }

    function handleResponse(e) {

        var lock = LockService.getPublicLock();
        lock.waitLock(30000);

        try {
            var doc = SpreadsheetApp.openById("1bEJwGt5ixDjvIUH56jcNB5j71rSetrVcvtrjMQCrVp4");
            var sheet = doc.getSheetByName("DRV");

            var headRow = e.parameter.header_row || 1;
            var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
            var nextRow = sheet.getLastRow()+1;
            var row = [];

            for (i in headers){
                if (headers[i] == "Timestamp"){
                    row.push(new Date());
                } else {
                    row.push(e.parameter[headers[i]]);
                }
            }

            sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
            return ContentService
                  .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
                  .setMimeType(ContentService.MimeType.JSON);
        } catch(e){
            return ContentService
                  .createTextOutput(JSON.stringify({"result":"error", "error": e}))
                  .setMimeType(ContentService.MimeType.JSON);
        } finally {
            // lock.releaseLock();
        }
    }

    function setup() {
        var doc = SpreadsheetApp.getActiveSpreadsheet();
        SCRIPT_PROP.setProperty("key", doc.getId());
    }

</script>

I have tried running all the code from the Code.gs file and I have treid running it all within the tags on the myForm.html file but neither does anything. Can anyone advise me what I am doing wrong or point me to an example of a html form that writes to a Google spreadsheet that works so I can where I am failing?

Thank you!

read the official guide on how to use htmlService. you are putting server code in your client side and that will never work. — Zig Mandel
Thanks Zig. That's useful in so far as I understand it. So how on earth can I get my form embedded in my site to talk to the sheet? This is so daunting! — Ci Budr
read the html app guide. it explains the diff between .gs (server) and html/js (client) files and shows how to call from client to server code. try a sample there. once you get it running once it won't be so dauting. — Zig Mandel
do you mean this one? developers.google.com/apps-script/guides/html — Ci Budr
look at the whole guide. in there youll find developers.google.com/apps-script/guides/html/communication — Zig Mandel

Alan Wells Alan Wells · Accepted Answer · 2015-08-10T00:46:24

Don't use doPost(e) in your myForm.html file. You must use:

google.script.run.someFunctionNameHere();

to invoke a function in a .gs file. The handleResponse(e) function must also be in a .gs file.

There is probably no need for you to use doPost(e) at all. That only gets invoked when the script is first called with an HTTPS POST request. You don't need a POST request to send the form input values to the server. All you need is a function in a .gs file to receive the data in a parameter.

Make sure to look at the Forms documentation:

Google Documentation - Forms - HTML

html form not writing to google sheet

1 Answers