【GAS】スプレッドシートで在庫管理を行い、自動で注文書を生成&PDF化してGmailで送信する

手軽に使えて、便利なスプレッドシートで在庫を管理している!という方は少なくないかと思います

ただでさえ便利に使えるスプレッドシートに、「たったひと手間」加えるだけで、さらに便利に使うことができるようになる魔法の方法をご紹介したいと思います!

​そこで、この記事ではGoogle Apps Script(GAS)を使用して、スプレッドシートを活用し、在庫が不足した場合に自動的に注文書を生成し、それをPDF化してメールで送信する方法を詳しく解説します!

本記事の内容
  • スプレッドシート内のすべてのデータを取得し、発注が必要な場合は、注文書をGoogleドキュメントで自動作成する
  • 自動作成したGoogleドキュメントをPDF変換する
  • 変換したPDFを添付ファイルとして相手先にメールを送信する
こーすけ先生

​​この記事を読めば、在庫管理と注文業務の効率を大幅に向上させるためのスキルを身につけることができます!

もし、スプレッドシートを使用して在庫管理を行い、必要数量が在庫数を上回った場合に自動的に注文書を生成&注文のメールを送信できたらめちゃくちゃ便利じゃないですか?

手動での作業を自動化することで、時間と労力を節約できるようになります

​GASを極めたい方や、業務の効率化を図りたい方は、ぜひこの記事を読んください!
難しいことはGASに任せて、我々人間は楽しちゃいましょう!

この記事の執筆者について
  • GASの人
  • ITベンダSEとして12年勤務する中で民間、金融、官公庁の現場を一通り経験済
  • 現在は公務員をやりながら起業に向けて着々と準備中
GASなら任せろ!
こーすけ先生

当ブログでは実際に仕事でGASを扱っている私が、GASの魅力について徹底的に取り上げていきます!

目次

【お題】発注が必要な場合、発注先に注文書をPDF化してメールを送信する

本記事で取り組む演習
  • スプレッドシート内のすべてのデータを取得し、行ごとに評価する
  • 発注が必要な場合は、注文書をGoogleドキュメントで自動作成する
  • GoogleドキュメントをPDF変換する
  • 変換したPDFを添付ファイルとして相手先にメールを送信する
こーすけ先生

張り切って演習に取り組んでいきましょう!

事前準備

こーすけ先生

本記事は初心者の方向けに丁寧に解説しているので、全体のコードがみたい!という方はここをクリックして、ジャンプしてください

こーすけ先生

今回、こちらの「GAS練習(PDF化→メール送信)」を
使って演習します!
必要に応じてコピーして使ってください!

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

Googleドキュメントで注文書テンプレートを適当に作成しましょう!

がすぴょん

この{{date}}みたいに波括弧で囲んであるのは何なの?

こーすけ先生

波括弧の部分は、「可変文字部」といって、
今回の大事なポイントはここなんだ!!!

こーすけ先生

詳しくは、以下の部分で説明するよ!
もう少し待っててね!

PDF出力場所の作成

こーすけ先生

スプレッドシートの内容をGoogleドキュメントに反映し、PDFに変換するため、その出力先を作成しておきましょう!

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

がすぴょん

IDは緑色の四角で囲った部分だよ!

まずは完成形のコードを見てみる

こーすけ先生

完成形のコードはこちら!
順を追って解説していくよ!

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

// sendMail_注文書のテンプレートファイル
const ORDER_FORM_TEMPLATE = DriveApp.getFileById('自分のGoogleドキュメントのIDに置き換える');

// PDF出力先
const PDF_OUTDIR = DriveApp.getFolderById('自分のPDF用のフォルダIDに置き換える');

function sendMail_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}}', 'こーすけ先生');
  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();
}
がすぴょん

長くて心が折れそう……

こーすけ先生

解説しながら進めていくから大丈夫だよ!

スクリプトを実行すると、以下のメールが自動送信されます

こーすけ先生

このGASのコードは、スプレッドシートを使用して在庫管理を行い、必要数量が在庫数を上回った場合に自動的に注文書を生成→PDFファイル化して、メール送信するものです!便利!

がすぴょん

一気に実務に活かせそうな内容になった!
頑張らなきゃっ…!

【STEP1】セル位置を定数化

// sendMail_簡易在庫管理表のヘッダーカラム
const COL = {
  NUM:1
  , PRODUCT_NAME:2
  , REQUIRED_QUANTITY:3
  , INVENTORY_CNT:4
  , PRICE:5
  , COMPANY:6
  , STAFF:7
  , MAIL:8
}
がすぴょん

ふむふむ
「セルの定数化」は前に学んだので、大丈夫そうです!

こーすけ先生

「セルの定数化」はマスターすると便利なので、
ガンガン活用していこうね!

こーすけ先生

sendMail_SpreadSheetの部分は過去記事を参考にしてください!

【STEP2】Googleドキュメントを編集する

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];
}
こーすけ先生

createGDoc は、テンプレートとなるドキュメントをコピーし、特定の箇所({{}}部分)に必要なデータを挿入し、注文書を生成しています

テンプレートファイルをコピーする

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

ORDER_FORM_TEMPLATE は、以下の部分を指しています

// sendMail_注文書のテンプレートファイル
const ORDER_FORM_TEMPLATE = DriveApp.getFileById('自分のGoogleドキュメントのIDに置き換える');
こーすけ先生

「const」を使って、Googleドライブのファイルを
「ORDER_FORM_TEMPLATE」という名前の箱に
入れました!

スクロールできます
変数再宣言再代入スコープ
letできないできるブロックスコープ
constできないできないブロックスコープ
varできるできる関数スコープ
※注釈アリ
こーすけ先生

「const」は箱の中身を変えたくない時に使います!
「let」や「var」を使って定義すると、うっかり中身が
書き換わってしまう恐れがあるので…

【注意】
厳密にはconstは再代入はできないけどオブジェクト型の中身を変更することは可能です
例えば、const wVals = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getDataRange().getValues();
で定義したとしても、wVals[1][5] = 3 としてもエラーにはなりません要はオブジェクト自体の再代入は確かにできないのですが、オブジェクトのプロパティ値(配列でいうなら各要素とか)は変更できてしまいます!しかし、ここでは「constだと原則、変更できないんだな」くらいに捉えて頂けるとと思います!

 // テンプレートファイルをコピーする
  const wCopyFile = ORDER_FORM_TEMPLATE.makeCopy()
        , wCopyFileId = wCopyFile.getId()
        , wCopyDoc = DocumentApp.openById(wCopyFileId); // コピーしたファイルをGoogleドキュメントとして開く
  let wCopyDocBody = wCopyDoc.getBody(); // Googleドキュメント内の本文を取得する
上記コードで行っていること
  • ORDER_FORM_TEMPLATE.makeCopy()
    Googleドキュメントファイルをコピーして、新しいファイルを作成。wCopyFile には新しいファイルへの参照が格納される。
  • wCopyFile.getId()
    新しいファイルのIDを取得して、wCopyFileId に格納する。このIDは、後でファイルにアクセスする時に使用する。
  • DocumentApp.openById(wCopyFileId)
    新しいファイルを Google ドキュメントとして開く。
  • wCopyDoc.getBody()
    ドキュメント内の本文(テキストや書式設定など)を取得し、wCopyDocBody に格納。これにより、後で可変部分のテキストの置き換えなどの操作を行うことができる。
がすぴょん

ふむふむ!
1行ずつ、読んでいけばなんとなく何しているか分かるぞ!

こーすけ先生

時間があったら公式リファレンスに
目を通して勉強してみてね!

用意した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}}', 'こーすけ先生');
  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();

ここでポイントとなるのは、冒頭にも申しあげた通り、{{XXXXXX}}で示した可変文字部です

Googleドキュメントにおいてはスプレッドシートと異なり、セル位置がないため特定の場所を更新する(スプレッドシートで例えると、「C11セル」とか)ことは不得意です

こーすけ先生

そのため、特定の場所にあらかじめ変更するための目印をつけておくことが必要になります

そのうえで、getBody()によりGoogleドキュメントのBodyを取得し、replaceTextを用いて編集します。

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

可変文字部を変更したGoogleドキュメントを新しいファイル名で保存する

 // ファイル名を変更する
  let fileName = rowVal[COL.COMPANY-1]+'宛注文書_'+Utilities.formatDate(new Date(), 'JST', 'yyyyMMdd');
  wCopyFile.setName(fileName);
  // コピーしたファイルIDとファイル名を返却する(あとでこのIDをもとにPDFに変換するため)
  return [wCopyFileId, fileName];
上記コードで行っていること
  • fileName
    新しいファイル名を作成するための処理。このファイル名は、「会社名」+「宛注文書」+「日付」が付加されるようになっている
  • wCopyFile.setName(fileName)
    setName メソッドを使用して、ファイル名を fileName で指定した名前に変更する
  • return [wCopyFileId, fileName];
    新しいファイル名とファイルIDの組み合わせを配列として返す
    この情報は、後でPDFに変換するために使用される

この部分のコードは、新しいファイル名を作成し、ファイルの名前を変更して、その新しい名前とファイルIDを返す準備をしています!

こーすけ先生

この作業により、後続の処理でファイルを識別し、必要な操作を実行できるようになります!

【STEP3】自動作成したGoogleドキュメントをPDF化する

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();
}
こーすけ先生

この関数は 、新しく作成したGoogleドキュメントファイルを PDF ファイルに変換し、その PDF ファイルを指定したフォルダに保存する役割を果たしています

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

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

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

${docId} には、新しく作成したGoogleドキュメントファイルのIDが挿入されています!

 // headersにアクセストークンを格納する
  let wOtions = {
    headers: {
      'Authorization': `Bearer ${ScriptApp.getOAuthToken()}`
    }
  }; 

ScriptApp.getOAuthToken() は Google Apps Script のメソッドで、現在のスクリプトに認証されたユーザーのアクセストークンを取得します
(アクセストークンは、Google サービスへのアクセス許可を持つユーザーを識別し、認証を行います)

こーすけ先生

これにより、認証済みユーザーとして Google ドキュメントに
アクセスし、PDF ファイルを生成できるようになります

このアクセストークンを通じて、スクリプトはユーザーのアカウント権限を利用し、 Google ドキュメントにアクセスできるようになります!

Bearer認証

'Authorization':Bearer〇〇部分は、HTTP リクエストを送信する際、API やウェブサーバーに認証情報を送信する必要がある場合によく使用されます

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

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

// PDFを作成する
  let wBlob = UrlFetchApp.fetch(wUrl, wOtions).getBlob().setName(fileName + '.pdf');

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

この関数は Google ドキュメントを PDF に変換し、指定されたフォルダに保存して、その PDF の ID を取得するためのものです

実行結果

こーすけ先生

実行すると、スプレッドシート内で指定したメールアドレス宛に、
新しく作成されたPDF付のメールが送信されます!

がすぴょん

わっ!すごい!
添付されたファイルがちゃんと僕の名前に
置き換わってる!

こーすけ先生

この方法は、過去にめちゃくちゃ苦労した末に
編み出した技なんだよね(苦笑)

まとめ

大変お疲れ様でした!
今回も「GASを用いてスプレッドシートで遊んでみよう」という試みの中で、少し複雑なことをしてみました

しかし、一つ一つを見てみるとこれまで行ったことを組み合わせただけであることに気づくかと思います

こーすけ先生

いかなるプログラムやシステムも基本的には
基礎を組み合わせて出来上がったものです!

これまで学んだGASスキルを少しずつ組み合わせて、ぜひ業務効率化を進めてみてください

次回も引き続き、スプレッドシートで遊んでいきましょう!!

こーすけ先生

X(旧:Twieer)にて、ブログの更新やQiita記事の更新、GAS情報をお届けしますので、是非フォローしてください!

100日後に起業する公務員

こーすけ先生

退職までの漫画をゆるくXにて更新中!
是非フォローしてね!

この記事が気に入ったら
いいね または フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

GASの人。ITベンダSEとして12年勤務し、民間、金融、官公庁の現場を一通り経験済。html、css、JavaScript、Java、PHPも分かります。最近は専らGASで小規模アプリケーションを頻繁に作成しています。GASのことなら何でもお任せあれ!現在は公務員として働きながら、起業に向けて着々と準備中です!

目次