Googleフォームは使ったことあるけど『GAS』って何者？
こんにちは。
私は、とある企業のECサイトの部署で受発注から取引先様情報の管理、商品登録、お客様対応など様々な業務に携わっています。
部署内の業務は目を酷使するアナログ作業が多いのでそれを少しでも改善しようとGoogleフォームのデータをGASを使って集計するプログラムを組んでみました。
どのような事をしたのかというと、商品の出荷をお願いしている各メーカーさまの長期休業期間（お盆や年末年始など）が商品の出荷日となってしまわないように事前設定するためのデータ作りです。
Googleフォームは使ったことあるけど『GAS』は初めて聞く単語で一体何者？どうやるの？難しいのかな？なんて考えながらも、とりあえずやってみようと思い取り組んでみました。
困っていること😵💫
現在自社のECサイトとモール型ECサイトのA店、B店、計3モールの運営していますが
出荷メーカーごとに休業期間が異なるため、各モールのシステム内での出荷不可日の設定がうまくできません。
なので３つのモールの受注データを集約しているシステムに連携しているスプレットシートに『ここに入っている日にちは出荷日にしたらダメっ！』という内容の設定をする必要があるのですが、これがほぼ手作業で時代遅れな感じなんです。
いつもする作業は...
- Googleフォームを使って各メーカー様へ回答をお願いするメールを送信
- 回答完了後Googleフォームの内容（下記リスト）から各メーカーの出荷不可期間を目視で確認
- 設定用のスプレットシートへメーカーごとの出荷不可日を入力
作業自体は単純ですが（約100社×休業日数）のデータ手入力、リストC列の各セルに日付が所狭しと入っているため大変見づらく時間もかかっています。
やりたいこと✊
各メーカーさまへのメール送信もGASのプログラムを使って自動化したい。
Googleフォームとその内容を表示したスプレットシートにChatGPTに教えてもらったGASのスクリプトを入れて出荷不可期間設定用の元データを簡単に作りたい。
使用したツール💻
必要なものを準備してChatGPTと共にGO！🚴♀️
＜準備するもの＞
- メール一斉送信用のリスト
- 出荷不可日回答用のGoogleフォーム
メール一斉送信用のリストより、各メーカーへメール一斉送信📨
『送信先一覧』のA列（タイトル）、B列（宛先）、C列（本文）の内容を取得し、それに基づいてメールを送信するGASのスクリプトをChatGPTに聞く。
無事、送信完了！
Googleフォームの回答内容を集計して出荷不可日設定用の元データを作成
Googleフォームの回答結果をスプレットシートで表示させ「集計」シートを追加する。
ChatGPTに出荷不可日設定用の元データにするためのGASのスクリプトを聞く。
1回目GO🚴♀️
// スプレッドシートを取得
// 元データのB列（メーカー名）とC列（出荷不可日）を取得
// データをグループ化
// 出荷不可日を「, 」で分割して追加
// 集計シートをクリア
// 集計結果を転記
あれっ？上手くいかない...
これだとメーカー名がA列のすべてのセルに転記されず空白部分があるし、C列の次回出荷日も日付が転記されるようにしなくては完成ではない。
再度、ChatGPTに既存のスクリプトを改良して空白欄へのメーカー名転記と次回出荷日も転記されるスクリプトを聞いた。
今度こそ成功させるぞ💪
2回目GO🚴♀️🚴♀️
// スプレッドシートを取得
// 元データのB列（メーカー名）とC列（出荷不可日）を取得
// データをグループ化
// 出荷不可日を「, 」で分割して追加
// 集計シートをクリア
// ヘッダーを設定
// 集計結果を転記
// A列の空白欄にメーカー名をコピー
// C列に最終日付 +1 を記入
// 同じメーカー名の全てのC列に同じ日付を記入
【ChatGPTが返してきたスクリプトはこれ！】
function aggregateAndFillManufacturers() {
// スプレッドシートを取得
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = spreadsheet.getSheetByName("フォームの回答 1"); // 元データのシート名
const targetSheet = spreadsheet.getSheetByName("集計"); // 集計先のシート名
// 元データのB列（メーカー名）とC列（出荷不可日）を取得
const sourceData = sourceSheet.getDataRange().getValues(); // シート全体のデータを取得
const headerRow = 1; // ヘッダー行の数（1行目はスキップ）
const manufacturerColumnIndex = 1; // B列（メーカー名）
const noShipDatesColumnIndex = 2; // C列（出荷不可日）
// データをグループ化
const groupedData = {};
for (let i = headerRow; i < sourceData.length; i++) {
const manufacturer = sourceData[i][manufacturerColumnIndex];
const noShipDates = sourceData[i][noShipDatesColumnIndex];
if (manufacturer && noShipDates) {
if (!groupedData[manufacturer]) {
groupedData[manufacturer] = [];
}
// 出荷不可日を「, 」で分割して追加
const dates = noShipDates.split(",").map(date => date.trim());
groupedData[manufacturer].push(...dates);
}
}
// 集計シートをクリア
targetSheet.clearContents();
// ヘッダーを設定
targetSheet.getRange(1, 1).setValue("メーカー名");
targetSheet.getRange(1, 2).setValue("出荷不可日");
targetSheet.getRange(1, 3).setValue("次回出荷日");
// 集計結果を転記
let currentRow = 2; // 転記開始行
for (const manufacturer in groupedData) {
targetSheet.getRange(currentRow, 1).setValue(manufacturer); // A列にメーカー名
const dates = groupedData[manufacturer];
dates.forEach((date, index) => {
targetSheet.getRange(currentRow + index, 2).setValue(date); // B列に日付
});
currentRow += dates.length; // 次のメーカーの行を計算
}
// A列の空白欄にメーカー名をコピー
const lastRow = targetSheet.getLastRow();
const aColumnValues = targetSheet.getRange(2, 1, lastRow - 1).getValues(); // A列データ取得
let currentManufacturer = "";
for (let i = 0; i < aColumnValues.length; i++) {
if (aColumnValues[i][0]) {
currentManufacturer = aColumnValues[i][0]; // メーカー名を更新
} else {
aColumnValues[i][0] = currentManufacturer; // 空欄を埋める
}
}
targetSheet.getRange(2, 1, lastRow - 1).setValues(aColumnValues); // 更新後のデータを書き戻す
// C列に最終日付 +1 を記入
const manufacturers = {};
for (let i = 2; i <= lastRow; i++) {
const manufacturer = targetSheet.getRange(i, 1).getValue(); // A列（メーカー名）
const date = new Date(targetSheet.getRange(i, 2).getValue()); // B列（日付）
if (!manufacturers[manufacturer]) {
manufacturers[manufacturer] = { lastDate: date, rows: [] };
}
if (date > manufacturers[manufacturer].lastDate) {
manufacturers[manufacturer].lastDate = date; // 最終日付を更新
}
manufacturers[manufacturer].rows.push(i); // 該当行を保存
}
for (const manufacturer in manufacturers) {
const nextDate = new Date(manufacturers[manufacturer].lastDate);
nextDate.setDate(nextDate.getDate() + 1); // 最終日付に1日を加算
// 同じメーカー名の全てのC列に同じ日付を記入
manufacturers[manufacturer].rows.forEach(row => {
targetSheet.getRange(row, 3).setValue(Utilities.formatDate(nextDate, Session.getScriptTimeZone(), "yyyy/MM/dd")); // C列に記入
});
}
Logger.log("集計、空白欄の埋め、最終日付+1の処理が完了しました");
}
できたぁ👏
感激😭😭😭
空白だった部分にメーカー名、次回出荷日も入りました。抜けていたヘッダーも！
でも一部の日付けがyyyy/mm/dd 形式で入らず、ChatGPTに聞いて修正を試みてみましたが直りませんでした。結局スプレットシート内【表示形式→数字→日付（yyyy/mm/dd）】で修正し解決となりました🆗
🎊作成したデータを設定用のスプレットシートに貼り付け出荷不可日の設定完了！
実際に使用している出荷不可日を設定するスプレットシートは、これ以外にも様々なプログラムへの設定用に使っているものなので他に影響を及ぼす危険があるかも知れないと判断し、今回のプログラムで作ったデータを直接反映させることはしませんでした。
作成したデータはコピペする形で使用します。
これは年に2～3回の作業にしか使わないものにはなりますが、この作業をする時期は、お歳暮、お中元など大型イベントの予約承り時期と重なり猫の手😺も借りたいくらいの繁忙期となります。
そんな中、できたデータを張り付けるだけになるので大幅な作業時間削減、空いた時間は顧客対応のフォローやこの時期に増える時間外労働の削減にも繋がるし、自分以外のスタッフでも簡単に設定できるようになります。
苦労したこと😢
各メーカーに回答してもらった出荷不可期間を別シート（集計）に設定用のデータと同じ形式で転記するまでは割とスムーズにできたけど、1つだけ各メーカーの出荷不可日（B列）の最終日にプラス1日した日付を次回出荷日（C列）に転記するところが上手くいきませんでした。
ChatGPTに聞く際、やりたいことを言葉でどう表現すればいいのか分からず何度も試みたけどやっぱりダメ。
この部分だけ関数を使おうと思い、ふさわしい関数をChatGPTに聞いてみたけど失敗に終わりました。
聞く相手がAIだったとしても自分の伝えたい事を分かりやすく伝える難しさを痛感しました。
最後に🐶
GAS＝難しそう、自分に使いこなせるだろうか？など悪い印象ばかりでしたが
あれこれ触っていくうちに、ChatGPTとGASを使えば今困っている面倒な手作業を改善できるツールが私でも作れるかも！面白そう！と楽しく課題に取り組むことができました。
内容はシンプルで簡単なものですが理想の形にできたので満足しています。
また私に不足している『自信』も少しついたので今はとても爽やかな気分です💕
前回同様に、困った時に手を差し伸べて下さった方々に感謝いたします。