Last active
March 7, 2024 07:02
-
-
Save moayadhani/5835369fdebbecf980029f7339e4d769 to your computer and use it in GitHub Desktop.
Extract Email Text from Google Sheet using App Script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//Sample email that is received and whose details are extracted: | |
/* | |
*Someone* just submitted your form named Online Course Booking from your | |
website | |
https://www.abcd.com/ | |
Message Details | |
Full Name: Mohamed Ali | |
Email Address: abcd123@gmail.com | |
Phone Number: 009725991122334 | |
Nationality: Palestine | |
Wish List Items: JavaScript for Beginners, Android App Development | |
To edit your email settings, go to your Inbox on desktop. | |
*/ | |
var ui = SpreadsheetApp.getUi(); | |
function onOpen(e){ | |
ui.createMenu("Gmail Manager").addItem("Get Emails by Label", "getGmailEmails").addToUi(); | |
} | |
function getGmailEmails(){ | |
var input = ui.prompt('Label Name', 'Enter the label name that is assigned to your emails:', Browser.Buttons.OK_CANCEL); | |
if (input.getSelectedButton() == ui.Button.CANCEL){ | |
return; | |
} | |
var label = GmailApp.getUserLabelByName(input.getResponseText().trim()); | |
var threads = label.getThreads(); | |
for(var i = threads.length - 1; i >=0; i--){ | |
var messages = threads[i].getMessages(); | |
for (var j = 0; j <messages.length; j++){ | |
var message = messages[j]; | |
if (message.isUnread()){ | |
extractDetails(message); | |
GmailApp.markMessageRead(message); | |
} | |
} | |
threads[i].removeLabel(label); //delete the label after getting the message | |
} | |
} | |
function extractDetails(message){ | |
var emailData = { | |
date: "Null", | |
sender: "Null", | |
subject: "Null", | |
body: "Null", | |
fullName: "Null", | |
emailAddr: "Null", | |
phoneNum: "Null", | |
nationality: "Null", | |
wishlistItems: "Null" | |
} | |
var emailKeywords = { | |
fullName: "Full Name:", | |
emailAddr: "Email Address:", | |
phoneNum: "Phone Number:", | |
nationality: "Nationality:", | |
wishlistItems: "Wish List Items:" | |
} | |
emailData.date = message.getDate(); | |
emailData.subject = message.getSubject(); | |
emailData.sender = message.getFrom(); | |
emailData.body = message.getPlainBody(); | |
var regExp; | |
regExp = new RegExp("(?<=" + emailKeywords.fullName + ").*"); | |
emailData.fullName = emailData.body.match(regExp).toString().trim(); | |
regExp = new RegExp("(?<=" + emailKeywords.phoneNum + ").*"); | |
emailData.phoneNum = emailData.body.match(regExp).toString().trim(); | |
regExp = new RegExp("(?<=" + emailKeywords.emailAddr + ").*"); | |
emailData.emailAddr = emailData.body.match(regExp).toString().trim(); | |
regExp = new RegExp("(?<=" + emailKeywords.nationality + ").*"); | |
emailData.nationality = emailData.body.match(regExp).toString().trim(); | |
regExp = new RegExp("(?<=" + emailKeywords.wishlistItems + ").*"); | |
emailData.wishlistItems = emailData.body.match(regExp).toString().trim(); | |
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); | |
var emailDataArr = []; | |
for(var propName in emailData){ | |
emailDataArr.push(emailData[propName]); | |
} | |
activeSheet.appendRow(emailDataArr); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks, I send you and message at gmail