How to Use Google Apps Script to Create a Contact List

by

Creating a contact list using Google Apps Script is a practical way to manage contacts efficiently within Google Sheets. This blog post will guide you through the steps to set up a contact list, write the necessary script, and understand each part of the code.

Step 1: Set Up Your Google Sheet

First, create a Google Sheet with columns for your contact list. For this example, we’ll use the following columns: Name, Email, Phone, and Address.

Step 2: Create a New Google Apps Script Project

  1. Open your Google Sheet.
  2. Go to Extensions > Apps Script.
  3. Rename your project to something like “Contact List Manager”.

Step 3: Write the Script to Add and Retrieve Contacts

We’ll write functions to add a new contact and retrieve all contacts from the sheet.

Code Explanation:

  • addContact(name, email, phone, address): This function takes contact details as input and appends them to the sheet.
  • getContacts(): This function retrieves all contacts from the sheet and returns them as an array of objects.

Script Code:

// Function to add a new contact to the sheet
function addContact(name, email, phone, address) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Contacts'); // Replace 'Contacts' with your sheet name
  sheet.appendRow([name, email, phone, address]);
}

// Function to retrieve all contacts from the sheet
function getContacts() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Contacts'); // Replace 'Contacts' with your sheet name
  var data = sheet.getDataRange().getValues();
  var contacts = [];
  
  // Loop through the data to create an array of objects
  for (var i = 1; i < data.length; i++) { // Start at 1 to skip header row
    var contact = {
      name: data[i][0],
      email: data[i][1],
      phone: data[i][2],
      address: data[i][3]
    };
    contacts.push(contact);
  }
  return contacts;
}

Step 4: Create a Simple Web App Interface

Now, let’s create an HTML interface for adding and displaying contacts.

  1. Click on the “+” icon next to Files in the script editor and select HTML.
  2. Name it Index.

Index.html Code:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      table {
        width: 100%;
        border-collapse: collapse;
      }
      th, td {
        border: 1px solid black;
        padding: 8px;
        text-align: left;
      }
      th {
        background-color: #f2f2f2;
      }
    </style>
  </head>
  <body>
    <h1>Contact List</h1>
    
    <form id="contactForm">
      <label for="name">Name:</label>
      <input type="text" id="name" name="name"><br><br>
      <label for="email">Email:</label>
      <input type="email" id="email" name="email"><br><br>
      <label for="phone">Phone:</label>
      <input type="text" id="phone" name="phone"><br><br>
      <label for="address">Address:</label>
      <input type="text" id="address" name="address"><br><br>
      <input type="button" value="Add Contact" onclick="addNewContact()">
    </form>
    
    <h2>All Contacts</h2>
    <table id="contactsTable">
      <thead>
        <tr>
          <th>Name</th>
          <th>Email</th>
          <th>Phone</th>
          <th>Address</th>
        </tr>
      </thead>
      <tbody>
        <!-- Contacts will be inserted here -->
      </tbody>
    </table>
    
    <script>
      function addNewContact() {
        var name = document.getElementById('name').value;
        var email = document.getElementById('email').value;
        var phone = document.getElementById('phone').value;
        var address = document.getElementById('address').value;
        
        google.script.run.addContact(name, email, phone, address);
        document.getElementById('contactForm').reset();
        loadContacts();
      }
      
      function loadContacts() {
        google.script.run.withSuccessHandler(displayContacts).getContacts();
      }
      
      function displayContacts(contacts) {
        var tableBody = document.getElementById('contactsTable').getElementsByTagName('tbody')[0];
        tableBody.innerHTML = '';
        
        contacts.forEach(function(contact) {
          var newRow = tableBody.insertRow();
          var nameCell = newRow.insertCell();
          nameCell.textContent = contact.name;
          var emailCell = newRow.insertCell();
          emailCell.textContent = contact.email;
          var phoneCell = newRow.insertCell();
          phoneCell.textContent = contact.phone;
          var addressCell = newRow.insertCell();
          addressCell.textContent = contact.address;
        });
      }
      
      // Load contacts on page load
      window.onload = loadContacts;
    </script>
  </body>
</html>

Step 5: Deploy the Web App

  1. Click on the Deploy button (the rocket icon) in the top right corner of the script editor.
  2. Select New deployment.
  3. Choose Web app.
  4. Set the Execute as option to Me and Who has access to Anyone.
  5. Click Deploy.

Step 6: Test Your Web App

After deployment, you will receive a URL. Open this URL in your browser to see the contact list application in action. You can add new contacts using the form, and they will be displayed in the table below.

Conclusion

You have now successfully created a contact list application using Google Apps Script. This app allows you to add new contacts and display all contacts in an HTML table. You can extend this basic example by adding features such as editing and deleting contacts, search functionality, and more.

About the Author

Laurence Svekis is an experienced developer and author specializing in Google Apps Script and educational content creation. He has written several books, including “Google Apps Script Sheets Custom Functions: Over 150 Apps Script Code Examples for Sheets” and “FrontEnd Code Playground: HTML, CSS, and JavaScript Exercises to Develop Your Skills”.