how Send specific part of the sheet as email?

2
votes

this script sends a pdf copy to my email when I type Send in cell L6 , this code send the whole sheet and I need to send as only A1:J22 , any Ideas ? 

function onEdit2(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var r  = sheet.getRange('L6').getValue();
  if (r == "Send") {
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var ssID = ss.getId();
    var sheetgId = ss.getActiveSheet().getSheetId();
    var sheetName = ss.getName();

    var token = ScriptApp.getOAuthToken();

    var email = "EMAIL HERE";
    var subject = "Daily report ";
    var body = "Please find the attached Daily report";

    var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export?" 
    + "format=xlsx" +  "&gid="+sheetgId+ "&portrait=true" + 
      "&exportFormat=pdf";

    var result = UrlFetchApp.fetch(url, {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });

    var contents = result.getContent();

    MailApp.sendEmail(email,subject ,body, {
      attachments: [{
        fileName: sheetName + ".pdf", 
        content: contents, 
        mimeType: "application//pdf"
      }]
    })
  }
}
1
google-apps-scriptgoogle-sheets

1 Answers

1
votes
  • You want to retrieve the cells of A1:J22 and want to create them to PDF data.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • In this modification, create a temporal sheet and copy the values of the cells of A1:J22 to it. Then, the temporal sheet is converted to PDF file. And, the temporal sheet is deleted.

Modified script:

When your script is modified, please modify as follows.

function onEdit2(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var r  = sheet.getRange('L6').getValue();
  if (r == "Send") {
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var ssID = ss.getId();

    var sheet = ss.getActiveSheet(); // Added
//    var sheetgId = ss.getActiveSheet().getSheetId(); // removed

    var sheetName = ss.getName();
    var token = ScriptApp.getOAuthToken();
    var email = "EMAIL HERE";
    var subject = "Daily report ";
    var body = "Please find the attached Daily report";

    var tempSheet = ss.insertSheet("tempSheet"); // Added
    sheet.getRange("A1:J22").copyTo(tempSheet.getRange(1, 1)); // Added
    SpreadsheetApp.flush(); // Added
    var sheetgId = tempSheet.getSheetId(); // Added

    var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export?" + "format=xlsx" +  "&gid="+sheetgId+ "&portrait=true" + "&exportFormat=pdf";
    var result = UrlFetchApp.fetch(url, {headers: {'Authorization': 'Bearer ' +  token}});
    var contents = result.getContent();
    MailApp.sendEmail(email,subject ,body, {attachments: [{fileName: sheetName + ".pdf", content: contents, mimeType: "application//pdf"}]});

    ss.deleteSheet(tempSheet); // Added
  }
}

References:

If I misunderstood your question and this was not the direction you want, I apologize.