CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
ExcelNoob
ExcelNoob

Reputation: 23

Import XLS from Gmail - sheet based on yesterdays date

I have the following script which imports XLS attachments from my email.

At present, it imports it into a specified sheet name (in the below example "xxxx").

I would instead like it to import into a sheet name based on the day prior to the script being run (as it is reporting data on the previous day in a DD/MM/YY) format.

function ImportXLSFromGmail_Stats() {
  var sheetName = "xxxx";
  var threads = GmailApp.search("from:[email protected] subject:Email Subject");

  var messages = threads[0].getMessages();
  var message = messages[messages.length - 1];
  var attachment = message.getAttachments()[0];
  attachment.setContentTypeFromExtension();
  var data = [];
  (attachment.getContentType() == MimeType.MICROSOFT_EXCEL || attachment.getContentType() == MimeType.MICROSOFT_EXCEL_LEGACY)
    var tempFile = Drive.Files.insert({title: "temp_Availability", 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(1, 1, data.length, data[0].length).setValues(data);
  }
}

Upvotes: 2

Views: 87

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

You want to:

  • get the date from yesterday:

    const date = new Date();
date.setDate(date.getDate() - 1);

  • format it to DD/MM/YY and assign that to sheetName:

    const sheetName =  Utilities.formatDate(date, timezone, "dd/MM/YY")

  • check if a sheet with this name exist, otherwise create it:

    const sheets = ss.getSheets().map(sh=>sh.getName());
if(!sheets.includes(sheetName)){
   ss.insertSheet(sheetName);
};

Solution:

function ImportXLSFromGmail_Stats() {
  const ss = SpreadsheetApp.getActive();
  const date = new Date();
  const timezone = ss.getSpreadsheetTimeZone();
  date.setDate(date.getDate() - 1);
  const sheetName =  Utilities.formatDate(date, timezone, "dd/MM/YY"); // logs 04/02/21  
  const sheets = ss.getSheets().map(sh=>sh.getName());
  if(!sheets.includes(sheetName)){
     ss.insertSheet(sheetName);
  };
  
  //
  // rest of your code
  //
  
}

References:

Upvotes: 2

Related Questions