Did you know that you can pull in emails from your Gmail account into Google Sheets? This can be useful if you don’t want to open up Gmail and do a search; you can do it right within Google Sheets. You can extract the body, subject, and other attributes. This can make it easy to scan through your messages and potentially parse out data from the body. Below, I’ll share with you the code to do this and how it works. You can also download the template if you don’t want to create it yourself.
Creating the sheet and setting up the variables
You probably don’t want to pull every email into your Google Sheets file. For that reason, it’s important to set up variables that will allow you to do a search. In my template, I’ve got an area to search by the subject and by label, with the named ranges being keysubject, and keylabel, respectively. This is where the search terms go. And this is similar to how you would search within Gmail, searching by both the subject and the label.
The Google Apps Script code
To attach the code to your Google Sheets file, you’ll need to go the Extension tab and select the option for Apps Script
From there, you should see a new tab open that gives you an untitled project where you can enter in code:
The function name can remain as default, the key is to copy the code within the curly brackets, { and }. The code that I use for the function to pull in emails is as follows:
var ss = SpreadsheetApp;
var sht = ss.getActiveSheet();
var lastrow = sht.getLastRow();
var k = 6;
var rng = sht.getRange(k,1,lastrow,4);
rng.clearContent();
var emailstring = 'https://mail.google.com/mail/u/0/#inbox/';
var emaillink;
var keysubject = "subject:(" + sht.getRange("keysubject").getValue().toString()+")";
var keylabel = sht.getRange("keylabel").getValue().toString();
var searchquery = GmailApp.search(keylabel + " " + keysubject);
var allthreads = GmailApp.getMessagesForThreads(searchquery);
var emaildate;
var emailsubject;
for (var i=0; i<allthreads.length; i++) {
var activethread = allthreads[i];
for (var j=0; j<activethread.length; j++) {
emaildate = activethread[j].getDate();
emailsubject = activethread[j].getSubject();
emailbody = activethread[j].getPlainBody().substring(0,300);
emailID = activethread[j].getId();
sht.getRange(k,1).setValue(emaildate);
sht.getRange(k,2).setValue(emailsubject);
emaillink = emailstring + emailID
sht.getRange(k,3).setValue(emaillink);
sht.getRange(k,4).setValue(emailbody);
k +=1
}
}
There are a couple things to note in the code, should you want to change the layout of your file and where you want the data to go.
At the beginning of the code, there is a variable, k. It determines the starting row for the data. In my code, the value is set to 6 because my headers are in row 5. That means row 6 is the starting point for the data. If you want your headers to be in row 10, for example, you’ll want to set the k value to 11, so that it starts on the following row.
Towards the end of the code, you’ll see where the values are being populated. For example, the date of the email is being populated with the following line:
sht.getRange(k,1).setValue(emaildate);
The k variable is specified at the beginning of the code. However, you can change the the column number (1) at this line. Do not change the k value here. If you do, then your data will be overwritten in the same row over and over. This is because in this part of the code the function is doing a loop and it will increment the k value. And so if you want to change it, you need to do it when the k variable is first set up — before the loop.
If, however, you want to change the column that the value is going to, this is the correct place to do so. For example, suppose you don’t want the date going into column A, then you can change the column number. For example, if you want to change it to column B, then you would change (k,1) to (k,2).
If there are certain fields that you don’t want to be populating, then you can also just remove those lines entirely.
For the body of the email, you may want to adjust how much of it gets pulled into the file. Too much text can force your column to get spread out. And if there are line breaks, the row can also get expanded. In my code, I’ve set the limit to the first 300 characters. However, you can change that by adjusting the following line of code:
emailbody = activethread[j].getPlainBody().substring(0,300);
One last note before moving on from this section — remember to save any changes before trying to run the macro again. If you don’t save, then the changes won’t be applied when you run the macro.
Adding a button to trigger the macro
The one thing that you may want to do after adding the code is to create a button on your spreadsheet to trigger it. Otherwise, you’ll need to go to the Apps Script tab and click the run button each time, which isn’t practical.
Instead of doing that, select the Insert button on the Google Sheets file and select Drawing. You’ll have a blank canvas where you can create a button. Here, you can select an option to create a shape and enter text within it. You can apply different colors to also make it stand out. One you’re done designing it, click on Save and close and the button will be on your spreadsheet.
Once it’s within your spreadsheet, you’ll see that there will be three dots off to the right of the button. This is where you can assign your button to the macro that you’ve created. In my example, my function is called getEmails and that’s what I’ll enter when I’m assigning the button to a script;
If you’ve used a different function name, you will need to enter it above, and then click OK. Don’t enter the parentheses, (), which come after the function in Apps Script. Once you’ve assigned the script to the button, you can now click on the button and run the function.
This will only run on the email account you’re logged in on
If you’re like me and you have multiple Gmail accounts, the one thing you need to know is that this will macro will run on the account you’re logged in on; it won’t be able to toggle between different accounts for you.
Download the file
You can set up this file yourself but if you prefer to just use the version I’ve created, you can download a copy of my template here.
If you liked this post on How to Get Emails Into Google Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.