Sending emails from Sheets in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula for sending emails?

Given the formula =IF(A2="", "No email", IF(ISERROR(SEARCH("@", A2)), "Invalid email", "Ready to send")) in cell B2, what will be the output if A2 contains userexample.com?

A "Ready to send"

B "No email"

C "Invalid email"

D An error message

💡 Hint

Check if the email contains the '@' symbol.

The formula checks if cell A2 is empty first. If not, it looks for '@' in the email. Since userexample.com lacks '@', it returns "Invalid email".

❓ Function Choice

intermediate

Which function sends emails from Google Sheets?

You want to send an email automatically from a Google Sheets script. Which function should you use?

A MailApp.sendEmail()

B SpreadsheetApp.sendEmail()

C GmailApp.sendMail()

D EmailApp.send()

💡 Hint

Look for the official Google Apps Script service for sending emails.

MailApp.sendEmail() is the correct function to send emails from Google Sheets scripts. GmailApp also sends emails but is more for Gmail-specific actions.

🎯 Scenario

advanced

You want to send personalized emails to a list in Sheets. Which approach is best?

You have a sheet with columns: Name, Email, and Score. You want to send each person an email with their name and score. What is the best way to do this?

A Manually send emails one by one using the Gmail interface.

B Use the =EMAIL() formula in Sheets to send emails automatically.

C Use a formula in Sheets to create email text and copy-paste it into your email client.

D Write a Google Apps Script that loops through rows and uses MailApp.sendEmail() with personalized messages.

💡 Hint

Think about automation and personalization using scripts.

Option D automates sending personalized emails directly from the sheet using a script. Other options are manual or invalid (no =EMAIL() formula exists).

📊 Formula Result

advanced

What error occurs with this script snippet?

Consider this Google Apps Script code snippet: function sendEmails() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data'); var email = sheet.getRange('B2').getValue(); MailApp.sendEmail(email, 'Hello', 'Test message'); } What error will occur if cell B2 is empty?

A TypeError: Cannot send email to an empty address

B No error, email sent to blank address

C ReferenceError: sheet is undefined

D SyntaxError in the script

💡 Hint

Think about what happens if the email address is empty.

If the email address is empty, MailApp.sendEmail() throws a TypeError because it requires a valid recipient address.

❓ data_analysis

expert

How many emails will be sent by this script?

A sheet named 'Contacts' has 5 rows of data starting from row 2. Column A has names, column B has emails. The script below runs: function sendBatchEmails() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Contacts'); var data = sheet.getRange(2, 1, 5, 2).getValues(); for (var i = 0; i < data.length; i++) { if (data[i][1].indexOf('@') > -1) { MailApp.sendEmail(data[i][1], 'Hi ' + data[i][0], 'Your info'); } } } If 2 emails in column B are empty strings and 1 email is missing '@', how many emails will be sent?

A 0

B 2

C 5

D 3

💡 Hint

Count only rows where the email contains '@'.

Out of 5 rows, 2 emails are empty (no '@'), 1 email missing '@', so only 2 emails have '@'. The script sends emails only if '@' is present, so 2 emails sent.

But the question states 2 empty and 1 missing '@', so 5 - 3 = 2 emails sent.