Google Apps Scriptとスプレッドシートで作る予実管理システム
今回は 【基本情報管理編】 になります。
このファイルには
- スタッフ概要（氏名、報酬単価、振り込み口座情報など）
- 退職者情報
- 施設、店舗情報
- 関連フォルダ、ファイルへのリンク情報
が掲載されています。
クライアントには、スタッフや施設・店舗の増減があればこちらのファイルに加筆、修正いただくだけで他の実績管理や明細管理のプログラムに反映されるよう対応しました。
関連するファイル、フォルダについては経時的に増えていくことが予測され、特に実績の管理ファイルを月毎に作成しようとすると毎月リンクが増えていくことになるためこの処理を自動化しました。
フォルダ情報、その中のファイル情報を再帰的に処理
基本情報管理.gs
function getAllFoldersAndFiles() {
// 対象のフォルダのIDを指定します
var folderId = "フォルダID";
// フォルダとファイルを格納するための配列を初期化します
var foldersAndFiles = [];
// フォルダを再帰的に取得する関数を呼び出します
getFoldersAndFiles(folderId, foldersAndFiles);
// スプレッドシートに結果を一括で書き込むための二次元配列を作成します
var data = [];
foldersAndFiles.forEach(function(item) {
data.push([item.name, item.type, item.url, item.id]);
});
// スプレッドシートにデータを一括で書き込みます
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.getRange(1, 1, data.length, 4).setValues(data);
}
// フォルダを再帰的に取得する関数
function getFoldersAndFiles(folderId, array) {
// 指定されたフォルダの中身を取得します
var folder = DriveApp.getFolderById(folderId);
var contents = folder.getFiles();
while (contents.hasNext()) {
var file = contents.next();
array.push({
name: file.getName(),
type: "ファイル",
url: file.getUrl(),
id: file.getId()
});
}
var folders = folder.getFolders();
while (folders.hasNext()) {
var subFolder = folders.next();
// フォルダを配列に追加します
array.push({
name: subFolder.getName(),
type: "フォルダ",
url: subFolder.getUrl(),
id: subFolder.getId()
});
// 再帰的にサブフォルダを取得します
getFoldersAndFiles(subFolder.getId(), array);
}
}
function folder_file_link(){
var root_id = ROOT_ID;
var root_file = SpreadsheetApp.openById(root_id);
var root_sheet_name = FOLDER_FILE_SHEET;
var root_sheet = root_file.getSheetByName(root_sheet_name);
/** データの重複を避けるためヘッダー行を除いた3行目以降のセルデータを削除して初期化する部分 **/
var last_row = root_sheet.getRange(1,2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
// 2行目までは削除してはいけないので、最終行からの差分を削除する
var diff = last_row - 2; //2行目までデータを残しておくので2を引く
if(diff > 1){
root_sheet.getRange(3,2,diff,2).clearContent();
}
var root_folder_id = "ROOT_FOLDER_ID";
var client_root_folder = DriveApp.getFolderById(root_folder_id);
var child_folders = client_root_folder.getFolders();
var folder_info = [];
while(child_folders.hasNext()){
var folder = child_folders.next();
// console.log(folder.getName());
// console.log(folder.getUrl());
folder_info.push([folder.getName(),folder.getUrl()]);
}
// 取得した二次元配列を3行目以降に貼り付ける
for(i=0; i<folder_info.length; i++){
root_sheet.getRange(i+3,2,1,2).setValues([folder_info[i]]);
}
SpreadsheetApp.flush();
// 現状のフォルダ量
var cont_length = root_sheet.getRange(1,3).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
for(i=3; i<cont_length; i++){
var child_folder_id = root_sheet.getRange(i,4).getValue();
var grand_child_folder_lists = [];
var grand_child_folder = DriveApp.getFolderById(child_folder_id);
var gcf = grand_child_folder.getFolders();
while(gcf.hasNext()){
var gc_folder = gcf.next();
grand_child_folder_lists.push([gc_folder.getName(),gc_folder.getUrl()]);
}
for(j=0; j<grand_child_folder_lists.length; j++){
root_sheet.getRange(j+cont_length+1,2,1,2).setValues([grand_child_folder_lists[j]]);
}
}
SpreadsheetApp.flush();
// ファイル情報の入力
// データが4行目以降にあれば削除
// そのために何行目までデータが入っているか確認する
var file_last_row = root_sheet.getRange(1,6).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
// 3行目までは削除してはいけないので、最終行からの差分を削除する
var diff = file_last_row - 3; //3行目までデータを残しておくので2を引く
// console.log("差分は" + diff);
if(diff <= 1){
root_sheet.getRange(4,5,file_last_row,2).clearContent();
}else if(diff > 1){
root_sheet.getRange(4,5,diff,2).clearContent();
}
// ドライブのURL／ID情報から含まれているファイルの名前をIDを取得する
for(p=0; p<last_row; p++){
var parent_id = root_sheet.getRange(p+3,4).getValue();
console.log("親フォルダのID" + parent_id);
var file_info = [];
try{
var conc_folder = DriveApp.getFolderById(parent_id);
var files = conc_folder.getFiles();
while(files.hasNext()){
var file = files.next();
file_info.push([file.getName(),file.getUrl()]);
}
// ファイル情報入力のたびに最終行は更新されていく
var col_e_last = root_sheet.getRange(1,5).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
for(a=0; a<file_info.length; a++){
root_sheet.getRange(col_e_last+1+a,5,1,2).setValues([file_info[a]]);
console.log("入力したデータ：" + file_info[a]);
}
}catch(e){
console.log("エラーメッセージ:" + e.message);
}
}
}
function fileCopy(){
/** 実績管理ファイル原本をコピーして当該月の名称に置き換えるプログラム */
// 実績管理『ファイル』のIDを取得する
var baseFILE_id = BASE_FILE_ID;
var baseFILE_sheet_name = FOLDER_FILE_LINK_SHEET;
var baseFILE = SpreadsheetApp.openById(baseFILE_id);
var linkSheet = baseFILE.getSheetByName(baseFILE_sheet_name);
var len = linkSheet.getRange(1,5).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
var arr = [];
for(i=1; i<=len; i++){ //E列に記載されている行数分ループ
var name = linkSheet.getRange(i,5).getDisplayValue();
arr.push(name);
}
var file_key = "実績管理ファイル_原本";
var reserve_file_row = arr.indexOf(file_key) + 1;
var reserveFILE_id = linkSheet.getRange(reserve_file_row,7).getValue();
var file = DriveApp.getFileById(reserveFILE_id); //🔥ファイルコピー時に使用
// 実績管理『フォルダ』のIDを取得する
//管理基本ファイル＞フォルダ／ファイルリンクから該当のフォルダ名を検索してセル番地を見つける
// 実績管理フォルダの名称からフォルダIDを取得する
var key = "実績管理";
var len = linkSheet.getRange(1,2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
var folder_arr = [];
for(i=1; i<=len; i++){ //B列に記載されている行数分ループ
var name = linkSheet.getRange(i,2).getDisplayValue();
folder_arr.push(name);
}
var reserv_folder_row = folder_arr.indexOf(key) + 1;
var reserveFolder_id = linkSheet.getRange(reserv_folder_row,4).getValue();
var folder = DriveApp.getFolderById(reserveFolder_id); //🔥ファイルコピー時に使用
// コピーしたファイルの名前を変更する
var d = new Date();
var year = d.getFullYear();
var next_month = d.getMonth() ;//+ 1;
if(next_month == 0){
var year = year - 1;
var next_month = 12
} else if(next_month == -1){
var year = year - 1;
var next_month = 11
}
var copied_file_name = "実績管理_" + year + "年" + next_month + "月";
if(arr.indexOf(copied_file_name == -1)){
try{
file.makeCopy(copied_file_name, folder); //『管理フォルダ』に予約管理ファイルのコピーを作成し、名称を変更する処理
}catch(e){
console.log(e.message);
}
}
}
現状の運用として必ず利用しているのではないけれど、今後の運用でもしかしたらまた必要になるかもと思いなかなか消せずにいるプログラムでした。。。