When it comes to organizing our online life, getting email contents into a spreardsheet or an Excel file becomes important. If you are currently doing that manually with a lot of copy-paste work, you will end up spending time and exerting effort on things that matter less than things that matter the most. With the help of powerful free tools like Google Apps Script, you can automate that.
Here in this blog and the video tutorial listed at the end, I will guide you how to get Gmail emails into a spreadsheet automatically and how to export them to an Excel sheet! let me go straight and explain to you how to achieve that with simplicity.
Gmail Filters
I assume that you need to get certain emails that have specific texts or that are received from a specific sender. Therefore, we will first create a Gmail filter that will automatically filter these emails whether new or old ones.
Creating Gmail filters can be done in different ways. One way is to use the search bar and click on the options icon 
that is at the right side as demonstrated in the screen recording below. Here you can filter out the emails that, for example, come from a specific sender and/or the ones containing specific texts in the email body or subject line. In this screen recording, I assign a label named Nak Collection to every email that comes from the address [email protected] and that has the word ramadan in it. In our script, we will use this label to read all the emails that are labeled with Nak Collection.
Creating the Spreadsheet Script/Program
Intuitively, we need a spreadsheet to get our emails into it. To do that, you need to visit http://sheets.google.com and create a new spreadsheet or use any of your existing spreadsheets. To start a script for your spreadsheet, you will need to go to the Tools menu and click Script Editor option. This will open a new window where you can type JavaScript codes using Google Apps Script. By default, the following empty function presents:
function myFunction() {
}
We will change the name of the function myFunction() to getGmailEmails().
It is important to know that Gmail messages are grouped in conversations or threads. The program below loops through every single email that is assigned the label and, when it finishes, it removes the label from the emails so that it does not read the same emails again the next time the program runs. Every message will be stored in a variable named message.
function getGmailEmails(){
var label = GmailApp.getUserLabelByName('Nak Collection');
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];
}
threads[i].removeLabel(label);
}
}
Now, we understand that the message variable actually has the desired email data, but we do not yet know how to get its details like the email date, sender, subject, body, etc. To get this information, we can create a new function called extractDetails() that takes this message variable as a parameter. The following code snippet stores this information in the variables dateTime, subjectText, senderDetails and bodyContent.
function extractDetails(message){
var dateTime = message.getDate();
var subjectText = message.getSubject();
var senderDetails = message.getFrom();
var bodyContent = message.getPlainBody();
}
All we need to do next is to populate the values of these variables to our spreadsheet. We can do that by getting the current active sheet in our spreadsheet, then appending a new row with this information. This is done using the following code snippet:
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); activeSheet.appendRow([dateTime, senderDetails, subjectText, bodyContents]);
I am not a mind-reader, but I guess that the question that is coming to your mind would be how to run this program, right? Well, one simple way is to create a custom menu in our spreadsheet so that when we click it, the functions will be executed. This menu should appear automatically once you open the spreadsheet. In Google Apps Script, there is a dedicated function that gets executed automatically immediately after the spreadsheet is opened. It is onOpen(e). The following code snippet creates a cutom menu called Gmail Manager which has an option called Get Emails to get all the emails that are labeled Nak Collection.
var ui = SpreadsheetApp.getUi();
function onOpen(e){
ui.createMenu("Gmail Manager").addItem("Get Emails", "getGmailEmails").addToUi();
}
The full program that extracts the emails and populates them to the spreadsheet would then be:
var ui = SpreadsheetApp.getUi();
function onOpen(e){
ui.createMenu("Gmail Manager").addItem("Get Emails", "getGmailEmails").addToUi();
}
function getGmailEmails(){
var label = GmailApp.getUserLabelByName('Nak Collection');
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);
}
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]);
}
Granting Permissions & Running the Program
When you are done with adding this code, you click on save and name the project whatever name you like and reload the spreadsheet. When you reload it, you will see the Gmail Manager menu beside the Help menu. When you click it, the program will get executed and you will need to grant this program the required permissions to run on your Gmail account. The following screen recording shows how to grant the required permissions to your Gmail extractor project:
Once you grant the required permissions, you can click the menu again and you will see your emails getting brought to your spreadsheet and arranged in new rows.
How to Get Only the First or Last Message in a Conversation?
The previous code loops through all of the messages within a thread/conversation. Some of you guys are interested in getting only the first message or the last one in a thread/conversation. It is really easy to do that by modifying our getGmailEmails() function. Here we loop through the messages variable using a for loop, right?
Well, to get the first message only, we do not need to loop through it but we would rather take the first message whose index is 0 into consideration (the first element index in a Javascript array starts from 0 not 1). The code to get the first message only would then be:
function getGmailEmails(){
var label = GmailApp.getUserLabelByName('Nak Collection');
var threads = label.getThreads();
for(var i = threads.length - 1; i >=0; i--){
var messages = threads[i].getMessages();
var message = messages[0];
extractDetails(message);
threads[i].removeLabel(label);
}
}
A similar argument is applicable to how to get the last message in a thread. The difference is that the number of the last message would be equal to the length of the messages array. Since array elements in Google Apps Script start from 0 index not 1, then the index of the last message would be messages.length - 1. We use this calculation because we do not know, in advance, how many messages the current Gmail thread is having. The final code to get the last message in thread would be:
function getGmailEmails(){
var label = GmailApp.getUserLabelByName('Nak Collection');
var threads = label.getThreads();
for(var i = threads.length - 1; i >=0; i--){
var messages = threads[i].getMessages();
var message = messages[messages.length - 1];
extractDetails(message);
threads[i].removeLabel(label);
}
}
Export Spreadsheet to Excel
If you wish to export your Google spreadsheet to an Excel file, you can do that by downloading it from File>Download>Microsoft Excel (.xlsx). This is a manual work to do although this process can be programmed.
I published a YouTube video to my Code Wondo channel that explains this in quite good details. Here is the video:
A highly committed and skillful software engineer with years of experience in developing business solutions and productivity tools that empower businesses to acheive more in less time.
Feel free to contact me over email for help.
Hey Moayad,
thank you for sharing this but I’m not getting the permission request from google Sheets and I’m getting these errors in the app script.
Exception: Cannot call SpreadsheetApp.getUi() from this context.
Will you Please Help Me Here
One More I forgot to Mention
Exception: Cannot read property ‘getThreads’ of null
Hello!
Thanks a lot for the code and sharing it, it works amazing and with the time triggers, it’s saving me a lot of manual work and time. I’m just wondering if there’s a way to automatically copy the formulas in the additional columns I added in the file, I tried to use the array formula to do so, but it ended up in getting the new emails at the bottom of the column and not in the lines I was expecting to complete the data extraction I was lookig to do. (I.E. I used this formula “={“Ext Customer Comments”;arrayformula(IFERROR(REGEXEXTRACT(D2:D,”.*Customer Comment:(.*)”),”No Data”))}, but ended up getting the new emails at row 999 instead of the expected row (let’s say, 461, 462.. etc, I didn’t modified column D, it’s where the entire mail body contents populate from Gmail).
I’ll really appreciate it if you can direct me in the right direction.
Hi Jorge,
You are welcome. I’m glad my code saved you time and effort.
Well, if you would like to have your formulas there, you cannot use the method
appendRow()method. You need to scan all the cells for the specific column that does not have the formula. Once the empty cell is found, you need to get the range bygetRange()method then populate the desired table of data usingsetValues()method.The other way is to still use the
appendRow()method, however you need to pupulate your formula every time a new row is added.I understand this is tricky and not that easy to do. As such, please send me an email over [email protected] and share your spreadsheet with me so that I can have a better look and help you out.
Hi
What should I change within this code if I do not have any threads only single email, can you help me
thanks Pete
Thanks,
It works great, but I send emails to phones as a text and when they return some have a “+” ie — send email to “[email protected]” and the return comes from “[email protected]” and I can’t use an automatic reply because the “+” causes and error in the cell and I can only remove it manually. I’ve tried many options with no luck. Any ideas??
Frakly, I did not get the exact issue that you are facing. Can you send me more details over my email. My email is [email protected].
Hi
great tutorial.
i followed it and it works , i changed the code a little bit to get all inbox emails but i dont know why when i execute it it is able to read only 500 rows.
do you know why?
Pls can you share that code I need it
Hi @Suhail,
What code would you like to have?
Hey, my name is Sergei!
I’m using this code to make my work more productive and efficient. However I ran into three problems.
First, if I change the code to extract JUST my last email, it still does it in a loop. I have no clue why and I can send my code to show you.
Second, my emails are extracted are extracted to one column only. It does not spread for some reason.
Lastly, Is there way to modify the code to include the emailKeywords() from your other code to this one? If so, what do I copy from the newer code to this one to make it work without asking for which Label I want to use.
Thank you very much. Waiting for your response!
The goal I am trying to accomplish is to:
1 – Sort Gmail Labels – DONE
2 – Log emails to different sheets within the same Google Sheet Doc – Partially completed by workflow above
3 – Automate replies sent based on sheet/ Google Sheet Doc location.
I’m looking to smush together your workflow above with something like this:
https://stackoverflow.com/questions/59711350/google-sheet-auto-email
Seems really easy to spin up if you know just a little more than I do.
Happy to hear any suggestion you might have.
Best,
Crystal
Hi Crystal,
I know what you want is possible by probably naming the sheets exactly same as your email label. You can either use different spreadsheets with different labels without modifying the code, or you can rename the sheets so that each sheet’s name is exactly the same as your labels’ names. If you choose the latter way, you need to loop through each sheet using let sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() then get the name of each sheet using its index. For example, the name of the first sheet would be let currName = sheets[0].getSheetName(). Since now you have the sheet name which is exactly the same as your label name, you can use that label to get all the threads by using my code in this blog.
Please feel free to drop me an email over [email protected]
Hello,
Can any one tell me, how can i get data from login protected web browser to my google sheet or excel sheet, the data is changing dynamically.
Thanks
thank you , really cool script!!
I am glad it helped. Feel free to send me an email if you have any problems as well.
Thanks Moayad, your script works like magic.
Thank you Weiguang, I am pleased that it worked well for you. Please drop me an email in case you need further help. My email is [email protected]
Hi Moayad,
I was stuck with the code and You helped me to resolve it as well
This worked for me and saved me lot of time
Thank you once again
Stay blessed !
Hi Phavya,
I was glad to help you out.
Thanks
Hey Moayad,
thank you for sharing. I am not quite there yet.
TypeError: Cannot read property ‘length’ of undefined
OK
Can you let me know where I went wrong?
Kind regards
Ben
Hi Ben,
I am happy to help. This error means that you are trying to read a label that does not exist. Make sure that the label name in
var label = GmailApp.getUserLabelByName('Nak Collection');is exactly same as the one you use in Gmail.Please update me on this
How to het href from embedded image (image hyperlink) into spreadsheet?
Basically, you need a regular expression to extract this url. Since this extraction depends on your email template that you try to extract the image URL from, then please forward to me a sample email. My email is [email protected]
Thank you so much for this apps script. Was trying to develop one myself and got stuck!
One thing is that I notice that the email threads come in individually. Is there a way to only get the data from the last threaded email into one row, and avoid all the previous ones?
Would be really great to know!
Hi Jaosh,
I wonder if you actually mean to get only the last messgae in a thread or not. If you mean so, then I have actually updated my blog to guide you through on how to get this done. Here is the section link. Please let me know if this answers your question.
This works so well buddy. Ty so much. I notice that the email threads come in individually. Is there a way to combine the data from the threaded email into just one?
Thank you for your sharing and teaching, it’s so helpful!
Hi Su,
I am glad that my blog helped you out. If you are facing any problems, feel free to get back to me. My email is [email protected]
It works perfectly. This program saved me a lot of time.
Thank you so much
I am glad that my program works well for you. Feel free to get back to me at [email protected] in case you need further help.