経緯
支援管理表、ほぼ毎日書く必要があるのに書く項目多過ぎる問題
しかも Googleフォームで送信された内容とほぼ同じ事を書く欄もそこそこある。
フォーム > 回答収集スプレッドシート > 管理表スプレッドシート
回答収集のブックは利用者ごちゃ混ぜで入ってくる。
管理表のブックは個人個人で別のブックがある。
よって、一つのブックに纏める訳にもいかない。
つまり回答収集スプシを参照して管理表にぶち込む仕組みが必要だ !
目次
まず、一度決めたら絶対に変わらない値を自動で入力されるようにする。
名前とついでに日付だ。
function autoInput() {
const recordSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const userName = "手素斗 太郎";
// 利用者名
recordSheet.getRange("I7").setValue(userName);
// 学習年月日 = シート名
recordSheet.getRange("L7").setValue(recordSheet.getSheetName());
// recordSheet.getRange("L7").setFormula("=[関数]");
}
メソッドと変数名はかなりテキトー。
recordSheet は管理表(自動入力したいシート) を指す。
管理表は各々に 1つのブックが用意されているため、シートが増えても入力される名前は 1つだけ。よって、名前はセルに値をセットできるなら絶対実装できる。
今回は userName = “手素斗 太郎” としました。
日付は固定値ではないものの、シート名と同じなのよね。
という訳で管理表の今開いているシートのシート名を取得して、セルL7 に入力している。
わからないなりに GAS について調べながらスクリプトを書いた。
あっこれ使うかもと忘れないように .setFomula() をメモしていたが、使いませんでしたね。
.setFormula() は指定されたセル範囲に関数を入力する処理。
ここからは回答収集ブックの各種日報シートから値を引っ張ってくる。
手始めに通所日報から学習目標を参照･入力しよう。
というか一つできたら他も同じ要領だろうから、これができたらオールクリアだ !
function autoInput() {
const recordSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const userName = "手素斗 太郎";
/* 日報取得 */
const reportSheet = SpreadsheetApp.openById("[日報収集しているブックのID]");
const comeSheet = reportSheet.getSheetByName("通所日報");
const returnSheet = reportSheet.getSheetByName("退所日報");
// シートのデータを2次元配列で取得
// メモ: 利用者数から逆算してデータ取得レンジを調整した方が動作が軽く済みそう sheet.getRange(開始行数, 開始列数, 行数, 列数).getValues()
// 通所時: A:S --- 退所時: A:O
const comeData = comeSheet.getDataRange().getValues();
const returnData = returnSheet.getDataRange().getValues();
// 利用者名
recordSheet.getRange("I7").setValue(userName[0]);
// 学習年月日 = シート名
recordSheet.getRange("L7").setValue(recordSheet.getSheetName());
/* 学習目標 */
// 1行目ヘッダーのため i = 1
let matchedValue = null;
for (let i = 1; i < comeData.length; i++) {
if (comeData[i][3] === userName && comeData[i][4] === recordSheet.getSheetName()) {
matchedValue = comeData[i][15];
break;
}
}
// 出力
if (matchedValue !== null) {
recordSheet.getRange("B9").setValue(matchedValue);
}
}
▲書いてみたスクリプトがこちら
わかりづらいのだが、reportSheet は回答収集ブックの事。.openById() にてブックを参照している。
comeSheet と returnSheet にそれぞれの日報のシートを格納している。come が通所、return が退所を指している。なんとテキトーな…。
通所日報と退所日報にほぼ同じ処理をするため、ここからは通所日報のみ説明を入れていく。
comeData には通所日報シートのデータを 2次元配列で格納。
スクリプトの 24行目から 37行目にかけては条件に合う学習目標を取ってきて、入れたいセルに学習目標を入力するまでの処理が書かれている。つもりでした。
comeData(通所日報シート) の名前と日付を該当者の名前と日付でそれぞれ比較を行い、それを comeData の要素数分繰り返している。その後、条件を満たした場合は comeData の学習目標を matchedValue に代入。
最後に matchedValue の値をセルB9 に入力している。
が、何度試してもセルB9 に学習目標が入らず、苦戦しました。
できなかった理由として考えられる点が 2つ
– 2次元配列 comeData に日報が収まりきっていない ?
– comeData[i][4] が文字列ではなく Date型になっている ?
comeData にシートの全データを格納しているのだが、データが多過ぎて入りきっていないのでは ? という疑問。実際、デバッグした時は comeData 内の値は通所日報シートの途中で止まっていた。
そして、型の問題について。
学習目標は名前と日付が正しいかどうかを値取得の条件にしている。
通所日報シートの D列に名前、E列に日付が入っているため、comeData[i][3] に名前と comeData[i][4] に日付が入っているということになる。
ここで問題となるのが、日付の方。比較している日付のデータ型が異なっているのではないかという問題。型が違えば比較できない。
▼という訳で 2つの問題を解決しようとしたスクリプトがこちら。
function autoInput() {
const recordSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const userName = "手素斗 太郎";
/* DX利用者日報取得 */
const reportSheet = SpreadsheetApp.openById("[日報収集しているブックのID]");
const comeSheet = reportSheet.getSheetByName("通所日報");
const returnSheet = reportSheet.getSheetByName("退所日報");
// シートのデータを2次元配列で取得
// メモ: 利用者数から逆算してデータ取得レンジを調整した方が動作が軽く済みそう sheet.getRange(開始行数, 開始列数, 行数, 列数).getValues()
// 通所時: A:S --- 退所時: A:O
// const comeData = comeSheet.getDataRange().getValues();
const comeData = comeSheet.getRange(705-9, 1, 10, 18).getValues(); // ここ変えました
const returnData = returnSheet.getDataRange().getValues();
// 利用者名
recordSheet.getRange("I7").setValue(userName[0]);
// 学習年月日 = シート名
recordSheet.getRange("L7").setValue(recordSheet.getSheetName());
/* 学習目標 */
const recordDateStr = recordSheet.getSheetName();
let matchedValue = null;
// 日付比較用に comeData の値を文字列化
for (let i = 1; i < comeData.length; i++) {
const name = comeData[i][3]; // 名前欄
const dateVal = comeData[i][4]; // 日付欄
// Dateオブジェクトを "yyyy年M月d日" に整形
const dateStr = (dateVal instanceof Date)
? Utilities.formatDate(dateVal, "Asia/Tokyo", "yyyy年M月d日")
: dateVal; // 文字列に変換した通所時日報の日付を格納する変数
if (name === userName[1] && dateStr === recordDateStr) {
matchedValue = comeData[i][15]; // 学習目標欄
break;
}
}
// 出力
if (matchedValue !== null) {
recordSheet.getRange("B9").setValue(matchedValue);
}
}
元々は下記の文でシート内のデータを全取得していた。
const comeData = comeSheet.getDataRange().getValues();
ここを .getRange() に変えることで取得範囲を定めた。今回は試運転も兼ねて変数や関数を使わず数字を入れている。
const comeData = comeSheet.getRange(705-9, 1, 10, 18).getValues()
これでできるかとも思ったのですが、まだできず。
データ取得範囲を絞ったのは良かった。しかし、Date型 > 文字列 の変換が上手くいかず。
GPTに頼ったのが裏目に出たか。
const dateStr = (dateVal instanceof Date) ? Utilities.formatDate(dateVal, “Asia/Tokyo”, “yyyy年M月d日”) : dateVal;
この部分、おそらく dateVal が Date型であればyyyy年M月d日の文字列にしてそれ以外であれば直さない、のような処理だと思われる。
Date型でなくとも文字列に直さなければ道は無く、加えてdateVal は日付前提。ならば開き直って何が何でも yyyy年M月d日に直すように書いてしまおう。
▼そんなこんなで、できたのがこちら。先に書いてしまうと、このスクリプトで上手くいった。
function autoInput() {
const recordSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const userName = "手素斗 太郎";
/* DX利用者日報取得 */
const reportSheet = SpreadsheetApp.openById("[日報収集しているブックのID]");
const comeSheet = reportSheet.getSheetByName("通所日報");
const returnSheet = reportSheet.getSheetByName("退所日報");
// シートのデータを2次元配列で取得
// 通所時: A:S --- 退所時: A:O
const comeData = comeSheet.getRange(705-9, 1, 10, 18).getValues();
const returnData = returnSheet.getDataRange().getValues();
// 利用者名
recordSheet.getRange("I7").setValue(userName[0]);
// 学習年月日 = シート名
recordSheet.getRange("L7").setValue(recordSheet.getSheetName());
/* 学習目標 */
const recordDateStr = recordSheet.getSheetName();
let matchedValue = null;
// 日付比較用に comeData の値を文字列化
for (let i = 1; i < comeData.length; i++) {
const name = comeData[i][3]; // 名前（フルネーム）欄
const dateVal = comeData[i][4]; // 日付欄
const dateStr = Utilities.formatDate(dateVal, "JST", "yyyy年M月d日"); // 日付を文字列に変換
if (name === userName[1] && dateStr === recordDateStr) {
matchedValue = comeData[i][15]; // 学習標欄
break;
}
}
// 出力
if (matchedValue !== null) {
recordSheet.getRange("B9").setValue(matchedValue);
}
}
日報の名前入力欄、人によって書き方が違うんだよね。
となると日報の名前 = 管理表の名前 = 日報から探索する条件 とすると都合が悪い場面が出てくる。
まぁその点については管理表の名前を代入する変数と、日報から探索する名前を代入する変数とで分けて宣言してしまえば済む話。
とはいえ、回答者が常に同じ名前を入力するとも限らない。
時間がない時に名字だけ記入して回答されるかもしれない。そういうイレギュラーの拾い漏れを少しでも少なくできたらそれだけで万々歳なのでは ?
そういう訳で、あいまい検索みたいな探索方法無いかな。
というところから出てきた発想がこちら
function autoInput() {
const recordSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const userName = ["手素斗 太郎", "手素斗太郎", "手素斗 太郎", "手素斗", "テスト タロウ"];
/* 日報取得 */
// come: 通所、return: 退所
const reportSheet = SpreadsheetApp.openById("1wv9ECy7wRhaAsNh8n_JohH35MMQJ6l8X5FTyT5vAr1Y");
const comeSheet = reportSheet.getSheetByName("通所日報");
// const returnSheet = reportSheet.getSheetByName("退所日報");
const comeLastRow = comeSheet.getLastRow();
// const returnLastRow = returnSheet.getLastRow();
// シートのデータを2次元配列で取得
// 通所時: A:R(18列) --- 退所時: A:O(15列)
const comeData = comeSheet.getRange(comeLastRow - 19, 1, 20, 18).getValues();
// const returnData = returnSheet.getRange(returnLastRow - 19, 1, 20, 15).getValues();
// 利用者名
recordSheet.getRange("I7").setValue(userName[0]);
// 学習年月日 = シート名
const recordDate = recordSheet.getSheetName();
recordSheet.getRange("L7").setValue(recordDate);
/* 学習目標 */
let matchedValue = null;
for(let j = 0; j < userName.length; j++){
for (let i = 1; i < comeData.length; i++) {
const name = comeData[i][3]; // 回答名前欄
const dateVal = comeData[i][4]; // 回答日付欄
const dateStr = Utilities.formatDate(dateVal, "JST", "yyyy年M月d日"); // 日付を文字列に変換
if (name === userName[j] && dateStr === recordDate) {
matchedValue = comeData[i][15]; // スキル目標欄
break;
}
}
}
if (matchedValue !== null) {
recordSheet.getRange("B9").setValue(matchedValue);
}
}
脳筋戦法、炸裂。
スクリプトは自分の見やすいように書き換えている部分もあります。
検索する名前を増やそうの会。
あいまい検索ではないけど、実装が楽だしある程度予測できるしで、これで良いんじゃなかろうか。
1つ目の要素には管理表に入力する文字列を入れて、それ以降は回答収集から一致する文字列を探す用。
この配列を増やし過ぎると処理に時間がかかるので、過去のパターンから回答されたことのある名前だけを宣言すれば良いと思う。
今回はここまで。
▼次回
【スプレッドシート】GASによる自動入力2 