gasでスプレッドシート内の表データを加工して貼り付けたい。

Question

前提・実現したいこと

gasでスプレッドシート内の表データを加工して貼り付けたい。

画像1

画像2

画像1のような請求書作成のためのデータを格納した表があり、
これらをgasで整理し、同じ顧客への請求をまとめたいと思います。

例えば画像1内の顧客名「いいい」は３行目「訪問料」と４行目「顧問料」が存在していますので
画像２の３行目のように顧問料列、訪問料列にそれぞれ入力しようとしています。

しかし、同じ顧客であれば全て1行にするのではなく事業所Aと事業所Bの請求は別の請求書を発行する
必要があるため、加工する際も分ける必要があります。
（→画像２でいう顧客名「あああ」のような感じです。）

このように事業所ごとに請求をまとめて別のスプレッドシートへ出力したいと考えています。

発生している問題

スプレッドシートで大量のデータを扱う場合、配列というものを使ったほうがよいとのことですが
配列の加工方法を探してみても「配列内のデータを削除」や「配列に値をを加える」といった
ものばかりで、なかなか自分の探しているものがみつかりません。

配列内で加工をしようとしているからダメなわけで
配列を２つ用意して、配列1に加工前のデータを格納して配列2に加工したものをfor文で
入れていけばいいのかな？なども考えていますが、何分最近触り始めたばかりですので
これが良い処理方法なのか悪い処理方法なのかも判断ができないのです。

皆様はどのように処理を行うのでしょうか？
ご教授いただければ幸いです。

現状できているところまでのソースコードもはりますので
「こんな処理は避けたほうがいい」というものがありましたら
お願いいたします

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
var date = new Date();
var testsheet = spreadsheet.getSheetByName("test");


function NarrowDown_CheckList() {

  var lastRow = sheet.getRange(2, 3).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();  //getRange([行番号], [列番号])
  var lastCol = sheet.getRange(2, 1).getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();    
  var month = date.getMonth() + 1;   //今月を取得
  var firstRow = 3;   //項目行を無視（データが格納されている最初の行を指定）
  
  
  
    const dataRange = sheet.getRange(firstRow, 1, lastRow, lastCol);
    const values = dataRange.getValues();
  
const result = values.filter(rowData => rowData[month+6] == "1");   //今月請求する項目のみを抽出（[month+6]が当月の列）
var Vertical = result.length; //配列タテ
var side = result[0].length; //配列ヨコ





testsheet.getRange(2,1,Vertical,side).setValues(result)　//テスト出力用
}

Comments

[Automatic9045]

配列がどのような概念かは理解されていますか？
2次元配列の使用経験はありますか？

[hamada1110]

配列に関してはだいたい理解できていると思っています。

私のイメージ的には

配列はcsvみたいなもの。1行のみのcsvが一次元配列、複数行のcsvが2次元配列で
csvで用意したデータを別のソフトが読み取れるように配列に入っている値は
好きなタイミングで好きな場所を読み取れるデータ郡　といったイメージを持っています。

しかし経験となると・・・
1週間ほど前にプログラミングを初めて触りだしたので
2次元配列含めて経験はありません。

よろしくお願いいたします

[Automatic9045]

なるほど、csvみたいなものですか。良い考え方だと思います。
回答を書くので少々お待ちを。

[sawa]

ピボットテーブル機能やQuery関数でも希望に近い表は出力できそうですが、GASでやりたいってことでしょうか？

[hamada1110]

＞sawa様

ありがとうございます。
「前提・実現したいこと」では簡単に整理しておりましたが実際の運用にあたっては
６法人７事業所で毎月800件ほど請求が発生し、かつ毎月全体の３％ほどの請求が前月の請求内容から変更されます。

過去の請求の確認が必要になった際に請求履歴などを確認できるよう、gasでトランザクションシートに蓄積・Query関数で取り出しを行う予定です。

よろしくお願いいたします。

Answer 1

const q303432 = () => {
    const sheet1 = "シート1";
    const sheet2 = "シート2";
    const groupingKey = ["担当事務所", "顧客コード"];
    const item = "請求内容";
    const monthStarts = 4;
    const targetFixHeader = ["請求年月日", "担当事務所", "顧客コード", "顧客名"];
    const sum = "売上計";
    const data = SpreadsheetApp.getActive().getSheetByName(sheet1).getDataRange().getValues();
    const source = new src(data,groupingKey);
    const codeIdx = source.getHeaderIdx(groupingKey[1]);
    const itemIdx = source.getHeaderIdx(item);
    const customers = codeToName(data.slice(1),codeIdx,codeIdx + 1);
    const tgt = new target(targetFixHeader,arrayUnique(arrayColumn(data.slice(1),itemIdx)),sum);
    source.getKeys().forEach(e => {
        const assets = source.getRowOf(e);
        for(let i = monthStarts; i < monthStarts + 12; i++) {
            const groupedValue = assets[0].getKeys();
            let row = [data[0][i],...groupedValue,customers.get(groupedValue[1]),...Array.from({length:tgt.getSumColumnsLength()},_ => 0)];
            for(let j = 0; j < assets.length; j++) {
                row[tgt.getHeaderIdxOf(assets[j].getValue()[itemIdx])] = Number((assets[j].getValue()[i]))*(assets[j].getValue()[itemIdx+1]);
            }
            tgt.setRow(row);
        }
    });
    const results = tgt.toArray();
    if(results.length < 1) return;
    const sheet = SpreadsheetApp.getActive().getSheetByName(sheet2);
    sheet.getRange(1,1,results.length,results[0].length).setValues(results);
    sheet.getRange(2,1,sheet.getLastRow(),sheet.getLastColumn()).sort([{column: 1, ascending: true}, {column: 2, ascending: true}]);
};

const arrayColumn = (array,idx) => array.map(e=>e[idx]);
const arrayUnique = (array) => [...(new Set(array))];
const codeToName = (data,keyIdx,valIdx) => {
    const keys = arrayColumn(data,keyIdx);
    const values = arrayColumn(data,valIdx);
    const uniqueKeys = arrayUnique(keys);
    return new Map(uniqueKeys.map(e=> [e, values[keys.indexOf(e)]]));
};


class src {
    constructor(array,groupingKey) {
        this.header = [...array[0]];
        this.groupingKeyMap = new Map(groupingKey.map(e => [e, this.header.indexOf(e)]).filter(e => e[1] !== -1));
        this.row = array.slice(1).map(e => new srcRow(e, this.groupingKeyMap));
        this.groupedKeys = [...(new Set(this.row.map(e => e.getKeyString()))).values()];
        this.groupedRows = new Map(this.groupedKeys.map(e => [e, this.row.filter(f => f.isBelongsTo(e))]));
    }
    getHeaderIdx(name) {
        return this.header.indexOf(name) || -1;
    }
    getKeys() {
        return [...this.groupedKeys];
    }
    getRowOf(key) {
        return this.groupedRows.get(key);
    }
}

class srcRow {
    constructor(array, keyMap) {
        this.keys = keyMap;
        this.value = [...array];
        this.groupBy = [...this.keys.values()].map(e=>this.value[e]);
        this.idString = this.groupBy.join("\t");
    }
    getKeys() {
        return [...this.groupBy];
    }
    getKeyString() {
        return this.idString;
    }
    isBelongsTo(needle) {
        return needle === this.idString;
    }
    getValue() {
        return [...this.value];
    }
}

class target {
    constructor(header,sumColumns,sum) {
        this.header = [...header,...sumColumns,sum];
        this.sumColumnsName = [...sumColumns];
        this.sumColumns = sumColumns.map(e=>this.header.indexOf(e));
        this.rows = [];
    }
    getSumColumnsLength() {
        return this.sumColumnsName.length;
    }
    getHeaderIdxOf(name) {
        return this.header.indexOf(name);
    }
    setRow(array) {
        const sum = this.sumColumns.map(e=>array[e]).reduce((a,c)=>a+c);
        if (sum === 0) return;
        this.rows.push([...array,sum]);
    }
    toArray() {
        return [this.header,...this.rows];
    }
}

シート1

flg	担当事務所	顧客コード	顧客名	1月	2月	3月	4月	5月	6月	7月	8月	9月	10月	11月	12月	請求内容	金額	メモ
	事業所A	CODE01	あ	1	1	1	1	1	1	1	1	1	1	1	1	顧問料	100
	事業所A	CODE01	あ	1	1	1	1	1	1	1	1	1	1	1	1	訪問料	150
	事業所A	CODE01	あ	1	1	1	1	1	1	1	1	1	1	1	1	その他	80	手数料
	事業所A	CODE02	い		1											顧問料	1000	毎年2月
	事業所A	CODE02	い	1	1	1	1	1	1	1	1	1	1	1	1	訪問料	500
	事業所A	CODE03	う	1	1	1	1	1	1	1	1	1	1	1	1	訪問料	1000
	事業所A	CODE03	う	1	1	1	1	1	1	1	1	1	1	1	1	その他	50
	事業所A	CODE04	え	1	1	1	1	1	1	1	1	1	1	1	1	その他	80	手数料
	事業所B	CODE02	い	1	1	1	1	1	1	1	1	1	1	1	1	顧問料	600
	事業所B	CODE02	い	1	1	1	1	1	1	1	1	1	1	1	1	その他	30
	事業所B	CODE03	う						1							顧問料	300	毎年6月
	事業所B	CODE03	う	1	1	1	1	1	1	1	1	1	1	1	1	訪問料	400
	事業所B	CODE04	え	1	1	1	1	1	1	1	1	1	1	1	1	顧問料	250
	事業所B	CODE04	え	1	1	1	1	1	1	1	1	1	1	1	1	訪問料	120
	事業所B	CODE04	え	1	1	1	1	1	1	1	1	1	1	1	1	その他	10

シート2

請求年月日	担当事務所	顧客コード	顧客名	顧問料	訪問料	その他	売上計
2020/01/01	事業所A	CODE01	あ	100	150	80	330
2020/01/01	事業所A	CODE02	い	0	500	0	500
2020/01/01	事業所A	CODE03	う	0	1000	50	1050
2020/01/01	事業所A	CODE04	え	0	0	80	80
2020/01/01	事業所B	CODE02	い	600	0	30	630
2020/01/01	事業所B	CODE03	う	0	400	0	400
2020/01/01	事業所B	CODE04	え	250	120	10	380
2020/02/01	事業所A	CODE01	あ	100	150	80	330
2020/02/01	事業所A	CODE02	い	1000	500	0	1500
2020/02/01	事業所A	CODE03	う	0	1000	50	1050
2020/02/01	事業所A	CODE04	え	0	0	80	80
2020/02/01	事業所B	CODE02	い	600	0	30	630
2020/02/01	事業所B	CODE03	う	0	400	0	400
2020/02/01	事業所B	CODE04	え	250	120	10	380
2020/03/01	事業所A	CODE01	あ	100	150	80	330
2020/03/01	事業所A	CODE02	い	0	500	0	500
2020/03/01	事業所A	CODE03	う	0	1000	50	1050
2020/03/01	事業所A	CODE04	え	0	0	80	80
2020/03/01	事業所B	CODE02	い	600	0	30	630
2020/03/01	事業所B	CODE03	う	0	400	0	400
2020/03/01	事業所B	CODE04	え	250	120	10	380
2020/04/01	事業所A	CODE01	あ	100	150	80	330
2020/04/01	事業所A	CODE02	い	0	500	0	500
2020/04/01	事業所A	CODE03	う	0	1000	50	1050
2020/04/01	事業所A	CODE04	え	0	0	80	80
2020/04/01	事業所B	CODE02	い	600	0	30	630
2020/04/01	事業所B	CODE03	う	0	400	0	400
2020/04/01	事業所B	CODE04	え	250	120	10	380
2020/05/01	事業所A	CODE01	あ	100	150	80	330
2020/05/01	事業所A	CODE02	い	0	500	0	500
2020/05/01	事業所A	CODE03	う	0	1000	50	1050
2020/05/01	事業所A	CODE04	え	0	0	80	80
2020/05/01	事業所B	CODE02	い	600	0	30	630
2020/05/01	事業所B	CODE03	う	0	400	0	400
2020/05/01	事業所B	CODE04	え	250	120	10	380
2020/06/01	事業所A	CODE01	あ	100	150	80	330
2020/06/01	事業所A	CODE02	い	0	500	0	500
2020/06/01	事業所A	CODE03	う	0	1000	50	1050
2020/06/01	事業所A	CODE04	え	0	0	80	80
2020/06/01	事業所B	CODE02	い	600	0	30	630
2020/06/01	事業所B	CODE03	う	300	400	0	700
2020/06/01	事業所B	CODE04	え	250	120	10	380

(出力はまだ続きます)

Comments

[papinianus]

この画像2の結果は請求書のフォーマットとしては普通には考えられない体裁で、これをさらに請求書にするため、別な表にすることが予想されます(もしこれが最終形態で、画像2の1行を請求書として送っているというのだとしたら、その体裁をまず見直したほうがいいと思います)。

・提案(下に行くほど技術寄りです)
- 上記の予想も含んでですが、本当にやりたいことやらないといけないことは画像2を作ることなのでしょうか。電子化、自動化は今あることをそのまま機械処理にすることじゃなくて、業務分析してやるべきことにフォーカスしないと、電子化、自動化の工数がかさみます。
- 顧問料の金額がそれぞれ異なることが疑問です。顧客コードもそうですが、一意のコードを振って名前は別管理にしないと、揺れが意図したものか誤記なのか(質問だから適当なのか)分かりません。
- 請求内容(摘要や科目という名前が適切です)がこの3つであることやこの表の体裁がどうなっているのかなど、仕様として確定していないことが多いと感じます。
- 画像1も画像2も会計データとして特殊なかたちなので、1を2にすることは回答の例のとおり原理的には可能です。ただ、画像1が本来の姿に列が変更された場合であっても、うまく画像2に似た姿にするというのはかなり難しいです。なので、仕様の確定が必要です。
- 配列の操作を探して出てこないのは、データ構造が適切でないからです。データ構造が独特で、やることを一般的な方法に抽象化できないので、探しようがないのです。もちろん細分化していけば個々のコードは既知のやりかたの組み合わせに過ぎませんが、1行ずつのレベルまで分解してやりかたを探して、さらにそれらを使って目的の姿に組み上げるのは一般にハードルが高い作業だと感じます。
- データ構造はロジックに影響します。整った姿をしていれば、処理もコードも簡単になります。コードの難度が高いのはデータがおかしな姿だからです。特注品、オーダーメイドがやりたければ相応に高いコストを支払う必要があります。
- 「配列を使う」ことが目的ではないはずなので、知見のある人に依頼して型を正しく使ったコーディングをしたほうがいいです。

--
これかいてて気付きましたが、当月を出すには、"1月" が実際はどのような値("1月"なのか 2020/01/01 なのか)でそれをどう書式設定しているかなどが必要です。
日付でソートしているので、できたやつをフィルタしてくださればいいんじゃないでしょうか。

[hamada1110]

＞papinianus　様

回答ありがとうございます。

仰るとおり、データとしてかなり歪な形をしているかと思います。
と、いうのも弊社は税理士、社労士、弁護士など複数の士業法人の集まりです。

「異なる士業同士ですので業務の専門性が180度異なるが、請求の発行部署として日々の業務の消化・請求内容の集計をしなければならない」という課題に直面しております。

税理士用業務ソフト、弁護士用業務ソフトなどそれぞれの士業用の管理ソフトは存在していますが、異なる士業を横断的に使える業務ソフトは存在していません。

今まではこれらを人力でEXCELとスプレッドシートで集計していましたが、限界が見え始めたので自力でなんとかしようとしたのが今回gasを触り始めた要因でした。

--

さて、コードを作成いただきありがとうございます。
こちらのコードと提案いただいた内容を元に弊社に合うように勉強しつつ自力でコードを完成させていきたいと思います。

提案いただいた内容である

「＞請求内容(摘要や科目という名前が適切です)がこの3つであることやこの表の～」
→実際には税理士は「税務書類作成報酬」など、弁護士は「法律相談顧問報酬」などといったように
それぞれの科目が設定されていますが、グループを横断的に見るために各法人の請求を性格（月々、年1など）で分類し集計しやすくしている。

「＞顧問料の金額がそれぞれ異なることが疑問です。～」
→（例えば税理士業務の場合は）年間の仕訳数に応じて顧問料が決定しているため、顧客とグループのどの法人と契約しているかにより顧問料が変動している。

などなどグループの内部処理的に集計しやすいようにデータの流れを統一化することが難しい状況がありまして、オーダーメイドなども考えましたが
一見士業の6法人と大きく見える弊グループですが実態は個人レベルの事務所の集まりでして資金的にも難しい状況です。

以上のことから時間がかかってでも自力でなんとかしないと！との状況でしたがコードをいただけたおかげで
かなり作業が進みそうです。

全体の流れも見えてきましたので引き続き自力でなんとかできないか奮闘していみます。
ありがとうございました。

[papinianus]

これがこの 3 つの科目に限定され(つまり勘定科目を集約した結果、必ず 3 つに絞り込まれ)、かつこの表がこの体裁であるのであれば、このコードでシート 1 がシート 2 になります。手直しは不要です。

> 事業所Aと事業所Bの請求は別の請求書を発行する必要があるため、加工する際も分ける必要があります。

このとおり質問では、請求書発行を前提としているように拝見しました。会計に長けておられるかたもいらっしゃるようなのでご承知かと存じますが、普通請求書は1枚の請求書にさらに細目が表としてつきますよね。
画像2 の ABCD 列は請求書に対して必ず 1 個となる項目である一方、EFG(H)は細目として表になる項目、つまり、1請求書に対して、n 個(1-3個)の項目だと考えられます。
一般的な請求書の帳票にしようとしたとき、画像2では一旦横1列にしたものをまた n 倍する作業が必要なので、とても扱いにくい形式だと思います。

ところが目的が
> 内部処理的に集計しやすいようにデータの流れを統一化
であれば話はかわってきます。画像2が最終的に欲しい形態なのでしょうから。そしてそれが得られるコードを提供して発言しています。

配列操作が知りたいのであって内容への提言は求めない、ということであれば、配列操作に帰着できるような純粋なコード上の問題に、問題を限定し分割していただければと思います。

Answer 2

処理の流れの方針のみ示そうと思います。

---

GASではスプレッドシート関連の関数を1つ書く度にサーバーと通信する仕様です。この通信がなかなかの遅さなので、基本的に出来るだけ通信する回数が少なくなるように設計します。

例を示しましょう。
1行目から100列目までについて、A列とB列の値を取得したい場合、以下のように書くと、通信する回数は200回になってしまいます。

// const sheet = …中略…;
for (let i = 1; i <= 100; i++) {
    const valueA = sheet.getRange(i, 1).getValue();
    const valueB = sheet.getRange(i, 2).getValue();
    Logger.log(valueA + ", " + valueB);
}

通信する回数を減らす為に良く使うのが、シート内の値をまるまる2次元配列に代入してしまう方法です。この方法を用いると以下のように書け、この場合通信する回数は1回のみです。

// const sheet = …中略…;
const values = sheet.getRange(1, 1, 100, 2);
values.forEach(row => Logger.log(row[0] + ", " + row[1]));

さて、これを踏まえて本題に入りましょう。

##1. シート全体を取得
先程の例に従い、まずはセオリー通り、シート全体の値を取得して2次元配列（名前はvaluesなどで良いでしょう）に代入しておきます。
内容を含む最後の行・列は、それぞれsheet.getLastRow()、sheet.getLastColumn()で取得できるので、これを用いれば値を取得するべき範囲を動的に指定することが可能です。

##2. 事業所ごとに分割
先程取得した表について、各行を事業者ごとに振り分けていきます。
事業所がAとBの2種類のみであればofficeAValues、officeBValuesなどと事業所数分の変数をつくってやれば大丈夫ですが、事業所が多い場合や今後事業拡大の可能性がある場合は、JSONを用いて1つの変数にまとめると良いかもしれません。
JSONの構造は以下のようにします。

{
    "事業所A": [
        […中略… "いいい", …中略… "顧問料", 1000, "毎年2月請求"],
        …中略…
    ],
    "事業所B": [
        …中略…
    ]
}

##3. 同一顧客名の行を統合しながら新しいシートに記入する
ここが最も実装の面倒な部分だと思います。
おおまかな流れのイメージは以下の通りです。

新しいシートに記入する内容を記憶する為の2次元配列resultを定義
事業所ごとに繰り返し {
    一番最後の行まで繰り返し {
        もしresultにまだ無い顧客名なら {
            顧客名が同じ行を全て探して、売上をまとめる
            resultの末尾に顧客情報を追加
        }
    }
}
resultの内容を新しいシートに転記

---

もし分からないことがあれば、コメント欄などで聞いて頂ければお答えします。

Comments

[hamada1110]

＞Automatic9045　様

回答ありがとうございます。
全体の流れがもやっとしておりましたが、処理の方法など全体を可視化していただき
全体像がなんとなくですが掴めて参りました。

ココ数日でもう少し詰めていきたいと思います。
ありがとうございます！