1
votes

I have been playing around with this script for the last couple weeks. The goal of the script is to go through a reporting inbox, pull reporting data from email attachments, copy into a google spreadsheet, and then relabel the emails to remove them from the inbox to prevent accidental double copying reports.

The script functions in this order:

  1. Look for new emails in the Inbox with attachments
  2. Copy attachment data
  3. Paste into Spreadsheet in the next open row
  4. Relabel the email with "Report" instead of "Inbox" to move all reports into a reporting folder

I have successfully accomplished steps 1 - 3, but for the life of me, I can not get the relabeling to work. When I run debug in the Google Apps console, it doesn't come back with any errors. Pasted below is the excerpt from the script doing the relabeling:

  for (var i = 0; i < myLabel.length; i++) {
    labels = myLabel[i].getLabels();
    for (var j = 0; j < labels.length; j++) {
      labels[j].addLabel("test_2");
      labels[j].removeLabel("Test");
    }
  }

Below is the full script I am running.

function getCSV() {


  // Create variable that looks for Gmails in the main inbox
  var myLabel = GmailApp.getUserLabelByName("test");
  Logger.log("myLabel:",myLabel);

  // Create variable that is filled with all threads within Inbox    label
  var threads = myLabel.getThreads();
  Logger.log("threads:",threads);

  // Retrieves all messages in the specified thread
  var msgs = GmailApp.getMessagesForThreads(threads);
  Logger.log("msgs:",msgs);

  // Uses active sheet the script is implemented on
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("test");

  // Grabs CSV data from attachments and pastes into next available row in Spreadsheet
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var attachments = messages[j].getAttachments();
      Logger.log("attachments:",attachments);
      var csvData = Utilities.parseCsv(attachments[j].getDataAsString(), ",");
      Logger.log(csvData);
      for (var k = 1; k < csvData.length; k++) {
        var dataPaste = sheet.appendRow(csvData[k]);
        Logger.dataPaste;
      }
    }
  }
  // Removes Inbox Label and Adds Report Label
  for (var i = 0; i < myLabel.length; i++) {
    labels = myLabel[i].getLabels();
    for (var j = 0; j < labels.length; j++) {
      labels[j].addLabel("test_2");
      labels[j].removeLabel("Test");
    }
  }
}
1

1 Answers

0
votes

I ended up figuring this out. In addition, I added a section that can pull data if the CSVs are zipped.

function getCSV() {


  // Associated Inbox label and Report Label with variables
  var myInboxLabel = GmailApp.getUserLabelByName("Test");
  var myReportLabel = GmailApp.getUserLabelByName("test_2");

  // Create variable that is filled with all threads within Inbox label
  var threads = myInboxLabel.getThreads();
  Logger.log("threads:" + threads);

  // Retrieves all messages in the specified thread
  var msgs = GmailApp.getMessagesForThreads(threads);
  Logger.log("msgs:" + msgs);

  // Uses active sheet the script is implemented on
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("test");
/*   Script to pull data from CSV that is NOT zipped
  // Grabs CSV data from attachments and pastes into next available row in Spreadsheet
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var attachments = messages[j].getAttachments();
      Logger.log("attachments:" + attachments);
      var csvData = Utilities.parseCsv(attachments[j].getDataAsString(), ",");
      Logger.log("csvData:" + csvData);
      for (var k = 1; k < csvData.length; k++) {
        var dataPaste = sheet.appendRow(csvData[k]);
        Logger.dataPaste;
      }
    }
  }
*/

// Grabs CSV within a zip folder and pastes into next available row in Spreadsheet
 for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var attachments = messages[j].getAttachments();
      var extracted = Utilities.unzip(attachments[j]);
      var csvData = Utilities.parseCsv(extracted[j].getDataAsString(), ",");
      Logger.log(csvData);
      for (var k = 1; k < csvData.length; k++) {
        var dataPaste = sheet.appendRow(csvData[k]);
        Logger.dataPaste;
      }
    }
  }
  // Removes Inbox Label and Adds Report Label
  for (var x in threads) {
    var thread = threads[x];
    thread.removeLabel(myInboxLabel);
    thread.addLabel(myReportLabel);
  }
}