Automates book data organization in Google Sheets and uses Group Merge Add-on for personalized email sending to share book information.
This tool is designed to collaborate with the Group Merge add-on, eliminating the need for individual email creation, even when dealing with complex data structures.
- A Google account with access to Google Sheets.
- A foundational understanding of Google Sheets and Google Apps Script.
- Access a Sample Google Sheet: Begin by opening the Sample Google Sheet.
*Note: The samples provided are merely illustrative, showcasing e-book data at an educational institution.
- Copy the Google Sheet: Make a copy of the Google Sheet to create your personalized version.
- Authorization: The first time you run the script, it will ask for your permission to access certain data. If you're using a free Gmail account, you might see a warning that says
Not Verified by Google. Don't worry about this warning - you still need to give permission for the script to work. The script is yours alone, and it won't share any data outside of Google, unless you choose to share your Google Sheet. The only data it might send outside of Google is website status codes, for monitoring purposes.
Tool 1: Confirmation Emails
The script processes data to generate unique items for each confirmation email, such as recipient names, course information, and book details. It then uses Group Merge Add-on to send personalized confirmation emails to the recipients..
Usage and its Logics
- Input Reference Data: Enter reference data in the designated orange range for each column in the reference sheet,
Confirm Mail-Ref Data.
Figure: Input Reference Data - Process data: Select
Output Mail Merge Data for Confirm EmailfromCustom Menuto process data for confirmation emails.
This function processes data to generate mail merge data specifically for confirmation emails. The function's logic is as follows:
Below are example screenshots illustrating the input data and the output data:- Read each row of data from the input source.
- If the second faculty information is provided, combine the names and emails of the faculty members.
- If the first faculty has multiple records, combine the data to create a single entry for that faculty.
- If a faculty has two books for a certain course, list the book information as "BookXX)" where XX represents the book number.
- Generate the final mail merge data that can be used to send confirmation emails.
Figure: Input Data for Confirm 
Figure: Output Data for Confirm - Use Output Data for Mail Merge: Processed data are output in output sheet,
Confirm Mail-Mail Merge Data. Use the data for Mail Merge. As for the usage of Mail Merge, click HERE.
Tool 2: Link Share Emails
The script processes data to generate unique items for each email to share e-book links. It organizes the necessary information, including recipient names, course details, and e-book links. Using Group Merge Add-on, it sends personalized link share emails to the recipients, providing them with the relevant links to access e-books or resources.
Usage and its Logics
- Input Reference Data: Enter reference data in the designated orange range for each column in the reference sheet,
Link Share Mail-Ref Data. - Process data: Select
Output Mail Merge Data for Link Share EmailfromCustom Menuto process data for link share emails.
This function handles the processing of data to generate mail merge data specifically for link share emails. Once the button is clicked, you will be prompted to enter the discount code and expiration date. The logic of the function can be summarized as follows:
Below are example screenshots:- Read each row of data from the input source. (Unlike the confirmation email, this function is not designed to accept the second faculty information.)
- If the faculty has multiple records, combine the data to create a single entry for that faculty.
- If a faculty has two books for a certain course, combine the book information by "/" (e.g. Book A / Book B). This is because the e-book link is created by each course even if the course has multiple books. (But you can modify the codes to suit your case.)
- Generate the final mail merge data that can be used to send e-link share emails.
Figure: Input Screen for Discount Code 
Figure: Input Screen for Expiry Date 
Figure: Input Data for E-link Share 
Figure: Output Data for E-link Share
- Use Output Data for Mail Merge: Processed data are output in output sheet,
Link Share Mail-Mail Merge Data. Use the data for Mail Merge. As for the usage of Mail Merge, click HERE.
Please note that your use of this website is subject to the Terms and Conditions.
You can find the source code for this tool on Github. Click HERE .
Last updated: May 5, 2024
