Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Get Gmail Emails By Assigned Label
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());
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);
}
}
function extractDetails(message){
var dateTime = message.getDate();
var subjectText = message.getSubject();
var senderDetails = message.getFrom();
var bodyContents = message.getPlainBody();
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
activeSheet.appendRow([dateTime, senderDetails, subjectText, bodyContents]);
}
@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Jul 30, 2019

This Google Script code can be used in your Google spreadsheet to get Gmail emails from within that spreadsheet. It reads through all emails that are assigned the inputted label name, and lists down the following information in the spreadsheet: the date and time, subject, sender's address and name and email raw contents.

I have made a YouTube video for you guys to have a clear understanding about this code. Check it out:
https://youtu.be/gdgCVqtcIw4

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Nov 23, 2019

@d0335 Hi, I am glad you found my tutorial helpful. Did you make sure that all of the desired emails that you need to export are assigned the label? You can check this by clicking on the label name in Gmail and see if those un-exported emails are there or not.

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Dec 2, 2019

@d0335 You are trying to get large email threads from your emails. This will fail the process and make the system unable to handle them. This is mentioned in the Gmail API as indicated Here. The documentation suggests that you need to use getThreads(start, max) where start and max are the limiting parameters.

@kerenbir

This comment has been minimized.

Copy link

@kerenbir kerenbir commented Dec 29, 2019

extra from me to get receiver (to)
var receiverDetail = message.getTo();

@mth2020

This comment has been minimized.

Copy link

@mth2020 mth2020 commented Jan 27, 2020

Thank you for this. It's exactly what I wanted to do. I modified your code so that it works across multiple labels. I'm having an issue where emails without a label are getting picked up. My code is below. Is there something I'm missing here:

`
function onOpen(e){
var ui = SpreadsheetApp.getUi()

ui.createMenu("Importing Menu").addItem("Get Emails", "getGmailEmails").addToUi();
}

function getGmailEmails(){

var labelText = "MCM 6PR";
var label = GmailApp.getUserLabelByName(labelText);
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, labelText);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);

}

var labelText = "MCM 60A";
var label = GmailApp.getUserLabelByName(labelText);
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, labelText);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);

}

var labelText = "MCM 135A";
var label = GmailApp.getUserLabelByName(labelText);
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, labelText);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);

}

var labelText = "MCM SER";
var label = GmailApp.getUserLabelByName(labelText);
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, labelText);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);

}

var labelText = "MCM DFY";
var label = GmailApp.getUserLabelByName(labelText);
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, labelText);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);

}

var labelText = "MCM ExD";
var label = GmailApp.getUserLabelByName(labelText);
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, labelText);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);

}

var labelText = "MCM Capit";
var label = GmailApp.getUserLabelByName(labelText);
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, labelText);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);

}

var labelText = "MCM XY";
var label = GmailApp.getUserLabelByName(labelText);
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, labelText);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);

}

var labelText = "MCM SecA";
var label = GmailApp.getUserLabelByName(labelText);
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, labelText);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);

}

}

function extractDetails(message, labelText){
var dateTime = message.getDate();
var subjectText = message.getSubject();
var senderDetails = message.getFrom();
var bodyContents = message.getPlainBody();

var ss = SpreadsheetApp.getActiveSpreadsheet();

SpreadsheetApp.setActiveSheet(ss.getSheetByName(labelText));

ss.getSheetByName(labelText).appendRow([dateTime, senderDetails, subjectText, bodyContents]);
}
`

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Jan 27, 2020

@mth2020 If emails that have no label are picked up, it means that these emails correspond to the same exact thread (Gmail conversation). I suspect that these emails are having the exact same title and are simply grouped when you enable the "Conversation View" from Gmail settings.

The lengthy modified code that you placed is a bad programming practice. List your desired labels in an array and iterate through its elements using a for or .forEach(Function) loop

@mth2020

This comment has been minimized.

Copy link

@mth2020 mth2020 commented Jan 27, 2020

@mth2020

This comment has been minimized.

Copy link

@mth2020 mth2020 commented Jan 27, 2020

@SilverStarMW

This comment has been minimized.

Copy link

@SilverStarMW SilverStarMW commented Feb 26, 2020

I just found your video and code link. Thank you. This is super close to what I need done. I am really new to script writing. It's been over 10 years since I worked in IT or written even basic html.

How would I modify your code to export all the email addresses, and only email addresses, contained in the Gmail Emails under the Label -- Bounced Emails?

I send emails to my list, then Label all those that bounce so I can follow up with my clients. However, the email that comes back letting me know the original has bounced is sent To my own email address. The original recipient address in the To field is now embedded in the reply email from the server. I need the original To field address captured for each bounced email.

Suggestions? Help! Many thanks.

@makingdigitaleasy

This comment has been minimized.

Copy link

@makingdigitaleasy makingdigitaleasy commented Mar 18, 2020

Hello @MoayadAbuRmilah thank you for the wonderful code you shared.

Am looking for a way to ensure the-:

  1. code runs automatically whenever a new email is received.

  2. Also currently whenever I run it.. it repeats some of the emails even after removing the labels.

How can I solve this??

@Zohaib321

This comment has been minimized.

Copy link

@Zohaib321 Zohaib321 commented Mar 23, 2020

This Google Script code can be used in your Google spreadsheet to get Gmail emails from within that spreadsheet. It reads through all emails that are assigned the inputted label name, and lists down the following information in the spreadsheet: the date and time, subject, sender's address and name and email raw contents.

I have made a YouTube video for you guys to have a clear understanding about this code. Check it out:
https://youtu.be/gdgCVqtcIw4

Hello,

Thanks for making this. I am a newbie to this and I was wondering if you can tell me how to incorporate the below code into your script?

Code I want to incorporate.

`function isTimeUp_(start) {
var now = new Date();
return now.getTime() - start.getTime() > 300000; // 5 minutes
}

function myFunction() {

var threads = GmailApp.getInboxThreads(0, 50);
var start = new Date();

for (var t in threads) {
if (isTimeUp_(start)) {
Logger.log("Time up");
break;
}
// Process the thread otherwise
var messages = threads[t].getMessages();
}
}`

My current code

`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());
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);
  GmailApp.markMessageRead(message);
}
threads[i].removeLabel(label);
var label2 = "PaypalScanned";
threads[i].addLabel(GmailApp.getUserLabelByName(label2));

}

}

function extractDetails(message){
var dateTime = message.getDate();
var subjectText = message.getSubject();
var senderDetails = message.getFrom();
var replytoemail = message.getReplyTo()

var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
activeSheet.appendRow([dateTime, senderDetails, subjectText, replytoemail]);
}`

@Jason-bellmt

This comment has been minimized.

Copy link

@Jason-bellmt Jason-bellmt commented Apr 14, 2020

Thank you for a great code it works very well and has saved me a lot of trouble. I was wondering if there is a way that you don't have to specify a label though? Gmail does not list all sub-labels under the main label and I have over 200 sub-labels that I need to import. I have tried to edit the code all afternoon but I can't get it right.

Would you be kind enough to tell me how I can solve this problem?

Thank you

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Apr 17, 2020

@Jason-bellmt
Glad that you found this helpful.

I just checked the code using sub labels and it just works fine. Could you explain more what the issue you're facing? How does Gmail not list all sub-labels under the main label? If you apply the filters correctly, Gmail will list the sub labels properly.

@makingdigitaleasy

This comment has been minimized.

Copy link

@makingdigitaleasy makingdigitaleasy commented Apr 17, 2020

@MoayadAbuRmilah

Kindly help to identify why subsequent run of the code returns some duplicate threads from the label

Some how it 'remembers' data from the last fetch.

How do we solve this?

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Apr 17, 2020

Hi @makingdigitaleasy,
If you are coming after watching my YouTube video HERE, then this behavior that you described is possible to happen. I have modified the GitHub code to check if the message is unread. Without checking, it will may pull the previous messages in one thread because the label is applied to Gmail Threads and not to individual messages. Kindly take note of the following code snippet that I modified:

 if (message.isUnread()){
    extractDetails(message);
    GmailApp.markMessageRead(message);
  }
@makingdigitaleasy

This comment has been minimized.

Copy link

@makingdigitaleasy makingdigitaleasy commented Apr 17, 2020

@MoayadAbuRmilah

This worked perfect. Thank you.

I will let you know of more progress

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Apr 17, 2020

@makingdigitaleasy,
Glad to help and yeah update me please. You may subscribe my channel as I have plans for more videos that you may find useful.

Best regards

@makingdigitaleasy

This comment has been minimized.

Copy link

@makingdigitaleasy makingdigitaleasy commented Apr 17, 2020

I subscribed already. Will share with others to follow and subscribe

@bricenf

This comment has been minimized.

Copy link

@bricenf bricenf commented Apr 18, 2020

Thank you for the tutorial. Within 3 minutes, I knew that it was very useful and informative so I dropped that like button. I just have a few questions.

  1. Is there any way you can alter it to go through the inbox?
  2. How can you make it automatically update the spreadsheet when you get an email?
@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Apr 19, 2020

@bricenf,

Most welcome dear.
You can actually run specific script functions automatically based on time-driven triggers. This can be accessed from the Edit menu and then choosing "Current project's triggers". After that, you will be directed to an interface to select which function you need to run and the time period.

As for question No. 1, I hope you can explain it for me to understand it well. You may also send me an email to: moa29000@gmail.com

@makingdigitaleasy

This comment has been minimized.

Copy link

@makingdigitaleasy makingdigitaleasy commented Apr 19, 2020

@bricenf

You need to create an email filter which will sort the inbox as target emails.

As @MoayadAbuRmilah suggested. Please provide details of what you would like to achieve and your email provide eg. Gmail, yahoo etc

@dianaot

This comment has been minimized.

Copy link

@dianaot dianaot commented May 4, 2020

Hi @MoayadAbuRmilah, I'm trying to use your email to google sheet code but am not having much success. Basically what I am trying to do is scrape the data that comes through a link in my inbox everyday. I figured that I could use your code to get the link from my inbox to a google sheet and then run another query to scrape the contents within that link. Please let me know if you can help!

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented May 5, 2020

Hi @dianaot,

Could you please send me more details over email? You may send a sample email to understand you better.

Thank you

@paulinhamb

This comment has been minimized.

Copy link

@paulinhamb paulinhamb commented May 6, 2020

Hi @MoayadAbuRmilah first thank you for the video and code, super explanatory! However, I've been having some problem running the code, this message is showing "TypeError: Cannot read property 'getThreads' of nul;" probably because the emails are not set as threads... is there a way to go around it?

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented May 7, 2020

H @paulinhamb,
I am pleased that my video helped you out. Your error is because there is not email having the provided label in your Gmail. Make sure the label is there and make sure the spelling is exact.

Feel free to subscribe my channel to get updated on my next video

@paulinhamb

This comment has been minimized.

Copy link

@paulinhamb paulinhamb commented May 7, 2020

Thanks again @MoayadAbuRmilah but I think I'm really missing something...

this is part of the code

function getGmailEmails(){
var input = ui.prompt('emailpagseguro', 'emailpagseguro', Browser.Buttons.OK_CANCEL);

and this is the label
label:emailpagseguro

and the label exists and has emails under it... what it doesn't have is a thread :/

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented May 7, 2020

@paulinhamb
This line of code is not the one that you need to key in the label. You key in the label in the prompt box that will appear on your sheet after pressing the customized menu. You may share a video of the issue if it persists. My email is moa29000@gmail.com

@makingdigitaleasy

This comment has been minimized.

Copy link

@makingdigitaleasy makingdigitaleasy commented May 27, 2020

@MoayadAbuRmilah

I came across some instances where the script stopped due to 'limit exceeded Maximum execution time' issues.
Also some unread messages were skipped in the process.

It occurs to me that somehow we need to limit the volume of thread to be processed during each run. (for timed trigger)...
Kindly assist

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented May 27, 2020

@makingdigitaleasy
Since you need to get many emails that are assigned labels, then you do not use the method getThreads(). You can use getThreads(start, max).

@makingdigitaleasy

This comment has been minimized.

Copy link

@makingdigitaleasy makingdigitaleasy commented Jun 23, 2020

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Jun 23, 2020

@makingdigitaleasy,
Obviously, you have reached the limit of your quota usage. Please check the following link to know more:
https://developers.google.com/gmail/api/v1/reference/quota

If you want to avoid the limit, reduce the reading frequency and reduce the number of emails you try to get

@lumibd

This comment has been minimized.

Copy link

@lumibd lumibd commented Jul 28, 2020

@makingdigitaleasy,
Obviously, you have reached the limit of your quota usage. Please check the following link to know more:
https://developers.google.com/gmail/api/v1/reference/quota

If you want to avoid the limit, reduce the reading frequency and reduce the number of emails you try to get

Hi @MoayadAbuRmilah, did you explain somewhere how to reduce the reading frequency ? I couldn't find in your link an explanation how to change ( but as I didn't understood exactly how the quota were displayed ... I'm afraid I am a nooooob).

Sorrry, I brain is in excess at the moment ;)

so, yes if you already have explained somewhere/ if you can explain how to modify the reading frequency in an app it would be great ;)

[edit]
I first tried this in a label at first level, with few documents, and it was working well.

I just tried with a subsublabel and it seems not to find my sublabel : I get a "Cannot read property 'getThreads' of null"
(I took it as that he couldn't find any thread in my label/ no label, am I right or mistaken ? )

I tried with both
label:xyz-abc-def
xyz-abc-def

any idea ?

[/edit]

@darkban

This comment has been minimized.

Copy link

@darkban darkban commented Dec 31, 2020

can you help me please?
this is an notification email

[COSM] COSM1687 (STORE SOM) Transaction OK - BUY
COSM1687 (STORE SOME) BUY  RCEIHS 1550.00 EUR FOR 1.252 ORDER

i want if message body contains

COSM1687 (STORE SOM) Transaction OK

then he will have to copy the euro amount (exsample in this case 1550.00) into a cell

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Jan 3, 2021

@darkban,
Hi, I have worked on the solution for you and I have sent you links of the spreadsheet as a reply to your email. Please check out your email.

@adamincolorado

This comment has been minimized.

Copy link

@adamincolorado adamincolorado commented Feb 4, 2021

This is awesome! Thank you.

@MoayadAbuRmilah how would I reconfigure the script to run every automatically hour, and only add new, unindexed (i.e. not in the google sheet already) emails to the list?

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Feb 4, 2021

Hi @adamincolorado
You can run scripts automatically by creating time-driven triggers as an example. I have made an animated image as a simple demonstration for you guys on how to do that, and here is the link:
https://i.imgur.com/UvHgsak.gif

The script that I am sharing in this Git takes care of what you want by not considering the previously processed emails and that is done by making processed emails as READ. You can view my YouTube video to see a demonstration about this script.

Please get back to me to me at moa29000@gmail.com in case you need further help.

@vichang1

This comment has been minimized.

Copy link

@vichang1 vichang1 commented Mar 8, 2021

@MoayadAbuRmilah Thank you for sharing! This seems to be of great help. However, it seems that I'm getting the "ReferenceError: getPlainBody is not defined" under the function getGmailEmails. I'm relatively new to programming and would also like to find a way to do a search with multiple labels without deleting them after. Is this possible? If you could be of help, it'd be awesome! Thanks!

Screen Shot 2021-03-08 at 17 24 13

@MoayadAbuRmilah

This comment has been minimized.

Copy link
Owner Author

@MoayadAbuRmilah MoayadAbuRmilah commented Mar 9, 2021

Hi @vichang1,

Please fix the following line:
var bodyContents = message = getPlainBody()
to
var bodyContents = message.getPlainBody().

If you wanna extract multiple labels, then the program need to be updated to loop in every label. However, to use the same program, I advise you to create and assign a new label just for the program using Gmail filters.

Regarding the deletion of the label, you can simply apply 2 labels to the emails, one that will keep being there and another for the script.

@BeyUhwl

This comment has been minimized.

Copy link

@BeyUhwl BeyUhwl commented Jun 15, 2021

@MoayadAbuRmilah Hi, I have a question. Is there any way to add the new emails to the old emails without refreshing the whole sheet and make the sheet start from old to new? the problem is when you write any notes besides any email, the place of the email changes.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment