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

Akhil S Kumar — Sat, 04 Feb 2023 17:35:05 +0000

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 = "youremail@gmail.com";

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

An Open Source project on job search engine

Akhil S Kumar — Wed, 02 Feb 2022 17:54:00 +0000

We all learn new skills everyday, but making something out of that is the most happiest thing and making into a product gives us much more happiness and motivation to create more things.

But, do you all know how I made this and what's behind the seen of this application?

Hi, my name is Akhil pursuing Btech in Electronics and Computer Engineering at Amrita Vishwa Vidyapeetham, Amritapuri campus Kollam.

Idea!

The Idea behind this application is quite simple, since I'm a YouTuber I have a large community of people who are in search of jobs. So, the problem is that going through every notification and reading through 8 - 10 pages sounds very hard. As a results I figured out various things they wanted to know in order to submitting an application for a Job.

Technologies I used to build the product

Python-Django for Backend.
React-JS for frontend (WebApp)
React-Native for Mobile Application
GraphQL for API

Open Source?

Yes, the project is an open source. You can simply contribute to its Web Application and make a pull request. Click here to start contributing.

Soon, the backend and mobile app will be made open source.

Have a look at the project