Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Extract Gmail content to a spreadsheet #gas #gmail #sheet
var SHEET_ID = YOUR_SPREADSHEET_ID;
var SHEET_NAME = YOUR_SHEET_NAME;
function getEmails_(q){
var emails = [];
var thds = GmailApp.search(q);
for(var i in thds){
var msgs = thds[i].getMessages();
for(var j in msgs){
emails.push([msgs[j].getBody().replace(/<.*?>/g, '\n').replace(/^\s*\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n')]);
}
}
return emails;
}
function appendData_(sheet, array2d){
sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);
}
function run(){
//Gmail Advanced search https://support.google.com/mail/answer/7190
var array2d = getEmails_("the");
if(array2d) {
var ss = SpreadsheetApp.openById(SHEET_ID);
var sheet = ss.getSheetByName(SHEET_NAME);
if(!sheet) sheet = ss.insertSheet(SHEET_NAME);
appendData_(sheet, array2d);
}
}

Vic800 commented Jan 26, 2016

Is there a way to extract email body in pdf format into a google spreadsheet

Owner

oshliaer commented Apr 25, 2016

@Vic800, how does it have to store?

lumibd commented Sep 12, 2016

ok, I tried to understand it but I didn't have as much time as I would like, and I never learned any programming languages, so it will be noob question time :
I tried to put my spreadsheet ID in row 1 YOUR_SPREADSHEET_ID (I replaced it in row 1) first only the ID, but there was a "Missing ; before statement. (line 2, file "Code")" error, so I put my id between "" was it the correct things to do ?
I done the same with my sheet name (row 2)
is it ok ? Is the "" a bad idea? did I need to change only there, in the "var" part, or every time there is "Sheet ID" ? (NOOB question I said)

If it was the correct things to do I wanted to extract content only from email in a specific label so as I didn't understood where I needed to put the question I changed line 22 to var array2d = getEmails_("label:pro-adbs-adbs-stage");`
Is it ok ? I saw after that in line 4 and 6 mention to search, is it there the search has to be provided? somewhere else I didn't see?

after that I tried but I have only

Please, can you explain where I have to search /modify ?

Thanks in advance
Lum'

@lumibd - Put the two variables in quotes - "NAME"

@oshliaer - The script is dumping ALL of the email, not the mail listed under the specific label/search criteria. Am I missing something?

Owner

oshliaer commented Jan 4, 2017

@DaveTavres, this is just scratch. For an example

function run(){
  //Gmail Advanced search https://support.google.com/mail/answer/7190
  var array2d = getEmails_("category:updates from:google.com label:keys");
  if(array2d) {
    var ss = SpreadsheetApp.openById(SHEET_ID);
    var sheet = ss.getSheetByName(SHEET_NAME);
    if(!sheet) sheet = ss.insertSheet(SHEET_NAME);
    appendData_(sheet, array2d);
  }
}

@oshliaer Any idea as to why I'm getting this message? Did I miss something?

TypeError: Cannot read property "length" from undefined. (line 17, file "Code")

Thank you for this code. It works beautifully. I tried to add on additional gets as follows:

emails.push([msgs[j].getBody().replace(/<.?>/g, '\n')
.replace(/^\s
\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n'), [msgs[j].getDate(), [msgs[j].getFrom(), [msgs[j].getTo(),

But only the first two arguments - Body and Date - populate on the sheet. If I make From or To the 2d argument, it gets those arguments instead of Date. In other words, the script only pulls the first two arguments whatever they are. Can the script be modified to grab all 4? Thanks!

hub2git commented Aug 30, 2017

How can I get just the email's subject line into Google Spreadsheet?

This is awesome! Any suggestions on how to make it pretty? It returns a lot of JSON in the cells.

pattik77 commented Nov 2, 2017

I had used a prior version of this and it worked very well, except it stopped at 500 rows. I tried a couple of times to be sure and had the same results. I am using Google Sheets. I would like to try the newer code, but have a question about one of the variables. I understand the sheet name variable,but don't understand what to put in the Your_Spreadsheet_ID field. Can you help?
Thanks - Patti

Somehow the script doesn't get more than 553 rows but I've got more than 3000 emails. Any idea why is this happening?

ferroao commented Nov 27, 2017

@hub2git replace in this code, https://ctrlq.org/code/20053-save-gmail-to-google-spreadsheet
getBody with getSubject

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