Googleスプレッドシートのリストからメールを自動送信する(個別添付ファイル付き)

はじめに

今回はGoogleスプレッドシートのリストから添付ファイルを個別に生成して自動でメールを送る方法をご紹介します。
Googleスプレッドシートで顧客情報を管理している場合もあると思います。100件近くなってくるとメールの送付もそこそこ手間になってきますので、そういった場合にこの方法を使うと非常に便利です。大幅な時間削減になります。
メインはGASを使ったプログラミングになります。

仕組み

①顧客リストから明細取得
②PDF作成してDriveに格納
③メールに添付して送信

手順①シートの用意

用意するシートは4枚です。

• 顧客リスト

• 明細

• 請求書テンプレ

• オプション

顧客リストシート

【シート名】

• 顧客リスト

【カラム構成】

• A1:顧客番号(※1)

• B1:氏名

• C1:メールアドレス

• D1:送信ステータス(※2)

※1.A列の顧客番号は整数以外でも明細シートとの整合性が取れれば何でも問題ありません。(a1011とか)

※2.D列の送信ステータスはプログラムで自動的に入力されるので空白で構いません。

サンプルイメージ

注)赤枠の部分は後ほどコードに貼り付けるので控えておいてください。

明細シート

【シート名】

• 明細

【カラム構成】

• A1:顧客番号

• B1:品目

• C1:数量

• D1:単位

• E1:単価

• F1:請求年

• G1:請求月

※請求年と請求月はいずれも整数のみで入力してください。

サンプルイメージ

明細は逐次蓄積されることを想定しているため、次月以降の明細が入っていても問題ありません。↑の例では2023年12月に請求する場合は、請求月が12の明細に限定されますので2024年1月以降のログは除外されます。

請求書テンプレシート

【シート名】

・請求書テンプレ

テンプレートはこちらのサイトからダウンロードして利用しています。

請求書エクセルテンプレート_タテ型_001

サンプルイメージ

これは請求書に限らずテンプレートを変えることであらゆる資料に対応できます。

オプションシート

メール送信におけるオプションを設定します。

【シート名】

• オプション

【カラム構成】

• A1:送信元メールアドレス(※1)

• A2:表示名

• A3:メールの件名

• A4:CC

• A5:Bcc

• A6:返信先アドレス

• A7:本文ID(※2)

• A8:請求書保存フォルダID(※3)

サンプルイメージ

※1.送信元メールアドレスはこのシートを作成しているGoogleアカウントに紐づいていれば、Gmail以外でも利用できます。
詳しくは↓の記事などを参考にしてみてください。

Gmailにメールアドレスを追加する

※2.次の手順②で用意する、GoogleドキュメントのIDをここに格納します。

※3.生成した請求書を格納するGoogle Drive上のフォルダIDです。下記手順で設定してください。

①フォルダの作成

請求書というフォルダを作成

②フォルダを開いてIDを取得→オプションシートのB8セルに貼り付ける

手順②返信用本文の用意

Googleドキュメントを使用して返信用の本文を作成します。

サンプルイメージ

※1.1行目の「〇〇様」はGASで挿入するのでそれ以降をここに記載してください。

※2.赤枠の部分はオプションシートのB7セルに貼り付けてください。

手順③コードを書く

GASを開く

スプレッドシートの拡張機能から「Apps Script」を選択

myfunctionを消す

赤枠がデフォルトで記入されてるので、一旦消去

コードを貼り付ける

↑で消したところに↓のコードをそのまま貼り付けます。

function exe(){
  const ss = SpreadsheetApp.getActive()
  const db = ss.getSheetByName("顧客リスト")
  const detailSh = ss.getSheetByName("明細")
  const tmp = ss.getSheetByName("請求書テンプレ")
  const opSh = ss.getSheetByName("オプション")
  const customers = db.getRange(2,1,db.getLastRow() - 1,db.getLastColumn()).getValues()
  const fromAdress = opSh.getRange(1,2).getValue();
  const fromName = opSh.getRange(2,2).getValue();
  const mailsubject = opSh.getRange(3,2).getValue();
  const cc = opSh.getRange(4,2).getValue();
  const bcc = opSh.getRange(5,2).getValue();
  const replyTo = opSh.getRange(6,2).getValue();
  const docId = opSh.getRange(7,2).getValue();
  const folderId = opSh.getRange(8,2).getValue();
  const text = DocumentApp.openById(docId).getBody().getText();
  let count = 0
  customers.forEach((customer,index) => {
    if(customer[3] == "" && count < 7){
      const detail = createDetail(detailSh,customer[0])
      detail.forEach(detail => {
        detail.splice(5,2)
        detail.splice(0,1)
        detail.splice(1,0,"","")
      })
      detailSh.getRange(15,1,14,4).clearContent()
      if(detail.length > 0){
        const name = customer[1]
        const subject = "御請求書の送付"
        const list = [
          [2,1,1,1,[[name]]],
          [6,2,1,1,[[subject]]],
          [15,1,detail.length,detail[0].length,detail]
        ]
        pasteSh(tmp,list)
        SpreadsheetApp.flush()
        const file = createPdf(folderId,ss.getId(),tmp.getSheetId(),`${name} 様　御請求書`)
        try{
          const email = customer[2]
          sendEmail(email,mailsubject,name,fromAdress,fromName,cc,bcc,replyTo,file,text) 
        }catch{
          db.getRange(index + 2,4).setValue("エラー")  
        }
        db.getRange(index + 2,4).setValue("済")
      }else{
        db.getRange(index + 2,4).setValue("請求なし")  
      }
      count += 1
    }
  })
}

function createDetail(sh,foreignKey){ 
  const allDetails = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues()
  const year = new Date().getFullYear()
  const month = new Date().getMonth() + 1
  const details = allDetails.filter(arg => {
    return arg[0] == foreignKey && arg[5] == year && arg[6] == month
  })
  return details
}

function pasteSh(sh,list){
  list.forEach(arg => {
    sh.getRange(arg[0],arg[1],arg[2],arg[3]).setValues(arg[4])
  })
}

function createPdf(folderId, ssId, shId, fileName){
  //PDFを作成するためのベースとなるURL
  const baseUrl = "https://docs.google.com/spreadsheets/d/"
    +  ssId
    + "/export?gid="
    + shId;
 
  //★★★自由にカスタマイズしてください★★★
  //PDFのオプションを指定
  const pdfOptions = "&exportFormat=pdf&format=pdf"
    + "&size=A4" //用紙サイズ (A4)
    + "&portrait=true"  //用紙の向き true: 縦向き / false: 横向き
    + "&fitw=true"  //ページ幅を用紙にフィットさせるか true: フィットさせる / false: 原寸大
    + "&top_margin=0.50" //上の余白
    + "&right_margin=0.50" //右の余白
    + "&bottom_margin=0.50" //下の余白
    + "&left_margin=0.50" //左の余白
    + "&horizontal_alignment=CENTER" //水平方向の位置
    + "&vertical_alignment=TOP" //垂直方向の位置
    + "&printtitle=false" //スプレッドシート名の表示有無
    + "&sheetnames=false" //シート名の表示有無
    + "&gridlines=false" //グリッドラインの表示有無
    + "&fzr=false" //固定行の表示有無
    + "&fzc=false" //固定列の表示有無;

  //PDFを作成するためのURL
  const url = baseUrl + pdfOptions;

  //アクセストークンを取得する
  const token = ScriptApp.getOAuthToken();

  //headersにアクセストークンを格納する
  const options = {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  };
 
  //PDFを作成する
  const blob = UrlFetchApp.fetch(url, options).getBlob().setName(fileName + '.pdf');

  //PDFの保存先フォルダー
  //フォルダーIDは引数のfolderIdを使用します
  const folder = DriveApp.getFolderById(folderId);

  //PDFを指定したフォルダに保存する
  const file = folder.createFile(blob);
  return file
}

function sendEmail(toAdress,subject,name,fromAdress,fromName,cc,bcc,replyTo,file,text) {   
  // オプションを設定する
  const option = {
    from:fromAdress,
    name:fromName,
    cc:cc,
    bcc:bcc,
    replyTo:replyTo,
    attachments:file
  }

  // メール本文を作成する
  const body =`${name}様\n${text}\n`

  // メールを送信する
  GmailApp.sendEmail(toAdress,subject,body,option)
}

手順④トリガーの設定

トリガーを押下

右下の「トリガーを追加」→設定

これで1分おきにプログラムが起動し、リストの中から未対応のユーザーにのみ処理を実行します。

1分おきにした理由は、APIのリクエスト上限によりPDFの連続作成に制限があるためです。7個以上だとエラーが発生します。そこで一回の実行につき7件に限定するため、定期的に呼び起こす必要があるのです。
尚、全件送り終えたらこのトリガーは削除してください。

手順⑤プログラム認証

初回の実行に限り認証が必要です。
↓の手順に沿って進めてください。

まとめ

以上でGoogleスプレッドシートのリストから個別PDFを生成、メールに添付して自動送信できるようになりました。
ぜひ一度お試しください。