Motivational quotes to start your day using Google Sheets and Apps Script

In this tutorial, I'll show you how to build a simple application that sends an email every morning with an inspirational quote. The quote will be selected at random from a Google Sheets spreadsheet containing a number of quotes that inspire or motivate you.

Prerequisites

This tutorial assumes that you are familiar with Google Sheets and Google Apps Script. If you are new to Google Sheets and/or Apps Script, please refer to the following tutorials:

• Introduction to basic coding concepts using Google Sheets and Apps Script.

• Reading data from Google Sheets using Apps Script.

• Sending emails from Google Sheets using Apps Script.

4 steps to receive motivational quotes daily using Google Sheets

• Step 1 — Create a Google Sheets spreadsheet containing quotes that inspire you

• Step 2 — Create an Apps Script to select a quote at random and email it to you

• Step 3 — Test your script by running it

• Step 4 — Set up a time trigger to run your script every morning

Step 1 — Create a Google Sheets spreadsheet containing quotes that inspire you

The first step is to create a Google Sheets spreadsheet containing quotes. In your spreadsheet, create a sheet called "Quotes" that has two columns: Quote and Author. The Apps Script code that we'll create in the next step assumes that the spreadsheet is structured in this way. So if you decide to use a different structure, please remember to modify your code accordingly.

Step 2 — Create an Apps Script to select a quote at random and email it to you

The next step is to create an Apps Script to select a quote at random from the spreadsheet and email it to you every morning. In order to implement this, we will need the following:

• A function that reads all the quotes from the spreadsheet.

• A function that selects a quote at random from the set of quotes.

• A function to generate the email to be sent.

• A function that brings the above three steps together and sends out the email.

Step 2.1 — Create a function that reads all the quotes from the spreadsheet.

The function readAllRowsFromSheet(sheetName) reads the data range from a sheet named "sheetName" in the Google Sheets spreadsheet and returns them as a two-dimensional array of values.

/**
* Return the data range of the specified sheet as a 2D array.
* @param {string} sheetName The name of the sheet.
* @return A 2D array containing data in the sheet's data range.
*/
function readAllRowsFromSheet(sheetName) {
 let rows = SpreadsheetApp.getActive()
              .getSheetByName(sheetName)
              .getDataRange()            
              .getValues();
 return rows;
}

Step 2.2 — Create a function that selects a quote at random from the set of quotes.

The function selectRowAtRandom(rows) picks a row at random from a set of rows and returns it.

/**
 * Select a row at random from a number of rows
 * @param {array} rows A 2D array of values (rows and columns)
 * @return A row selected at random.
 */
function selectRowAtRandom(rows) {
  let numRows = rows.length;
  let randomRowIndex = Math.floor(Math.random() * numRows);
  return rows[randomRowIndex];
}

Step 2.3 — Create a function to generate the email to be sent

The next step is to generate the email to be sent. To generate the email copy, we will first create an email template that contains placeholders for the quote and its author. Then we will use information from the selected quote to populate these placeholder values in the template and then send out the email.

The function evaluateTemplateUsingRow(row, templateFileName) populates placeholder values in the template specified by the templateFileName with values from the row. This function assumes that the placeholders in the template are named col{N} where {N} is the column in the row whose value should be used to populate the placeholder. So, col1 would be the first column, col2 the second column and so on.

/**
 * Populates HTML template using a row.
 * @param {array} row An array of values from a row.
 * @param {string} templateFileName The name of the HTML template file.
 * @return The HTML content after populating values from the row in the template.
 */
function evaluateTemplateUsingRow(row, templateFileName) {
  let template = HtmlService.createTemplateFromFile(templateFileName);
  for(let index = 0; index < row.length; index++)
    template["col" + (index +1)] = row[index];
  return template.evaluate().getContent();
}

Hi col2,

Thank you for contacting us. We will get back to you within two business days.

Thanks,
ABC support team  

Hi Simon P,

Thank you for contacting us. We will get back to you within two business days.

Thanks,
ABC support team

We also need to create a template for the email. The template will use two placeholder values, one for the quote and another for its author. Create a template file called email.html with the following content in it. Please feel free to modify the template to suit your needs but note that in the template below, col1 is the placeholder for the quote and col2 is the placeholder for the quote's author.

Good morning,

Here is your quote of the day:

"<?= col1 ?>"

 - <?= col2 ?>

Step 2.4 — Create a function that brings the above three steps together and sends out the email.

The function main() first reads all the quotes from a sheet called Quotes, selects a quote at random, populates the email template with the quote and sends you an email.

The main() function also uses two other functions:

• The getCurrentDate() function returns the current date in MM-DD-YYYY format. This is used to create the email's subject line.

• The skipRow() function is used to skip the header row in the sheet before selecting a quote to send. Otherwise, the header row could be selected in the random selection and it is unlikely to motivate or inspire you! So we'll skip this row before we select a quote to email.

/**
 * Fetch a quote randomly from the spreadsheet and email it.
 */
function main() {
  let quotes = readAllRowsFromSheet("Quotes");
  quotes = skipRow(quotes);
  let quoteToEmail = selectRowAtRandom(quotes);
  let content = evaluateTemplateUsingRow(quoteToEmail, "email.html");
  MailApp.sendEmail("<ENTER YOUR EMAIL>",`Quote of the day [${getCurrentDate()}]`,content)
}


/**
 * Return the current date in MM-DD-YYYY format.
 */
function getCurrentDate() {
  let date = new Date();
  let month = date.getMonth() + 1;
  let day = date.getDate();
  let year = date.getFullYear();
  return `${month}-${day}-${year}`;
}

/**
 * Remove the first row.
 * @return The array of rows after removing the first row.
 */
function skipRow(rows) {
  if(rows && rows.length > 0)
    rows.splice(0, 1);
  return rows;
}

Step 3 — Test your script by running it

Run the main() function to test your script and confirm that you receive an email containing a quote from your spreadsheet.

If your script works, you should receive an email that looks like this:

Step 4 — Set up a time trigger to run your script every morning

Now that you have a working script that sends you a quote by email, the final step is to automate sending you a motivational quote every morning. All we have to do is create a time trigger that will run the main() function every morning. You can create time triggers from the apps script UI or via code.

Here is how you create a time-based trigger using the Apps Script UI:

You can also create time-based triggers using code. The function setUpTimeTrigger() sets up a time-based trigger that runs the main() function daily around 6AM. It checks to see if the trigger has already been set up and if not it creates it.

/**
 * Set up a time based trigger that runs every morning at 6AM.
 */
function setUpTimeTrigger() {
  let triggers = ScriptApp.getProjectTriggers();
  triggers.filter(function (trigger) {
    return trigger.getHandlerFunction() === "main";
  });

  if(triggers.length === 0) {
    ScriptApp.newTrigger("main")
    .timeBased()
    .atHour(6)
    .everyDays(1)
    .create();
  }
}

The full code

If you've been following along, you should have two files: (1) Code.gs containing your Apps Script code, and (2) email.html containing your email template.

The full code that you should have in the file Code.gs is below.

//@OnlyCurrentDoc

/**
 * Return the data range of the specified sheet as a 2D array.
 * @param {string} sheetName The name of the sheet.
 * @return A 2D array containing data in the sheet's data range.
 */
function readAllRowsFromSheet(sheetName) {
  let rows = SpreadsheetApp.getActive()
               .getSheetByName(sheetName)
               .getDataRange()             
               .getValues();
  return rows;
}

/**
 * Remove the first row.
 * @return The array of rows after removing the first row.
 */
function skipRow(rows) {
  if(rows && rows.length > 0)
    rows.splice(0, 1);
  return rows;
}

/**
 * Select a row at random from a number of rows
 * @param {array} rows A 2D array of values (rows and columns)
 * @return A row selected at random.
 */
function selectRowAtRandom(rows) {
  let numRows = rows.length;
  let randomRowIndex = Math.floor(Math.random() * numRows);
  return rows[randomRowIndex];
}

/**
 * Populates HTML template using a row.
 * @param {array} row An array of values from a row.
 * @param {string} templateFileName The name of the HTML template file.
 * @return The HTML content after populating values from the row in the template.
 */
function evaluateTemplateUsingRow(row, templateFileName) {
  let template = HtmlService.createTemplateFromFile(templateFileName);
  for(let index = 0; index < row.length; index++)
    template["col" + (index +1)] = row[index];
  return template.evaluate().getContent();
}

/**
 * Return the current date in MM-DD-YYYY format.
 */
function getCurrentDate() {
  let date = new Date();
  let month = date.getMonth() + 1;
  let day = date.getDate();
  let year = date.getFullYear();
  return `${month}-${day}-${year}`;
}

/**
 * Fetch a quote randomly from the spreadsheet and email it.
 */
function main() {
  let quotes = readAllRowsFromSheet("Quotes");
  quotes = skipRow(quotes);
  let quoteToEmail = selectRowAtRandom(quotes);
  let content = evaluateTemplateUsingRow(quoteToEmail, "email.html");
  MailApp.sendEmail("<ENTER YOUR EMAIL>",`Quote of the day [${getCurrentDate()}]`,content)
}

The code you should have in the file email.html is below. Feel free to change the email template but remember that col{N} is a placeholder for values that will be merged in from column N in the spreadsheet.

Good morning,

Here is your quote of the day:

"<?= col1 ?>"

  - <?= col2 ?>

Conclusion

In this tutorial, I showed you how to use Google Sheets and Apps Script to send motivational or inspirational quotes via email on a regular basis. Hope you found this tutorial useful!

Thanks for reading!