-1

I have a google sheet which I use to send simple invoices. It is basic enough and it runs through an iPad (for fixed reasons) and this is the process which is kind of clunky :

-edit the template sheet which creates an invoice sheet (that's fixed obviously)

This is the awkward part :

-share/print/select one page (the invoice sheet)/print on a local printer

-share/print/select one page (the invoice sheet)/share (pdf) (edit mail)

I was wondering is it possible to have an apps script which :

-prints the first page of the sheets document, local printer

-opens the iPad mail program (not gmail)

-attaches the first page of the sheets document as a pdf

-changes the subject to a cell in the sheet (i.e. make the subject cell C1)

-changes the body to a cell in the sheet (i.e. make the body the contents of C2)

Cliff Stamp
  • 531
  • 5
  • 11
  • 1
    How are you printing the page? Also, which file are you trying to send as a PDF? What exactly do you mean "change the subject/body to a cell"? Would you mind offering more details? Cheers! – ale13 Nov 05 '20 at 09:09

2 Answers2

2

The question is strategic. If you have to control the final message version before sending, then use Gmail link to open "Compose window" with the corresponding parameters, as described here. Please note, you have no possibility to include any attachment by link in this case. You can (and should) do it manually.

Another way is to use templates (in drafts folder) which are more flexible for fill in data fields and for attachments too. The script can do your work completely but without the message preview. GmailDraft class has a useful getMessage() method for that and you can adjust details before sending. If you work with spreadsheets, you can adjust the script to show the message "preview" (similar to compose window) in sidebar or dialog window with a "Send" batton for confirmation.

Александр Ермолин
  • 1,145
  • 1
  • 10
  • 17
  • Sorry, to clarify I am limited to iPad and specific apps so I need to use the default iPad mail program. – Cliff Stamp Nov 05 '20 at 13:55
  • @CliffStamp ipad doesn't have gmail/can't google "gmail" and pull up the web page? a gmail is free and easy to access. google scripts has gmail api, not "random generic email" api – Baby_Boy Nov 05 '20 at 14:13
  • The mail app has to be used for workplace constraints. – Cliff Stamp Nov 06 '20 at 16:19
1

Just in case, in addition to the strategic solution of Alexander Ermolin. As far as I know you can't print your documents in PDF via some printer. But you can make a PDF from spreadsheet pretty easy. Here is an example:

function myFunction() {

  var ID   = '';                                          // ID of your spreadsheet
  var copy = DriveApp.getFileById(ID).makeCopy();         // make a copy of the spreadsheet
  var ss   = SpreadsheetApp.openById(copy.getId())        // open the copy

  // convert all formulas into static values
  var range = ss.getActiveSheet().getDataRange();  // get data range
  var values = range.getDisplayValues();           // get all displayed values in the range
  range.setValues(values);                         // paste the values back
  
  while (ss.getSheets().length>1) ss.deleteSheet(ss.getSheets()[1]); // remove all sheets except the first one
  
  DriveApp.createFile(ss.getBlob().setName('sheet.pdf')); // make a PDF file
  copy.setTrashed(true);                                  // delete the copy

}
Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • if I copy this exact file, change it to makePDF() it generates an error : Unexpected error while getting the method or property getFileById on object DriveApp. – Cliff Stamp Nov 05 '20 at 14:01
  • 1
    You have to paste ID of your spreadsheet in the second line: `var ID = '';` – Yuri Khristich Nov 05 '20 at 14:07
  • Ok, got that, however this is problematic because the first page actually has linked references to the other pages so when they are deleted it wipes them out so the pdf just has a bunch of #REF's in it. – Cliff Stamp Nov 06 '20 at 16:20
  • 1
    All you need to preserve values is: to copy all _displayValues_ and put them back before another sheets are removed. I've updated my answer. – Yuri Khristich Nov 06 '20 at 18:32