google script specify active sheet

Question

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.

Answer 1

I've fixed it up a little and added some comments. There were a lot of little things I fixed up biggest thing was that you should reuse variables that you've created.

This hasn't been tested...

function endOfWK_1 () {

  //Replace all formulas in range "WK 1!A6:A29" with values

  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetWK1 = activeSpreadsheet.getSheetByName('WK 1');
  var range = sheetWK1.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(); //this should probably be changed depending on what sheet you are trying to access: activeSpreadsheet.getSheetByName('Email')

  // 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"); //Create a spreadsheet to copy to
  // var originalSheet = activeSpreadsheet.getSheetByName("WK1");  Already defined above as sheetWK1

  //var projectname = SpreadsheetApp.getActiveSpreadsheet(); Seems like this is not used.

  sheetWK1.copyTo(newSpreadsheet); //Take the original sheet and copy it to the newSpreadsheet

  // Find and delete the default "Sheet1"
  newSpreadsheet.deleteSheet(newSpreadsheet.getSheetByName("Sheet1")); //We can just call the deleteSheet method.

  // Create the PDF, currently called "Tracking Sheet.pdf"
  var pdf = newSpreadsheet.getAs('application/pdf').getBytes(); //No need to get the Spreadsheet object again, as we alreat have it!
  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
  newSpreadsheet.setTrashed(true); //Again no need to find the object. We have it.

  // Write the date and time that the script ran
  var date = sheet.getRange('Statistics!A1').getValues();
  activeSpreadsheet.getRange('Analysis!E5').setValues(date);

}

The main issue was var originalSpreadsheet = SpreadsheetApp.getActive(); you are getting the active sheet and using that to create you pdf.

---

EDIT: I've cleaned up the whole thing a little and ended up with this. It hasn't been tested.

function endOfWK_1 () {
 //Replace all formulas in range "WK 1!A6:A29" with values

  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetWK1 = activeSpreadsheet.getSheetByName('WK 1');
  var emailSheet = activeSpreadsheet.getSheetByName("Email");

  var range = sheetWK1.getRange("WK 1!A6:A29");
  range.copyTo(range, {contentsOnly: true});

  // FOR WK1 ONLY!!!

  // Set the message to attach to the email.
  var message = "Please see attached.";

  // Get Dates from Email!B5
  var period = emailSheet.getRange("Email!B5").getValues();

  // Construct the Subject Line
  var subject = period;

  // Get contact details from "Email" sheet and construct To: Header
  var numRows = emailSheet.getLastRow();
  var emailTo = emailSheet.getRange(17, 2, numRows, 1).getValues();

  // Create a new Spreadsheet and copy the current sheet into it.
  var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");

  sheetWK1.copyTo(newSpreadsheet);

  // Find and delete the default "Sheet1"
  newSpreadsheet.deleteSheet(newSpreadsheet.getSheetByName("Sheet1"));

  // Create the PDF, currently called "Tracking Sheet.pdf"
  var pdf = newSpreadsheet.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 = activeSpreadsheet.getRange('Statistics!A1').getValues();
  activeSpreadsheet.getRange('Analysis!E5').setValues(date);

}