Go to Qiita Advent Calendar 2024 Top
LoginSignup
4
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

記事投稿キャンペーン 「エンジニア×非エンジニアのコミュニケーション」
@gas-suke

【0からGASを学ぶ】GASを用いてスプレッドシート+ドキュメント+メール送信を自動化しよう

Last updated at Posted at 2023-10-31

はじめに

本シリーズでは、GASの始め方や便利な使い方、ビジネス活用まで幅広く解説します。シリーズをひと通り読んでいただければ、あなたもきっとGASマスターになれるはずです。

シリーズの対象者

  • そもそもGASってなんだかわからない方
  • GASを学びたいけど何から始めればいいかわからない方
  • GASはわかり始めたけど、もっと活用ができないかと模索している方
  • とにかくGoogleが好き！ という方

前回記事

スプレッドシートで遊ぶ

では早速始めていきましょう。【0からGASを学ぶ】シリーズの第7回は「GASを用いてスプレッドシート+ドキュメント+メール送信を自動化しよう」です。今回は少し複雑なことをやってみようと思います。これまで、GoogleドキュメントやGmailをGASから操作しました。また第5回では、スプレッドシートのデータを評価し、メールを自動送信しました。これらを組み合わせてみましょう。

今回やること

  1. スプレッドシート内のすべてのデータを取得し、行ごとに評価する。
  2. 発注が必要な場合は、注文書をGoogleドキュメントで作成する。
  3. GoogleドキュメントをPDF変換する。
  4. 変換したPDFを添付ファイルとして相手先にメールを送信する。

事前準備

今回はサンプルでいくつかファイルを作ります。もちろんサンプルなのでこの通りである必要はありませんが、これらを前提にプログラムは書かせてもらいます。

在庫管理表の準備

スプレッドシートのデータを取得しなければ始まらないので、取得元となるスプレッドシートを作りましょう。私は以下のようなスプレッドシートを作成しました。

注文書テンプレートの準備

Googleドキュメントで注文書テンプレートを適当に作成しましょう。どんな様式でも構いません。

枠で囲った箇所を可変文字部と呼ばせてもらいますが、今回のポイントはここです。

PDF出力場所の作成

「今回やること」にも記載しましたが、途中でGoogleドキュメントをPDF変換するため、その出力先を作成しておきましょう。

IDの取得

スプレッドシートはコンテナバインド型で開くため、IDは不要ですが、注文書テンプレートとPDF出力場所のIDは事前に取得しておきましょう。

プログラム開始

GASエディタを開く

今回も在庫管理表に対するコンテナバインド型でGASプログラムを記述していきましょう。こちらを参考にGASエディタを起動してください。では、どんどんいきますよ、ついてきてください。

結論

今回はまず最終形をお見せします。そのうえで、解説していきましょう。

Qiita007.gs
// Qiita007_簡易在庫管理表のヘッダーカラム
const COL = {
  NUM:1
  , PRODUCT_NAME:2
  , REQUIRED_QUANTITY:3
  , INVENTORY_CNT:4
  , PRICE:5
  , COMPANY:6
  , STAFF:7
  , MAIL:8
}

// Qiita007_注文書のテンプレートファイル
const ORDER_FORM_TEMPLATE = DriveApp.getFileById('15ZqXoJxpuq96NaPJBbWinEYx4H-Qn2lxkRCNr2dgnKM');

// PDF出力先
const PDF_OUTDIR = DriveApp.getFolderById('1DNlhIKHlFVJ419UGh3RR3eCZkC_xyUfi');

function Qiita007_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet()

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();

  // 今回はforEachでデータを回したいので、shiftメソッドを使って配列の最初の要素を削除する
  wVals.shift();

  // データを走査
  wVals.forEach(function(wVal) {
    // [必要数]>[在庫数]の行を特定する
    if (wVal[COL.REQUIRED_QUANTITY-1]>wVal[COL.INVENTORY_CNT-1]) {
      // PDF変換する元ファイルを作成する
      let wFileRtn = createGDoc(wVal);
      // PDF変換してファイルIDを取得する
      let wPdfId = createPdf(wFileRtn[0], wFileRtn[1]);
      // PDF変換したあとは元ファイルを削除する
      DriveApp.getFileById(wFileRtn[0]).setTrashed(true);
      // 今回はPDFファイルを添付してメールを送信する
      GmailApp.sendEmail(
        wVal[COL.MAIL-1]
        , '【自動送信メール】発注依頼'
        , `${wVal[COL.PRODUCT_NAME-1]}の在庫がなくなりました。
必要数：${wVal[COL.REQUIRED_QUANTITY-1]}
在庫数：${wVal[COL.INVENTORY_CNT-1]}`
        , {attachments: DriveApp.getFileById(wPdfId).getBlob()}
      )
    } else {
      // 何もしない
    }
  });
}


function createGDoc(rowVal) {
  // テンプレートファイルをコピーする
  const wCopyFile = ORDER_FORM_TEMPLATE.makeCopy()
        , wCopyFileId = wCopyFile.getId()
        , wCopyDoc = DocumentApp.openById(wCopyFileId); // コピーしたファイルをGoogleドキュメントとして開く
  let wCopyDocBody = wCopyDoc.getBody(); // Googleドキュメント内の本文を取得する

  // 注文書ファイル内の可変文字部（として用意していた箇所）を変更する
  wCopyDocBody = wCopyDocBody.replaceText('{{date}}', Utilities.formatDate(new Date(), 'JST', 'yyyy年MM月dd日'));
  wCopyDocBody = wCopyDocBody.replaceText('{{company_name}}', rowVal[COL.COMPANY-1]);
  wCopyDocBody = wCopyDocBody.replaceText('{{staff_name}}', rowVal[COL.STAFF-1]);
  wCopyDocBody = wCopyDocBody.replaceText('{{company_from}}', 'GAS大好きカンパニー');
  wCopyDocBody = wCopyDocBody.replaceText('{{name_from}}', 'gas-suke');
  wCopyDocBody = wCopyDocBody.replaceText('{{product_name}}', rowVal[COL.PRODUCT_NAME-1]);
  wCopyDocBody = wCopyDocBody.replaceText('{{count}}', rowVal[COL.REQUIRED_QUANTITY-1]-rowVal[COL.INVENTORY_CNT-1]+100);
  wCopyDocBody = wCopyDocBody.replaceText('{{price}}', rowVal[COL.PRICE-1]);
  wCopyDocBody = wCopyDocBody.replaceText('{{total}}', rowVal[COL.PRICE-1]*(rowVal[COL.REQUIRED_QUANTITY-1]-rowVal[COL.INVENTORY_CNT-1]+100));
  wCopyDoc.saveAndClose();

  // ファイル名を変更する
  let fileName = rowVal[COL.COMPANY-1]+'宛注文書_'+Utilities.formatDate(new Date(), 'JST', 'yyyyMMdd');
  wCopyFile.setName(fileName);
  // コピーしたファイルIDとファイル名を返却する（あとでこのIDをもとにPDFに変換するため）
  return [wCopyFileId, fileName];
}


function createPdf(docId, fileName){
  // PDF変換するためのベースURLを作成する
  let wUrl = `https://docs.google.com/document/d/${docId}/export?exportFormat=pdf`;

  // headersにアクセストークンを格納する
  let wOtions = {
    headers: {
      'Authorization': `Bearer ${ScriptApp.getOAuthToken()}`
    }
  }; 
  // PDFを作成する
  let wBlob = UrlFetchApp.fetch(wUrl, wOtions).getBlob().setName(fileName + '.pdf');

  //PDFを指定したフォルダに保存する
  return PDF_OUTDIR.createFile(wBlob).getId();
}

実行すると、対象に添付付きメールが送信されます。

また、添付されたファイルは以下のように出力されています。

無事に可変文字部が在庫管理表内のデータを用いて編集されていることを確認できます。

解説

Qiita007_SpreadSheet自体にはそこまで目新しいものはないため、createGDocとcreatePdfを中心に見ていきましょう。

createGDoc

createGDoc
function createGDoc(rowVal) {
  // テンプレートファイルをコピーする
  const wCopyFile = ORDER_FORM_TEMPLATE.makeCopy()
        , wCopyFileId = wCopyFile.getId()
        , wCopyDoc = DocumentApp.openById(wCopyFileId); // コピーしたファイルをGoogleドキュメントとして開く
  let wCopyDocBody = wCopyDoc.getBody(); // Googleドキュメント内の本文を取得する

  // 注文書ファイル内の可変文字部（として用意していた箇所）を変更する
  wCopyDocBody = wCopyDocBody.replaceText('{{date}}', Utilities.formatDate(new Date(), 'JST', 'yyyy年MM月dd日'));
  wCopyDocBody = wCopyDocBody.replaceText('{{company_name}}', rowVal[COL.COMPANY-1]);
  wCopyDocBody = wCopyDocBody.replaceText('{{staff_name}}', rowVal[COL.STAFF-1]);
  wCopyDocBody = wCopyDocBody.replaceText('{{company_from}}', 'GAS大好きカンパニー');
  wCopyDocBody = wCopyDocBody.replaceText('{{name_from}}', 'gas-suke');
  wCopyDocBody = wCopyDocBody.replaceText('{{product_name}}', rowVal[COL.PRODUCT_NAME-1]);
  wCopyDocBody = wCopyDocBody.replaceText('{{count}}', rowVal[COL.REQUIRED_QUANTITY-1]-rowVal[COL.INVENTORY_CNT-1]+100);
  wCopyDocBody = wCopyDocBody.replaceText('{{price}}', rowVal[COL.PRICE-1]);
  wCopyDocBody = wCopyDocBody.replaceText('{{total}}', rowVal[COL.PRICE-1]*(rowVal[COL.REQUIRED_QUANTITY-1]-rowVal[COL.INVENTORY_CNT-1]+100));
  wCopyDoc.saveAndClose();

  // ファイル名を変更する
  let fileName = rowVal[COL.COMPANY-1]+'宛注文書_'+Utilities.formatDate(new Date(), 'JST', 'yyyyMMdd');
  wCopyFile.setName(fileName);
  // コピーしたファイルIDとファイル名を返却する（あとでこのIDをもとにPDFに変換するため）
  return [wCopyFileId, fileName];
}

ここでポイントとなるのは、冒頭にも申しあげた通り、{{XXXXXX}}で示した可変文字部です。Googleドキュメントにおいてはスプレッドシートと異なり、セル位置がないため特定の場所を更新する（例えばスプレッドシートでC11セルとか）ことは不得意です。そのため、特定の場所にあらかじめ変更するための目印をつけておくことが必要になります。
そのうえで、getBody()によりGoogleドキュメントのBodyを取得し、replaceTextを用いて編集します。

{{}}をお決まりのような書き方をしていますが、区切り文字はなんだってかまいません。ご自身の中で分かりやすいマークであり、明確に可変文字部とわかればそれでいいです。

createPdf

createPdf
function createPdf(docId, fileName){
  // PDF変換するためのベースURLを作成する
  let wUrl = `https://docs.google.com/document/d/${docId}/export?exportFormat=pdf`;

  // headersにアクセストークンを格納する
  let wOtions = {
    headers: {
      'Authorization': `Bearer ${ScriptApp.getOAuthToken()}`
    }
  }; 
  // PDFを作成する
  let wBlob = UrlFetchApp.fetch(wUrl, wOtions).getBlob().setName(fileName + '.pdf');

  //PDFを指定したフォルダに保存する
  return PDF_OUTDIR.createFile(wBlob).getId();
}

ここでポイントとなるのは、https://docs.google.com/document/d/${docId}/export?exportFormat=pdfのURLとなります。

  1. https://docs.google.com/document/d/${docId}までが対象のGoogleドキュメントを指しています。
  2. そのあとのexport?でエクスポートを指示しており、GETパラメタへ続きます。
  3. exportFormat=pdfは読んで字のごとく、エクスポートの形式を指示します。

Bearer認証

Bearer認証はアクセストークン認証といい、ログインID・パスワードなどでユーザ認証を行なった後に、サービスから発行されるアクセストークン（GASにおいては、スクリプト自体のアクセストークンgetOAuthToken()を使用） を受け取って、APIのリクエスト時に送信する方式です。

ヘッダー項目を
Authorization: Bearer {アクセストークン}
で設定して、HTTPリクエストを送信します。

さらっとfor文をforEach文に書き換えました

前回
let wVals = wSheet.getDataRange().getValues();

// 1行目（index:0）はヘッダー列なので、2行目からデータを走査
for (let rIdx=1; rIdx<wVals.length; rIdx++) {
  // [必要数]>[在庫数]の行を特定する
  if (wVals[rIdx][COL.REQUIRED_QUANTITY-1]>wVals[rIdx][COL.INVENTORY_CNT-1]) {
    …
  }
}
今回
let wVals = wSheet.getDataRange().getValues();

// 今回はforEachでデータを回したいので、shiftメソッドを使って配列の最初の要素を削除する
wVals.shift();

// データを走査
wVals.forEach(function(wVal) {
  // [必要数]>[在庫数]の行を特定する
  if (wVal[COL.REQUIRED_QUANTITY-1]>wVal[COL.INVENTORY_CNT-1]) {
    …
  }
});

一見すると同様のことが行えているため、書き方のちょっとした違いのようにも見えますが、forとforEachはまるで異なります。
forは構文でありいつでもどこでも使えます。しかし、forEachは配列要素に対するメソッドであり、使用箇所は限定的となります。ただし、見ていただくとわかる通り、forの場合に行っていたindexの管理が不要であり、配列データを扱う場合には非常に美しくプログラムを記載することができます。

forEachでもindexを扱うことは可能です。

wVals.forEach(function(wVal, index) {
  console.log(index);
}

実際には第3引数も指定できますが、この辺りの詳細はまた今度！

おわりに

お疲れ様でした。
第7回は「GASを用いてスプレッドシート+ドキュメント+メール送信を自動化しよう」ということで、少し複雑なことをしてみました。しかし、一つ一つを見てみるとこれまで行ったことを組み合わせただけであることに気づくかと思います。つまり、いかなるプログラムやシステムも基本的には基礎を組み合わせて出来上がったものです。これまで学んだGASスキルを少しずつ組み合わせて、ぜひ業務効率化を進めてみてください。次回も引き続き、スプレッドシートで遊んでいきましょう。
記事を読んで、「良いな」や「今後に期待できる！」と感じて頂けたらいいねやフォロー、コメントいただけると幸いです。それではまた次回をお楽しみに！

ブログでより詳しく解説しています！

以下画像をクリックしてブログにアクセス！！

4
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?