Google Sheets App Scripts

About

NOTE: This page is a daughter page of: Google Sheets

The Google Sheets App Script feature allows you to do read and write operations (and more) to your Google Sheets with simple JavaScript code. Think of it like a macro. One of the most common types of app scripts is to add new Menu option which perform some function when clicked. For me the me trickiest thing about these App Scripts, is that you will need to click the "Advanced" option to allow your sheet to access your project when you click run.

What you can do

Permissions step

How To: To Create an "Email" App Script for Google Sheets

So this particular script allows you to send custom emails to email addresses listed on your sheet. In theory a "mailto:" link could do this, but in practice, it doesn't work... and the advantage of this script is that it can also update a field value to show when the email is sent - helping make sure you don't send the same email twice.

The right API to help is the Google Docs API... but that's slow to process, so here's what you need to do to create a new menu item that does something:

1. Create a brand new Google Doc ............... (tip: Try typing into Chrome: docs.new)
2. Click menu bar: Extensions > App Scripts.
3. Enter the javascript code below into the Script Editor and hit "Save" and "Deploy" (as "Editor Add-On")
   ..... (tip: You can hit the play button to test one of the functions before saving)
4. Reload your Google Doc and approve permissions.
5. Run the Script by clicking the new menu item that appears: Email Scripts.

(see pictures of this process)

Here's the code to add. Just to be fancy it uses named ranges as the template for the message...

SendEmailScript.gs

function sendAckEmail() {
  // Get the active spreadsheet and the active sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRange = sheet.getActiveCell();
  var activeRow = activeRange.getRow();
  
  // Get the value in the 2nd column (B) of the active row
  var status = sheet.getRange(activeRow, 2).getValue();
  
  // Check if the value is "not yet"
  if (status.toLowerCase() === "not yet") {
    // Construct the subject and body for the email from cell values
    var recipientName = sheet.getRange(activeRow, 7).getValue();
    var recipientEmail = sheet.getRange(activeRow, 11).getValue();
    var subject = getNamedRangeValue("ackMessageSubject");
    var body = getNamedRangeValue("ackMessageBody");
    body = substituteName(body, recipientName)
    
    // Call the promptForEmail function
    if (promptForEmail(recipientEmail, subject, body)) {
      // If the email was sent, update the status to "yup"
      sheet.getRange(activeRow, 2).setValue("yup");
    }
  } else {
    // Show a prompt if the value is not "not yet"
    SpreadsheetApp.getUi().alert("You must put your cursor onto a row that says 'not yet' in 'Ack Message' (column C) for this to work");
  }
}

function sendInviteEmail() {
  // Get the active spreadsheet and the active sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRange = sheet.getActiveCell();
  var activeRow = activeRange.getRow();
  
  // Get the value in the 4th column (D) of the active row
  var status = sheet.getRange(activeRow, 4).getValue();
  
  // Check if the value is "empty"
  if (status === "") {
    // Construct the subject and body for the email from cell values
    var recipientName = sheet.getRange(activeRow, 7).getValue();
    var recipientEmail = sheet.getRange(activeRow, 11).getValue();
    var subject = getNamedRangeValue("inviteMessageSubject");
    var body = getNamedRangeValue("inviteMessageBody");
    body = substituteName(body, recipientName)
    
    // Call the promptForEmail function
    if (promptForEmail(recipientEmail, subject, body)) {
      // If the email was sent, update the "Invited" column to "Jul 28"
      sheet.getRange(activeRow, 4).setValue(todaysDateShort());
      sheet.getRange(activeRow, 3).setValue("yes + emailed");
    }
  } else {
    // Show a prompt if the value is not "not yet"
    SpreadsheetApp.getUi().alert("You must put your cursor onto a row that is empty in 'Invited' column (column D) for this to work");
  }
}

function promptForEmail(recipientEmail, subject, body) {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Confirm Email', 'Do you want to send the following email?\n\nTo:       ' + recipientEmail + '\nSubject:  ' + subject + '\n####################\n' + removeTags(body) + '\n####################\n(optional PS:)', ui.ButtonSet.YES_NO);
  
  // Check the user's response
  if (response.getSelectedButton() == ui.Button.YES) {
    // If optional PS is added: append it to the end of the message.
    var optionalPs = response.getResponseText();
    if (optionalPs.trim() != "") {
      body = body + "\n\nPS: " + optionalPs;
    }
    htmlBody = addHtmlTags(body)
    
    // Send the email
    MailApp.sendEmail({
      to: recipientEmail,
      subject: subject,
      htmlBody: htmlBody});
    ui.alert('Email sent successfully! (but check send folder in case)');
    return true; // Email was sent
  } else {
    ui.alert('Email not sent.');
    return false; // Email was not sent
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('SEND EMAILS')
      .addItem('Send Ack Email', 'sendAckEmail')
      .addItem('Send Invite Email', 'sendInviteEmail')
      .addToUi();
}

TextUtil.gs

// TextUtils.gs

function addHtmlTags(text) {
  // Replace newline characters with <br> tags
  return text.replace(/\n/g, '<br>');
}

function removeTags(html) {
  // Replace <b> and </b> tags with *
  html = html.replace(/<b>/g, '*');
  html = html.replace(/<\/b>/g, '*');  
  
  // Replace <br> tags with newline characters
  html = html.replace(/<br\s*\/?>/g, '\n');
  
  // Other substitutions:
  html = html.replace(/<span style="color: gray;">/g, '');
  html = html.replace(/<\/span>/g, '');

  return html;
}

function substituteName(message, name) {
  return message.replace(/\[NAME\]/g, name);
}

function getNamedRangeValue(rangeName) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var namedRange = spreadsheet.getRangeByName(rangeName);
  if (namedRange) {
    return namedRange.getValue();
  } else {
    throw new Error('Named range ' + rangeName + ' not found');
  }
}

function todaysDateShort() {
  var date = new Date();
  var options = { month: 'short', day: 'numeric' };
  return date.toLocaleDateString('en-US', options);
}

Links

• Google Sheets API - About the API.