Send Text and Emails from a Spreadsheet

By
If you use spreadsheets to hold customer, you have probably wanted send emails or text messages directly from your spreadsheet. If you haven’t wanted to, maybe you do now! In this two-part video tutorial, I will show you how make a Google Sheet send an email or text message at the click of a button.

 In Part 1 of the Employee Database tutorial, I explain how to format your Google Sheet.

  

 Part 2 explains how to use Google Apps Script to automate sending emails and text messages directly from your spreadsheet.



Click here to access the sample sheet.
Script:
function topRow() {
var rowNum = 8; // Row 7 is the actual top row, but it is being skipped, so new rows will copy the data validation rules
  return rowNum
}
function menuRow() {
var rowNum = 6; // Column title row
  return rowNum
}
function eSubject() {
var cellAddr = "F1"; // Cell address
  return cellAddr
}
function eMessage() {
var cellAddr = "F3"; // Cell address
  return cellAddr
}

function anotherfunction() {
var sheet = SpreadsheetApp.getActiveSheet(),
    range = sheet.getRange(2,5,sheet.getLastRow(),1);
  range.setValue('hey');
}

function clearSelection() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange
var range = sheet.getRange(topRow(),1,sheet.getLastRow(),1);

range.setValue(false);
}

function texting() {
sendEmail("Text")  
}

function emailing() {
sendEmail("Email");
}

function addNewRow() {
 SpreadsheetApp.getActiveSheet().insertRowAfter((menuRow() + 1)); 
}

function deleteRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
   .getSheetByName("Database");
  var firstRow = topRow();
  // Get rows that need deleted
  var rowNumbersToDelete = rowsToDelete(ss.getRange(firstRow, 1, ss.getLastRow(),1)); 
  // Checks the length of the emailList, if it's 0 that means a box wasn't checked. Script ended
    if (rowNumbersToDelete.length < 1) return; 
  var ui = SpreadsheetApp.getUi();
     //Pop-up box to confirm delete rows
  var response = ui.alert(
    'You are about to delete ' + rowNumbersToDelete.length + ' row(s). Do you want to continue?', 
    ui.ButtonSet.YES_NO);
  // Process the user's response.
  if (response == ui.Button.YES) {
 // Delete specified rows starting from the bottom of the sheet 
  for (var i = rowNumbersToDelete.length - 1; i>=0; i--) {
  ss.deleteRow(rowNumbersToDelete[i]); 
}
  // Uncheck boxes
  ss.getRange("A"+firstRow+":A" + ss.getLastRow()).setValue("FALSE");
  
  } else {
    Browser.msgBox("Maybe next time.");
  }
}

function sendEmail(button) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  .getSheetByName("Database");
  // Row with column titles
  var titleRow = menuRow(); 
  // Top frozen row
  var firstRow = topRow(); 
  // Subject range from spreadsheet
  var subject = ss.getRange(eSubject()); 
  // Message range from spreadsheet
  var message = ss.getRange(eMessage()); 
  // Employee name column 

  // Range where the checkboxes are
  var boxRange = ss.getRange(firstRow, 1, ss.getLastRow(), 1);
  
  // Check which button was pressed
   if (button === "Email") {
     // If an email is being sent, check to make sure the subject and message cells are not empty
     if (subject.isBlank() || message.isBlank()) { 
       // Let user know they need to do something
       Browser.msgBox("You must include an email subject and message") 
       // Activate subject box
       subject.activateAsCurrentCell() 
       // Exit script
       return; 
     }
     // Spreadsheet range which contains the emails
      var emailRange = ss.getRange(firstRow, 6, ss.getLastRow() - titleRow, 1); 
   }
  else if (button === "Text") {
   // If a text is being sent, check to make sure the message cell isn't empty  
    if (message.isBlank()) { 
      // Let user know they need to include a message
      Browser.msgBox("You must include a message") 
      // Activate subject box
      message.activateAsCurrentCell() 
      // Exit script
      return; 
      // Check if the text message is going to exceed 160 characters
    } else if (message.length > 160) { 
      // Warn user that their message may have issues being received by some phones
      Browser.msgBox('Your text message exceeds 160 characters. Some phones may encounter issues receiving this message'); 
  }
    // Spreadsheet range which contains the text to emails
    var emailRange = ss.getRange(firstRow, 7, ss.getLastRow() - titleRow, 1); 
  }
  // Get the list of email addresses to send
  var emailList = getEmailAddresses(emailRange, boxRange);
  // Get total number of recipients from the email list
  var recNumb = emailList.length; 
  // Checks the length of the emailList, if it's 0 that means a box wasn't checked
  if (recNumb < 1) { 
    // Let user know they need to do something
    Browser.msgBox("You must make a selection") 
    // Exit script
    return; 
    // Check if the number of emails exceeds the limit of 50 at a time.
  } else if (recNumb > 50) { 
    // Let user know they need to do something
    Browser.msgBox("You may only send up to 50 emails at one time.") 
    // Exit script
    return; 
  }
   
   var ui = SpreadsheetApp.getUi();
  // Use a pop-up box to ask user if they want to continue
  var response = ui.alert('You are about to send ' + recNumb + ' message(s). Do you want to continue?', 
                          ui.ButtonSet.YES_NO);
  // If the user says yes, then...
  if (response == ui.Button.YES) {
    // Popup message to let the user know the emails are being sent
    SpreadsheetApp.getActiveSpreadsheet().toast("Working..."); 
    // begin loop to send each email individually
    for (var i = 0; i < recNumb; i++){ 
      // send email
    MailApp.sendEmail(emailList[i], subject.getValue(), message.getValue()); 
    }
    // Popup message to let user know the emails have been sent
    SpreadsheetApp.getActiveSpreadsheet().toast("Email Sent!"); 
    // Uncheck boxes
    ss.getRange(firstRow, 1, ss.getLastRow() - (titleRow + 1), 1).setValue("FALSE");  
  } else { 
    // If the user decides they don't want to send the messages after all
    Browser.msgBox("Better safe than sorry. That's what I always say.")
  }
}


function getEmailAddresses(Range1, Range2) {
var vE = Range1.getValues(); // Values to return
  var bE = Range2.getValues(); // Values to test
  var arrE = [], cE = []; // New arrays
  for (var r = 0; r < vE.length; r++) {
      if (bE[r][0] === true) { // Test if box is checked
        if (vE[r][0] !== "") { // Test for blank emails
          arrE.push(vE[r]);          
        }
      }
    }
return arrE;    
  }

function rowsToDelete (range) {
  var firstRow = topRow(); // Top frozen row
  var boxes = range.getValues(); // Values to test 
  var numbers = []; // New array
  for (var r = 0; r < boxes.length; r++) {
      if (boxes[r][0] === true) { // Test if box is checked
        numbers.push(r + firstRow); // Array starts at 0 and there are 6 rows before the start      
      }
    }    
  return numbers;
}

Note:  In order to successfully send a text message, you must know the cell carrier of the person receiving the message.
If you're interested in learning how to do this with Microsoft Excel, check out this video.
Micah is an entrepreneur and spreadsheet enthusiast.

Comments

  1. Hadi shaikhJuly 24, 2021 at 1:43 AM

    How do I convert email to google sheets I have read all the comments and suggestions posted by the visitors for this article are very fine,We will wait for your next article so only.Thanks!

    ReplyDelete

Post a Comment

Popular posts from this blog

Connect Google Sheets to Cloud SQL Using Apps Script

By
Image
Occasionally, you have more information than a spreadsheet can handle. When you have a million or more rows of customer data, it may be time to switch to a database. But how do you keep using Google Sheets if your data is stored in a database? In this video, I demonstrate how to connect Google Cloud SQL to your spreadsheet using Google Apps Script. Interested in learning more about Google Apps Script and Google Sheets? Subscribe to my channel.
Read more

4 Reasons Why An Office 365 Subscription Is Worth The Money

By
Image
Introduction Microsoft Office is the standard for operating a small business. It provides products for number crunching, document creation, presentations, email, and databases. It’s the bee’s knees. But few small businesses come close to utilizing the full strength and unique features of Excel and Word. Fewer still use PowerPoint or even know what to do with Access (except bang their heads against a wall). In general, very few small businesses are getting their money’s worth out of these programs. This is because cheap and free options exist which perfectly meet the needs of most small businesses. I am a self-proclaimed spreadsheet enthusiast. Excel may be my favorite computer program (I’m not even kidding). So when I tell someone they don’t need to pay for Excel, or any other Office product, it’s not coming from a place of: “Don’t use that! Use MY favorite product!” The recommendation comes from a place of: “Don’t pay for something you don’t need.” So instead of tellin...
Read more

Create Custom Online Survey for FREE!

By
Image
Many small businesses utilize online surveys to learn about their customers. Surveys are a great way to get direct feedback about your products, services, and customer satisfaction. If you have ever used an online survey, you may know there is no shortage of free options for creating them. However, these are usually “freemium” - meaning their free version restricts access to certain functions unless you pay. But what if you’re on a budget, and you don’t want to work around a bunch of restrictions? Google Forms to the rescue! Here’s what you need to know about Google Forms: It’s Free! Google Forms is a completely free online form that is available to all Gmail users. Unlimited Form Responses (Sort of) Technically, there isn’t a limit on form entries. However, if you are connecting your responses to a Google Sheet, accepting more than 100k responses may pose an issue (this number varies greatly, depending on the number of questions in your form). You also have the option to downloa...
Read more