CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
mrcni
mrcni

Reputation: 509

Creating Spreadsheets and filling them with specific data

The object is to collect gmail data from incoming and outgoing messages, from within all labels, and log it the information to various spreadsheets, respective to the label they belong to.

Essentially - the label will determine which spreadsheet its corresponding gmail data belongs in. I currently have it all setting into a single spreadsheet. This method will soon become unmanageable. 

  for (var l = 0; l < labels.length; l++) {// ** Runs a for-loop over "labels array". 
      var label = labels[l].getName();//Gets "this" label name.       
      var labelThreads = GmailApp.getUserLabelByName(label).getThreads(start, end);//Gets threads in "this" label. (*Set Limits Here*) 
      var labelMessages = GmailApp.getMessagesForThreads(labelThreads);//Gets array with each email from "this" thread. 

    for (var t = 0; t <labelThreads.length; t++){// ** Runs a for-loop over threads in a label. 
      if (labelMessages[t] == undefined){}// If it's empty, skip.
        else {// If it's not empty.
          emailBody.push([" "]);    
          emailFrom.push([labelMessages[t][0].getFrom()]);
          emailTo.push([labelMessages[t][0].getTo()]);
          emailDate.push([Utilities.formatDate(labelMessages[t][0].getDate(), "GMT", "MMM d, EEE, yyyy - HH:mm")]);
          emailSubject.push([labelMessages[t][0].getSubject()]);
          emailLabel.push([labels[l].getName()]);  
        }
}

  // ** THEN, LOG THE FILLED DATA ARRAYS TO ROWS **
  //getSheetValues(startRow, startColumn, numRows, numColumns)
    mySheet.getRange(2,2,emailLabel.length,1).setValues(emailLabel);
    mySheet.getRange(2,4,emailFrom.length,1).setValues(emailFrom);
    mySheet.getRange(2,3,emailTo.length,1).setValues(emailTo);
    mySheet.getRange(2,1,emailDate.length,1).setValues(emailDate);
    mySheet.getRange(2,5,emailSubject.length,1).setValues(emailSubject);
    mySheet.getRange(2,6,emailBody.length,1).setValues(emailBody);
  }
}

While I was able to programatically create spreadsheets with the same names as all my labels, I don't quite know how to select and fill them with the correct email data.

I understand that my code currently works because the same number of email data is captured in each array, so all the rows of data "line-up" (this information is provided at (start,end).

full code here: http://pastie.org/9768890

Update- Clarified Question: The specific question is- how can I separate the information stored in the arrays- emailFrom, emailTo, emailDate, emailSubject, emailLabel, into separate sheets - by label. A spreadsheet will have a certain email label name as a title, and I want all email pertaining to that label to be entered into that spreadsheet.

Upvotes: 0

Views: 73

Answers (1)

John
John

Reputation: 916

Here's one way to open a specific spreadsheet document (not necessarily the active document):

var ss = SpreadsheetApp.openById("id");

where "id" is a string representing the document ID. If the URL to your spreadsheet is "https://docs.google.com/spreadsheets/d/abc123/edit#gid=0" then the ID is "abc123".

From there, you can access individual sheets like this:

var sheet = ss.getSheetByName("name");

where "name" is a string representing the sheet name.

Once you have your sheet variable (or whatever you want to name it), you can insert data just like you already did: use getRange() to get the range where you want the data, and setValues() to actually insert the data.

Upvotes: 1

Related Questions