このファイル全部開いて確認するのか…
皆さま毎日お疲れ様です！
最近ME:Iというアイドル推しているアラサー会社員です。
8月に新曲の発売が決まり、推しの供給が増えているためテンションが上がっている今日この頃です。
さて本題に入りますが、今回はプログラミング初心者の私が、
各店舗分ファイルを開いて閉じてを繰り返すのはもう嫌だ！！と思ったため
chatGPTに聞きながら完了報告を簡単集約にチャレンジした模様をお伝えしたいと思います。
before
after
▼前回の記事はこちら！
【初心者のLINEbot作成チャレンジ】
なぜチャレンジしたのか
皆さまは学校や会社から完了報告を求められる機会はありませんか？
2、3か月に1回ぐらいメールやGoogleスプレッドシート等で求められることもあると思います！
今回は自部署で実施している端末機器のアップデート完了報告の集約について簡単にできないか考えてみました。
報告集約の一番の問題は、なんといっても集約するのに時間がかかる！！
投稿されたExcelシートを〇〇〇店舗分開いて、コピーして、貼り付けて。これを✖日まで繰り返すだと…⁉ 私の会社では、完了報告がExcelで送られてきて、さらにそれをまとめるExcelに転記する作業が必要になります。しかもその作業量は100店舗分！！これだとコピーミスが起きそうだし、どの店舗がいつ投稿したのかもわからないし、内容を自動的に貼り付けてくれればいいのにな～ということで作ってみました。
使用したツール
chatGPTの使用
今回は、なんとChatGPTにGASを書いてもらいます。
GASは何か簡単にいうと、Googleのさまざまな業務プロセスを自動化するためのツールです。Excelでのマクロに近いものです。
ChatGPTがGASを書いてくれるなんて、知りませんでした。びっくりです。
もちろん、理想に忠実にGASを書こうと思うのであれば自分でコード入力できる方が望ましいと思います。
ですが、仕組みがよく分からなくても 「形にすることができる」 というのは、初心者にとって、とってもありがたいことです。
またChatGPTの良いところは、追加で要望を書き込むと、その機能を追加して新たにコードを書き出してくれるところです。使えるものはどんどん使っていきましょう。
それでは作成手順を紹介していきます。
作成手順
ChatGPTに自分が作りたいアプリの概要を書き込む
ChatGPTを開き、以下のように入力しました。
どう入力したらいいかわからずざっくりとした内容を聞いていました。
ChatGPTが書き出したコード
print(function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Aggregate Reports', 'aggregateReports')
.addToUi();
}
function aggregateReports() {
var folderId = 'YOUR_FOLDER_ID'; // 完了報告フォルダのIDを指定
var mainSheetId = 'YOUR_MAIN_SHEET_ID'; // MainSheetシートが含まれるスプレッドシートのIDを指定
var mainSheetName = 'MainSheet'; // MainSheetシートの名前
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var mainSpreadsheet = SpreadsheetApp.openById(mainSheetId);
var mainSheet = mainSpreadsheet.getSheetByName(mainSheetName);
// MainSheetシートをクリア
mainSheet.clear();
mainSheet.appendRow(['店舗名', '日付', 'その他の情報']); // 必要に応じてヘッダーを設定
while (files.hasNext()) {
var file = files.next();
if (file.getMimeType() === MimeType.GOOGLE_SHEETS) {
var spreadsheet = SpreadsheetApp.open(file);
var sheets = spreadsheet.getSheets();
sheets.forEach(function(sheet) {
var sheetName = sheet.getName();
var data = sheet.getDataRange().getValues();
// データをMainSheetに追加
for (var i = 1; i < data.length; i++) { // ヘッダーを除外
mainSheet.appendRow([sheetName, data[i][0], data[i][1]]);
}
});
}
}
}
)
ChatGPTが書き出したコードにより書き上げたシートがこちらです。
店舗名・日付・その他情報は書き出されていますが不足があります。
〇店：完了報告シート内に記載された店舗名・日付・機種管理番号・完了報告・備考を記載するようにしたいと思います。
ChatGPTが書き出したコード
print(function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Aggregate Reports', 'aggregateReports')
.addToUi();
}
function aggregateReports() {
var folderId = 'YOUR_FOLDER_ID'; // 完了報告フォルダのIDを指定
var mainSheetId = 'YOUR_MAIN_SHEET_ID'; // MainSheetシートが含まれるスプレッドシートのIDを指定
var mainSheetName = 'MainSheet'; // MainSheetシートの名前
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var mainSpreadsheet = SpreadsheetApp.openById(mainSheetId);
var mainSheet = mainSpreadsheet.getSheetByName(mainSheetName);
// MainSheetシートをクリア
mainSheet.clear();
mainSheet.appendRow(['店舗名', '日付', '機種管理番号', '完了報告', '備考']); // ヘッダーを設定
while (files.hasNext()) {
var file = files.next();
if (file.getMimeType() === MimeType.GOOGLE_SHEETS) {
var spreadsheet = SpreadsheetApp.open(file);
var sheets = spreadsheet.getSheets();
sheets.forEach(function(sheet) {
var sheetName = sheet.getName();
var data = sheet.getDataRange().getValues();
// ヘッダーを除いたデータをMainSheetに追加
for (var i = 1; i < data.length; i++) { // ヘッダーを除外
var row = data[i];
var storeName = sheetName.split('_')[0]; // 店舗名をシート名から取得
var date = row[0]; // 日付
var modelNumber = row[1]; // 機種管理番号
var completionReport = row[2]; // 完了報告
var notes = row[3]; // 備考
mainSheet.appendRow([storeName, date, modelNumber, completionReport, notes]);
}
});
}
}
}
)
ChatGPTが書き出したコードにより書き上げたシートがこちらです。
機種管理番号・完了報告・備考は転記できています。
しかし、店舗名がすべてA店かつ日付に店舗名が記載されています。
これを修正するために再びchatGPTに聞いてみました。
ChatGPTが書き出したコード
print(function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Aggregate Reports', 'aggregateReports')
.addToUi();
}
function aggregateReports() {
var folderId = 'YOUR_FOLDER_ID'; // 完了報告フォルダのIDを指定
var mainSheetId = 'YOUR_MAIN_SHEET_ID'; // MainSheetシートが含まれるスプレッドシートのIDを指定
var mainSheetName = 'MainSheet'; // MainSheetシートの名前
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var mainSpreadsheet = SpreadsheetApp.openById(mainSheetId);
var mainSheet = mainSpreadsheet.getSheetByName(mainSheetName);
// MainSheetシートをクリア
mainSheet.clear();
mainSheet.appendRow(['店舗名', '日付', '機種管理番号', '完了報告', '備考']); // ヘッダーを設定
while (files.hasNext()) {
var file = files.next();
if (file.getMimeType() === MimeType.GOOGLE_SHEETS) {
var spreadsheet = SpreadsheetApp.open(file);
var sheets = spreadsheet.getSheets();
sheets.forEach(function(sheet) {
var data = sheet.getDataRange().getValues();
// ヘッダーを除いたデータをMainSheetに追加
for (var i = 1; i < data.length; i++) { // ヘッダーを除外
var row = data[i];
var storeName = sheet.getName().split('_')[0]; // 店舗名をシート名から取得
var date = row[0]; // 日付
var modelNumber = row[1]; // 機種管理番号
var completionReport = row[2]; // 完了報告
var notes = row[3]; // 備考
mainSheet.appendRow([storeName, date, modelNumber, completionReport, notes]);
}
});
}
}
}
)
ChatGPTが書き出したコードにより書き上げたシートがこちらです。
あれ？何も変わっていない(´;ω;｀)
こうなったらB列を店舗名にして、A列を日付にすればいいのでは⁉と思い、再び聞いてみます。
ChatGPTが書き出したコード
print(function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Aggregate Reports', 'aggregateReports')
.addToUi();
}
function aggregateReports() {
var folderId = '1K8VdCe-Jr1FtbcXe20Il8o-ZItiWNkZU'; // 完了報告フォルダのIDを指定
var mainSheetId = '1z1P6PKndTD95nI_IRlWEj2w8qTkrqgbFbVJ9pWiTbNc'; // MainSheetシートが含まれるスプレッドシートのIDを指定
var mainSheetName = 'MainSheet'; // MainSheetシートの名前
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var mainSpreadsheet = SpreadsheetApp.openById(mainSheetId);
var mainSheet = mainSpreadsheet.getSheetByName(mainSheetName);
// MainSheetシートをクリア
mainSheet.clear();
mainSheet.appendRow(['日付', '店舗名', '機種管理番号', '完了報告', '備考']); // ヘッダーを設定
while (files.hasNext()) {
var file = files.next();
if (file.getMimeType() === MimeType.GOOGLE_SHEETS) {
var spreadsheet = SpreadsheetApp.open(file);
var sheets = spreadsheet.getSheets();
sheets.forEach(function(sheet) {
var data = sheet.getDataRange().getValues();
// ヘッダーを除いたデータをMainSheetに追加
for (var i = 1; i < data.length; i++) { // ヘッダーを除外
var row = data[i];
var storeName = sheet.getName().split('_')[0]; // 店舗名をシート名から取得
var date = row[0]; // 日付
var modelNumber = row[1]; // 機種管理番号
var completionReport = row[2]; // 完了報告
var notes = row[3]; // 備考
// 日付、店舗名、機種管理番号、完了報告、備考を指定された順序で記入
mainSheet.appendRow([date, storeName, modelNumber, completionReport, notes]);
}
});
}
}
}
)
A列とB列が入れ替わっただけでした(´;ω;｀)
死にたいという気持ちはありませんが(ただの打ち間違い)、こちらも必死ですので再び聞きます。
ChatGPTが書き出したコード
print(function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Aggregate Reports', 'aggregateReports')
.addToUi();
}
function aggregateReports() {
var folderId = '1K8VdCe-Jr1FtbcXe20Il8o-ZItiWNkZU'; // 完了報告フォルダのIDを指定
var mainSheetId = '1z1P6PKndTD95nI_IRlWEj2w8qTkrqgbFbVJ9pWiTbNc'; // MainSheetシートが含まれるスプレッドシートのIDを指定
var mainSheetName = 'MainSheet'; // MainSheetシートの名前
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var mainSpreadsheet = SpreadsheetApp.openById(mainSheetId);
var mainSheet = mainSpreadsheet.getSheetByName(mainSheetName);
// MainSheetシートをクリア
mainSheet.clear();
mainSheet.appendRow(['店舗名', '投稿日', '機種管理番号', '完了報告', '備考']); // ヘッダーを設定
while (files.hasNext()) {
var file = files.next();
if (file.getMimeType() === MimeType.GOOGLE_SHEETS) {
var spreadsheet = SpreadsheetApp.open(file);
var sheets = spreadsheet.getSheets();
sheets.forEach(function(sheet) {
var data = sheet.getDataRange().getValues();
// ヘッダーを除いたデータをMainSheetに追加
for (var i = 1; i < data.length; i++) { // ヘッダーを除外
var row = data[i];
var storeName = sheet.getName().split('_')[0]; // 店舗名をシート名から取得
var postDate = new Date(row[0]); // 投稿日の日付をDateオブジェクトとして取得
var modelNumber = row[1]; // 機種管理番号
var completionReport = row[2]; // 完了報告
var notes = row[3]; // 備考
// 店舗名、投稿日の日付、機種管理番号、完了報告、備考を指定された順序で記入
mainSheet.appendRow([storeName, formatDate(postDate), modelNumber, completionReport, notes]);
}
});
}
}
}
// 日付をフォーマットするヘルパー関数
function formatDate(date) {
if (Object.prototype.toString.call(date) === '[object Date]') {
// 日付オブジェクトが有効な場合
return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM/dd');
}
return date; // 日付オブジェクトでない場合はそのまま返す
}
)
近づいてきていますが、店舗名がA店のみ。投稿日もうまく取れていません。
ChatGPTが書き出したコード
print(function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Aggregate Reports', 'aggregateReports')
.addToUi();
}
function aggregateReports() {
var folderId = '1K8VdCe-Jr1FtbcXe20Il8o-ZItiWNkZU'; // 完了報告フォルダのIDを指定
var mainSheetId = '1z1P6PKndTD95nI_IRlWEj2w8qTkrqgbFbVJ9pWiTbNc'; // MainSheetシートが含まれるスプレッドシートのIDを指定
var mainSheetName = 'MainSheet'; // MainSheetシートの名前
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var mainSpreadsheet = SpreadsheetApp.openById(mainSheetId);
var mainSheet = mainSpreadsheet.getSheetByName(mainSheetName);
// MainSheetシートをクリア
mainSheet.clear();
mainSheet.appendRow(['店舗名', '投稿日', '機種管理番号', '完了報告', '備考']); // ヘッダーを設定
while (files.hasNext()) {
var file = files.next();
if (file.getMimeType() === MimeType.GOOGLE_SHEETS) {
var spreadsheet = SpreadsheetApp.open(file);
var sheets = spreadsheet.getSheets();
sheets.forEach(function(sheet) {
var data = sheet.getDataRange().getValues();
// ヘッダーを除いたデータをMainSheetに追加
for (var i = 1; i < data.length; i++) { // ヘッダーを除外
var row = data[i];
var storeName = sheet.getName().split('_')[0]; // 店舗名をシート名から取得
var postDate = new Date(row[0]); // 投稿日の日付をDateオブジェクトとして取得
var modelNumber = row[1]; // 機種管理番号
var completionReport = row[2]; // 完了報告
var notes = row[3]; // 備考
// 店舗名、投稿日（月日）、機種管理番号、完了報告、備考を指定された順序で記入
mainSheet.appendRow([storeName, formatMonthDay(postDate), modelNumber, completionReport, notes]);
}
});
}
}
}
// 月日だけをフォーマットするヘルパー関数
function formatMonthDay(date) {
if (Object.prototype.toString.call(date) === '[object Date]') {
// 日付オブジェクトが有効な場合
return Utilities.formatDate(date, Session.getScriptTimeZone(), 'MM/dd');
}
return date; // 日付オブジェクトでない場合はそのまま返す
}
)
投稿日は日付のみになりましたが、正しい投稿日・店名は取れていません。
ChatGPTが書き出したコード
print(function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Aggregate Reports', 'aggregateReports')
.addToUi();
}
function aggregateReports() {
var folderId = '1K8VdCe-Jr1FtbcXe20Il8o-ZItiWNkZU'; // 完了報告フォルダのIDを指定
var mainSheetId = '1z1P6PKndTD95nI_IRlWEj2w8qTkrqgbFbVJ9pWiTbNc'; // MainSheetシートが含まれるスプレッドシートのIDを指定
var mainSheetName = 'MainSheet'; // MainSheetシートの名前
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var mainSpreadsheet = SpreadsheetApp.openById(mainSheetId);
var mainSheet = mainSpreadsheet.getSheetByName(mainSheetName);
// MainSheetシートをクリア
mainSheet.clear();
mainSheet.appendRow(['店舗名', '投稿日', '機種管理番号', '完了報告', '備考']); // ヘッダーを設定
while (files.hasNext()) {
var file = files.next();
if (file.getMimeType() === MimeType.GOOGLE_SHEETS) {
var spreadsheet = SpreadsheetApp.open(file);
var sheets = spreadsheet.getSheets();
sheets.forEach(function(sheet) {
var data = sheet.getDataRange().getValues();
// ヘッダーを除いたデータをMainSheetに追加
for (var i = 1; i < data.length; i++) { // ヘッダーを除外
var row = data[i];
var storeName = sheet.getName().split('_')[0]; // 店舗名をシート名から取得
var postDate = new Date(row[0]); // 投稿日の日付をDateオブジェクトとして取得
var modelNumber = row[1]; // 機種管理番号
var completionReport = row[2]; // 完了報告
var notes = row[3]; // 備考
// 店舗名、投稿日の日付、機種管理番号、完了報告、備考を指定された順序で記入
mainSheet.appendRow([storeName, formatDate(postDate), modelNumber, completionReport, notes]);
}
});
}
}
}
// 日付をフォーマットするヘルパー関数
function formatDate(date) {
if (Object.prototype.toString.call(date) === '[object Date]') {
// 日付オブジェクトが有効な場合
return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM/dd');
}
return date; // 日付オブジェクトでない場合はそのまま返す
}
)
近づいてきていますが、店舗名がA店のみ。投稿日もうまく取れていません。
ChatGPTが書き出したコード
print(function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Aggregate Reports', 'aggregateReports')
.addToUi();
}
function aggregateReports() {
var folderId = '1K8VdCe-Jr1FtbcXe20Il8o-ZItiWNkZU'; // 完了報告フォルダのIDを指定
var mainSheetId = '1z1P6PKndTD95nI_IRlWEj2w8qTkrqgbFbVJ9pWiTbNc'; // MainSheetシートが含まれるスプレッドシートのIDを指定
var mainSheetName = 'MainSheet'; // MainSheetシートの名前
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var mainSpreadsheet = SpreadsheetApp.openById(mainSheetId);
var mainSheet = mainSpreadsheet.getSheetByName(mainSheetName);
// MainSheetシートをクリア
mainSheet.clear();
mainSheet.appendRow(['店舗名', '日付', '機種管理番号', '完了報告', '備考']); // ヘッダーを設定
while (files.hasNext()) {
var file = files.next();
if (file.getMimeType() === MimeType.GOOGLE_SHEETS) {
var spreadsheet = SpreadsheetApp.open(file);
var sheets = spreadsheet.getSheets();
var fileName = file.getName();
var storeName = getStoreName(fileName); // ファイル名から店舗名を取得
var fileDate = getFileDate(fileName); // ファイル名から日付を取得
sheets.forEach(function(sheet) {
var data = sheet.getDataRange().getValues();
// ヘッダーを除いたデータをMainSheetに追加
for (var i = 1; i < data.length; i++) { // ヘッダーを除外
var row = data[i];
var date = formatMonthDay(fileDate); // ファイル名から取得した日付をフォーマット
var modelNumber = row[1]; // 機種管理番号
var completionReport = row[2]; // 完了報告
var notes = row[3]; // 備考
// 店舗名、日付、機種管理番号、完了報告、備考を指定された順序で記入
mainSheet.appendRow([storeName, date, modelNumber, completionReport, notes]);
}
});
}
}
}
// ファイル名から店舗名を取得するヘルパー関数
function getStoreName(fileName) {
var parts = fileName.split('_');
return parts.length > 0 ? parts[0] : '';
}
// ファイル名から日付を取得するヘルパー関数
function getFileDate(fileName) {
var parts = fileName.split('_');
if (parts.length > 1) {
var dateStr = parts[1];
return new Date(dateStr);
}
return null;
}
// 月日だけをフォーマットするヘルパー関数
function formatMonthDay(date) {
if (Object.prototype.toString.call(date) === '[object Date]') {
// 日付オブジェクトが有効な場合
return Utilities.formatDate(date, Session.getScriptTimeZone(), 'MM/dd');
}
return date; // 日付オブジェクトでない場合はそのまま返す
}
)
ようやく無事に転記できました！！
トリガーを設定する
毎日手動で稼働するのも手間なので、トリガーを設定します。
時間稼働型にし、1日1回自動で更新されるようにして設定完了です。
やってみてと反省
今回、ChatGPTを使うことで、制作に費やす時間を大幅に減らすことができました。
これまで約3時間ほどかかっていた集約の作業が、約1分程度になります。
これが年4回程あるので、年間にすると約12時間。半日はすごい業務改善になる思いました。
メンバーに使用感を見てもらうと、時短になるのでありがたい・コピペミスやコピー漏れがなくなりそうという反面、店舗がファイル名の入力を間違えたら作動しないのか・投稿者のITリテラシーによっては複雑なリカバリ―が発生しそうとの意見をいただきました。
今後は今までよりも短い時間でできるようになるので、問合せ対応や端末の運用管理が手厚くできるようになるかもしれません。
作ってみての反省としては、chatGPTへの質問力が必要だと思いました。「ここが間違っているから直してほしい」ではなく、「A列にはファイル名のここを記入、B列には投稿日を記入」のような具体的な質問をしないとうまく反映されませんでした。
結果として、自分で一人ではできなかったGASコードの作成という新しいことにチャレンジを成功で終えることができ楽しかったです。
少し長くなってしまいましたが今回は以上となります。
最後までお付き合いいただきありがとうございました。