How do I add CSV or XLS content to next available row from Gmail into Google Sheets?

Question

I have an email that comes everyday with an attached .xls (sometimes a CSV) spreadsheet file. I would like to import this SS everyday and have the new content added to the next available rows.

How do I adjust this script to do that action?
I would like to have this script add the content to a specific sheet/tab.
In addition, how would I make this script look for a specific label instead of my primary inbox to pull the file from?

Reference of script: Import CSV from Gmail attachment into Google Sheets

function importCSVFromGmail() {

var threads = GmailApp.search("from:Email Address Here");
var messages = threads[0].getMessages();
var message = messages[messages.length - 1];
var attachment = message.getAttachments()[1];

// Is the attachment a CSV file

attachment.setContentType('text/csv'); 
//attachment.setContentTypeFromExtension();

if (attachment.getContentType() === "text/csv") {

var sheet = SpreadsheetApp.getActiveSheet();
var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");

// Remember to clear the content of the sheet before importing new data
sheet.clearContents().clearFormats();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
 }
}

Tanaike Tanaike · Accepted Answer · 2019-10-31T22:48:02

You want to append the CSV data and XLSX data to the specific tab in the active Spreadsheet.
You want to retrieve emails with the specific label of the gmail.
You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Modification points:

Data can be directly retrieved from CSV file. But in the case of XLSX file, it is required to convert it to Google Spreadsheet and retrieve the data from the converted Spreadsheet.
- XLSX file is converted to Google Spreadsheet with Files: insert of Drive API.
In order to append the data to the specific tab, in this modification, I used getSheetByName() and getLastRow().
In order to retrieve emails with the specific label, I used the search query of label:###label###.

Modified script:

Please modify your script as follows. And before you run the script, please enable Drive API at Advanced Google services.. And please set the tab name to sheetName. And please set the search query to GmailApp.search("from:###email### label:###label###"). ###email### and ###label### are the email address and the label, respectively.

function importCSVFromGmail() {
  var sheetName = "Sheet1"; // Please set the tab name you want to append the data.
  var threads = GmailApp.search("from:###email### label:###label###"); // Please set here.

  var messages = threads[0].getMessages();
  var message = messages[messages.length - 1];
  var attachment = message.getAttachments()[1];
//  attachment.setContentTypeFromExtension(); // This might be required for your situation.
  var data = [];
  if (attachment.getContentType() == MimeType.CSV) {
    data = Utilities.parseCsv(attachment.getDataAsString(), ",");
  } else if (attachment.getContentType() == MimeType.MICROSOFT_EXCEL || attachment.getContentType() == MimeType.MICROSOFT_EXCEL_LEGACY) {
    var tempFile = Drive.Files.insert({title: "temp", mimeType: MimeType.GOOGLE_SHEETS}, attachment).id;
    data = SpreadsheetApp.openById(tempFile).getSheets()[0].getDataRange().getValues();
    Drive.Files.trash(tempFile);
  }
  if (data.length > 0) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
  }
}

References:

If I misunderstood your question and this was not the result you want, I apologize.

How do I add CSV or XLS content to next available row from Gmail into Google Sheets?

1 Answers

Modification points:

Modified script:

References: