Getting Started With Google App Scripts - Send Email From Google Spreadsheets

In This Modern World it would be better to have triggers to do our works automatically with less input feed and make more automated manner.and Recently,This was running in my head and concurrently i'm one of the member of Google Student club in my College and we were about to conduct a small workshop and one of the member took in-charge of getting online forms filled and select 30 students and sending them a confirmation mail to them and instruction.and it was successfully implemented in native hand written scripts in PHP and MYSQL.And there comes my idea of using Google App Scripts with Triggers in Action to Automate our Selection panel and E-mail Sending to who have registered.and suddenly gone through DOCS and tried with my mail and was sending mails in minutes from spreadsheet and thought of sharing it my followers,and recently i got request to write Google Apps scripts tutorial.

This is just Kick Start for Google App Scripts @ +i-visionblog !You can expect more business and Productivity based app scripts soon in our blog or personally contact me in mail for Other Apps Scripts for Business and Productivity.

Reference : Demo | Download Script

Motive :

Our Goal in this post is to send Email to the peoples who have submitted the form or recorded response and as well as Admin.The mail will be delivered from your mail inbox to the client through Gmail API.

Prerequisites :

• A Google Account with Google Drive Enabled.
• A Little knowledge in JavaScript to handle Arrays and functions.
• Google Forms and Spreadsheet.

And little patient to test and Debug the code and check the log for error handling! 

Procedure :

Setup up A Basic Form with Google Forms.

• Open the link create Simple Form with Name and Email as TEXT attribute and make it as mandatory by ticking required.
• Then publish the form public and test whether it is working and accepting the form submission over public.

select Script Editor

Setting up Basic Script for sending the Mail with Script editor in Spreadsheet :

• Go to the corresponding the Response Form Spreadsheet and open and view in Browser.
• under Tools > Script Editor select it and will open the new tab with Google scripts page.
• create new blank Project in Google App Script Editor.
• And then with default code.gs file will be prompting you to type the code.
• So,it's time now to write down the code for Responding the user with the mail who submitted the form with our function written in Google App Script Editor.

 Code : 

 function onFormSubmit(e) {

  var timestamp = e.values[0];

  var mailaddress = e.values[2];

  var body = e.values[1];

  MailApp.sendEmail(mailaddress,"TEST MAIL",body);

  }

The Above Code is self explanatory one ! however you could get the the response from the e variable as array e.values and with MailApp.sendEmail function you could send the email by passing the parameters as mail address ,Subject and body.note that always the first array value will be timestamp of submission and next will be your form values in according your arrangement in Google Form.

Steps to Execute :

Follow the steps correctly to test and execute the script.

select current project trigger

Click Current Trigger Project in Google App Script page Toolbar.

You will be listed with Triggers with corresponding functions written in code.gs,it must be mapped with corresponding events like spreadsheet on view,edit,update and adding entries and form submissions.

set up trigger 

• Click on Notification > and change it to immediately for crash Reports 

change to immediately to check your errors

• Click Okay and in main Project Trigger confirm your identity by accepting the OAuth from google for delivering the mails on your behalf and with your name.
• Now view the live form and test it in your browser and if all goes well just you will be getting mail who submitted the form with correct mail id.
• If you need admin Email also just copy the same function and replace with your email hardcoded so that you may also get mail whenever the form is submitted.
• If something you(Admin) will receive the script Failure Exception details via Email update immediately since we set immediately in our notification of current project triggers.

My Result on Testing the Google Form :

Test Mail successfully Received

thus have a live Demo from above given link in reference section and download the code and try yourself.Always validate the input from the client! side for improving the security.

Note: This post deals with the basic of Google App Script usage.You can do a lot with Google App Scripts almost you can Automate all your activities.Let us see about it in future post.subscribe our blog for updates and recent posts.

For Bugs/Hugs/comments/doubts/updates and projects just drop mail to [email protected] or chat with me in Facebook/Google+ chat and for updates and interesting tweets/updates follow me in Twitter.

Share is care.Feel free to comment

Read More