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)
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.
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.
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.
関連記事： spreadsheetをPDF化してメール送付する
spreadsheetで作成したデータをPDF化してメールしてみましょう。spreadsheetのPDF化の方法（１つのシート、全てのシート、特定の複数シート）を紹介しています。
