google apps script - Import XLS from Gmail - sheet based on yesterdays date

Question

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);
  }
}

question from:https://stackoverflow.com/questions/66064073/import-xls-from-gmail-sheet-based-on-yesterdays-date

Answer 1

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:

• Date
• Utilities.formatDate(date, timeZone, format)