3

I have the following G.A.S script to email a google sheet as a pdf attachment.

  var spreadsheet = SpreadsheetApp.getActive();
  var subject = spreadsheet.getRange("U1:U1").getValues();
  var emailTo = spreadsheet.getRange("V1:V1").getValues();
  var message = spreadsheet.getRange("W1:W1").getValues();
  var pdf = DriveApp.getFileById(spreadsheet.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:subject,content:pdf, mimeType:'application/pdf'};
  MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});

The above code works well except that the file attached to the email message has a bizarre name like "[Ljava.lang.Object_@4e63998c" with no ".pdf" extension! I am looking for a way to set a name for the pdf file before being attached to the email. The file name should equal the "subject" variable.

Thanks in advance. Omid

Omid
  • 177
  • 2
  • 2
  • 10
  • Try as `getBlob` instead of `getBytes` ? – tehhowch Jun 19 '18 at 00:48
  • @tehhowch TypeError: Cannot find function getBlob in object Blob. – Omid Jun 20 '18 at 04:58
  • @omid use it without calling `getBytes()` – tehhowch Jun 20 '18 at 05:13
  • Dear @Tanaike, That was a great help and worked. Check your answer's comments for more detail. However, I still don't understand what does that small `[0][0]` do in the code. Could you please let me know about a page or link to learn about the use of this `[0][0]`? Thanks a lot pal. Take care :) – Omid Jun 20 '18 at 05:52
  • @Tanaike BTW, is there any way I can send this pdf file to a specific person through Telegram instead of Email!? – Omid Jun 20 '18 at 06:01
  • @tehhowch When I drop `getBytes()`, I get this error message "Attachment content not recognized as string or binary." – Omid Jun 20 '18 at 06:09
  • I thought that @tehhowch might propose to modify from ``var pdf = ...`` and ``var attach = ...`` in your script to ``var attach = DriveApp.getFileById(spreadsheet.getId()).getBlob().setName(subject[0][0]);``. To tehhowch. If I misunderstand your comment, I'm sorry. – Tanaike Jun 20 '18 at 06:38

2 Answers2

1

Values retrieved by getValues() is 2 dimensional array. I think that the filename becomes such string because the array is used as the filename. Please retrieve the element from the array and try again. So could you please modify as follows?

From :

var attach = {fileName:subject,content:pdf, mimeType:'application/pdf'};

To :

var attach = {fileName:subject[0][0],content:pdf, mimeType:'application/pdf'};

You can also use the following modification. In this case, getValue() can retrieve the value as a string from the cell "U1".

From :

var subject = spreadsheet.getRange("U1:U1").getValues();

To :

var subject = spreadsheet.getRange("U1:U1").getValue();

Reference :

If this was not what you want, please tell me. I would like to think of other solutions.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Wow! Great! It solved the issue when I inserted `[0][0]`. However, changing `getValues()` to `getValue()` resulted in a file name consisting of only one character which was the first character of the specified variable's value. BTW, thanks a lot. Bravo :) – Omid Jun 20 '18 at 05:48
  • I also appreciate your clear and detailed answer that was completely useful to a novice like me and I could easily apply that. Thanks a lot pal :) – Omid Jun 20 '18 at 05:53
  • BTW, is there any way I can send this pdf file to a specific person through Telegram instead of Email!? – Omid Jun 20 '18 at 06:00
  • And one more question. The pdf file which is attached includes about 13 pages but only the first couple of pages contain information and the other pages are blank. Is there a way to limit the pdf file only to the part of the sheet that contains data and trim the blank pages?! – Omid Jun 20 '18 at 06:04
  • @Omid Thank you for your response. ``[0][0]`` of ``subject[0][0]`` means the address of the element in the 2 dimensional array retrieved by ``getValues()``. In your case, the value from "U1:U1" is only one. So the address is ``[0][0]``. You can see the detail information at https://stackoverflow.com/questions/966225/how-can-i-create-a-two-dimensional-array-in-javascript – Tanaike Jun 20 '18 at 06:15
  • Thanks for your reply and useful explanation. But you forgot to include the link to detailed information. :) – Omid Jun 20 '18 at 06:18
  • @Omid I'm sorry. I added the URL. Please confirm it. – Tanaike Jun 20 '18 at 06:18
  • @Omid I'm sorry for my poor English skill. About your additional questions, could you please post it including the detail information as a new question? Because I couldn't understand your situation from your comments. If you posted it as a new question, I think that it will help many users including me think of your solution. – Tanaike Jun 20 '18 at 06:20
  • Thanks I got the link :) – Omid Jun 20 '18 at 06:20
  • Actually your English is very good. OK I will post it as a new question. Cheers. – Omid Jun 20 '18 at 06:22
  • @Omid Thank you for quick reply. Also I would like to confirm about your post. – Tanaike Jun 20 '18 at 06:23
  • Dear @Tanaike I posted my question as a new post here https://stackoverflow.com/q/50941647/9863856 – Omid Jun 20 '18 at 06:32
  • @Omid Thank you for letting me know it. – Tanaike Jun 20 '18 at 06:33
0

I'm a bit late, but another way to solve this problem might be:

var spreadsheet = SpreadsheetApp.getActive();
var subject = spreadsheet.getRange("U1:U1").getValues();
var emailTo = spreadsheet.getRange("V1:V1").getValues();
var message = spreadsheet.getRange("W1:W1").getValues();

var pdf = DriveApp.getFileById(spreadsheet.getId())
                  .getAs('application/pdf')
                  .getBlob()
                  .setName(subject);

MailApp.sendEmail(emailTo, subject, message, {attachments:[pdf]});

The Blob class has a setName method https://developers.google.com/apps-script/reference/base/blob#setName(String), that can be chained into a Blob object (which is the result of getBlob())

After that you just need to add the Blob object inside attachments array of function MailApp.sendEmail

0xh8h
  • 3,271
  • 4
  • 34
  • 55