Detailed Documentation for Google Apps Script for Sending Emails on Checkbox Edit

Overview:

This document provides a comprehensive explanation of a Google Apps Script designed to send emails when a checkbox is clicked in a Google Sheets document. The script consists of several functions, each serving a specific purpose in the email-sending process.

Function Details:

1. runToAuthorizeScopes()

• Purpose:
  • To remind the user to update the appsscript.json file with the required OAuth scopes before executing the script.
• Functionality:
  • Displays a message reminding the user to update the appsscript.json file.
• Instructions:
  • The user should execute this function after updating the OAuth scopes in the appsscript.json file.

2. scriptSettings()

• Purpose:
  • To provide a centralized location for configuring script settings.
• Functionality:
  • Returns an object containing various settings required for the script, such as trigger column, data ending column, recipient column, email subject column, and email body column.
• Instructions:
  • The user should modify the settings in this function according to their specific Google Sheets layout.

3. translateLetterToColumn(letter)

• Purpose:
  • To convert a column letter (e.g., "A", "B", "C") to its corresponding numerical column index (e.g., 1, 2, 3).
• Functionality:
  • Accepts a column letter as input and calculates its numerical index.
• Instructions:
  • This function is internally used for converting column letters to numerical indices.

4. onCheckboxEdit(e)

• Purpose:
  • The main function triggered when a checkbox is edited, responsible for sending emails when the checkbox is checked.
• Functionality:
  • Retrieves HTML content for the email from an HTML file named "email.html".
  • Identifies the target range and retrieves values from the row where the checkbox was clicked.
  • Constructs an email option object with HTML content and other necessary parameters.
  • Determines recipient, checkbox value, and email subject from retrieved values.
  • Sends an email if the checkbox is checked, logs a message if unchecked, and logs an error if unexpected.
• Instructions:
  • The user should ensure proper configuration of script settings and HTML content before triggering this function.

Setup Instructions:

1. OAuth Scopes Update:

   • Before executing the script, the user must update the appsscript.json file with the required OAuth scopes as mentioned in the runToAuthorizeScopes() function.

2. Script Configuration:

   • Set up the Google Sheets document with checkbox columns.
   • Configure script settings using the scriptSettings() function according to the sheet layout.

3. HTML Content Setup:

   • Create an HTML file named "email.html" containing the desired HTML content for the email body.

4. Execution:

   • After completing the setup, whenever a checkbox is clicked in the specified trigger column, the script will automatically send an email with the specified content to the recipient mentioned in the specified column.

Additional Notes:

• It is essential to verify and adjust the script settings according to the specific Google Sheets layout to ensure accurate functionality.
• The HTML content in the "email.html" file should be customized according to the user's requirements for the email body.
• In case of unexpected errors or issues, the user should review the logs generated by the script to diagnose and resolve the problem effectively.

Demo Sheet Structure ("Demo.xlsx"):

To provide users with a visual reference for how the Google Sheets document should be structured, a demo sheet named "Demo.xlsx" is provided.

Note: Ensure that the demo sheet follows the specified structure and layout to ensure proper functionality of the Google Apps Script.