0

My first time scripting, I'm trying to create an order sheet for outside sales reps, that they can fill out, and click a button to submit a template file. Sheets 0 & 1 are survey questions that fill in references on sheet 2 (Final Product). Sheets 3 & 4 are data validation fields for sheets 0 & 1 including email addresses for recipients.

I am trying to copy a spreadsheet to a new spreadsheet, --> converting a specific sheet from the copy to text so that I can delete all sheets except the converted sheet and not get reference errors --> email converted sheet via pdf format to contacts on sheet 3. My code does it all except delete the 4 sheets that I want to(0,1,3,4). The script is a container script, so whenever I try to call SpreadsheetApp.getActiveSpreadsheet(); It automatically grabs the container file and deletes the 'template' sheets. I need to know how to delete indexes 0,1,3,4 of an external spreadsheet, in a different folder of my drive. The code below is a hodgepodge of snippets I have butchered and pieced together. I hope you can understand all of this.

Here is what I have:

function SubmitOnClicks() {
  // Set the Active Spreadsheet so we don't forget
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var orderSheet = ss.getSheetByName("Order Sheet");
  var ValidationRef = ss.getSheetByName("Validation References");
  orderSheet.activate();

  // Set the message to attach to the email.
  var message = "Please see attached";
  
  // Get Project Name from Range B3:D3
  var projectname = ss.getRange("B3").getValues();
  // Get BFS Size from Range C25:E25
  var size = ss.getRange("C25:E25").getValues();
  // Construct the Subject Line
  var subject = projectname + " " + size;
  
  // Get contact details from "Validation References" sheet and construct To: Field
  var numRows = ValidationRef.getLastRow();
  var emailTo = ValidationRef.getRange(2, 12, 5, 2).getValues();
  
  // Google scripts can't export just one Sheet that I know of
  var submittalDate = orderSheet.getRange(17, 2).getValues();
  var submittalName = "BFS Submittal"
  var folderID = "My Drive Folder ID"; // Folder id to save Copy to: MyDrive/_Sheets/Shared/BFS Exports
  var folder = DriveApp.getFolderById(folderID);
  var sourceSheet = ss.getSheetByName("Order Sheet");
  var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  var sourcevalues = sourceRange.getValues();
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(ss.getId()).makeCopy(submittalName, folder))
  var destSheet = destSpreadsheet.getSheets()[2];
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  
  // Replace cell values with text (to avoid broken references)
  destRange.setValues(sourcevalues);
  
  var files = DriveApp.searchFiles(
    'mimeType = "BFS Submittal' + MimeType.GOOGLE_SHEETS + '"');
  while (files.hasNext()) {
  var spreadsheet = SpreadsheetApp.open(files.next()); //I'm stuck after this Line
  var sheet = spreadsheet.getSheets()[0, 1, 3, 4]; 
  
        
  }
    
  // Make the PDF, currently called "BFS Submittal.pdf"
  var pdf = DriveApp.getFileById(ss.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:'BFS Submittal',content:pdf, mimeType:'application/pdf'};
  
  // Send the freshly constructed email 
  MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
 
}
Tanaike
  • 181,128
  • 11
  • 97
  • 165
Rich
  • 1
  • 1
  • 1
    If my understanding of your situation is correct, for example, how about checking the methods for opening Spreadsheet like [open(file)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openfile), [openById(id)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyidid) and [openByUrl(url)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyurlurl)? – Tanaike Dec 10 '18 at 23:04
  • I have seen these in my search. Once I get the file, how do I make a sheet active and then delete it. Under what namespace or class do I use: DriveApp, Spreadsheet, Folder, File? I feel like I have tried them all, I get an undefined error. – Rich Dec 11 '18 at 03:43
  • 1
    @Rich To delete a sheet use the Spreadsheet service (SpreadsheetApp) – Rubén Dec 11 '18 at 03:45
  • Possible duplicate of [Deleting sheets through script](https://stackoverflow.com/questions/43936740/deleting-sheets-through-script) – Rubén Dec 11 '18 at 03:50

0 Answers0