and thanks for the help. I've got a noob question, and need a noob answer.
Trying to email a specific google sheet as a pdf weekly, but script emails out whatever sheet happens to be open at the time.
Stole various snippets of code, here's what I've got: (And no, I don't think that this block of code was formatted and posted correctly.)
function endOfWK_1 () {
//This script converts all formulas to values in the currently displayed sheet, then converts the currently displayed sheet to a pdf, then emails the
pdf as an attachment to the addresses shown in cell B17 in the "Email" sheet.
//Replace all formulas in range "WK 1!A6:A29" with values
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('WK 1');
var range = sheet.getRange("WK 1!A6:A29");
range.copyTo(range, {contentsOnly: true});
// FOR WK1 ONLY!!!
// Set the Active Spreadsheet so we don't forget
var originalSpreadsheet = SpreadsheetApp.getActive();
// Set the message to attach to the email.
var message = "Please see attached.";
// Get Dates from Email!B5
var period = originalSpreadsheet.getRange("Email!B5").getValues();
// Construct the Subject Line
var subject = period;
// Get contact details from "Email" sheet and construct To: Header
var contacts = originalSpreadsheet.getSheetByName("Email");
var numRows = contacts.getLastRow();
var emailTo = contacts.getRange(17, 2, numRows, 1).getValues();
// Create a new Spreadsheet and copy the current sheet into it.
var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var projectname = SpreadsheetApp.getActiveSpreadsheet();
sheet = originalSpreadsheet.getActiveSheet();
sheet.copyTo(newSpreadsheet);
// Find and delete the default "Sheet1"
newSpreadsheet.getSheetByName('Sheet1').activate();
newSpreadsheet.deleteActiveSheet();
// Create the PDF, currently called "Tracking Sheet.pdf"
var pdf = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
var attach = {fileName:'Tracking Sheet.pdf',content:pdf, mimeType:'application/pdf'};
// Send the freshly constructed email
MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
// Delete the sheet that was created
DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true);
// Write the date and time that the script ran
var date = sheet.getRange('Statistics!A1').getValues();
SpreadsheetApp.getActiveSheet().getRange('Analysis!E5').setValues(date);
}
This is a bound script, attached to a google workbook containing 5 sheets. My problem is that my script always emails the sheet that happens to be open at the time.
I want to email one specific sheet, whether the workbook is open or closed. How can I do this? (I hope to install a trigger to make this script run automatically.)
Also, anyone want to critique my code?
Thanks to all.