Email script is not sending the correct sheet from Google spreadsheet

Question

I have the code below which creates a duplicate sheet, adds some data and then sends that new sheet as an email attachment. The issue is that the attachment is the sheet called sending ALL sheets and not solely the newly created sheet. I think what i need to do is set the new sheet as active setActiveSheet or Activate, but neither of these have worked for me.

  var OrderSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("OTW Sheet");
  var Row = OrderSheet.getLastRow();
  var Values1 = OrderSheet.getRange(Row, 5).getValue();
  var Values2 = OrderSheet.getRange(Row, 6).getValue();
  var Values3 = OrderSheet.getRange(Row, 7).getValue();
  var Values4 = OrderSheet.getRange(Row, 8).getValue();
  var Values5 = OrderSheet.getRange(Row, 9).getValue();
  var Values6 = OrderSheet.getRange(Row, 2).getValue();



  var Invoice = SpreadsheetApp.openById("13JpYjMNg5CcGwnRX_ZMXJIw5jeGfUrVvAC47xCNzHdg"); 
  Invoice.getSheetByName("BLANK").copyTo(Invoice).setName("OTW Invoice " + new Date).activate();


  var Newsheet = Invoice.getActiveSheet();
  Newsheet.getRange(17, 2).setValue(Values1);
  Newsheet.getRange(18, 2).setValue(Values2);
  Newsheet.getRange(19, 2).setValue(Values3);
  Newsheet.getRange(20, 2).setValue(Values4);
  Newsheet.getRange(21, 2).setValue(Values5);
  Newsheet.getRange(6, 7).setValue(Values6);

  var message = {
    to: "wilson.aidan101@gmail.com",
    subject: "Test Invoice",
    body: "Hi,\n\nPlease find the latest invoice attached.\n\nThank you,\nAidan",
    name: "Aidan",
    attachments: [Invoice.getAs(MimeType.PDF).setName("Invoice")]
  }
  MailApp.sendEmail(message);

  OrderSheet.getRange(Row, 1).setValue("COMPLETE");

`

Issue resolved using [this link](https://stackoverflow.com/questions/67984549/how-to-select-a-specific-google-sheet-and-attach-as-a-pdf-for-sending-email-usin/). — Aidan Wilson, Dec 19 '21 at 20:15
If that answer helps you can upvote it. The more votes for useful answers the better for everyone. — Yuri Khristich, Dec 20 '21 at 14:26

score 0 · Answer 1 · answered Dec 17 '21 at 23:35

0

As a guess, try to add:

Invoice.moveActiveSheet(1);

Before this line:

var Newsheet = Invoice.getActiveSheet();

Perhaps you see only first sheet in the PDF. So it makes sense to make the 'Newsheet' a first sheet.

answered Dec 17 '21 at 23:35

Yuri Khristich

13,448
2
8
23

So the attachment is actually sending ALL sheets. I only want to send the newly created sheet. – Aidan Wilson Dec 18 '21 at 15:10
1

Look here: https://stackoverflow.com/questions/67984549/how-to-select-a-specific-google-sheet-and-attach-as-a-pdf-for-sending-email-usin/ – Yuri Khristich Dec 18 '21 at 15:28
Does the link above solve your problem? – Yuri Khristich Dec 19 '21 at 12:31
Yes its does. Thank you. – Aidan Wilson Dec 19 '21 at 20:14
1

The best way to thank is to upvote the useful answer. – Yuri Khristich Dec 19 '21 at 22:11

Email script is not sending the correct sheet from Google spreadsheet

1 Answers1