Skip to content

README.md

Latest commit

 

History

History
75 lines (53 loc) · 3.83 KB

README.md

File metadata and controls

75 lines (53 loc) · 3.83 KB

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.