pleabargain · September 16, 2024 05:53
diff --git a/will send selected line from google sheets by email. At this time it will only send one selected line.txt b/will send selected line from google sheets by email. At this time it will only send one selected line.txt
 --.gs--
 function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Custom Menu')
    .addItem('Cool Trick', 'showSidebar')
    .addToUi();
 }

 function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('Send Email')
    .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
 }

 function sendSelectedRowsEmail(recipients, subject) {
  try {
    var sheet = SpreadsheetApp.getActiveSheet();
    var selection = sheet.getActiveRange();
    var headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    
    // Ensure we're not selecting the header row
    var startRow = selection.getRow() > 1 ? selection.getRow() : 2;
    var numRows = selection.getNumRows();
    var numCols = headerRow.length;
    
    // Get selected rows (excluding header if it was selected)
    var selectedRows = sheet.getRange(startRow, 1, numRows, numCols).getValues();
    
    // Prepare email content as HTML table
    var emailContent = "<html><body><table border='1' style='border-collapse: collapse;'><tr>";
    
    // Add header row
    for (var i = 0; i < headerRow.length; i++) {
      emailContent += "<th style='padding: 5px; background-color: #f2f2f2;'>" + escapeHtml(headerRow[i]) + "</th>";
    }
    emailContent += "</tr>";
    
    // Add selected rows
    for (var i = 0; i < selectedRows.length; i++) {
      emailContent += "<tr>";
      for (var j = 0; j < headerRow.length; j++) {
        emailContent += "<td style='padding: 5px;'>" + escapeHtml(selectedRows[i][j]) + "</td>";
      }
      emailContent += "</tr>";
    }
    
    emailContent += "</table></body></html>";
    
    // Send email to each recipient
    var recipientList = recipients.split(',').map(function(email) { return email.trim(); });
    for (var i = 0; i < recipientList.length; i++) {
      if (validateEmail(recipientList[i])) {
        MailApp.sendEmail({
          to: recipientList[i],
          subject: subject,
          htmlBody: emailContent
        });
        Logger.log("Email sent successfully to: " + recipientList[i]);
      } else {
        throw new Error("Invalid email address: " + recipientList[i]);
      }
    }
    
    return "Emails sent successfully!";
  } catch (error) {
    Logger.log("Error: " + error.toString());
    return "An error occurred: " + error.message;
  }
 }

 function escapeHtml(unsafe) {
  if (unsafe === null || unsafe === undefined) {
    return '';
  }
  return String(unsafe)
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#039;");
 }

 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(String(email).toLowerCase());
 }

 --sidebar.html--
 <!DOCTYPE html>
 <html>
  <head>
    <base target="_top">
    <style>
      body { font-family: Arial, sans-serif; }
      .form-group { margin-bottom: 10px; }
      label { display: block; margin-bottom: 5px; }
      input[type="text"], textarea { width: 100%; padding: 5px; }
      button { padding: 10px; background-color: #4CAF50; color: white; border: none; cursor: pointer; }
    </style>
  </head>
  <body>
    <div class="form-group">
      <label for="recipients">Recipients (comma-separated):</label>
      <textarea id="recipients" rows="3"></textarea>
    </div>
    <div class="form-group">
      <label for="subject">Subject:</label>
      <input type="text" id="subject">
    </div>
    <button onclick="sendEmail()">Send Email</button>
    <div id="result"></div>

    <script>
      function sendEmail() {
        var recipients = document.getElementById('recipients').value;
        var subject = document.getElementById('subject').value;
        google.script.run.withSuccessHandler(showResult).withFailureHandler(showError).sendSelectedRowsEmail(recipients, subject);
      }

      function showResult(result) {
        document.getElementById('result').innerHTML = result;
      }

      function showError(error) {
        document.getElementById('result').innerHTML = "Error: " + error.message;
      }
    </script>
  </body>
 </html>
	--.gs--
	function onOpen(e) {
	SpreadsheetApp.getUi()
	.createMenu('Custom Menu')
	.addItem('Cool Trick', 'showSidebar')
	.addToUi();
	}

	function showSidebar() {
	var html = HtmlService.createHtmlOutputFromFile('Sidebar')
	.setTitle('Send Email')
	.setWidth(300);
	SpreadsheetApp.getUi().showSidebar(html);
	}

	function sendSelectedRowsEmail(recipients, subject) {
	try {
	var sheet = SpreadsheetApp.getActiveSheet();
	var selection = sheet.getActiveRange();
	var headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

	// Ensure we're not selecting the header row
	var startRow = selection.getRow() > 1 ? selection.getRow() : 2;
	var numRows = selection.getNumRows();
	var numCols = headerRow.length;

	// Get selected rows (excluding header if it was selected)
	var selectedRows = sheet.getRange(startRow, 1, numRows, numCols).getValues();

	// Prepare email content as HTML table
	var emailContent = "<html><body><table border='1' style='border-collapse: collapse;'><tr>";

	// Add header row
	for (var i = 0; i < headerRow.length; i++) {
	emailContent += "<th style='padding: 5px; background-color: #f2f2f2;'>" + escapeHtml(headerRow[i]) + "</th>";
	}
	emailContent += "</tr>";

	// Add selected rows
	for (var i = 0; i < selectedRows.length; i++) {
	emailContent += "<tr>";
	for (var j = 0; j < headerRow.length; j++) {
	emailContent += "<td style='padding: 5px;'>" + escapeHtml(selectedRows[i][j]) + "</td>";
	}
	emailContent += "</tr>";
	}

	emailContent += "</table></body></html>";

	// Send email to each recipient
	var recipientList = recipients.split(',').map(function(email) { return email.trim(); });
	for (var i = 0; i < recipientList.length; i++) {
	if (validateEmail(recipientList[i])) {
	MailApp.sendEmail({
	to: recipientList[i],
	subject: subject,
	htmlBody: emailContent
	});
	Logger.log("Email sent successfully to: " + recipientList[i]);
	} else {
	throw new Error("Invalid email address: " + recipientList[i]);
	}
	}

	return "Emails sent successfully!";
	} catch (error) {
	Logger.log("Error: " + error.toString());
	return "An error occurred: " + error.message;
	}
	}

	function escapeHtml(unsafe) {
	if (unsafe === null \|\| unsafe === undefined) {
	return '';
	}
	return String(unsafe)
	.replace(/&/g, "&")
	.replace(/</g, "<")
	.replace(/>/g, ">")
	.replace(/"/g, """)
	.replace(/'/g, "'");
	}

	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(String(email).toLowerCase());
	}

	--sidebar.html--
	<!DOCTYPE html>
	<html>
	<head>
	<base target="_top">
	<style>
	body { font-family: Arial, sans-serif; }
	.form-group { margin-bottom: 10px; }
	label { display: block; margin-bottom: 5px; }
	input[type="text"], textarea { width: 100%; padding: 5px; }
	button { padding: 10px; background-color: #4CAF50; color: white; border: none; cursor: pointer; }
	</style>
	</head>
	<body>
	<div class="form-group">
	<label for="recipients">Recipients (comma-separated):</label>
	<textarea id="recipients" rows="3"></textarea>
	</div>
	<div class="form-group">
	<label for="subject">Subject:</label>
	<input type="text" id="subject">
	</div>
	<button onclick="sendEmail()">Send Email</button>
	<div id="result"></div>

	<script>
	function sendEmail() {
	var recipients = document.getElementById('recipients').value;
	var subject = document.getElementById('subject').value;
	google.script.run.withSuccessHandler(showResult).withFailureHandler(showError).sendSelectedRowsEmail(recipients, subject);
	}

	function showResult(result) {
	document.getElementById('result').innerHTML = result;
	}

	function showError(error) {
	document.getElementById('result').innerHTML = "Error: " + error.message;
	}
	</script>
	</body>
	</html>