How to convert a spreadsheet to PDF with GAS and send it by email

You can share the spreadsheet as it is, but if you don’t want to share the file itself (or if you can’t share it because the other party is outside), the major method is to convert it to PDF and attach it to an email.

Let’s implement the same method in GAS.

Please note that the code is messy (it works) because it was created in a hurry to respond to when someone asked me to send the reward data of the IPO startup released yesterday.

Convert spreadsheet to PDF (PDF creation)
1. Convert the entire spreadsheet to PDF (create PDF)
2. convert one specific sheet to PDF
SUMMARY

Convert spreadsheet to PDF (PDF creation)

Of course, spreadsheets often contain multiple sheets, but I think that the target you want to convert to PDF is mainly divided into the following three patterns.

convert the entire spreadsheet to PDF
convert one specific sheet to PDF
convert multiple (two or more) specific sheets to PDF

This time, we need to deal with cases 1 and 2, so I will explain these two partern.

The method of converting 3 specific multiple sheets to PDF is a derivative of 2 specific 1 sheet, so we plan to create a separate article.

Target spreadsheet

First of all, the prerequisite spreadsheet is as follows.

まとめシート(summary sheet)
各社シート(each company sheet)

Convert the entire spreadsheet to PDF (create PDF)

Then, from the method of converting the whole to PDF and emailing it immediately. This is easy because the methods are provided.

function createFilePDFAndEmail(mailAdress) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = ss.getName();
  // Can getAs method only convert all sheets to PDF...
  var pdf = ss.getAs("application/PDF").setName(sheetName+"PDF");
  
  
  var recipient = mailAdress; 
  var subject = sheetName + "";
  var body = "報酬情報まとめファイル";
  var options = {attachments: pdf};

  GmailApp.sendEmail(recipient, subject, body, options)
}

I think the point is the following part that creates the PDF.

var pdf = ss.getAs("application/PDF").setName(sheetName+"PDF");

The getAs (contentType) method belongs to the spreadsheet class. See below for a reference.

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet.html#getAs(String)

It will create a file (blob) according to the MIME TYPE that specifies the entire spreadsheet. However, the parameter blob included in the contentType is only’application / pdf’.

Name	Type	Description
`contentType`	`String`	The MIME type to convert to. For most blobs, `'application/pdf'` is the only valid option. For images in BMP, GIF, JPEG, or PNG format, any of `'image/bmp'`, `'image/gif'`, `'image/jpeg'`, or `'image/png'` are also valid.

In short, it is a method that can only create pdf or images. ..

Moreover, the method that belongs to the spreadsheet class means that the entire spreadsheet is converted to PDF. It is not good when you want to convert only individual sheets to PDF.

convert one specific sheet to PDF

Actually this is not officially supported. The sheet class doesn’t actually have a getAs (contentType) method.

https://developers.google.com/apps-script/reference/spreadsheet/sheet.html

Then, when I googled what to do, the following article was a hit.

Google Apps Script で指定したシートをPDF化するスクリプト

Looking at it, the point is that it is possible to download in PDF or CSV format by passing a parameter like / export? Format = pdf, so I feel like trying to create it by Urlfetch using it.

I knew it for the first time. csv can also be processed in this form. That means that GAS hasn’t made it public, and if you want to do it, you can make a method. .. (Implement it …)

Aside from the voice of my heart, I made it roughly as follows.

function createSheetPDFAndEmail(){
   // getAsは全てのシートをPDF化するしかできない->そのために、urlfetchで処理する
   //アクティブシートのIDとGIDを取得する（メニューバー項目開いているシートを送るようにするため）
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var sheetName = sheet.getName();
   var sheetID = sheet.getSheetId();
   var key = ss.getId();
 //OAuth対応のためにtokenを取得
   var token = ScriptApp.getOAuthToken();
 //URLを作る
   var url = "https://docs.google.com/spreadsheets/d/" + key + "/export?gid=" + sheetID + "&format=pdf&portrait=false&size=A4&gridlines=false&fitw=true"
 //PDF生成するためにURLfetch
   var pdf = UrlFetchApp.fetch(url, {headers: {'Authorization': 'Bearer ' +  token}}).getBlob().setName(sheetName + "報酬シート.pdf");
 //PDFアタッチでメール
   var recipet = "XXX";//mailadressいれる
   var subject = sheetName + "報酬シート";
   var body = sheetName + "ファイルを送ります";
   GmailApp.sendEmail(recipet, subject, body, {attachments:pdf});
 }

I think the points are as follows.

Creating URL

//URLを作る
   var url = "https://docs.google.com/spreadsheets/d/" + key + "/export?gid=" + sheetID + "&format=pdf&portrait=false&size=A4&gridlines=false&fitw=true"

It is a form that simply sets the sheet URL and? Gid (this is the element that specifies individual sheets) and passes multiple other parameters to create it.

Acquisition and use of token required for OAuth

After that, we have to get Token by this method OAuth, so I also use ScriptApp class.

The acquisition of tokens is as follows.

//OAuth対応のためにtokenを取得
   var token = ScriptApp.getOAuthToken();

Attach the acquired token to the header with Bearer.

 //PDF生成するためにURLfetch
   var pdf = UrlFetchApp.fetch(url, {headers: {'Authorization': 'Bearer ' +  token}}).getBlob().setName(sheetName + "報酬シート.pdf");

By the way, Bearer means a bearer. One of the concepts of OAuth created on the assumption that the person who has the token is the correct owner (because it is a maniac, non-professional members do not need to know lol)

SUMMARY

I was wondering why sheetClass doesn’t have a getAs method, but I figured out that I could do a lot with / export? Format =. I may not use it much, but I will remember it.

This time, we focused on data on executive compensation by company, which is not used by anyone on a daily basis, but you can do the same with other reports and invoices.

Learn and apply the elements that you can use in practice.