Select case to email different sheet depending on selection

Question

I'm using the following script that converts a sheet to a .csv file and then emails it to a person.

RANGE="A1:AC20";
SHEET_NAME="Biomisters.csv";


//Types available : pdf,csv or xlsx
EXPORT_TYPE="csv";

function EmailBiomisters() {
  //Assign The Spreadsheet,Sheet,Range to variables
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheetByName(SHEET_NAME);
  var range=sheet.getRange(RANGE);
  
  //Range values to export
  var values=range.getValues();
  
  //Create temporary sheet
  var sheetName=Utilities.formatDate(new Date(), "GMT", "MM-dd-YYYY hh:mm:ss");
  var tempSheet=ss.insertSheet(sheetName);
  
  //Copy range onto that sheet
  tempSheet.getRange(1, 1, values.length, values[0].length).setValues(values);  
  
  //Save active sheets (Unhidden)
  var unhidden=[];
  for(var i in ss.getSheets()){
      if(ss.getSheets()[i].getName()==sheetName) continue;
      if(ss.getSheets()[i].isSheetHidden()) continue;
      unhidden.push(ss.getSheets()[i].getName());
      ss.getSheets()[i].hideSheet();
  }
  
  //Authentification 
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

  var url="https://docs.google.com/spreadsheets/d/"+ss.getId()+"/export?format="+EXPORT_TYPE;
          
  //Fetch URL of active spreadsheet
  var fetch=UrlFetchApp.fetch(url,params);
  
  //Get content as blob
  var blob=fetch.getBlob(); 
  
  var mimetype;
  if(EXPORT_TYPE=="pdf"){
      mimetype="application/pdf";      
  }else if(EXPORT_TYPE=="csv"){
      mimetype="text/csv";    
  }else if(EXPORT_TYPE=="xlsx"){
      mimetype="application/xlsx";   
  }else{
      return;
  }
  
  //Send Email
  var sheet1 = ss.getSheetByName('data').getRange("B1").getValue();
  //var mesbody = sheet1.getRange("B1");
  GmailApp.sendEmail('[email protected]',
                     'Biomisters.csv', 
                     sheet1,
                     {
                         attachments: [{
                         fileName: "Biomisters" + "."+EXPORT_TYPE,
                         content: blob.getBytes(),
                         mimeType: mimetype
                      }]
    });
  
  //Reshow the sheets
  for(var i in unhidden){
      ss.getSheetByName(unhidden[i]).showSheet();
  }
  
  //Delete the temporary sheet
  ss.deleteSheet(tempSheet);
}

What i want to achieve is to be able to select a SHEET_NAME from a drop-down in cell A3.
In this instance SHEET_NAME is "Biomisters.csv", but I have ten other sheets to choose from.
I then need to change every instance in the code where "Biomisters.csv" is mentioned to the current selection in A3.

The RANGE doesn't change, the email address to send too doesn't change either. The only things that would change on selection are:

SHEET_NAME; email subject line; email attachment

P.S. I take no credit for the above code. I found it online and edited it in the most basic form to suit my needs.

Answer 1

• Use ss.getSheetByName("Name of the sheet with the dropdown").getRange("A3").getValue() to retrieve the chosen value of the dropdown dinamically
• Use SHEET_NAME.split(".")[0] to retrieve the name of the sheet without extension - see here
• Replace the hardcoded subjet with a variable

Sample:

RANGE="A1:AC20";

//Types available : pdf,csv or xlsx
EXPORT_TYPE="csv";

function EmailBiomisters() {
  //Assign The Spreadsheet,Sheet,Range to variables
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var SHEET_NAME=ss.getSheetByName("Name of the sheet with the dropdown").getRange("A3").getValue();//"Biomisters.csv";
  var sheet=ss.getSheetByName(SHEET_NAME);
  var range=sheet.getRange(RANGE);

  //Range values to export
  var values=range.getValues();

  //Create temporary sheet
  var sheetName=Utilities.formatDate(new Date(), "GMT", "MM-dd-YYYY hh:mm:ss");
  var tempSheet=ss.insertSheet(sheetName);

  //Copy range onto that sheet
  tempSheet.getRange(1, 1, values.length, values[0].length).setValues(values);  

  //Save active sheets (Unhidden)
  var unhidden=[];
  for(var i in ss.getSheets()){
      if(ss.getSheets()[i].getName()==sheetName) continue;
      if(ss.getSheets()[i].isSheetHidden()) continue;
      unhidden.push(ss.getSheets()[i].getName());
      ss.getSheets()[i].hideSheet();
  }

  //Authentification 
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

  var url="https://docs.google.com/spreadsheets/d/"+ss.getId()+"/export?format="+EXPORT_TYPE;

  //Fetch URL of active spreadsheet
  var fetch=UrlFetchApp.fetch(url,params);

  //Get content as blob
  var blob=fetch.getBlob(); 

  var mimetype;
  if(EXPORT_TYPE=="pdf"){
      mimetype="application/pdf";      
  }else if(EXPORT_TYPE=="csv"){
      mimetype="text/csv";    
  }else if(EXPORT_TYPE=="xlsx"){
      mimetype="application/xlsx";   
  }else{
      return;
  }

  //Send Email
  var sheet1 = ss.getSheetByName('data').getRange("B1").getValue();
  //var mesbody = sheet1.getRange("B1");
  var subject = SHEET_NAME;
  var nameWithoutExtension = SHEET_NAME.split(".")[0];
  GmailApp.sendEmail('[email protected]',
                     subject, 
                     sheet1,
                     {
                         attachments: [{
                         fileName: nameWithoutExtension + "."+EXPORT_TYPE,
                         content: blob.getBytes(),
                         mimeType: mimetype
                      }]
    });

  //Reshow the sheets
  for(var i in unhidden){
      ss.getSheetByName(unhidden[i]).showSheet();
  }

  //Delete the temporary sheet
  ss.deleteSheet(tempSheet);
}