Use Google App Script to email only ONE of the 4 'tabs' in a worksheet

Question

NOT A CODER - opportunistic stumbler through little scripts/code. I currently have a google spreadsheet that is connected to a google form. It cointains four tabs, namely ChangeLog, Secondary, form responses 1 (hidden), send log (hidden):

• ChangeLog: Is a tab using a query (linked to Form Responses 1) and formatting to pull the raw data in, looking a certain way. Is the 'Active' sheet

• Secondary: Is a tab using a query (linked to Form Responses 1) to show only select columns

• Form Responses 1: is hidden; houses raw info from the google form.

• Send Log: is hidden; from an addon; not used/important

I have a script that sends the whole spreadsheet to specific people on a daily basis; borrowed from someone kind enough to post their script online. I'd like to write a second script file that would send ONLY the 'Secondary' sheet/tab out (not sharing protected information), same schedule as the current script. I've hunted around and played with my script but just get errors OR I still get all the tabs.

This is my current working 'sends it all to everyone' script... what can I add to limit what is sent to ONE SHEET/TAB? I can't even have the other tabs hidden.

function sendEmail() {

  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  //var email = Session.getUser().getEmail();

  var email_ID1 = "email1@co.com";

  var email_ID2 = "email2@co.com";

  var email_ID3 = "email3@co.com";

  var subject = "Change Notice Log";

  var body = "Attached is the Change Notice Log. You will be asked if you trust this attachment, please select 'Yes' at the prompt. This daily report will capture all submittals, not just the day sent, so please refer to the timestamp column for the most recent date. This log is scheduled to automatically send on a daily basis, regardless of new submissions. If you have any questions, please contact HR Team. Thanks!  ";

  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};

  var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=xlsx&id="+ssID;

  var result = UrlFetchApp.fetch(url , requestData);  
  var contents = result.getContent();

  MailApp.sendEmail(email_ID1,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

  MailApp.sendEmail(email_ID2,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

  MailApp.sendEmail(email_ID3,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

};

I apologize if this has been asked & answered somewhere, I've scrolled through a lot and couldn't make any other suggestions work for me. I appreciate any help anyone can provide.

Answer 1

To attach a specific sheet you need to include a sheet Id in your url as mentioned here. Basically, your url structure will look like this:

https://docs.google.com/spreadsheets/d/KEY/export?format=csv&id=KEY&gid=SHEET_ID

Where Key is your Spreadsheet ID and Sheet_ID is the ID of the sheet you want to export

To get Sheet ID programmatically you can use a function like this

function getSheetID(name){
 var ss = SpreadsheetApp.getActive().getSheetByName(name)
 var sheetID = ss.getSheetId().toString() 
 return sheetID
}

You Final code will be the following:

function sendEmail() {

  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  //var email = Session.getUser().getEmail();

  var email_ID1 = "email1@co.com";

  var email_ID2 = "email2@co.com";

  var email_ID3 = "email3@co.com";

  var subject = "Change Notice Log";

  var body = "Attached is the Change Notice Log. You will be asked if you trust this attachment, please select 'Yes' at the prompt. This daily report will capture all submittals, not just the day sent, so please refer to the timestamp column for the most recent date. This log is scheduled to automatically send on a daily basis, regardless of new submissions. If you have any questions, please contact HR Team. Thanks!  ";

  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
  var shID = getSheetID("Secondary") //Get Sheet ID of sheet name "Secondary"
  var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=xlsx&id="+ssID+"&gid="+shID;

  var result = UrlFetchApp.fetch(url , requestData);  
  var contents = result.getContent();

  MailApp.sendEmail(email_ID1,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

  MailApp.sendEmail(email_ID2,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

  MailApp.sendEmail(email_ID3,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

};
function getSheetID(name){
 var ss = SpreadsheetApp.getActive().getSheetByName(name)
 var sheetID = ss.getSheetId().toString() 
 return sheetID
}

Hope that helps.