Mass Data Entry into Web Forms Using Google Apps Script

Recently I was presented with a scenario where a large amount of data that was stored in a spreadsheet needed to be entered into a system with no mass upload feature.  Since the alternative was having someone wear the paint of their keyboard copying and pasting, I needed to automate it.

Most systems provide a way to do this via the uploading of a CSV file.  Some small niche products either lack this feature or require the user to pay for the provider to add the data on the back end.  This system, which shall remain nameless, had this issue.  Here is the interface I was working with:

interface

I have hundreds of names that need to be entered for each year, going back several years.  There were two problems I needed to solve.

Problem #1 – There are only 10 fields to enter data into.

Problem #1 is really easy to fix using the iMacros for Chrome extension.  iMacros allows you to record and play back interactions with a web browser (like clicking a button several times).  Here are the steps I used to get the number of text boxes I needed.

  1. Install the iMacros for Chrome extension.
  2. Go to the page that contains the web interface you need to interact with.
  3. Click the iMacros extension icon to open the interface.
  4. Click Record then Record Macro.
  5. Click the button in the web interface that you would rather not repetitively press (in my case, the “Add More Name” button).
  6. Click Stop.
  7. The iMacros editor will open.  You should have 3 lines of code.
    1. Line 1 is version information, ignore it.
    2. Line 2 tells the web browser to go to the page you are on.  Since we are going to loop this code, we don’t want it opening the web page every time.  To prevent that, simply delete this line.
    3. Line 3 is what “clicks” the button.
  8. Once you have removed line 2, click the Save & Close button.
  9. With the web interface of the system you are using, select #Current.iim (the default macro you just created) in the left column.
  10. Click Play Macro.  This should perform the click, and the result should be displayed (in my case, 2 input boxes are added).
  11. I want to add 100 more boxes however.  Because each click adds two boxes, I’ll need to run this macro 50 times.  Under “Play macro repeatedly”, change “Max” to “50” and press Play Loop.  The macro should run 50 times, and the desired result should be shown in the web interface.

Here is a short video of this in action:

Problem #2 – Get the information from the spreadsheet into the web form.

Here is what my spreadsheet looks like:

googlesheet

I have all the names I want to enter in column ‘A’ of a Google Sheet – all I need to do is automate their entry into all the fields I generated above.  The way I chose to do this is with a keyboard emulator.  I often use keyboard emulators to perform repetitive tasks – they allow you to write code that emulates the pressing of keys on your keyboard.  One example is a physical keyboard emulator I use (USB Rubber Ducky) to automate the process of enrolling Chromebooks.

In this case, I am going to use a software emulator.  WinKeySim is the emulator I chose for this project, there are several other freeware versions available.

In order for this to work, we need to create the list of commands to put into WinKeySim.  Based on WinKeySim’s syntax, the pseudo-code would look something like this:

1 open the command with ["
2 set x to 1
3 add the name from row x
4 if x != the last row
  wait for 200ms [!200] and send a [tab] to go to the next field
  x++
  repeat from 3
5 else this is the last row so close the command with "]

I won’t go over the intricacies Google Apps Script here.  I will tell you it is a very powerful tool, and if you use Google Apps and have a need to automate, it will prove very useful to you.  Based on my spreadsheet above, here is the code I used to build the command I used with WinKeySim.

function myFunction() {
 //get the active spreasheet
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 //get the sheet named 'Sheet1'
 var sheet = ss.getSheetByName("Sheet1");
 //get the row number of the last row
 var lastRow = sheet.getLastRow();
 
 //create a new google doc to store the emulator commands in
 var doc = DocumentApp.create('Honor Roll Students');
 var body = doc.getBody();
 var text = body.editAsText();
 
 //start the document with [" (syntax for WinKeySim)
 text.appendText('[\"');
 //loop through each row of the spreadsheet
 for(i=1;i<=lastRow;i++){
 //get the name in column A
 var name = sheet.getRange(i,1).getValue();
 //append the name to the sheet
 text.appendText(name);
 //if we are not at the last row, add a 200ms delay and
 //send a tab to change fields (syntax for WinKeySim)
 if(i !== lastRow){
 text.appendText('[!200][tab]');
 }
 }
 //if this is the last row, end the docuemnt with "]
 if(i !== lastRow){
 text.appendText('"]');
 }
}

When executed on the spreadsheet of names, this will spit out a new Google Doc into my Google Drive containing the emulator command I will use to input the data.  It will look something like this:

googledoc

I can then paste that command into WinKeySim and the data will be entered automatically.  Check out the video below to see it in action.

If you have any questions about this, please leave a comment below!

2 thoughts on “Mass Data Entry into Web Forms Using Google Apps Script

  1. What do you think is easier to use for this type of activity, google apps script or imacros? When would you use one versus the other?

  2. Michael – I used them both. iMacros is good for interacting with the browser, Google Apps Script is good for compiling the command (from a data set) to enter into the browser using a keyboard emulator (like I did here with WinKeySim)

Leave a Reply