Pulling Data from an Email and Pasting it into a Google Spreadsheet

Question

Background

The reason why I'm doing this is because at my company, we get emails a lot from different locations that have relevant data we need for other processes. The problem is that this data is always in a CSV or excel file format, and downloading the file, then copying all of the data, then pasting it in the proper location takes away time that people could be using on other relevant business-related tasks.

To mitigate the above issue, I've been working on a Google App Script library for my company that simplifies a lot of the functions that we use on a regular basis inside of app script. Because I'm still fairly new to JS and how function overloading works, I realized I can concatenate some of the functions currently in the library more succinctly, so they're more readable. Below is an example of what 4 internal functions looked like inside another function, and then with some fiddling what I was able to decrease it to.

What the code does is fairly simple, a user specifies an emailSubject and a targetSheet. These two parameters must always be specified. What this does is it looks into the user's Gmail and finds the first email that matches user-specified emailSubject. Once it finds that email, it will take the first attachment (currently only if it is an excel or CSV file), takes the data from that attachment, and then pastes it onto a google spreadsheet, specifically on the sheet that the user specified as targetSheet. The other parameters are attachmentNumber, row, and column. attachmentNumber is used in the case that an email has multiple attachments, but the user only wants to pull a specific attachment that is not the first one. attachmentNumber will NOT correspond to the index in the attachments array in the emailAttachmentRetrieval function, as that is already taken into account. (So if the attachment is the fifth attachment in an email, the user just needs to enter 5 and not worry about index number of the attachments array). row and column are the index of both the row and column on the targetSheet data is initially pasted to that the user specified if they don't want the data to be pasted starting at row 1, column 1. I call the functions function1, function2, etc., because that was the way that I found it online, but if there is a different naming convention that would be superior, I am also open to hearing it.

My Request

I'm not a fan of just using if-else statements, as I know that there are other, better ways of doing this that I am currently not aware of. I tried using switch-case, but couldn't figure out how that could be an implemented properly, or even if it was an improvement. I think one of the issues is that attachmentNumber is causing issues as a parameter in functions 2 and 3. Any advice would be appreciated, and I can provide further details as necessary

My Code

function emailAttachment() {
  var function1 = function(emailSubject, targetSheet){
    let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
    let data = emailAttachmentRetrieval(emailSubject, 1)
    ss.getRange(1, 1, data.length, data[0].length).clear().setValues(data)
  }

  var function2 = function(emailSubject, targetSheet, attachmentNumber){
    if(attachmentNumber < 1){
      throw new Error('Attachment number cannot be less than 1')
    } else{
      let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
      let data = emailAttachmentRetrieval(emailSubject, attachmentNumber)
      ss.getRange(1, 1, data.length, data[0].length).clear().setValues(data)
    }
  }
  
  var function3 = function(emailSubject, targetSheet, row, column){
    if(row < 1 || column < 1){
      throw new Error('Row and column number cannot be less than 1')
    } else{
      let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
      let data = emailAttachmentRetrieval(emailSubject, 1)
      ss.getRange(row, column, data.length, data[0].length).clear().setValues(data)
    }
  }

  var function4 = function(emailSubject, targetSheet, attachmentNumber, row, column){
    if(attachmentNumber < 1){
      throw new Error('Attachment number cannot be less than 1')
    } else if(row < 1 || column < 1){
      throw new Error('Row and column number cannot be less than 1')
    } else{
      let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
      let data = emailAttachmentRetrieval(emailSubject, attachmentNumber)
      ss.getRange(row, column, data.length, data[0].length).clear().setValues(data)
    }
  
  try{
      if(arguments.length === 2){
        function1(arguments[0], arguments[1])
      } else if(arguments.length === 3){
        function2(arguments[0], arguments[1], arguments[2])
      } else if(arguments.length === 4){
        function3(arguments[0], arguments[1], arguments[2], arguments[3])
      } else if(arguments.length === 5){
        function4(arguments[0], arguments[1], arguments[2], arguments[3], arguments[4])
      }
    } catch(e){
      Logger.log(e)
    }
  }

My Attempt at Improving It:

function emailAttachment1(emailSubject, targetSheet, attachmentNumber, row, column) {
  let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
  if(attachmentNumber === null && row === null && column === null){
    let data = emailAttachmentRetrieval(emailSubject, 1)
    ss.getRange(1,1,data.length, data[0].length).clear().setValues(data)
  } else if(row === null && column === null){
    if(attachmentNumber < 1){
      attachmentError()
    } else{
      let data = emailAttachmentRetrieval(emailSubject, attachmentNumber)
      ss.getRange(1,1,data.length,data[0].length).clear().setValues(data)
    }
  } else if(attachmentNumber === null){
    if(row < 1 || column < 1){
      rowColumnError()
    } else{
      let data = emailAttachmentRetrieval(emailSubject, 1)
      ss.getRange(row, column, data.length, data[0].length).clear().setValues(data)
    }
  } else{
    if(attachmentNumber < 1){
      attachmentError()
    } else if(row < 1 && column < 1){
      rowColumnError()
    } else{
      let data = emailAttachmentRetrieval(emailSubject, attachmentNumber)
      ss.getRange(row, column, data.length, data[0].length).clear().setValues(data)
    }
  }
}

Other Relevant Code:

function emailAttachmentRetrieval(emailSubject, attachmentNumber){
  var msgs = GmailApp.getMessagesForThreads(GmailApp.search('subject: ' + emailSubject,0,1))
  for (var i = 0 ; i < msgs.length; i++) {
    for (var j = 0; j < msgs[i].length; j++) {
      var attachments = msgs[i][j].getAttachments()
    }
  }
  if(attachmentNumber < attachments.length){
    throw new Error('attachment number is outside the scope of the attachments array')
  } else{
    var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, attachments[attachmentNumber-1]).id
    var data = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0].getDataRange().getValues()
    Drive.Files.remove(convertedSpreadsheetId)
  }
  return data
}

function attachmentError(){
  throw new Error('Attachment number cannot be less than 1')
}

function rowColumnError(){
  throw new Error('Row and column number cannot be less than 1')
}

Answer 1

This is what I came up with so far that I felt helped concatenate all my code. I used a similar methodology from this question that I also asked: Sending Email Through App Script. Anyone is welcome to improve it further if they believe that there is room for improvement.

What I Changed

• Because this specificemailAttachmentRetrieval() function will only be used by the emailAttachment() function, I decided to include it within the function itself rather than leave it floating in case there is another function that would use the same name. (I don't think I will, but you never know).
• Because emailSubject and targetSheet will always be included within the function parameters, I created an object that I will pull the necessary properties I need from it when I need to
• If there are 3 or 5+ parameters, attachmentNumber is added as the third property
• Because there can be 4 or 5 parameters when calling the function, there is check to see how many there are. If there are 4, row is added to the options object as the third property, and column is added as the fourth. If there are 5 parameters, then row is added as the fourth property and column is added as the fifth
• Added a bunch of ternary operators, specifically in the data variable and calling the functions that paste the data copied from the email into the spreadsheet. Currently inside those ternary operators, if the value in the options object does exist and is greater than 1, then it is pulled from the options object and used in the execution, otherwise 1 is used.

Those are all of the things that I have currently done to improve the emailAttachment() function as it currently stands, however if someone else finds this question in the future and believes they can improve it further, please feel free to do so.

function emailAttachment(){
  try{
    const options = {
      emailSubject: arguments[0],
      targetSheet: arguments[1],
    }
    //Both options have the 3rd argument as the attachment number
    arguments.length === 3 || arguments.length >= 5 ? options.attachmentNumber = arguments[2] : null
    
    if(arguments.length >= 4){
      /*
        For 4 arguments, get the 3rd and 4th arguments
        For 5 arguments, get the 4th and 5th arguments
      */
      let index = arguments.length === 4 ? 2 : 3
      options.row = arguments[index]
      options.column = arguments[index + 1]
    }
    let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(options.targetSheet)
    let data = emailAttachmentRetrieval(options.emailSubject, (options.attachmentNumber && options.attachmentNumber > 1 ? options.attachmentNumber : 1))
    ss.getRange(
      (options.row && options.row > 1 ? options.row : 1), 
      (options.column && options.column > 1 ? options.column : 1), 
      data.length, data[0].length).clear().setValues(data)
  } catch(e){
    Logger.log(e)
  }

  //Function used in all the emailAttachment functions; Used for retrieving data from emails
  function emailAttachmentRetrieval(emailSubject, attachmentNumber){
    let msgs = GmailApp.getMessagesForThreads(GmailApp.search('subject: ' + emailSubject,0,1))
    for (let i = 0 ; i < msgs.length; i++) {
      for (let j = 0; j < msgs[i].length; j++) {
        let attachments = msgs[i][j].getAttachments()
      }
    }
    if(attachmentNumber < attachments.length){
      throw new Error('attachment number is outside the scope of the attachments array')
    } else{
      let convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, attachments[attachmentNumber-1]).id
      let data = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0].getDataRange().getValues()
      Drive.Files.remove(convertedSpreadsheetId)
    }
    return data
  }
}