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