0

This code works perfect but sends the whole sheet, I am wanting to only send the current tab/sheet. How do I make it where it only captures the current tab columns A-H instead of the entire thing google sheet with all tabs?

function send_report_email(spreadsheetId) {
  var spreadsheet   = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId(); 
  var file          = DriveApp.getFileById(spreadsheetId);
  var url           = 'https://docs.google.com/spreadsheets/d/'+spreadsheetId+'/export?format=xlsx';
  var token         = ScriptApp.getOAuthToken();
  var response      = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

 var fileName = (spreadsheet.getName()) + '.xlsx';
 var blobs   = [response.getBlob().setName(fileName)];

 var receipient = "andrew@barrelrace.com"
 var subject = "xxxx"
 var emailbody = "first line \n\nsecond line \n\nCheers!,"

 MailApp.sendEmail(receipient, subject, emailbody, {attachments: blobs});
  }
Lonestar Dads
  • 13
  • 4
  • Try getting the Sheet ID and appending that to your URL as a `gid` parameter - see [this Stack Overflow question's](https://stackoverflow.com/questions/27000699/google-spreadsheet-direct-download-link-for-only-one-sheet-as-excel) answers for more details. – Clay Roper Mar 24 '21 at 17:08
  • Can you give me an example by using the code above? – Lonestar Dads Mar 24 '21 at 18:08
  • I figured out where to put the gid... but now I'm curious if I can have it only download columns A through H of that specific tab? – Lonestar Dads Mar 24 '21 at 18:21
  • I might try something like copying the columns you want to a new, temporary, Sheet, get that sheet's GID, export the Excel file, then delete the temp sheet – Clay Roper Mar 24 '21 at 18:27

1 Answers1

1

You can follow these steps to achieve your goal:

  1. Create a temporary sheet
  2. Copy the data from A-H to temp sheet
  3. Delete the columns after column H (This is optional since you only copy the data of column A-H to a temporary sheet and some application may still show the deleted columns)
  4. Get the gid of the temporary sheet
  5. Fetch the sheet by adding /export?format=xlsx&gid='+tempSheetId to the url.
  6. Get the the response blob and attach to an email.
  7. Delete temporary sheet.

Example:

Sample Data:

enter image description here

Code:

function send_report_email() {
  var spreadsheet   = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheetRange = sheet.getDataRange();

  //check if tempsheet is existing and delete if true
  var tempSheet = spreadsheet.getSheetByName("tempSheet");
  if (tempSheet != null) {
    spreadsheet.deleteSheet(tempSheet);
  }

  //copy data from Sheet1 to temp sheet
  var temp = spreadsheet.insertSheet("tempSheet", spreadsheet.getSheets().length);
  sheetRange.copyTo(temp.getRange(1,1));
  //remove columns after column H.
  temp.deleteColumns(9, 18);
  
  var tempSheetId = temp.getSheetId();    
  var spreadsheetId = spreadsheet.getId(); 

  var url           = 'https://docs.google.com/spreadsheets/d/'+spreadsheetId+'/export?format=xlsx&gid='+tempSheetId;
  var token         = ScriptApp.getOAuthToken();
  var response      = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  var fileName = (spreadsheet.getName()) + '.xlsx';
  var blobs   = [response.getBlob().setName(fileName)];

  var receipient = "email here"
  var subject = "xxxx"
  var emailbody = "first line \n\nsecond line \n\nCheers!,"

  MailApp.sendEmail(receipient, subject, emailbody, {attachments: blobs});
  //delete temporary sheet
  spreadsheet.deleteSheet(temp);
}

Email attachment:

enter image description here

Reference:

Nikko J.
  • 5,319
  • 1
  • 5
  • 14
  • Thank you! Figured out it's easier to just create a different tab. Question, what's the code to get the GID for that specific tab using the regular code I attached above? So where the var URL will actually get the current sheet gid instead of me having to manually type it in. – Lonestar Dads Mar 24 '21 at 19:32
  • 1
    To get the GID of a specific tab, you can use [getSheetId()](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetid). In your code you can add `var sheetId = spreadsheet.getSheetByName("Insert Sheet Name Here").getSheetId()` or `var sheetId = SpreadsheetApp.getActiveSheet().getSheetId()` – Nikko J. Mar 24 '21 at 19:53