Assume that you are running a very popular online service and you have many clients who have subscribed to your service. It will be a very hard thing if you want to send a newsletter to all of them. Well, it won’t be an issue if the number of subscribers is low. But if the number is high, you are actually putting a very high load on the server. This forces you to buy extra servers for doing this task or rely on a third party service provider who may charge for using the service.

This edition of techblog guides you how to do this using Google spreadsheet.

Why not Google apps for domain?

You might be wondering why can’t one opt for the email service offered by Google apps. Well, if you use that email account for sending emails to your clients or users, you can only send a very limited number of messages. Google will temporarily disable the account if you send a message addressed to more than 500 recipients (if you are using  a POP3 client, then the maximum is 100).

But this is not the case if you are using an API based method (like in spreadsheet). You can make a maximum of 7000 mail API calls and you can send emails up to 2000 users (recipients) a day.

Setting up Spreadsheet

Go to Google docs and create a new spreadsheet (if you want to use your website’s email address, please use google apps for domains and follow these steps). And add some sample entries under the new columns created – say, Email and Message.

Now go to Tools –> Scripts –> Script editor

And enter the following code in the Script editor (that has been popped up in the new window)

function sendemail() {

  var techblog_samplesheet = SpreadsheetApp.getActiveSheet() , first_row_no = 2, total_no_rows = 2 ;
  var getdata =  techblog_samplesheet.getRange(first_row_no, 1, total_no_rows, 2)
  var sample_data = getdata.getValues();
  for (i in sample_data) {
    var row = sample_data[i];
    var email = row[0];
    var message = row[1];
    var subject = "Techblog Sample email: Testing Google spreadsheet options ";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Please note that the explanation of the script is given at the end of this tutorial.

After entering the script, go to Run –> Run selected function and authorise the script.

Now select ‘Run selected function’ again. 

Google will now send the email to the users in the list.

An alternative you can consider is creating a mailing list with Google Groups. But you won’t be able to use the email id attached to your domain name.

Explanation of the code

The function, sendmail(), is a very simple Javascript function that uses the APIs provided by Google. We created a variable techblog_samplesheet and used this to target the current (working) spreadsheet. Then, we used two more variables (first_row_no , total_no_rows) to define the first row of the ‘list’ and the total number of rows (the values are provided in the next step). If you  have more than 2 users in the list, you need to change the values allocated to these variables while calling the API function getRange() – which is used to select the rows.

Now, we use iteration to get the email address and message from each row. Then we use the API function sendEmail() for sending the email to each of these users.

Real life situation

Typically you will have all your contacts in a CSV file (some of you might be aware of the fact that you can export your Gmail / Google mail contacts as a CSV file). If you want to send a newsletter, you can import this CSV file to Google spreadsheet and copy the same message for all the users. There are different ways by which you can improvise this further (say by replacing the generic salutation by the user’s name). I’ll be covering these topics in another edition.