Send Data From Website to Google Spreadsheet using JavaScript & Apps Script

One of the common ways to store user data is through a web form. However, managing the collected data can be time-consuming and challenging, especially when you have a large amount of it. Fortunately, with Google Spreadsheet, you can easily store and manage your data. In this tutorial, we'll show you how to send website form filled data to a Google Spreadsheet using JavaScript and Apps Script.

Step 1: Create a Google Spreadsheet

To start, create a new Google Spreadsheet. You can create a new spreadsheet by going to Google Drive and clicking on the "New" button. From the drop-down menu, select "Google Sheets."

Step 2: Set Up the Spreadsheet

Once you've created a new spreadsheet, set up the columns that correspond to the form fields. For example, if you have a form with "Name," "Email," and "Message" fields, you should create three columns in the spreadsheet with the same headings.

Step 3: Create a New Google Apps Script

Next, you need to create a new Google Apps Script. To do this, click on the "Tools" menu in the spreadsheet, then select "Script editor." A new tab will open with a blank script.

Step 4: Add the Code

Now, it's time to add the code to your Google Apps Script. Replace the default code in the script editor with the following code:

var TO_ADDRESS = "[e-mail]";

function formatMailBody(obj, order) {
  var result = "";
  if (!order) {
    order = Object.keys(obj);
  }

  for (var idx in order) {
    var key = order[idx];
    result += "<h4 style='text-transform: capitalize; margin-bottom: 0'>" + key + "</h4><div>" + sanitizeInput(obj[key]) + "</div>";

  }
  return result; 
}

function sanitizeInput(rawInput) {
   var placeholder = HtmlService.createHtmlOutput(" ");
   placeholder.appendUntrusted(rawInput);

   return placeholder.getContent();
 }

function doPost(e) {

  try {
    Logger.log(e);
    record_data(e);

    var mailData = e.parameters;

    var orderParameter = e.parameters.formDataNameOrder;
    var dataOrder;
    if (orderParameter) {
      dataOrder = JSON.parse(orderParameter);
    }

    var sendEmailTo = (typeof TO_ADDRESS !== "undefined") ? TO_ADDRESS : mailData.formGoogleSendEmail;

    if (sendEmailTo) {
      MailApp.sendEmail({
        to: String(sendEmailTo),
        subject: "Contact form submitted",
        htmlBody: formatMailBody(mailData, dataOrder)
      });
    }

    return ContentService
          .createTextOutput(
            JSON.stringify({"result":"success",
                            "data": JSON.stringify(e.parameters) }))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(error) {
    Logger.log(error);
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": error}))
          .setMimeType(ContentService.MimeType.JSON);
  }
}

function record_data(e) {
  var lock = LockService.getDocumentLock();
  lock.waitLock(30000);
  try {
    Logger.log(JSON.stringify(e)); 
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheetName = e.parameters.formGoogleSheetName || "responses";
    var sheet = doc.getSheetByName(sheetName);

    var oldHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var newHeader = oldHeader.slice();
    var fieldsFromForm = getDataColumns(e.parameters);
    var row = [new Date()];
    for (var i = 1; i < oldHeader.length; i++) {
      var field = oldHeader[i];
      var output = getFieldFromData(field, e.parameters);
      row.push(output);
      var formIndex = fieldsFromForm.indexOf(field);
      if (formIndex > -1) {
        fieldsFromForm.splice(formIndex, 1);
      }
    }
    for (var i = 0; i < fieldsFromForm.length; i++) {
      var field = fieldsFromForm[i];
      var output = getFieldFromData(field, e.parameters);
      row.push(output);
      newHeader.push(field);
    }

    var nextRow = sheet.getLastRow() + 1;
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    if (newHeader.length > oldHeader.length) {
      sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
    }
  }
  catch(error) {
    Logger.log(error);
  }
  finally {
    lock.releaseLock();
    return;
  }

}

function getDataColumns(data) {
  return Object.keys(data).filter(function(column) {
    return !(column === 'formDataNameOrder' || column === 'formGoogleSheetName' || column === 'formGoogleSendEmail' || column === 'honeypot');
  });
}

function getFieldFromData(field, data) {
  var values = data[field] || '';
  var output = values.join ? values.join(', ') : values;
  return output;
}

Step 5: Publish the Script as a Web App

The final step is to publish the script as a web app. To do this, click on the "Publish" menu in the script editor and select "Deploy as web app." You'll need to grant the necessary permissions to access your Google Spreadsheet.

Step 6: Connect the Form to the Google Apps Script

Finally, you need to connect the form to the Google Apps Script. You can do this by adding a simple JavaScript to your form that will call the Google Apps Script and submit the form data. Add the following code to your form:

<script>
    $("#submit-form").submit((e) => {
      e.preventDefault();
      $.ajax({
        url: "your exec id",
        data: $("#submit-form").serialize(),
        method: "post",
        success: function (response) {
          document.getElementById("submit-form").reset();
          document.getElementById("outputMessage").classList.add('success');
          document.getElementById("outputMessage").innerHTML = "You have been added to our mailing list successfully";
          return false
        },
        error: function (err) {
          document.getElementById("outputMessage").classList.add('fail');
          document.getElementById("outputMessage").innerHTML = "Our Server is busy right now. Try again later!"
          return false
        },
      });
    });
  </script>

And that's it! You've successfully set up a connection between your form and Google Spreadsheet using JavaScript and Apps Script. Every time a user submits your form, the data will be automatically added to the Google Spreadsheet, where you can easily manage it.

In conclusion, using Google Spreadsheet to store user data collected from a web