-
-
Save moayadhani/5835369fdebbecf980029f7339e4d769 to your computer and use it in GitHub Desktop.
| //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); | |
| } |
@xtianbalagtas,
I do not know where you attached the photo. Have you sent me an email at moa29000@gmail.com?
@xtianbalagtas,
Use the following code and change Your Label Here text to your label name:
function getGmailEmails(){
var label = GmailApp.getUserLabelByName('<Your Label Here>');
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
}
}
@xtianbalagtas,
Kindly share with me your spreadsheet over email at moa29000@gmail.com. I will check it out.
You need to replace Lead Data with your sheet/tab name in the spreadsheet.
@xtianbalagtas,
Yes, I have replied to your email.
Hi @SneakyBonBon ,
This means the regular expression evaluation failed. Your email format is not similar to the sample email that I demonstrated here. Please provide me with a sample email. You can forward to me a sample email to moa29000@gmail.com.
hello how can we get the values corresponding to the names from the table?
Hi @emrec76,
Extracting such data from a table in Gmail would require parsing your template HTML code. Since HTML formatting can widely be different from email to email based on the email provider template, I would actually need to have a sample email. You may attach the sample email here or send it to my email as a file. My email is moa29000@gmail.com. You may download Gmail emails as I demonstrate in the link below:
https://i.imgur.com/NfwPmi3.gif
Hi, @moayadhani , I sent the e-mail to Gmail. Thank you so much for your help, I've been working on this for 3 days:(
@moayadhani You are a king! You have saved me so much time. I owe you!
You are most welcome. I am happy that it worked well for you
Hello,
I have been using this script to automatically pull data from emails successfully for a few months now. About a week ago, I started getting this error:
4:33:01 PM
Error
TypeError: Cannot read property 'toString' of null
extractDetails @ GMailExtractor.gs:48
getGmailEmails @ GMailExtractor.gs:13
This is extractDetails @ GMailExtractor.gs:48
var dDate = emailData.commentsDue = emailData.body.match(regExp).toString().trim();
This is getGmailEmails @ GMailExtractor.gs:13
extractDetails(message);
I use a modified version of the code you developed, and can't, for the life of me, figure out why or how this particular part broke.
Full code is below:
var label = GmailApp.getUserLabelByName("Plan Review Emails");
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);
SpreadsheetApp.flush();
GmailApp.markMessageRead(message);
}
}
}
}
function extractDetails(message){
var emailData = {
date: "Null",
sender: "Null",
subject: "Null",
body: "Null"
}
var emailKeywords = {
commentsDue: "Reviewers' Comments Due:",
timeDue: "Time:",
projectName: "Project:",
projectNum: "Project No.:",
projectMgr: "Project Manager:",
folderName: "PLAN REVIEW:",
projectMgrEmail: "or:",
designPhase: "Design Phase:"
}
emailData.date = message.getDate();
emailData.sender = message.getFrom();
emailData.body = message.getPlainBody();
var regExp;
regExp = new RegExp("(?<=" + emailKeywords.commentsDue + ").*");
var dDate = emailData.commentsDue = emailData.body.match(regExp).toString().trim();
regExp = new RegExp("(?<=" + emailKeywords.timeDue + ").*");
var dTime = emailData.timeDue = emailData.body.match(regExp).toString().trim();
regExp = new RegExp("(?<=" + emailKeywords.projectName + ").*");
var pName = emailData.projectName = emailData.body.match(regExp).toString().trim();
regExp = new RegExp("(?<=" + emailKeywords.designPhase + ").*");
emailData.designPhase = emailData.body.match(regExp).toString().trim();
regExp = new RegExp("(?<=" + emailKeywords.projectNum + ").*");
emailData.projectNum = emailData.body.match(regExp).toString().trim();
regExp = new RegExp("(?<=" + emailKeywords.projectMgr + ").*");
emailData.projectMgr = emailData.body.match(regExp).toString().split("@")[0].trim();
regExp = new RegExp("(?<=" + emailKeywords.projectMgrEmail + ").*");
emailData.projectMgrEmail = emailData.body.match(regExp).toString().trim();
var emailDataArr = [];
var planReviewList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan Review List").getDataRange();
planReviewList.getFilter().remove();
SpreadsheetApp.flush();
planReviewList.createFilter();
SpreadsheetApp.flush();
for(var propName in emailData){
emailDataArr.push(emailData[propName]);
}
planReviewList.appendRow(emailDataArr);
var emailList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email List").getRange("A2:A1000").getValues();
var body = "Please log into the <a href = https://drive.google.com/drive/folders/insert-folder-code-here>Plan Review Workgroup Drive</a> to start review of the <strong>*" + pName + "</strong> project. It is due on <strong>*" + dDate + "</strong> at <strong>*" + dTime + "</strong>.<br>br> Snooze this email until a week before this email is due as a reminder.";
GmailApp.sendEmail(emailList,"New Plan Reviews due " + dDate,body,{htmlBody: body});
}
Hi @kalel5654 ,
This means that the regular expression that gets the TimeDue fails. Since you have been using the script for sometime with no issues, it means that your email providers has changed its format (it could be some internal HTML formatting). You need to check your email template. You may also send me an email and I will help you out. My email is moa29000@gmail.com
I'm trying to use your code and It's totally useful and help me a lot.
Now I want to put the data into a particular cell or collum. I see you talk to use this code: "let mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Lead Data');
mySheet.getRange('C5').setValue('Lead 5');"
But for Lead5, I don't know what to fill in
Please give me an example to me understand it
For example, How can we put phoneNum into sheet names "sheet2", collum B.
Hi, how do i edit the code to only extract new emails?
Hi , my regular expression on regex101.com works fine but it will not work in the script.
my regex is : (?<=Name*\s$\n\n).*\w+\n
I need to extract data two lines below the work name, I tried to enter it directly without using the emailkeyword variable.
Is there away just to put the regex for the fields to be extracted, I only have a few fields so I'm happy not to use variables in my regex.
When I run the script with the following regex code
var regExp;
regExp = new RegExp("(?<=Name*\s$\n\n).*\w+\n");
emailData.fullName = emailData.body.match(regExp);
no data is returned in the xls sheet , I know the extract works because the main body is extracted but not other data is populated by the regex query in the cols next to the main body.
I'm sure I'm doing something very silly, it might just be a regex formatting issue.
I would really appreciate any help its been a frustrating day googling trying to fix this.
Thank you in advance.
Hi, I am new to this and not very good at this, butmanage with the first part to fetch the data from emails. Now I am trying to specific texts to columns and I am getting error: TypeError: Cannot read properties of null (reading 'toString')
This is my code:
function onOpen(e){
var ui = SpreadsheetApp.getUi();
ui.createMenu("Contact Request").addItem("Get Emails","getGmailEmails").addToUi();
}
function getGmailEmails(){
var label = GmailApp.getUserLabelByName("ToBeProcessedByGas");
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];
extractDetails(message);
}
}
}
function extractDetails(message){
var emailData = {
date: "Null",
sender: "Null",
subject: "Null",
body: "Null",
country: "Null",
yourname: "Null",
phoneNum: "Null",
problem: "Null",
picture: "Null"
}
var emailKeywords = {
country: "3. Country*:",
yourname: "6. your name*",
phoneNum: "7. contact phone number*",
problem: "8. Summary of the problem*",
picture: "9. Attach pictures, video"
}
emailData.date = message.getDate();
emailData.subject = message.getSubject();
emailData.sender = message.getFrom();
emailData.body = message.getPlainBody();
var regExp;
regExp = new RegExp("(?<=" + emailKeywords.country + ").*");
emailData.country = emailData.body.match(regExp).toString().trim();
regExp = new RegExp("(?<=" + emailKeywords.phoneNum + ").*");
emailData.phoneNum = emailData.body.match(regExp).toString().trim();
regExp = new RegExp("(?<=" + emailKeywords.yourname + ").*");
emailData.yourname = emailData.body.match(regExp).toString().trim();
regExp = new RegExp("(?<=" + emailKeywords.problem + ").*");
emailData.problem = emailData.body.match(regExp).toString().trim();
regExp = new RegExp("(?<=" + emailKeywords.picture + ").*");
emailData.picture = 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);
}
Not sure what is wrong. I saw somebody else had this same issue, but there was nothing how to fix it
Regards Johanna
@johannasumelius,
Well, from the error message that you face, it means that your email template does not match with the email template that I demonstrated in my video or that your email sender applies some internal HTML formatting that prevents the program to work. I would like you to send me a sample email over moayad@codewondo.com and I will help you out.
Hi, i have emails with attached pdf, is there a way to the pdf file to google drive and then get place the link to drive file in a column?
Hi @fjgb85
Yes this is possible. basically you need to do the following:
- read the email which this code does.
- get the email attachments using the code
const attachments = message.getAttachments(); - get the main Drive folder to store the attachment in:
let mainFolder = DriveApp.getRootFolder(); - loop through all attachments and create a copy of it as a file in
mainFolderthen get its link:
let attachmentLinks = [];
attachments.forEach(attachment => {
attachmentLinks.push(subFolder.createFile(attachment.copyBlob()).setName(attachment.getName()).getUrl());
});
- the join the
attachmentLinksin a single line separated by commas:
const finalJoinedLinks = attachmentLinks.join(',') - put this variable in the
emailDataobject:
emailData.fileLinks = finalJoinedLinks
Feel free to drop me an email in case you need help or want to hire me for business solutions development over moayad@codewondo.com and cc moa29000@gmail.com
Hi @fjgb85 Yes this is possible. Basically you need to do the following:
- read the email which this code does.
- get the email attachments using the code
const attachments = message.getAttachments();- get the main Drive folder to store the attachment in:
let mainFolder = DriveApp.getRootFolder();- loop through all attachments and create a copy of it as a file in
mainFolderthen get its link:let attachmentLinks = []; attachments.forEach(attachment => { attachmentLinks.push(subFolder.createFile(attachment.copyBlob()).setName(attachment.getName()).getUrl()); });
- the join the
attachmentLinksin a single line separated by commas:
const finalJoinedLinks = attachmentLinks.join(',')- put this variable in the
emailDataobject:
emailData.fileLinks = finalJoinedLinksFeel free to drop me an email in case you need help or want to hire me for business solutions development over moayad@codewondo.com and cc moa29000@gmail.com
Thanks, I send you and message at gmail
@xtianbalagtas,
I am not sure as of which photo you are referring to! If you wish to run a program every hour, you can actually create time-driven triggers. You can see this answer that I provided to @beamstrazy here:
https://gist.github.com/moayadhani/5835369fdebbecf980029f7339e4d769#gistcomment-3735049