Google apps script for form data to gsheet

apps-script.js

// Submit form data directly to Google sheets
// 
// (Based on http://railsrescue.com/blog/2015-05-28-step-by-step-setup-to-send-form-data-to-google-sheets/)
//
// ## Setup
//
//  1. Create a Google spreadsheet and name it
//  2. Choose Tools > Script editor
//  3. Replace the sample code with the contents of this file
//  4. Name the script project something like "Submit form data to Google sheet" (click on 'Untitled project' to change name)
//  5. Choose Run > setup
//  6. Approve the authorisation prompt
//  7. Choose Resources > Current project's triggers, and create a trigger for `doPost` + `From spreadsheet` + `On form submit`
//  8. Choose Publish > Deploy as web app. Project version 'new', access for 'anyone, even anonymous', execute as 'me'
//  9. Copy the provided endpoint URL
// 10. Send POST data to the endpoint as `application/x-www-form-urlencoded` (query string in request body)
//
// ## POST format
//
// Send POST data with fields that match the column headers of your spreadsheet, lowercased.  Some fields also have some special meanings:
//
// - `sheet_name`: Name of sheet within spreadsheet doc.  Defaults to 'Sheet1'
//
// Some column names in your spreadsheet are also special:
//
// - `id`: Will be populated normally if you send an `id` field from your form POST, but
//   if not, any id column will be populated by an auto-generated UUID which will then
//   be returned in the JSON response
// - `timestamp`: Will be populated with the time when the submission was received.
//   Cannot be overridden by sending a timestamp field in the form POST.
//
// ## Troubleshooting
//
// - If data is not appearing in the sheet, check that you are sending it in url-encoded format, not multipart/form-data
// - If you don't see the 'Anyone, even anonymous' option, your Google Apps domain admin has disabled public sharing
//
////////////////////////////////////////////////////////////////////////////////////////////////////////

// New property service
var SCRIPT_PROP = PropertiesService.getScriptProperties();

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

function handleResponse(e) {
    
  // Get public lock, one that locks for all invocations
  // (https://gsuite-developers.googleblog.com/2011/10/concurrency-and-google-apps-script.html)
  var lock = LockService.getPublicLock();
  
  // Allow the write process up to 30 seconds
  lock.waitLock(30000);

  try {
    
    // Generate a (not very good) UUID for this submission
    var submissionID = e.parameter.id || 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
      var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
      return v.toString(16);
    });
    
    // Open the spreadsheet document and select the right sheet page
    var sheetName = e.parameter.sheet_name || 'Sheet1';
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(sheetName);

    // Get column headers from spreadsheet
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow() + 1; // Get next available row
    var row = [];
    
    // Loop through the column headers and find matching fields from the form submission to populate a new row
    for (i in headers) {
      if (headers[i].toLowerCase() == "timestamp") {
        row.push((new Date()).toISOString());
      } else if (e.parameter[headers[i].toLowerCase()] !== undefined) {
        row.push(e.parameter[headers[i].toLowerCase()]);
      } else if (headers[i].toLowerCase() == "id") {
        row.push(submissionID);
      } else {
        row.push('');
      }
    }
    
    // More efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    
    // Return result in JSON
    return ContentService
      .createTextOutput(JSON.stringify({"result":"success", "row": nextRow, "id": submissionID}))
      .setMimeType(ContentService.MimeType.JSON)
    ;

  } catch(e) {
    
    // If an error occurs, return it in a JSON wrapper
    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());
}

page-script.js

document.querySelectorAll('form[data-gsheet]').forEach(elForm => {

    const inputs = Array.from(elForm.querySelectorAll('input, button, select, textarea'));
    const gsheetURL = elForm.dataset.gsheet;

    elForm.addEventListener('submit', submitEvent => {

        submitEvent.preventDefault();
        submitEvent.stopPropagation();

        const data = new FormData(elForm);
        const query = new URLSearchParams();
        Array.from(data.keys()).forEach(k => query.set(k, data.get(k)));
        const reqData = { method: 'POST', body: query };

        inputs.forEach(elInp => elInp.setAttribute('disabled', 'disabled'));

        fetch(gsheetURL, reqData)
            .then(resp => resp.json())
            .then(data => {
                if (data.result === 'success') {
                    inputs.forEach(elInp => elInp.removeAttribute('disabled'));
                    elForm.classList.add('survey--done');
                    setTimeout(() => elForm.classList.remove('survey--done'), 1500);
                } else {
                    alert(data.result || data);
                }
            })
        ;
    });
});

Is there any reason not to use Utilities.get_uuid in order to get a UUID?

Setting the project's triggers is now under Edit > Current Project's Triggers

Hi there, is a way to automatically run a function as form content is delivered to sheets,like when using Google forms uses a trigger form activation ?