Google Apps Scripts Spreadsheet Submission

A.markdown

Post To Google Sheets -> Scripts Submission Handler

A Google Apps Script to handle submissions sent to a Google Sheet via a webform, and send emails each time a submission is received.

Largely inspired by Google Sheets as a Database – INSERT with Apps Script using POST/GET methods.

This script must be added to your Google Sheet via 'Tools' -> 'Script Editor...'.

Your spreadsheet's column names must match the field names on your firm to properly submit data.

Submitting locally via a Node.js may require data to be posted as a query parameter for the handler to accept any data.

submission.js

/*
 * When using this script for the first time, use Run -> setup to link it 
 * to the active spreadsheet, and then publish it as a web app using
 * Publish -> deploy as a web app
 */

var SHEET_NAME = "Sheet1";
var SCRIPT_PROP = PropertiesService.getScriptProperties();
var ADMIN_EMAILS = ['[email protected]'];

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

function doPost(e){
  
  handleResponse(e);
  
  // for testing posting
  return ContentService.createTextOutput(JSON.stringify(e.parameter));
}

function handleResponse(e) {
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  
  
  try {
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    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 = Object.keys(headers).map(function(key) {
      return headers[key] === 'Timestamp' ? new Date() : e.parameter[headers[key]];
    });

    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    
    sendMail(headers, 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());
}

function sendMail(headers, data) {
  var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
  var url = doc.getUrl();
  var details = Object.keys(headers).reduce(function(acc, key, i) {
    return acc + '<strong>' + headers[key] + '</strong>: ' + data[i] + '<br/>';
  }, '');
  var msg = '<p>We have a new submission!</p>' +
    '<p>' + details + '</p>' +
    '<a href=' + url + ' target="_blank">view the spreadsheet</a>';
  
  ADMIN_EMAILS.map(function(email) {
    MailApp.sendEmail({
      to: email,
      subject: 'Your Website Submission', 
      htmlBody: msg,
      name: 'Your Website Submission Spreadsheet',
      replyTo: '[email protected]',
    });
  });
}





function testPost() {
  var url = ScriptApp.getService().getUrl();
  
  var payload = {
    // fields to submit
  };
  
  var options = {
    "method"  : "POST",
    "payload" : payload,
    "followRedirects" : true,
    "muteHttpExceptions": true,
  };
  
  var result = UrlFetchApp.fetch(url, options);
  
  if (result.getResponseCode() == 200) {
    var params = JSON.parse(result.getContentText());
    
    Object.keys(params).map(function(key) {
      Logger.log(params[key]);
    });
  }
}