回答編集履歴

2021/09/11 22:17

投稿

スコア0

test CHANGED Viewed

@@ -166,7 +166,7 @@
-・**あらかめエディタの左の方にある「＋サービス」からDriveサービスを追加しておいてください。**
+・**あらかめエディタの左の方にある「＋サービス」からDrive APIを追加しておいてください。**
 ![イメージ説明](241ebee5b9ed3b2b338d36869bb2da59.png)

修正：Driveサービス追加の件＆Logger内の変数

2021/09/11 22:17

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -164,6 +164,14 @@
 ・あらかじめトリガーで「フォーム送信時」に「onSubmitForm」関数を実行するように指定しておきます。
+・**あらかめエディタの左の方にある「＋サービス」からDriveサービスを追加しておいてください。**
+![イメージ説明](241ebee5b9ed3b2b338d36869bb2da59.png)
 ・申請用エクセルから読み取った内容は、フォームの回答を集約するスプレッドシートとは別のシート（説明の便宜上「チェックシート」という名称とします）に記録するようにしています。
 ・質問者さんが処理内容を理解してカスタマイズしやすいように、あえてmap等は使わず冗長なコードにしている部分があります。
@@ -210,7 +218,7 @@
       Logger.log('エクセルファイルを変換できませんでした。\n' +
-        `timestamp: ${timestamp}\n mail:${mailAddress}\n fileId:${uploadFileId}`
+        `fileId:${uploadFileId}`
       )

チェックボックスの引数修正

2021/09/11 22:12

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -352,7 +352,7 @@
   // チェックボックスをB列に追加
-  checkSheet.getRange(lastRow, 2).insertCheckboxes(false);
+  checkSheet.getRange(lastRow, 2).insertCheckboxes();
 }

2021/09/10 23:59

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -162,7 +162,7 @@
 ・下記コードは、フォームの回答を集約するスプレッドシートのスクリプトに記述します。
-・あらかじめトリガーで「フォーム送信時」に「onSubmit」関数を実行するように指定しておきます。
+・あらかじめトリガーで「フォーム送信時」に「onSubmitForm」関数を実行するように指定しておきます。
 ・申請用エクセルから読み取った内容は、フォームの回答を集約するスプレッドシートとは別のシート（説明の便宜上「チェックシート」という名称とします）に記録するようにしています。

2021/09/10 17:38

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -22,9 +22,9 @@
-回答を集計しているGoogleスプレッドシート（S）の
+３．回答を集計しているGoogleスプレッドシート（S）の
-３．Fのコンテナバインドスクリプトは下記のような内容とする。（トリガー：フォームの送信時）
+コンテナバインドスクリプトは下記のような内容とする。（トリガー：フォームの送信時）
@@ -50,7 +50,7 @@
-５．S2にはボタンがあり、それをクリックすると、以下のような処理を行う（スクリプト）。
+５．S2にはボタンがあり、それをクリックすると、以下のような処理を行う（S2のスクリプト）。
 - S2のB列が「承認」の場合は「承認しました」という件名のメールを申請者のメールアドレス宛に送信。
@@ -164,13 +164,21 @@
 ・あらかじめトリガーで「フォーム送信時」に「onSubmit」関数を実行するように指定しておきます。
-・申請用エクセルから読み取った内容は、フォームの回答を集約するスプレッドシートとは別のシート（「チェックシート」）に記録するようにしています。
+・申請用エクセルから読み取った内容は、フォームの回答を集約するスプレッドシートとは別のシート（説明の便宜上「チェックシート」という名称とします）に記録するようにしています。
 ・質問者さんが処理内容を理解してカスタマイズしやすいように、あえてmap等は使わず冗長なコードにしている部分があります。
-たとえばコード中で申請エクセルの項目と集計シートの項目の対応を分かりやすくするために、insertValues()は配列ではなくMapかオブジェクトで返すようにするなど工夫できるかもしれませんがここでは割愛します。、
+たとえばコード中で申請エクセルの項目と集計シートの項目の対応を分かりやすくするために、insertValues()の戻り値は配列ではなく、Mapかオブジェクトとして返すようにするなど工夫できるかもしれませんが、ここでは割愛します。
-・lastRow取得のところなど、排他処理を使った方が安全な部分はあるかもしれません（ループを使って数秒間に２つのアカウントから10件をほぼ同時書き込みするテストでは特に問題が出ませんでしたが、人数やタイミングによっては問題が生じるかもしれません（未検証））
+・insertValuesの中のlastRow取得前後など、排他処理を使った方が安全な部分があるかもしれません（ループを使って数秒間に２つのアカウントから10件をほぼ同時書き込みするテストでは特に問題が出ませんでしたが、人数やタイミングによっては問題が生じるかもしれません（未検証））
+・質問者さんが記載している残りのコード（チェックボタン押下時にメール送信、等）は、基本的にチェックシートの方のスクリプトに書く形となります。
+（少しややこしいですが、下記スクリプトは「フォームの回答を集計しているシート」の方に書きます。
+一方、「チェックボタンをチェックしたらメール送信する」等のスクリプトは、チェックシートのスクリプトとして書きます）

修正

2021/09/10 17:37

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -366,4 +366,4 @@
 チェックシート（フォームから2件投稿後の状態）
-![イメージ説明](e79acb8d9f697c3add2af358f0eacb14.png)
+![イメージ説明](a2022cbbca9e37eb35b00ff0859d7f10.png)

追加

2021/09/10 17:18

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -149,3 +149,221 @@
 ならば、そのコードを質問文に記載し、「この部分のこういう機能を実現するためのやり方がわからない」、といった具体的な提示をしていただいた方が、回答も付きやすいのではないかと思います。
+---
+【9/11追記】
+ユースケースの説明ありがとうございます。
+一例として、3.の「フォームからアップロードされた申請用エクセルの内容を読み取って、1行ずつチェックボックスと一緒に記録する」コードを記します。
+・下記コードは、フォームの回答を集約するスプレッドシートのスクリプトに記述します。
+・あらかじめトリガーで「フォーム送信時」に「onSubmit」関数を実行するように指定しておきます。
+・申請用エクセルから読み取った内容は、フォームの回答を集約するスプレッドシートとは別のシート（「チェックシート」）に記録するようにしています。
+・質問者さんが処理内容を理解してカスタマイズしやすいように、あえてmap等は使わず冗長なコードにしている部分があります。
+たとえばコード中で申請エクセルの項目と集計シートの項目の対応を分かりやすくするために、insertValues()は配列ではなくMapかオブジェクトで返すようにするなど工夫できるかもしれませんがここでは割愛します。、
+・lastRow取得のところなど、排他処理を使った方が安全な部分はあるかもしれません（ループを使って数秒間に２つのアカウントから10件をほぼ同時書き込みするテストでは特に問題が出ませんでしたが、人数やタイミングによっては問題が生じるかもしれません（未検証））
+```js
+const CHECKSHEET_ID = 'チェックシートのスプレッドシートID'
+const SHEET_NAME = 'チェックシートのシート名';
+const checkSheet = SpreadsheetApp.openById(CHECKSHEET_ID).getSheetByName(
+  SHEET_NAME);
+function onSubmitForm(e) {
+  for (let i = 0; i <= e.range.rowEnd - e.range.rowStart; i++) {
+    const uploadFileId = e.namedValues['アップロードファイル'][i]
+      .replace('https://drive.google.com/open?id=', '');
+    const file = convertGSpreadFile(uploadFileId);
+    if (file == null) {
+      Logger.log('エクセルファイルを変換できませんでした。\n' +
+        `timestamp: ${timestamp}\n mail:${mailAddress}\n fileId:${uploadFileId}`
+      )
+    }
+    const expenseValues = getExpenseValues(file);
+    if (expenseValues == null) {
+      Logger.log('申請書ファイルの内容を読み取れませんでした。');
+    }
+    // 内容を読み取ったら変換後のGoogleスプレッドシートは削除する。
+    Drive.Files.remove(file.getId());
+    insertValues(expenseValues);
+  }
+}
+/***
+ エクセルファイルをGoogleスプレッドシートに変換する。
+ 　引　数： (String) fileId: 変換対象のエクセルのファイルID（Googleドライブ上のファイルID）
+ 　戻り値： (File)：変換後に生成されたGoogleスプレッドシートのFileオブジェクト
+          (変換に失敗した場合：null)
+***/
+function convertGSpreadFile(fileId) {
+  try {
+    const excelFile = DriveApp.getFileById(fileId);
+    const blob = excelFile.getBlob();
+    const resource = {
+      title: excelFile.getName().replace(/.xlsx?/, ""),
+      key: fileId
+    };
+    return Drive.Files.insert(resource, blob, {
+      convert: true
+    });
+  } catch (err) {
+    Logger.log(err.toString());
+    return null;
+  }
+}
+/***
+ ファイルの内容を読み取って配列にして返す
+ 　引　数： (File) file: 内容を読み取る対象のGoogleスプレッドシートファイル
+ 　戻り値： (Array)：スプレッドシートのB列の内容の配列
+          （読み取りに失敗した場合：null）
+***/
+function getExpenseValues(file) {
+  const sh = SpreadsheetApp.openById(file.getId());
+  const vals = sh.getDataRange().getValues();
+  try {
+    const ret = [];
+    for (let i = 0; i < vals.length; i++) {
+      ret.push(vals[i][1]);
+    }
+    return ret;
+  } catch (err) {
+    Logger.log(err.toString())
+    Logger.log(`ファイル内容：${vals}`)
+    return null;
+  }
+}
+/***
+ チェックシートにデータを挿入する。
+ 　引　数： (Array) values: エクセルから読み取ったデータの配列
+ 　戻り値： なし
+***/
+function insertValues(values) {
+  Logger.log(values)
+  checkSheet.appendRow(['=ROW()-1', '', ...values])
+  const lastRow = checkSheet.getLastRow();
+  // チェックボックスをB列に追加
+  checkSheet.getRange(lastRow, 2).insertCheckboxes(false);
+}
+```
+フォーム
+![イメージ説明](facb2c9b98d672abe4a0f94f51f2d1a3.png)
+申請用エクセル
+![イメージ説明](8c2553b82fa5e4bbcab8cf5c43853466.png)
+チェックシート（フォームから2件投稿後の状態）
+![イメージ説明](e79acb8d9f697c3add2af358f0eacb14.png)

修正

2021/09/10 17:14

投稿

退会済みユーザー

スコア0

test CHANGED Viewed

@@ -1,8 +1,8 @@
-以下のようなごく単純な交際費申請のワークフローを考えてみましょう。
+以下のようなごく単純な交通費申請のワークフローを考えてみましょう。
-・申請内容は、1つ（「交費精算の承認」）
+・申請内容は、1つ（「交通費精算の承認」）
 ・承認者は、1人（経理担当者Aさん）