How to Create an App From Your Google Sheets Using Google Apps Script

Image by Editor | Ideogram

Google Apps script is a powerful and useful tool to automate tasks, extend Google Sheets functionalities, and even it can create web apps. Google Apps Script creates web applications without requiring an external server. It is a diverse platform and code editor and supports different programming languages. In this article, we will show how to create a web app using Google Sheets Apps Script.

What is Google Apps Script?

Google Apps Script is a cloud-based application development platform that creates web applications while integrating with Google Workspace. It mainly uses JavaScript to automate tasks and enhance spreadsheet functionalities. You can use it as a bridge between your spreadsheet data and the web interface you want to create. Without installing anything you will get a code editor that runs on Google’s servers.

Step 1: Setting Up Google Sheet

Let’s create a web app that allows users to submit feedback data using a form directly to Google Sheets.

Open your Google Sheets app and create a new spreadsheet.
Name the sheet to Feedback Data and insert a few column headers.
- Name
- Email
- Message

Step 2: Open and Write Code in Google Apps Script

Open Google Apps Script

Go to the Extensions tab >> select Apps Script.

Click on Code.gs >> delete any existing code and replace it with this script.

function doGet() {
  return HtmlService.createHtmlOutputFromFile('FormPage')
    .setTitle("Submit Your Data")
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function processForm(formData) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses");
  sheet.appendRow([formData.name, formData.email, formData.message]);
  return "Data submitted successfully!";
}

Explanation:

doGet(): Loads an HTML page (FormPage.html) when the web app is accessed.
processForm(formData): Saves the submitted data to Google Sheets.

Step 3: Creating the HTML Form

In the Apps Script editor, select File >> New > HTML and name it “FormPage”.
Paste the following code inside FormPage.html.

<!DOCTYPE html>
<html>
<head>
    <title>Google Sheets Web App</title>
    <script>
        function submitForm(event) {
            event.preventDefault();

            var formData = {
                name: document.getElementById("name").value,
                email: document.getElementById("email").value,
                message: document.getElementById("message").value
            };

            google.script.run.withSuccessHandler(response => {
                document.getElementById("status").innerText = response;
                document.getElementById("dataForm").reset();
            }).processForm(formData);
        }
    </script>
</head>
<body>
    <h2>Submit Your Details</h2>
    <form id="dataForm" onsubmit="submitForm(event)">
        Name: <input type="text" id="name" required><br>
        Email: <input type="email" id="email" required><br>
        Message: <textarea id="message" required></textarea><br>
        <button type="submit">Submit</button>
    </form>
    <p id="status"></p>
</body>
</html>

Explanation:

Based on your required data type and field you can create a form.
We created a simple form with Name, Email, and Message fields.
When the form is submitted,
- Code captures the form data.
- Then send it to processForm() in the Apps Script.
- Finally, it displays a success message when submission is complete.

Step 4: Deploying the Web App

From the upper right corner click on Deploy >> select New Deployment.

Under Select type, choose Web App.
Configuration:
- In the Description field: Insert the description of your task.
- In Execute as filed: select (your Google account).
- In the Who has Meaccess field: select Anyone.
Select Deploy and authorize the script.

A New deployment dialog box will appear;

Copy the Web App URL.
Click Done.

Step 5: Accessing the Web App

Open the Web App URL in a browser.
Fill out the form and submit it.

Now go back to your Google Sheet, the data will appear instantly.

Output:

Advantages and Limitations of This Approach

Advantages:

In Google Sheets there is no need for external hosting, everything is managed inside Google Apps Script.
It offers fast and seamless integration with Google Sheets.
No need to deal with API keys or Google Cloud Console which makes it beginner-friendly.
Easy to update, maintain, and collaborate.
Free to use for simple applications and only requires an internet connection.

Limitations:

It supports basic UI. It has limited UI customization options compared to full-stack web development for advanced styling.
With limited control over permissions, you must set script permissions correctly. Permissions must be managed carefully to ensure security.

Conclusion

Google Apps Script offers a simple way to turn a Google Sheet into an interactive web app. You can use Apps Script to build a simple data entry system, feedback form, contact form, an API, or a reporting tool. This method allows you to do so without extra hosting costs or complex setup. Explore and customize the script to suit your specific needs.

Get FREE Advanced Excel Exercises with Solutions!