任意の表を集計するスクリプト

こんにちは！株式会社スマレジ、開発部のmasaです！

２月なのにめっちゃあったかくありませんか？汗

masaは実は先週から引越し作業をしているんですが、今週末は汗だくになってダンボールを運んでいました笑 気温のアップダウンが激しいと、体調を崩しやすくなるので、皆様もお気をつけくださいませ。

今日は、副業で作成したGASの一部を公開しようかと思います。

GASの依頼あるある「複数の表を一つにまとめたい」

飲食店や小売店であれば、売上日報などデイリーにつけている表などがあると思います。 スマレジ のエンジニアとしては、売上分析や締め機能のCSVダウンロードなどを使ってもらいたいですが、 オーナー様によっては、今までの帳票から経営状況を読み解くことと、弊社の機能を合わせて使いたいこともあるかと思います。

Chrome Book人気もあって、スプレッドシートを導入している小規模店舗も増えているようで、 必然的に「自分たちが毎日つけているシートを合算」を出したいニーズが発生します。

ここで、例えば家賃などの固定費に関する表であれば、常にフォーマットは同じなので、 関数を使えば集計は簡単ですが、変動費項目を集計する場合は項目数が表によって変わるため、 関数による集計が難しくなります。

集計しやすいテーブルとそうでないテーブル

行を継ぎ足しする・順番も前後させないようにするのであれば関数処理はできますが、スプレッドシートに慣れていない方にとっては、そういった操作は少しハードルが高いですし、おかしなデータを入れたときの復帰や検知も大変ですので、運用を変えるのが難しい場合もあると思います。

そこで、各表の見出しがまちまちでも集計できるGASを作成してみました。 依頼をいただいて作成したものなので、かなり限定的ですから、もし転用するのであれば、これをベースに改造してもらえますと嬉しいです。

集計GAS

仕様

• 集計対象の表は１つのスプレッドシート内にあり、シート毎に分かれている。
• 表の名前は「売上実績」で、表の一番左上にこれが入る。また、縦列・横列共に一番後ろが「総合計」になっている。
• シートの名前は「日報集計」
• 表の項目順は集計するシートの左側にあるものを基準とし、新たに出現したものを追加していく。（１シート目で「A商店・B商店」２シート目で「A商店・C商店」なら、集計テーブルは「A商店・B商店・C商店」となる）
• 表内は見出し以外は全て数値である。
• プログラムの実行は拡張メニューから実行する。

もし流用される場合、各キーワードは適宜変えて使ってください。 コメントがめっちゃ少ないですが、main関数とdocを見ていただければなんとなく処理の流れはわかるかと思います。

プログラム

function main() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const allSheets = ss.getSheets();
    let sheets = [];
    for (let i = 0; i < allSheets.length; i++) {
        if (allSheets[i].getName() === "日報集計") {
            continue;
        }
        sheets.push(allSheets[i]);
    }
    let workArray = [];

    for (let i = 0; i < sheets.length; i++) {
        workArray.push(getDailySheet(sheets[i]));
    }

    const paymentMethods = getPaymentMethods(workArray);
    const salesTypes = getSalesTypes(workArray);
    const workMap = getDailyMap(workArray);
    const resultMap = getTotal(workMap);
    const resultArray = getTotalArray(resultMap, paymentMethods, salesTypes);

    const totalSheet = ss.getSheetByName("日報集計");
    const printArea = getPrintSheetRange(totalSheet, paymentMethods, salesTypes);
    printArea.setValues(resultArray);
}

/**
 * 売上実績を取得[行][列]
 * @param sheet
 * @return {any[][]}
 */
function getDailySheet(sheet) {
    const cornerWord = "総合計";
    const tableTitle = "売上実績";
    const startArea = {row: -1, col: -1};
    const endArea = {row: -1, col: -1};
    const lastRow = sheet.getLastRow();
    const lastCol = sheet.getLastColumn();
    let values = sheet.getRange(1, 1, lastRow, lastCol).getValues();
    for (let i = 0; i < values.length; i++) {
        for (let j = 0; j < values[i].length; j++) {
            if(values[i][j] === cornerWord) {
                if (i > j) {
                    endArea.row = i;
                } else {
                    endArea.col = j;
                }
            }
            if (values[i][j] === tableTitle) {
                startArea.row = i + 1;
                startArea.col = j + 1;
            }
        }
    }

    return sheet.getRange(startArea.row, startArea.col, endArea.row, endArea.col).getValues();
}

/**
 * 売上実績を取得([シート番号][支払方法][売上区分])
 * @param workArray
 * @return res ([シート番号][支払方法][売上区分])
 */
function getDailyMap(workArray) {
    let res = [];
    for (let sheetIndex = 0; sheetIndex < workArray.length; sheetIndex++) {
        for (let i = 1; i < workArray[sheetIndex].length; i++) {
            for (let j = 1; j < workArray[sheetIndex][i].length; j++) {
                if (typeof res[sheetIndex] === "undefined") {
                    res[sheetIndex] = {};
                }
                if (typeof res[sheetIndex][workArray[sheetIndex][i][0]] === "undefined") {
                    res[sheetIndex][workArray[sheetIndex][i][0]] = {};
                }
                res[sheetIndex][workArray[sheetIndex][i][0]][workArray[sheetIndex][0][j]] = workArray[sheetIndex][i][j];
            }
        }
    }
    return res;
}

/**
 * 合計を取得
 * @param workMap
 * @return {[]}
 */
function getTotal(workMap) {
    const res = {};
    for (let sheetIndex = 0; sheetIndex < workMap.length; sheetIndex++) {
        for (const paymentMethod in workMap[sheetIndex]) {
            if (!workMap[sheetIndex].hasOwnProperty(paymentMethod)) {
                continue;
            }
            for (const salesType in workMap[sheetIndex][paymentMethod]) {
                if (!workMap[sheetIndex][paymentMethod].hasOwnProperty(salesType)) {
                    continue;
                }
                if (typeof res[paymentMethod] === "undefined") {
                    res[paymentMethod] = {};
                }
                if (workMap[sheetIndex][paymentMethod][salesType].length === 0) {
                    workMap[sheetIndex][paymentMethod][salesType] = 0;
                }
                if (!isNumber(workMap[sheetIndex][paymentMethod][salesType])) {
                    throw new Error("このスプレッドシートの左から" + sheetIndex + "シート目の" + paymentMethod + "/" + salesType + "の値が異常です。");
                }
                if (typeof res[paymentMethod][salesType] === "undefined") {
                    res[paymentMethod][salesType] = 0;
                }
                res[paymentMethod][salesType] += Number(workMap[sheetIndex][paymentMethod][salesType]);
            }
        }
    }
    return res;
}

/**
 * 集計表出力
 * @param totalSheet
 * @param paymentMethods
 * @param salesTypes
 * @return {GoogleAppsScript.Slides.TextRange | GoogleAppsScript.Spreadsheet.Range}
 */
function getPrintSheetRange(totalSheet, paymentMethods, salesTypes) {
    const tableTitle = "売上実績";
    const startArea = {row: -1, col: -1};
    const lastRow = totalSheet.getLastRow();
    const lastCol = totalSheet.getLastColumn();
    let values = totalSheet.getRange(1, 1, lastRow, lastCol).getValues();
    for (let i = 0; i < values.length; i++) {
        for (let j = 0; j < values[i].length; j++) {
            if (values[i][j] === tableTitle) {
                startArea.row = i + 1;
                startArea.col = j + 1;
            }
        }
    }
    return totalSheet.getRange(startArea.row, startArea.col, paymentMethods.length, salesTypes.length);
}

/**
 * 出力用配列に変換
 * @param resultMap
 * @param paymentMethods
 * @param salesTypes
 * @return {[]}
 */
function getTotalArray(resultMap, paymentMethods, salesTypes) {
    let res = [];
    let resultRow = {};
    salesTypes.unshift("売上実績");
    paymentMethods.unshift("売上実績");
    res[0] = salesTypes;
    for (let i = 1; i < paymentMethods.length; i++) {
        resultRow = resultMap[paymentMethods[i]];
        if (typeof res[i] === "undefined") {
            res[i] = [];
        }
        res[i][0] = paymentMethods[i];
        for (let j = 1; j < salesTypes.length; j++) {
            res[i][j] = resultRow[salesTypes[j]];
        }
    }
    return res;
}

/**
 * 支払方法を取得
 * @param workArray
 * @return {[]}
 */
function getPaymentMethods(workArray) {
    const resWork = [];
    for (let sheetIndex = 0; sheetIndex < workArray.length; sheetIndex++) {
        for (let i = 1; i < workArray[sheetIndex].length; i++) {
            if (typeof workArray[sheetIndex][i][0] === "undefined") {
                continue;
            }
            if (workArray[sheetIndex][i][0].length === 0) {
                continue;
            }
            resWork.push(workArray[sheetIndex][i][0]);
        }
    }
    const res = Array.from(new Set(resWork));
    for (let i = 0; i < res.length; i++) {
        if (res[i] === "総合計") {
            res.splice(i, 1);
            res.push("総合計");
        }
    }
    return Array.from(new Set(res));
}

/**
 * 販売区分を取得
 * @param workArray
 * @return {[]}
 */
function getSalesTypes(workArray) {
    const resWork = [];
    for (let sheetIndex = 0; sheetIndex < workArray.length; sheetIndex++) {
        for (let i = 1; i < workArray[sheetIndex].length; i++) {
            if (typeof workArray[sheetIndex][0][i] === "undefined") {
                continue;
            }
            if (workArray[sheetIndex][0][i].length === 0) {
                continue;
            }
            resWork.push(workArray[sheetIndex][0][i]);
        }
    }
    const res = Array.from(new Set(resWork));
    for (let i = 0; i < res.length; i++) {
        if (res[i] === "総合計") {
            res.splice(i, 1);
            res.push("総合計");
        }
    }
    return Array.from(new Set(res));
}

/**
 * 数値チェック関数
 * 入力値が数値 (符号あり小数 (- のみ許容)) であることをチェックする
 * [引数]   numVal: 入力値
 * [返却値] true:  数値
 *          false: 数値以外
 */
function isNumber(numVal){
    // チェック条件パターン
    var pattern = /^[-]?([1-9]\d*|0)(\.\d+)?$/;
    // 数値チェック
    return pattern.test(numVal);
}

/**
 * ファイルを開いたときのイベントハンドラ
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();           // Uiクラスを取得する
  var menu = ui.createMenu('○○店業務');  // Uiクラスからメニューを作成する
  menu.addItem('売上実績集計', 'main');   // メニューにアイテムを追加する
  menu.addToUi();                            // メニューをUiクラスに追加する
}