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

@Vic800, how does it have to store?

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?

@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()
]);