このファイル全部開いて確認するのか…

皆さま毎日お疲れ様です！
最近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]);
                }
            });
        }
    }
}

)