Google Apps Script Importation of a CSV file into Google Sheets from Gmail Sub-Label

Question

I am very new to google apps script and I am trying to automated some daily reports. I receive updated reports to my inbox daily and I am trying to use a code to grab this updated CSV report daily from my inbox and import into the appropriate sheet. I've created the code and set up a daily trigger to update the sheet.

Here is the code I'm using:

function automatedReportsGAM() {
  var labelName = 'MY_GMAIL_LABEL_NAME';
  var spreadsheetId = '1l6qtLINKCTLkxl0WqUjmCxb8VSZynQ7jJtJqwni5JGA';
  var sheetName = 'RAW 2023 DATA';

  // Get the Gmail label
  var label = GmailApp.getUserLabelByName(labelName);

  // Get the latest email with the specified label
  var threads = label.getThreads();
  if (threads.length === 0) {
    Logger.log('No emails with the label: ' + labelName);
    return;
  }
  var latestEmail = threads[threads.length - 1].getMessages()[0]; // Get the latest email in the latest thread

  // Fetch the attachments of the latest email
  var attachments = latestEmail.getAttachments();

  // Look for the CSV attachment
  var csvAttachment = null;
  for (var i = 0; i < attachments.length; i++) {
    if (attachments[i].getContentType() === 'application/octet-stream') {
      csvAttachment = attachments[i];
      break;
    }
  }

  // If CSV attachment not found, log and exit
  if (!csvAttachment) {
    Logger.log('No CSV attachment found in the latest email.');
    return;
  }

  // Read the CSV data as a string
  var csvData = csvAttachment.getDataAsString();

  // Parse the CSV data
  var parsedCsvData = Utilities.parseCsv(csvData);

  // Open the target spreadsheet and sheet
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName(sheetName);

  // Clear the entire sheet except for columns L-R
  sheet.getRange(1, 1, sheet.getLastRow(), 11).clearContent();

  // Write the parsed CSV data to the sheet, starting from cell A1
  sheet.getRange(1, 1, parsedCsvData.length, parsedCsvData[0].length).setValues(parsedCsvData);

  // Get the last row containing data in columns A-K
  var lastDataRow = sheet.getLastRow();

  // Get the formulas from row 2 in columns L-R
  var formulas = sheet.getRange(2, 12, 1, sheet.getLastColumn() - 11).getFormulasR1C1()[0];

  // Fill down the formulas to match the number of rows in columns A-K
  var formulasFilled = [];
  for (var i = 0; i < lastDataRow - 1; i++) {
    formulasFilled.push(formulas);
  }

  // Set the formulas in columns L-R from row 2 to the last row containing data in columns A-K
  sheet.getRange(2, 12, formulasFilled.length, formulasFilled[0].length).setFormulasR1C1(formulasFilled);
}

A couple of Nuances:

• The spreadsheet is comprised of two sections. First is columns A-K which are the columns that are supposed to be cleared and updated with the most recent daily report when the import happens.

• Columns L-R include column headers in row 1 and formulas beginning in row 2 which we need to keep intact when the import happens.

• We need the code to perform the following: locate the latest CSV attachment within the. label, clear the data entirely from columns A-K, paste the newest data from the CSV report into columns A-K, then understand what row the data ends in columns A-K and fill handle down the formulas in columns L-R to the last row containing data in columns A-K.

• Content type for the attachment is reported as "application/octet-stream" instead of "text/csv," which is why the script couldn't identify it as a CSV attachment. The "application/octet-stream" content type is a generic binary data type, and it can be used for various types of binary files, including CSV files. However, since the script was specifically looking for the "text/csv" content type, it didn't recognize the attachment as a CSV file. To resolve this issue, we've modified the script to check the attachment's file extension instead of relying solely on the content type. The ".csv" file extension is present in the email attachments. This seemed to resolve the issue of the code not recognizing a CSV attachment present.

The code seems to run correctly and finishes execution. It does a good job at keeping the formatting the same and fill handling the appropriate formulas down. However, it is either not grabbing the latest attachment within the thread or it is not fully importing the entire contents of the CSV attachment as there is 21 rows of data missing from the latest import.

Could you please advise on how I can tweak this to perform what I need it to do?

Answer 1

You have a script to extract a CSV file from a daily email marked by a label. The script executes without error but it is [...] not grabbing the latest attachment within the thread.

The problem is that you need the latest thread but your script is always returning the second last thread.

label.getThreads(); returns an array of threads. The order starts with the most recent thread and runs to the oldest thread.

DELETE

• var latestEmail = threads[threads.length - 1].getMessages()[0];

SUBSTITUTE

• var latestEmail = threads[0].getMessages()[0];