dangtrinhnt/googleemailscript.gs

## googleemailscript.gs
// Trinh Nguyen, Nov 18, 2014
function EmailFormConfirmation() {

  // name of the response sheet
  var sheetname = "sheet1"
  // insert a blank column at the end of the response sheet
  // store the sent mail result
  // add this formula to the first row of this column:
  // =indirect("AG"&counta(A1:A))
  var columnnumber = 34

  // get spreadsheet by ID of the reponse spreadsheet
  var myspreadsheet = SpreadsheetApp.openById('11BvY139qDAc06KxJrSUowCRwPNKVgNgSsPtmjGhOPQ');
  //var myspreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = myspreadsheet.getSheetByName(sheetname);
  var email = sheet.getRange(1,columnnumber).getValue();

  // Determines row number of most recent form submission and sets it as "lastrow"
  var lastrow = -1;
  if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {
    lastrow = sheet.getMaxRows()
        }
  else {
      var count = 0
      for (var i = 0; i < sheet.getMaxRows(); i++) {
        if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {
          lastrow = sheet.getMaxRows()-i
          break;
        }
      }
   }
  Logger.log("Last row: %s", lastrow);

  var subject = "Customer Feedback"

  // get the column names
  var columns = sheet.getRange(1,1,1,33).getValues()[0];
  var data = sheet.getDataRange().getValues()[lastrow-1];


  var message= '<html><body>';
  for (var keys in columns) {
    var key = columns[keys];
    message += '<p>+ ' + key + ': ' + data[keys] + '</p>'
  }

  message += "</body></html>"

  MailApp.sendEmail(email, subject, "", {htmlBody: message});

  sheet.getRange(lastrow,columnnumber,1,1).setValue("Email Sent");
}
	// Trinh Nguyen, Nov 18, 2014
	function EmailFormConfirmation() {

	// name of the response sheet
	var sheetname = "sheet1"
	// insert a blank column at the end of the response sheet
	// store the sent mail result
	// add this formula to the first row of this column:
	// =indirect("AG"&counta(A1:A))
	var columnnumber = 34

	// get spreadsheet by ID of the reponse spreadsheet
	var myspreadsheet = SpreadsheetApp.openById('11BvY139qDAc06KxJrSUowCRwPNKVgNgSsPtmjGhOPQ');
	//var myspreadsheet = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = myspreadsheet.getSheetByName(sheetname);
	var email = sheet.getRange(1,columnnumber).getValue();

	// Determines row number of most recent form submission and sets it as "lastrow"
	var lastrow = -1;
	if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {
	lastrow = sheet.getMaxRows()
	}
	else {
	var count = 0
	for (var i = 0; i < sheet.getMaxRows(); i++) {
	if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {
	lastrow = sheet.getMaxRows()-i
	break;
	}
	}
	}
	Logger.log("Last row: %s", lastrow);

	var subject = "Customer Feedback"

	// get the column names
	var columns = sheet.getRange(1,1,1,33).getValues()[0];
	var data = sheet.getDataRange().getValues()[lastrow-1];


	var message= '<html><body>';
	for (var keys in columns) {
	var key = columns[keys];
	message += '<p>+ ' + key + ': ' + data[keys] + '</p>'
	}

	message += "</body></html>"

	MailApp.sendEmail(email, subject, "", {htmlBody: message});

	sheet.getRange(lastrow,columnnumber,1,1).setValue("Email Sent");
	}