Automate Data Entry with Chrome Extension

/ Tutorials, Use Cases

In this post, we’ll see how to automatically input data into a web form using a Google Chrome extension.

Considering an application that doesn’t have a option to import data (more common than it sounds!), we’ll create a Google Chrome extension to get data from Google Sheets and input them to different fields of a web form in a very fast and reliable manner.

This tutorial covers how to:

  • Safely expose data from Google Sheets so that other applications can retrieve it.
  • Create a Google Chrome extension to retrieve remote data and input it into a web form.

As always, the source code for this solution can be found on GitHub. The file emulating a clients management system and some fake client data are also publicly available:

Scenario

Data entry is a common operation in the daily life of a lot of people and businesses, from gathering survey answers to transcribing notes from a meeting.

One particularly important (and time-consuming!) task related to data entry is the migration of data between two systems. For example, consider that we have data about clients in Google Sheets (Figure 1) and we want to transfer it to a clients management system (Figure 2). Normally, someone would have to copy each piece of data about a client from the spreadsheet and paste it on the corresponding field in the registration form.

Clients Data
Figure 1 - Clients Data
Client Management Sample System
Figure 2 - Clients Management Sample System

Systems like this usually have a way to export and import data, like saving to files or public APIs. However, our sample systems don’t have this kind of feature (this is more common than you’d imagine!), so it is necessary to do it manually.

Fortunately, we can still automate the data entry, even though we cannot do it via an API. There are a few ways to do that, like using RPA or UI testing tools, but we can also use something we already have right in front of us: our web browser! Most people just use browsers to visit websites, but they offer a lot of flexibility via add-ons and extensions. Among many possibilities (like blocking ads and real-time translation), browser extensions can also be used to automate tasks like data entry.

In this case, our extension needs to interact with the web page as a human would do: input data into fields, select options and click the Register button. The steps are simple, but there are many clients to register, so we can certainly save some time with this automation.

What Needs to Be Done

We have two main steps to achieve our goal:

  1. Get client data from a sheet in Google Sheets.
  2. Register each client by entering data to the clients management system’s form.
In the following sections, we’ll see the implementation details of these two steps.
 

Getting Data From Google Sheets

One important decision in our solution is how the extension will have access to the data.

One option is to connect to the Sheets API and pull the data we need. Although the API is flexible and allows many other operations, we’d have to handle the authentication ourselves (we could use Chrome Identity API for that), which requires some extra steps.

A simpler option is to create a web app based on the spreadsheet. This has a few advantages:

  • We can use Apps Script to manipulate the data before exposing it.
  • We don’t have to worry about authorization and authentication to access the data. We can define that only the user has access to the web app, but the extension can still access it through the user’s logged-in session on the same browser.

Let’s continue this tutorial using this second option and take a look at how we can create this web app based on the spreadsheet with clients’ data.

Creating an Web App

There are only two requirements to deploy a Google Apps Script as a web app:

  1. The script must contain a doGet() or a doPost() function. In our case, we just want to get some data from the spreadsheet, so we need a doGet() function. 
  2. This function must return either a HTML Service or a Content Service. To make it easier to manipulate, let’s make this function return the data as a JSON string (more about this later). Since that is just text, not an HTML output, we use Content Service.

The snippet below shows the implementation of our doGet() function in a script bound to the spreadsheet:

function doGet(e) {
  const clientsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Clients');
  const clientsData = clientsSheet.getDataRange().getValues();

  return ContentService.createTextOutput(JSON.stringify(biArrayToObject(clientsData)));
}

// Based on https://mashe.hawksey.info/2018/02/google-apps-script-patterns-using-the-destructuring-assignment-syntax-and-object-arrays-to-process-google-sheet-rows/#comment-184960
function biArrayToObject(data) {
  const header = data.shift();
  const object = data.map(function (row) {
    const nextRowObject = header.reduce(function (accumulator, currentValue, currentIndex) {
      accumulator[currentValue] = row[currentIndex];
      return accumulator;
    }, {});
    return nextRowObject;
  });

  return object;
}

The first two lines of the doGet() function get all the data from the Clients sheet, which results in a two-dimensional array with rows of that sheet.

The last line of doGet() passes the two-dimensional array to an auxiliary function that transforms it into a JSON object whose keys have the same names as the columns of our table. This JSON object is finally returned as text using Content Service. Although this step is not strictly necessary, it makes it easier to manipulate the data.

After this, we only need to deploy the script as a web app. This gives us a URL that is used by the Chrome extension to retrieve the clients’ data.

Entering Data to Web Form

Once the data is ready to be retrieved, let’s create the Chrome extension to do the data entry task.

For a better experience, we want the automation to be started when the user clicks on the the extension’s icon. Once that is clicked, the data is retrieved and input into the form, one client after the other, until all of them are registered.

In terms of implementation, the extension first fetches data from the web app using a service worker, and then passes it to a content script, which is responsible to entering the data. Let’s see the details of these components in the following sections.

Creating the Extension's Manifest File

The first thing we need to create is a manifest file. This is a JSON file that contains data about the extension, such as name, description and permissions. All extensions must have a manifest file, but not all its fields are mandatory.

In our case, we’ll have the required fields (i.e., manifest_version, name and version), and add some of the recommended ones: description, icons (the images used for the icons are also on the solution’s repository), action.

Other than that, we have a few additional fields that depend on the architecture of the extension:

  • background: The extension needs to identify when the extension icon is clicked so that it register clients. For that, we define a service worker (detailed later) that listens to a click on the extension’s icon to start the automation.
  • content_scripts: The extension also has to interact with the clients management system’s page, so we define a content script (detailed later) that allow us to do that. Note that we also need to define in what page the script should be injected. In our case, this page is the sample clients management system‘s page.
  • permissions: When the extension icon is clicked, the service worker sends a message to the tab from which the click originated (i.e., the tab with the clients management system), so the extension needs the tabs permission.
{
  "manifest_version": 3,
  "name": "Chrome Extension Data Entry Demo",
  "version": "0.1.0",
  "description": "Demonstration of how to use Chrome extension to input data into a web form.",
  "icons": {
    "128": "media/logo128.png",
    "48": "media/logo48.png",
    "16": "media/logo16.png"
  },
  "action": {
    "default_icon": {
      "128": "media/logo128.png",
      "48": "media/logo48.png",
      "16": "media/logo16.png"
    },
    "default_title": "Chrome Extension Data Entry Demo"
  },
  "background": {
    "service_worker": "worker.js"
  },
  "content_scripts": [
    {
      "js": ["content.js"],
      "matches": ["*://*/SampleForm.html*"]
    }
  ],
  "permissions": ["tabs"]
}

Next, let’s implement the service worker and the content script declared in the manifest file.

Service Worker

As mentioned, we need our service worker to know when the user clicks on the extension’s icon. For that, we register a listener to the onClicked action in the worker.js file.

// When extension icon is clicked, retrieve data and send message for data entry
chrome.action.onClicked.addListener((tab) => {
  const appsScriptUrl =
    'https://script.google.com/a/macros/mshcruz.com/s/AKfycbw2ukfV11c-pTWiN4EpdsErCVNu20IBHg7J3APEKO9mkO8-teUHzqX0/exec';
  fetch(appsScriptUrl)
    .then(response => response.json())
    .then(clientsData => {
      chrome.tabs.sendMessage(
        tab.id,
        { action: 'inputClientsData', clients: clientsData }
      );
    })
    .catch(error => {
      console.error(error);
    });
});

When triggered, the listener’s event first fetches data from the web app we created before. Make sure to change the URL to the one created when the web app was deployed!

The service worker cannot interact with the clients system management system directly, and needs the content script for that. So it sends a message to the tab from which the click originated, and passes the retrieved clients’ data. As we defined in the manifest file, this tab should contain the injected content script that can handle the client registration.

Content Script

We already made the extension’s service worker send a message when the extension’s icon is clicked. Now we have to make the content script to interact with the page and perform the data entry itself.

First, this content script needs to capture the message to input data. To do that, we include a message listener and check if the message is telling us to input the clients data. If it is, we register the client:

// Input data on page's form
chrome.runtime.onMessage.addListener((message) => {
  if (message.action === 'inputClientsData') {
    const registerButton = document.getElementById('registerButton');
    for (const client of message.clients) {
      registerWhenReady(registerButton, client);
    }
  }
});

An important detail is that the form’s button is briefly disabled while the registration is happening, so we cannot click on it for a short time after clicking it once. Because of that, we need to have a way to check whether the button is enabled before we attempt the registration.

One way to do that is by checking the property disabled of the button element. If the button is disabled, we can setup a timer to retry the registration a few moments later. If it’s enabled, we just get the form elements and set their values to using the data of a given client:

// Wait until register button is enabled before entering data
function registerWhenReady(registerButton, client) {
  if (registerButton.disabled) {
    setTimeout(() => {
      registerWhenReady(registerButton, client);
    }, 500);
  } else {
    const columnNameElementIDMap = {
      "Code": 'code',
      "Name": 'name',
      "Address": 'address',
      "Country": 'country',
      "Type": 'type',
      'Discount Percentage': 'discount',
      'Credit Limit': 'limit',
      'Credit Status': 'status',
      'Payment Terms': 'terms',
      "Language": 'language',
      "Currency": 'currency',
      "Active": 'active',
    };
    for (let [key, value] of Object.entries(client)) {
      const elementID = columnNameElementIDMap[key];
      if (elementID === 'active') {
        document.getElementById(elementID).checked = (value === 'YES');
      } else {
        document.getElementById(elementID).value = value;
      }

    }
    registerButton.click();
  }
}

Note that, to make it easier to manipulate the data, we create an auxiliary object (columnNameElementIDMap) to map the original names of the sheet’s columns to the IDs used by the form’s elements. For example, data from the column Discount Percentage should be input to the element whose ID is discount. To find out what are these IDs, we can take a look at the registration page’s source code:

<form id="registrationForm" class="row g-3">
  <div class=" col-md-2">
    <label for="code" class="form-label">Code</label>
    <input type="text" class="form-control" id="code" required>
  </div>
  <div class="col-md-10">
    <label for="name" class="form-label">Name</label>
    <input type="text" class="form-control" id="name" required>
  </div>
  <!-- ... -->
  <div class="col-md-4">
    <label for="type" class="form-label">Type</label>
    <select id="type" class="form-select" required>
      <option selected disabled value="">Choose...</option>
      <option>Legal</option>
      <option>Retail</option>
      <option>Finance</option>
      <option>Government</option>
    </select>
  </div>
  <!-- ... -->
</form>

The form uses some Bootstrap classes for styling, but its elements are common to most forms: labels, inputs and selects. Most importantly, they have fixed non-random IDs, so we can safely use them as keys for the JSON created based on the clients’ data.

We can then retrieve each form field by iterating through the mapped keys of the client’s data object. By doing so, we don’t need to explicitly get each element and set its value, making our extension more robust and easier to maintain. The only exception is the checkbox that indicates whether a client is active. In this case, we change the checked attribute instead of the value attribute of the selected form field.

With this, the implementation of our extension is complete. To install it, open the Extensions page on Chrome, enable the Developer mode, click on Load Unpacked and choose the folder used for the files in this tutorial.

Then, to test everything, go to the clients management system page and click on the extension’s icon. If your user’s session has access to the web app, the extension should access the deployment URL, get the data and start registering clients.

Time Saved

Before wrapping up, let’s estimate how much time this automation can save. We assumed that it takes one minute to copy and paste data to register one client, so registering 500 clients would take a bit more than 8 hours.

The automation we created can register each client in approximately one second, so it’d take about 8 minutes to complete the task. Considering we spend one hour to implement the automation, it is still about 8 times faster than doing it manually!

Another important point is that this automation have great scalability, since it can handle a much larger number of clients without any additional implementation effort.

Summary

This tutorial showed how to automate data entry with a Google Chrome extension. We first exposed data from a spreadsheet in Google Sheets via a web app and then used a Chrome extension to fetch it and input into a web form.

Tasks like the one shown in this tutorial are very common and, although quick and simple at first, the total time to perform it can quickly add up because of the number of times it is repeated. Don’t spend the whole day copying and pasting – automate it and do something more interesting and meaningful!

As mentioned before, the complete source code for this project can be found on GitHub. Feedback and suggestions are welcomed in the comments section!

Get in touch!

Want this Automation done for you?

If don’t have time to implement it and just want to get this task done, send me a message and I can assist you with that!

Contact