google forｍを利用して休日出勤届を会社へ自動で送信されるようにしたいです。

Question

google forｍを利用して休日出勤届を会社へ自動で送信されるようにしたいです。

フォームの回答はスプレッドシートに自動的に集計されています。
集計された一覧は残したまま、最新の回答のみを別シートに（提出用書式にあわせて）記入→メール送信、という流れを自動でできるようにしたいのです。

伝わり難い文章となって大変申し訳ありません。
編集してみましたが如何でしょうか…。
右も左もわからぬ新参者で大変恐縮です。
ご存知の方入らっしゃいましたらご教授いただければ助かります。

Comments

[papinianus]

作りたいものがわからないのですが、途中からであっても新しい申請をコピーしていくといずれ今回新設するシートも行が増えて、履歴になるのではないでしょうか？

[papinianus]

失礼しました。別シートは「書式」なのですね。確認ですが、その設計だとシートがどんどん増えますがよろしいのでしょうか？(消すとか、1シートを使い回すのは、同じタイミングで2人or2回の申請がると誤動作する可能性があります)。また書式はPDFにして送信するのですか？ただの文字でよければ、別シートを利用しないで送信してはいけないのでしょうか？

[rrrranco]

こちらこそ、失礼いたしました。質問頂けて大変うれしく思います。別シートは書式です。シートは増えないで、使いまわしできるやり方が望ましいです。同じタイミングで２人が同時に申請をする可能性はゼロではありませんが、今のところそちらは目をつむりたいです。PDFでの送信であれば助かります。私だけであればよいのですが、社長も見ますので見やすい書式でと注文を受けました。お手数をおかけしてしまいすみません。よろしくお願いします。

[papinianus]

追記・コメント、ありがとうございます、やりたい雰囲気は掴めてきました。書式は例えば、特定のセルに書くのですかね。それとも{名前}みたいな文書を入力値に置きかえるイメージですか？入力値は必ず集計一覧に含まれますよね？項目は何個くらいですか？日付以外にどのような種類の項目があるのですか(表示形式や長文の一部が見えなくなってしまう可能性)？送信先のアドレスは固定と思ってよいですか？

[rrrranco]

返信が遅くなり申し訳ありません。浅学な私のイメージですと、指定のセルに記入でした。文書を入力値に置き換えることができるのですね。入力値は集計一覧に含まれています。書くor置き換えですと、できれば難しくないほうがよいです。項目は申請者、区分、開始日、終了日、就業時間、業務内容、備考の7つです。文章の長さを鑑みて出来上がりの表示は調整します。送信先のアドレスは固定です。

[papinianus]

今後のメンテナンスとコードの書き易さからセルに記入にしました。書式を整えてどうかを見てください。文章の長さと調整の件ですが、添付するならPDFとなります。googleからexcelは出せないので(csvは出るのか今すぐは不明)。となると、書式テンプレで備考が3行くらいになっているのに、5行くらいのコメントを書くと、途中から途切れる可能性があります。そこはあまり私の勉強にならないので、調べる予定はありません(広めに領域を取るなどでご対処ください)。

Answer 1

何となく書いてみました。

「※」があるところを適切に設定してください。
最低でも「※」の1,5,6,7,8がご利用の環境にあわせて設定されていないと正常に動きません。

下記のコードをフォームの集計がされるシートを開いて、「ツール」→「スクリプトエディタ」を開いて貼り付けをします。
また、そのスクリプトエディタの画面で「編集」→「現在のプロジェクトのトリガー」にて、「実行」=「sender」「イベント」=「スプレッドシートから、フォーム送信時」として登録する必要があります。

上記が適切に設定されていれば、動作することを当方では検証済みです。

function sender() {
  const dat = createRequiredBlob();
  const blob = dat[0];
  const param = dat[1];
  const applicant = param["B2"]; // "B2"のところを※8で申請者の右に指定したセル番地にかえてください ※9
  const to = ""; // あてさきに設定するアドレス([e-mail]) ※1
  MailApp.sendEmail({
    to: to,
    name: applicant, //fromの名前がかわるはず。
    subject: "申請 (申請者:"+applicant +")", // 件名 ※2
    body: "添付ファイルをご確認ください", // 本文 ※3
    attachments: [blob.setName("休暇申請("+applicant+").pdf")] // ファイル名の変更可 ※4
  });
}
function createRequiredBlob() {
  const id = ""; // form回答が来るスプレッドシートのid ※5
  const template = ""; // 書式のテンプレとして利用するシートの名前 ※6
  const answers = "フォームの回答"; // form回答が入るシートの名前 ※7
  
  // 添付ファイル準備
  const spreadSheet = SpreadsheetApp.openById(id);
  const temporary = Utilities.getUuid();
  const ansSheet = spreadSheet.getSheetByName(answers);
  const tempSheetName = copySheet(spreadSheet, template, temporary);
  const param = buildFillinParam(getLastData(ansSheet));
  fillIn(spreadSheet, tempSheetName, param);
  SpreadsheetApp.flush();
  const tempSheet = spreadSheet.getSheetByName(tempSheetName);
  const blob = fetchBlob(spreadSheet.getId(), tempSheet.getSheetId());
  spreadSheet.deleteSheet(tempSheet);
  return [blob,param];
}

function getLastData(spreadSheet) {
  const full = spreadSheet.getDataRange().getDisplayValues();
  const last = full[full.length - 1];
  const header = full[0];
  return header.reduce(function(previousValue, currentValue, index, array) {
    previousValue[currentValue] = last[index];
    return previousValue;
  },{});
}

function buildFillinParam(formData) {
  const map = {申請者:"B2", 区分:"B3", 開始日:"B4", 終了日:"B5", 就業時間:"B6", 業務内容:"B7", 備考:"B8", }; // 書式のテンプレを確認しつつ、書き込みたいセル番地を適宜に変更 ※8
  const param = {};
  Object.keys(map).forEach(function(element, index, array) {
    param[map[element]] = formData[element];
  });
  return param;
}

function copySheet(spreadSheet, srcName, dstName) {
  const srcSheet = spreadSheet.getSheetByName(srcName);
  const copied = srcSheet.copyTo(spreadSheet);
  copied.setName(dstName);
  return copied.getSheetName();
}

function fillIn(spreadSheet, sheetName, dat) {
  const sheet = spreadSheet.getSheetByName(sheetName)
  Object.keys(dat).forEach(function(element, index, array) {
    sheet.getRange(element).setValue(dat[element]);
  });
}

// https://www.virment.com/create-pdf-google-apps-script/ より改変
function fetchBlob(ssid, sheetid) {
  const url = "https://docs.google.com/spreadsheets/d/SSID/export?".replace("SSID", ssid);

  const opts = {
    exportFormat: "pdf",    // ファイル形式の指定 pdf / csv / xls / xlsx
    format:       "pdf",    // ファイル形式の指定 pdf / csv / xls / xlsx
    size:         "A4",     // 用紙サイズの指定 legal / letter / A4
    portrait:     "true",   // true → 縦向き、false → 横向き
    fitw:         "true",   // 幅を用紙に合わせるか
    sheetnames:   "false",  // シート名をPDF上部に表示するか
    printtitle:   "false",  // スプレッドシート名をPDF上部に表示するか
    pagenumbers:  "false",  // ページ番号の有無
    gridlines:    "false",  // グリッドラインの表示有無
    fzr:          "false",  // 固定行の表示有無
    gid:          sheetid   // シートIDを指定 sheetidは引数で取得
  };
  
  const url_ext = [];
  
  for( optName in opts ){
    url_ext.push( optName + "=" + opts[optName] );
  }

  const options = url_ext.join("&");

  const token = ScriptApp.getOAuthToken();

  const response = UrlFetchApp.fetch(url + options, {
    headers: {
    'Authorization': 'Bearer ' +  token
    }
  });
  return response.getBlob();
}


// テスタ
function getLastData_test() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const answers = "フォームの回答";
  const sheet = spreadSheet.getSheetByName(answers);
  Logger.log(getLastData(spreadSheet));
}
function buildFillinParam_test() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const answers = "フォームの回答";
  const sheet = spreadSheet.getSheetByName(answers);
  Logger.log(buildFillinParam(getLastData(sheet)));
}
function copySheet_test() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const tmp = Utilities.getUuid();
  const sheetName = "template";
  Logger.log(copySheet(spreadSheet, sheetName, tmp));
}
function fillIn_test1() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = "template";
  const dat = {B2 : "b2", B3 : "b3", B4 : "b4", B5 : "b5", B6 : "b6", B7 : "b7", };
  fillIn(spreadSheet, sheetName, dat);
}
function fillIn_test2() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = "template";
  fillIn(spreadSheet, sheetName, buildFillinParam(getLastData(spreadSheet)));
}

こちらのミスでなければサポートはいたしません。

Comments

[papinianus]

個人情報を含むメールを送信する危険があります。テストのフォームを作成して試してみるなど、十分ご注意ください。こちらでは責任を負えません。

[rrrranco]

ありがとうございます！早速利用させていただきました！
うまくはいきませんでした…！途中経過をご報告させていただきます。
範囲が見つかりませんと表示されました！
行58ファイル「コード」です…。せっかく考えてくださったのにうまく使えずにすみません。メールも送信されませんでしたし、エラー表示の内容をちょくちょく直します…。

[rrrranco]

スクリプトエディタで『関数を実行』で実行すると、とてもうまくいきました！ありがとうございます！！
ですが…！フォームから回答が送信された時は起動してくれません。今のところなぜなのかわからないです。papinianus様は何か思い当ることはありませんでしょうか…。
しかし昨日より光明が見えております、ありがとうございます！感謝します！

[papinianus]

* 範囲が見つかりません(58行)については、※8の設定がおかしい、が疑うポイントですかね。
→こちらはその後解決したのでしょうか？

* フォームからは動かないとのことについては、トリガがちゃんと設定されているかどうか、が疑うポイントです。
(関数実行で動くなら、フォームで動かない理由はないはずです。なぜなら私も関数で実行をしながら作り、最後にフォームから動く設定をして確認したからです)

[rrrranco]

ででででできましたあー！！！トリガーがちゃんと起動できなかったみたいだったので、いったん中身をコピーしてスクリプトエディタを削除→改めて開いて貼付、トリガーを設定したらできました！ありがとうございます！！
※8に該当するセルが結合したものだったので、値を入力したかったのですがうまくいかなかったので結合を解除して１つ分のセルを入力したらなんとかなりました！！
本当にありがとうございます！！

[papinianus]

後学のために、結合セルのところにどうやって入れようとしたか教えていただけませんか？
なお、結合セルもスプレッドシートの左上では1セルの番地しか出ないはずで、そのセル番地を指定すれば、できるのではないかと夢想します。確かに複数セルは考えていませんでした。すみません

[rrrranco]

エクセルだとセル指定はこうだったような…の気持ちで、H5:I5のように入れてみました！まあ失敗でした。結合セルでも左端のセルだけ指定すればなんとかなるみたいでした。初心者ミスすぎてお恥ずかしいです！papinianusさんが謝ることは何一つありません！
そして。この質問に関しては解決していただいたので、papinianusuさんがお答えする必要はないのですがもう一つ質問させてください。
今は、メール送信者はフォームを作ったオーナーになっているのですが、回答者のメールアドレスで送る、もしくは、回答者の名前を件名に載せるというようなPDFを開く前に回答者がわかることはできるのでしょうか。

[papinianus]

いや、仕様バグですね。おはずかしいです。
MailAppという今つかっているやつだとfromのアドレスはかえられないです。fromをかえるのはgoogle的にあまり許しそうにないので、調査を手抜きしてしまいますが、送信者名およびタイトルに回答者名をのせる方向で追記します。

[papinianus]

今、google app scriptをトライできない環境のため、テストはしていません。エラーか予期しない結果だったら教えてください。
関数を実行からトライしてみてください

[rrrranco]

undefined からプロパティ「H5」を読み取れません。（行 6、ファイル「コード」）と表示されました！
fromを変えるのはそうですね、調べたらよろしくないこともできてしまいそうなので諦めました。

[papinianus]

param["B2"]みたいにparam["H5"]としてますか？
違いが微妙なのですが、param[H5]だと動かないはず。
ただ、H5にしても、記載のようなエラーにはならないはず。

fetchBlobとかも変えていますが、全体的にコピペしていただいてますか？
こちらで上記をコピペして実行してみましたが、B2である点を除き正常に動作しました。

[rrrranco]

メールアドレスだけ変更しました。申し訳ないです！『"』が一つ消えていたので付け足して実行しました！
改めて実行しますと、ReferenceError: 「blob」が定義されていません。（行 7、ファイル「コード」）と出ました。

function sender() {
const dat = createRequiredBlob();
const blog = dat[0];
const param = dat[1];
const applicant = param["H5"]; // "B2"のところを※8で申請者の右に指定したセル番地にかえてください ※9
const to = "[e-mail]"; // あてさきに設定するアドレス([e-mail]) ※1
MailApp.sendEmail({
to: to,
name: applicant, //fromの名前がかわるはず。
subject: "申請 (申請者:"+applicant +")", // 件名 ※2
body: "添付ファイルをご確認ください", // 本文 ※3
attachments: [blob.setName("所定外労働・休日出勤許可申請書("+applicant+").pdf")] // ファイル名の変更可 ※4
});
}
function createRequiredBlob() {
const id = "1C9JYBpQHkUSYeDPN1uEqoPvDd4TKJyRoMPjzJdZcqeo"; // form回答が来るスプレッドシートのid ※5
const template = "メール"; // 書式のテンプレとして利用するシートの名前 ※6
const answers = "フォームの回答"; // form回答が入るシートの名前 ※7

// 添付ファイル準備
const spreadSheet = SpreadsheetApp.openById(id);
const temporary = Utilities.getUuid();
const ansSheet = spreadSheet.getSheetByName(answers);
const tempSheetName = copySheet(spreadSheet, template, temporary);
const param = buildFillinParam(getLastData(ansSheet));
fillIn(spreadSheet, tempSheetName, param);
SpreadsheetApp.flush();
const tempSheet = spreadSheet.getSheetByName(tempSheetName);
const blob = fetchBlob(spreadSheet.getId(), tempSheet.getSheetId());
spreadSheet.deleteSheet(tempSheet);
return [blob,param];
}

function getLastData(spreadSheet) {
const full = spreadSheet.getDataRange().getDisplayValues();
const last = full[full.length - 1];
const header = full[0];
return header.reduce(function(previousValue, currentValue, index, array) {
previousValue[currentValue] = last[index];
return previousValue;
},{});
}

function buildFillinParam(formData) {
const map = {申請者:"H5", 区分:"B12", 開始日:"D13", 終了日:"F13", 就業時間:"D14", 業務内容:"B16", 備考:"B20", }; // 書式のテンプレを確認しつつ、書き込みたいセル番地を適宜に変更 ※8
const param = {};
Object.keys(map).forEach(function(element, index, array) {
param[map[element]] = formData[element];
});
return param;
}

function copySheet(spreadSheet, srcName, dstName) {
const srcSheet = spreadSheet.getSheetByName(srcName);
const copied = srcSheet.copyTo(spreadSheet);
copied.setName(dstName);
return copied.getSheetName();
}

function fillIn(spreadSheet, sheetName, dat) {
const sheet = spreadSheet.getSheetByName(sheetName)
Object.keys(dat).forEach(function(element, index, array) {
sheet.getRange(element).setValue(dat[element]);
});
}

// https://www.virment.com/create-pdf-google-apps-script/ より改変
function fetchBlob(ssid, sheetid) {
const url = "https://docs.google.com/spreadsheets/d/SSID/export?".replace("SSID", ssid);

const opts = {
exportFormat: "pdf", // ファイル形式の指定 pdf / csv / xls / xlsx
format: "pdf", // ファイル形式の指定 pdf / csv / xls / xlsx
size: "A4", // 用紙サイズの指定 legal / letter / A4
portrait: "true", // true → 縦向き、false → 横向き
fitw: "true", // 幅を用紙に合わせるか
sheetnames: "false", // シート名をPDF上部に表示するか
printtitle: "false", // スプレッドシート名をPDF上部に表示するか
pagenumbers: "false", // ページ番号の有無
gridlines: "false", // グリッドラインの表示有無
fzr: "false", // 固定行の表示有無
gid: sheetid // シートIDを指定 sheetidは引数で取得
};

const url_ext = [];

for( optName in opts ){
url_ext.push( optName + "=" + opts[optName] );
}

const options = url_ext.join("&");

const token = ScriptApp.getOAuthToken();

const response = UrlFetchApp.fetch(url + options, {
headers: {
'Authorization': 'Bearer ' + token
}
});
return response.getBlob();
}

// テスタ
function getLastData_test() {
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const answers = "フォームの回答";
const sheet = spreadSheet.getSheetByName(answers);
Logger.log(getLastData(spreadSheet));
}
function buildFillinParam_test() {
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const answers = "フォームの回答";
const sheet = spreadSheet.getSheetByName(answers);
Logger.log(buildFillinParam(getLastData(sheet)));
}
function copySheet_test() {
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const tmp = Utilities.getUuid();
const sheetName = "template";
Logger.log(copySheet(spreadSheet, sheetName, tmp));
}
function fillIn_test1() {
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetName = "template";
const dat = {B2 : "b2", B3 : "b3", B4 : "b4", B5 : "b5", B6 : "b6", B7 : "b7", };
fillIn(spreadSheet, sheetName, dat);
}
function fillIn_test2() {
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetName = "template";
fillIn(spreadSheet, sheetName, buildFillinParam(getLastData(spreadSheet)));
}
```

[papinianus]

タイプミスがありました。
上から３行目blogをblobにしてください。回答欄は更新しました

[rrrranco]

すごい！できました！！すごい！！タイプミスに気づけないほどのド素人で本当にお手間をおかけいたしました！すみません！ありがとうございます！