Integrating Google Forms with Mailman

Recently I was researching new email list solutions for the University of Canterbury Canoe Club. Our existing setup involves a Google Form which new members fill out, with data accessible in a Google Spreadsheet. Mailman is used for our mailing list. Currently the Webmaster is notified when a new form is submitted and manually puts the corresponding email address into the mailing system. This seemed cumbersome to me, and set me searching for a better approach.

The most promising alternative I found was MailChimp, a very popular email marketing tool. MailChimp has many options, takes steps to combat emails being wrongly detected as spam and can host a built-in signup form. However the flashy HTML emails it generates seemed overkill for our needs, and as it is designed for marketing campaigns, does not support the concept of any person on the mailing list being able to send email to it – a feature of Mailman that is utilized by the club (with emails coming to the Webmaster for moderation). Additionally, committee members wanting to send emails would have had to log into the MailChimp web application with a shared password, rather than using their personal email address and mail client. This presented two problems: non-technical committee members would have had to learn how to use a new tool, and there would not be a reply address matched to the actual person sending the email.

In light of these findings, I set about finding a way to integrate Google Forms with Mailman. I came across the answer in the form of Google Apps Scripts, a service provided by Google which allows chunks of Javascript code to be written against an API and integrate with a number of Google services – in my case Google Forms/Spreadsheets. Here I present the steps for taking the email address out of the form submit and submitting it to your Mailman server as a new subscriber.

I presume you have already created a Google Form which asks users to enter an email address. In the corresponding spread sheet, choose Tools -> Scripts Editor.

Paste in the following code, substituting the following strings with those of your setup:

• SheetName eg. Sheet1
• cellnumber eg. 5
• yourdomain eg. http://wikipedia.org
• yourmailinglist eg. allsubscribers
• yourpassword eg. password
• webmaster@yourdomain eg. webmaster@wikipedia.org

function SubscribeEmailOfNewMemberToMailman() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("SheetName");
  var row = sheet.getLastRow();
  var range = sheet.getRange(row,cellnumber);
  var email = range.getValue().trim();
  range.setValue(email); //Write the trimed email back to the spreadsheet.

  if (validateEmail(email)) {

UrlFetchApp.fetch(yourdomain/mailman/admin/yourmailinglist/members/add?subscribe_or_invite=0&' +
                                   'send_welcome_msg_to_this_batch=1&send_notifications_to_list_owner=0&subscribees_upload='+email+'&adminpw=yourpassword);

    	Logger.log("Subscribed {8b0b96060afa46d1ca7c90aeb24f8da085e11471ba2cd3d6bf9cf57471789b98}s with email {8b0b96060afa46d1ca7c90aeb24f8da085e11471ba2cd3d6bf9cf57471789b98}s.", name, email);

  } else {
    MailApp.sendEmail(
      "webmaster@yourdomain",
      "Failed to auto-subscribe new member",
      "Email regex failed for new member '" + name + "' with email '" + email + "'.");
  }
}

function validateEmail(email) {
    var re = /^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;
    return re.test(email);
}

The behaviour of this script is as follows:

1. The email address is pulled from the email column in the last row of the spreadsheet.
2. The email string is trimmed in case the user accidently left some whitespace at the start or end.
3. The email is feed into the validateEmail function which uses a regular expression to check if the email is of a valid form. It returns a Boolean.
   1. If it returns True. The email is past to the Mailman server in the URL. (Note: the mailing list password is sent in plain text in the URL which is probably not very secure). A log is made of this subscription attempt to help with any troubleshooting in the future.
   2. If it returns False an email is sent to the Webmaster with the problematic email for them to follow up on manually.

The final step is to tell the Google Spreadsheet to call the SubscribeEmailOfNewMemberToMailman function every time the form is submitted. To do this, from the scripts editor select Resources->Current script’s triggers. In that dialogue, select the function to invoke – in this case ‘SubscribeEmailOfNewMemberToMailman’, In the next two boxes, select ‘From spreadsheet’ and ‘On form submit’ respectively.

With this integration with Mailman and the club’s bank account and payment information supplied in the confirmation page of the form our signup process is now completely automated, making for a happy Webmaster!

The Google Apps Scripts platform is very well documented, and I’d encourage you to modify this script to suit your needs. One addition I made is to call a PHP function on the club website, passing through the user’s name and email address. The PHP function then creates a new username and password for the new member, and notifies them by email.

I hope you find this useful!