Extract Gmail content to a spreadsheet #gas #gmail #sheet

70e04a67f1f5fd96a708.md

Gmail to Spreadsheet

• Gmail Advanced search
• An another gist

Code.gs

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);
  }
}

@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!

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.

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?

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

This script works very well, but it stops at 500 rows. The directory I am searching has 553 items in it. Any idea why?

For me it stops at number 512 items. What to do?
Is it some restriction of spreadsheet of the script itself?

getting a "Bad value (line 24, file "Code")"
then it highlights this part : var ss = SpreadsheetApp.openById(555982521);
i made sure that its the correct id

Can you please guide me how can i edit the code instead of getting 1 data i'll get more other info such as FROM, CC, EMAIL SUBJECT. I keep trying to alter the code it only save in 1 column.

@irawrsilentpls

You need to edit this row

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

Just add something like this

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