gasでExcel使ったワークフローシステム

やりたい事

会社内でgasを使ってワークフローシステムを作りたいです。
①各申請者達はGoogleフォームでExcelを添付して申請して貰う。
(承認貰いたい内容は全てExcelに書いてある)
②承認者はそのExcelを開くことなく、スプレッドシート内でタスク管理したい。
⇒申請される度にExcelのデータがスプレッドシートに1行づつ反映される方向で検討

最終的にスプレッドシート内に申請数単位でに承認ボタンを設置し、承認メールを自動送信させるつもりですが、そこに関しては問題ないと思います。上記①と②で悩んでおります。

該当のソースコード

function rennbann() {//管理番号付与
  var mySheet2 = SpreadsheetApp.getActiveSheet(); //シートを取得
  
  var range = mySheet2.getRange(mySheet2.getLastRow(),1);
  if(range.isBlank() == true){
    range.setValue("=ROW()-1");
  }
}

function mMail() { //依頼メールを承認者へ送付
    var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
    var myCell = mySheet.getActiveCell();
    var row = myCell.getRow();

    var email = mySheet.getRange(row, 3).getValue();
    var tennpu =mySheet.getRange(row, 7).getValue();
    var kannri =mySheet.getRange(row, 1).getValue();
    var name = mySheet.getRange(row, 4).getValue();
    var bumon = mySheet.getRange(row, 5).getValue();
    var kinngaku =mySheet.getRange(row, 6).getValue();

    var subject = '【交通費精算依頼】管理No.' + kannri + ")";
    var body = "標記の件で、以下ご承認の程宜しくお願い致します\n\n" + "依頼者:" +  name + "\n部門：" + bumon + "\n金額：" + kinngaku + "\n\n以上、宜しくお願い致します\n"　+ tennpu;

    MailApp.sendEmail({to:email, subject:subject,body:body});
}

function kaisi(){
rennbann();
mMail();

}
--------------------------------------------------------------
function showCheckboxInfo() {　//承認者がチャックボックスを押下を確認
    var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
    var myCell = mySheet.getActiveCell();
    var rule = myCell.getDataValidation();
    if (rule != null) {
        var criteria = rule.getCriteriaType();
        var status = myCell.getValue()
            if ( criteria == 'CHECKBOX' && status == true) {
                var row = myCell.getRow();
                var result = Browser.msgBox('チェックボックスが押されました。メールを送信しますか？', Browser.Buttons.OK_CANCEL);
                if(result == 'ok'){
                    sendMail(row);
                } else if (result == 'cancel'){
                    mySheet.getRange(row,2).uncheck(); // チェックを外す
                }
            }
    }
}

function sendMail(row) {　//承認者がチェックボックスを押すとメール送信
    var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
    var myCell = mySheet.getActiveCell();
    var row = myCell.getRow();

    var email = mySheet.getRange(row, 8).getValue();
    var tennpu =mySheet.getRange(row, 7).getValue();
    var kannri =mySheet.getRange(row, 1).getValue();

    var subject = '【交通費精算依頼】管理No.' + kannri + ")";
    var body = "標記の件で、承認致します\n"　+ tennpu;

    MailApp.sendEmail({to:email, subject:subject,body:body});
}
--------------------------------------------------------------
function addCheckboxes() {　//チェックボックスを追加
  targetRange().insertCheckboxes();
}

function checked() {
  targetRange().check();
}

function unchecked() {
  targetRange().uncheck();
}

function remove() {
  targetRange().removeCheckboxes();
}

function judge() {
  var result = targetRange().isChecked();
  Logger.log(result);
}

function targetRange() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange('B2:B' + lastRow);
  return range;
}

調べたこと

Excelを一旦スプレッドシートに変更するなど、様々な方法を調べ検討しましたが、変換までしかできず、全く糸口が掴めず困っております。不可能なのか、可能であればサンプルコードを教えて頂けないでしょうか。

初投稿につき、至らない点などありましたら申し訳ございません。

9/10　追記内容

ご指摘を踏まえ、やりたいことをもう少し詳細に記載します。
それに伴いコードも作成し、追記致しました。

・申請内容は、1つ（「交通費精算の承認」）
・承認者は1人(申請者がエクセル内にて指定）

１．あらかじめフォーマットを決めた、交通費申請用のエクセル（E）を用意する。　
(例：B1セル；申請者のメールアドレス、B2：申請者部門名、B3：申請者名、B4セル：承認依頼先メールアドレス、B5セル：金額）

２．申請者は、Eに申請する内容を書き込み、Googleフォーム（F）からアップロードする。

３．回答を集計しているGoogleスプレッドシート（S）のFのコンテナバインドスクリプトは下記のような内容とする。（トリガー：フォームの送信時）

回答（アップロード）されたEに対応するGoogleドライブURLを読み取り、
DriveAppサービスを使ってEをGoogleスプレッドシートに変換し、
申請者のメールアドレス、申請者部門名、申請者名、承認依頼先メールアドレス、金額を取得し、（S）のEのリンクのある行に書き込む。
※別シートの末尾行に書き込むでも大丈夫です
・Sの1列目には管理NOとして連番1～を記載。2列目は承認用のチェックボックスを設置、残りの列は申請者のメールアドレス、申請者部門名、申請者名、承認依頼先メールアドレス、金額を記録する。

４．承認者は、Googleフォーム送信時にメールを自動受信、後にSを適当なタイミングで確認し、下記の作業を行う。

B列のチェックボックスのうち「未チェック」となっている行について、内容を確認する。
・内容が妥当であればチェック入れる。
・申請者へ承認完了の申請者のメールアドレス宛にメールが送信される。

上記のうち、３の箇所をどのようにすればいいかわからず、どうかお力を頂けないでしょうか。

m.ts10806

2021/09/09 07:19

タグ追加してください https://teratail.com/tags/Google+Apps+Script

oimokorori

2021/09/09 08:05

ありがとうございます。追加できたと思います

m.ts10806

2021/09/09 08:25

できてないようです。teratailの投稿バグもたまにあるので、一度画面更新して編集した内容が反映されているかご確認ください。 ※編集履歴リンクもつくはずです

oimokorori

2021/09/09 08:42

失礼致しました????今度こそ大丈夫だと思います

sawa

2021/09/10 13:24

参照セル5つ程度のシンプルな内容なら、わざわざExcelを添付とせずにGoogleフォームに項目用意した方がコードもシンプルで楽だと思いますが、なぜにExcel申請書を記入し添付という手法をとりたいのでしょうか？

oimokorori

2021/09/10 14:19

私も仰る通りと思いましたが、社内の強い要望がありました。実際には交通費申請ではなく項目も少し増えると想定していますが例えばとある案件の部材発注における承認依頼だとして、購入部材の項目に部材1と部材2と部材3を記載します。その部材を使う案件の項目を作るとしてa案件とb案件しか書いてなかったら、どの案件でどの部材を使うか不明となってしまいます。なのでExcelでは3つ部材があったら購入部材項目の列に縦に3セル入力させて、その場合対応する案件名も同じ行に3セル入力必須とさせたいです。それをスプレッドシートに反映させる際は3行を1行に全て収まっていないといけないと思いますが、それは関数やマクロでフォーム申請前段階にどうにかなると思っています。 1番大きな理由は上記ですが、申請者名や部門名など同じものはいちいち入力せずExcelを使い回したいなどという利用者の要望もあります????

行動規範の内容に同意します

回答1件

ベストアンサー

以下のようなごく単純な交通費申請のワークフローを考えてみましょう。

・申請内容は、1つ（「交通費精算の承認」）
・承認者は、1人（経理担当者Aさん）

１．あらかじめフォーマットを決めた、交通費申請用のエクセル（E）を用意する。　
(例：B1セル；申請者のメールアドレス、B2：申請者名、B3：日付、B4セル：金額、B5セル：経路、B6セル：メモ）

２．申請者は、Eに申請する内容を書き込み、Googleフォーム（F）からアップロードする。

３．回答を集計しているGoogleスプレッドシート（S）の
コンテナバインドスクリプトは下記のような内容とする。（トリガー：フォームの送信時）

回答（アップロード）されたEに対応するGoogleドライブURLを読み取り、
DriveAppサービスを使ってEをGoogleスプレッドシートに変換し、
申請者名、日付、金額、経路、メモを取得し、別のシート（S2）の末尾行に書き込む。

・S2に書き込む際は、書き込み日時と、[未確認・承認・否認]のリストボックスを先頭列に付与し、残りの列は申請者メルアド、申請者名、日付、金額、経路、メモを記録する。

４．経理担当者は、S2を適当なタイミングで確認し、下記の作業を行う。

B列のリストボックスのうち「未確認」となっている行について、内容を確認する。

・内容が妥当であればリストボックスを承認に変える。
・内容に間違いがあれば、リストボックスを否認に変え、「経理担当者連絡欄」列に、申請者に伝えたい内容を記入する。

５．S2にはボタンがあり、それをクリックすると、以下のような処理を行う（S2のスクリプト）。

S2のB列が「承認」の場合は「承認しました」という件名のメールを申請者のメールアドレス宛に送信。
「否認」の場合は、「差し戻し」の件名で、本文に「経理担当者連絡欄」に入力したメモを記載し、申請者のメールアドレス宛に送信。
「返信済み」列が空欄の行に関しては、何もしない。
送信に成功した行は、S2の「返信済み」列にチェックを入れ、「返信時刻」列に返信時刻を記録する。

上記は単純な場合でしたが、今回の交通費精算のワークフローに「交際費申請」も追加したいとします。

そうすると下記のような対応が必要になりそうです。

交通費用に加えて交際費会議費用のエクセルフォーマットを追加します。

　交際費会議費フォーマットは、経路は削除し、たとえば接待先、人数、出席者　等を追加することになるでしょう。
交通費と交際費会議費を区別するために、それぞれのエクセルフォーマットに「種別」行を追加する必要があるでしょう。
スクリプトに、交際費申請フォーマットの内容を読み取る処理を追加する必要があります。
種別を読み取って処理を分けるコードも必要になりますね。

経理担当者が見るシート（S2）も変える必要があります。

経理担当者はシートをいちいち切り替え手確認するのが面倒らしいので、交通費と交際費会議費を全部1つのシートで確認できるようにするとしましょう。
そうすると、交通費と交際費で異なる項目の列を混在追加させないといけません。
ボタンを押したときの処理スクリプトも変える必要があります。

交際費会議費の場合、経理担当者だけが承認すればよいのではなく、経理承認の前に部門長の承認が必要、という場面が多いでしょう。

そうすると、部門長が確認するシートを別途作成し、そのシートで部門長が承認した交際費だけ経理担当者のシートに流すという処理を書く必要があります。

もし部門が1つではなく複数になれば、下記のような対応が必要になります。

・部門の数毎に、部門長シートが増えます。
・各申請者の申請を、どの部門長に流すかを管理するテーブルとそれをもとに申請を振り分けるスクリプトが必要になります。

GAS無料枠の場合、DriveAppの使用回数には制限があるので、申請者の人数や一日当たりの申請件数によっては、処理をさばけなくなるかもしれません。

また処理を継続できるのは6分（有料なら30分）なので、各処理をその枠内に収める必要もあります。

上記のようにごく単純なケースなら実現できそうに見えても、ちょっと応用しようとするとメンテナンスや処理の複雑度が増してしまうことはお判りでしょうか？

最初のユースケースなら、多分私でも何とかコードは書けそうです。（それでも実際に運用した場合、利用人数によっては使い物にならない可能性はあります）

しかし、2番目のユースケースになると、かなり慎重に運用や業務要件を詰めて設計しないと破綻するでしょう。（複雑なワークフローにGASを利用するのはあきらめる、という選択肢もあり得ます）

さて、長々と書きましたが、そもそも、質問者さんの具体的なユースケースはどのようなものでしょうか。上記のレベルで分解して説明できますか。それらが明らかにされていない状態で、可能か不可能か論じるのは、エスパーでもない限りできないと思います。

あと「サンプルコードを提示してほしい」とのことですが、正直質問文に記載されているレベルの内容でコードを要望するのは「丸投げ」に該当します。

質問文に記載の「最終的にスプレッドシート内に申請数単位でに承認ボタンを設置し、承認メールを自動送信させるつもり」との文面から察するに、ある程度お手持ちのコードがあるが、一部やり方が分からなくて詰まっている、という状況ではないでしょうか。

ならば、そのコードを質問文に記載し、「この部分のこういう機能を実現するためのやり方がわからない」、といった具体的な提示をしていただいた方が、回答も付きやすいのではないかと思います。

【9/11追記】
ユースケースの説明ありがとうございます。
一例として、3.の「フォームからアップロードされた申請用エクセルの内容を読み取って、1行ずつチェックボックスと一緒に記録する」コードを記します。
・下記コードは、フォームの回答を集約するスプレッドシートのスクリプトに記述します。
・あらかじめトリガーで「フォーム送信時」に「onSubmitForm」関数を実行するように指定しておきます。

・あらかめエディタの左の方にある「＋サービス」からDrive APIを追加しておいてください。

・申請用エクセルから読み取った内容は、フォームの回答を集約するスプレッドシートとは別のシート（説明の便宜上「チェックシート」という名称とします）に記録するようにしています。
・質問者さんが処理内容を理解してカスタマイズしやすいように、あえてmap等は使わず冗長なコードにしている部分があります。
たとえばコード中で申請エクセルの項目と集計シートの項目の対応を分かりやすくするために、insertValues()の戻り値は配列ではなく、Mapかオブジェクトとして返すようにするなど工夫できるかもしれませんが、ここでは割愛します。
・insertValuesの中のlastRow取得前後など、排他処理を使った方が安全な部分があるかもしれません（ループを使って数秒間に２つのアカウントから10件をほぼ同時書き込みするテストでは特に問題が出ませんでしたが、人数やタイミングによっては問題が生じるかもしれません（未検証））

・質問者さんが記載している残りのコード（チェックボタン押下時にメール送信、等）は、基本的にチェックシートの方のスクリプトに書く形となります。
（少しややこしいですが、下記スクリプトは「フォームの回答を集計しているシート」の方に書きます。
一方、「チェックボタンをチェックしたらメール送信する」等のスクリプトは、チェックシートのスクリプトとして書きます）

js
1const CHECKSHEET_ID = 'チェックシートのスプレッドシートID'
2const SHEET_NAME = 'チェックシートのシート名';
3const checkSheet = SpreadsheetApp.openById(CHECKSHEET_ID).getSheetByName(
4  SHEET_NAME);
5
6function onSubmitForm(e) {
7  for (let i = 0; i <= e.range.rowEnd - e.range.rowStart; i++) {
8    const uploadFileId = e.namedValues['アップロードファイル'][i]
9      .replace('https://drive.google.com/open?id=', '');
10    const file = convertGSpreadFile(uploadFileId);
11
12    if (file == null) {
13      Logger.log('エクセルファイルを変換できませんでした。\n' +
14        `fileId:${uploadFileId}`
15      )
16    }
17    const expenseValues = getExpenseValues(file);
18    if (expenseValues == null) {
19      Logger.log('申請書ファイルの内容を読み取れませんでした。');
20    }
21    // 内容を読み取ったら変換後のGoogleスプレッドシートは削除する。
22    Drive.Files.remove(file.getId());
23    insertValues(expenseValues);
24  }
25}
26
27/***
28 エクセルファイルをGoogleスプレッドシートに変換する。
29 　引　数： (String) fileId: 変換対象のエクセルのファイルID（Googleドライブ上のファイルID）
30 　戻り値： (File)：変換後に生成されたGoogleスプレッドシートのFileオブジェクト
31          (変換に失敗した場合：null)
32***/
33function convertGSpreadFile(fileId) {
34  try {
35    const excelFile = DriveApp.getFileById(fileId);
36    const blob = excelFile.getBlob();
37
38    const resource = {
39      title: excelFile.getName().replace(/.xlsx?/, ""),
40      key: fileId
41    };
42
43    return Drive.Files.insert(resource, blob, {
44      convert: true
45    });
46
47  } catch (err) {
48    Logger.log(err.toString());
49    return null;
50  }
51}
52
53/***
54 ファイルの内容を読み取って配列にして返す
55 　引　数： (File) file: 内容を読み取る対象のGoogleスプレッドシートファイル
56 　戻り値： (Array)：スプレッドシートのB列の内容の配列
57          （読み取りに失敗した場合：null）
58***/
59function getExpenseValues(file) {
60  const sh = SpreadsheetApp.openById(file.getId());
61  const vals = sh.getDataRange().getValues();
62  try {
63    const ret = [];
64    for (let i = 0; i < vals.length; i++) {
65      ret.push(vals[i][1]);
66    }
67    return ret;
68  } catch (err) {
69    Logger.log(err.toString())
70    Logger.log(`ファイル内容：${vals}`)
71    return null;
72  }
73}
74
75/***
76 チェックシートにデータを挿入する。
77 　引　数： (Array) values: エクセルから読み取ったデータの配列
78 　戻り値： なし
79***/
80function insertValues(values) {
81  Logger.log(values)
82  checkSheet.appendRow(['=ROW()-1', '', ...values])
83  const lastRow = checkSheet.getLastRow();
84  // チェックボックスをB列に追加
85  checkSheet.getRange(lastRow, 2).insertCheckboxes();
86}

フォーム

申請用エクセル

チェックシート（フォームから2件投稿後の状態）

投稿2021/09/09 15:54

編集2021/09/11 22:17

退会済みユーザー

総合スコア0

oimokorori

2021/09/10 10:42

ご指摘、アドバイスありがとうございます。丸投げという形になってしまい申し訳ございませんでした。私のやりたいことを正確に察して頂いていたようで、交通費申請の例がとてもわかりやすく、編集させていただく際表現の多くを引用させて頂きました。知り合いの現役SEなど頼れるつては全て使っても出来ないと言われてしまい最後の可能性と思っています。失礼とは思いますが一人でも多くの方に伝わり可能性を広げたくお許し頂けないでしょうか。（ちなみに承認否認を区別できるリストボックスがあるとは驚きでしたが取り急ぎ今必要なものは承認のみの形ですので、その方向で作成してみました）

退会済みユーザー

2021/09/11 00:40

後半にコードを追記しました。

oimokorori

2021/09/11 15:02

ありがとうございます。とても助かりました。 qnoirさんは動作確認されているのでコード自体間違いないと思いますが、なぜか上手く動作しません。どうやらスプレッドシートへ変換のところgetIDのところで躓いているようですが、丸一日調べて答えが出ませんでした。基本的に作成して頂いたコードはコピペで使えて、注意するところと言えば以下くらいだという認識です。 ①一番上const CHECKSHEET_ID　と　const SHEET_NAME をチェックシートのシートIDと名前にする ②フォームの添付欄の名前をアップロードファイルにする ③フォームの回答を集計しているシートに書いて頂いたコードを書く ④エクセルの中データは一旦添付頂いた画像の通りに、拡張子もxlsx ⑤チェックシートの1行目は添付頂いた画像の通りにその結果、以下エラー ReferenceError: timestamp is not defined timestamp を含む1行のコードを削除すると以下エラー TypeError: Cannot read property 'getId' of null 初心者で申し訳ございません何か心当たりなどありませんでしょうか.... ここまで大きなヒント頂きましたのでこれ以上お答えいただかなくても心当たりなくとも十分ベストアンサーにさせて頂き、継続して頂いたコードを解読し続けますが、すぐに思いつくことありましたらご返信頂けますと幸いです。（可能であれば贈り物をさせて頂きたいくらいとても感謝しております。ありがとうございます）

退会済みユーザー

2021/09/11 22:16 編集

回答を追記・修正しました。・エディタの左の方にある「＋サービス」の「＋」ボタンを追加して、Drive APIを追加してください。・コード中のLogger内の、テスト時に使っていた変数を消しわすれていましたので、削除しました。（こちらのミスですみません）

oimokorori

2021/09/12 02:51

出来ました！こんな初心者に最後までご指導頂きありがとうございます。感動致しました！

行動規範の内容に同意します