GoogleAppsScript書き始めて二日目。

言語仕様はそのままJavaScriptらしいがC++しかまともにやってないのでなんもわからん。

ただまあ、for文の書き方はC++とほぼ同じだしvar let constの仕様さえ覚えればC++のつもりで書けるみたいなのでなんもわからんが何故かマクロが動いたのでよし。

理想的なデータ構造

個別ID	名前	項目1	項目2	項目n...
1	りんご	バラ科	しゃりしゃり	etc...
2	ごりら	ヒト科	ごりらごりら	etc...
...	...	...	...	...

こういうデータだと個別IDで検索をかけるだけでほしいデータをすぐに取り出せて良い。

今回整形するデータ

個別ID	項目
1	りんご	うまい
	バラ科
	しゃりしゃり
	赤い
	apple
2	ごりら
	ごりらごりら
	うほうほ
	森の賢者
3	ピカソ
	パブロ
	ディエゴ
	ホセ
	...

タイトルの通り、縦と横に関連データが広がっていてとても使いにくい。

そもそもこんなデータを作らないでほしい。

手作業で解決するにはコピーして転置貼り付けして……を繰り返すしか無いが、

今回はマクロを組んで解決した。3000行ぐらいあるデータを手作業で整形できるわけがなかった。

1:個別IDが書き込まれている行番号の配列を作る

GAS(GoogleAppsScript)にはスプレッドシート操作に便利なAPIが用意されているのだが、

いちいちそれを呼び出していると実行速度が遅くなりあまり良くない。

そのため必要なデータをシート側の関数を用いて作っておく。

今回のデータはA列に唯一無二のIDが書き込まれており、

あるIDをもつセルの行番号から次のIDを持つセルの行番号-1までの範囲をコピーして転置したい、ということにする。

空いている列に=IF( ISBLANK(A1), ,ROW( ) )と入れてオートフィルで表の下まで入力

するとIDがある行番号が返されるので、フィルタを作成して空白を除外表示する

f:id:amvirosa:20220107184838p:plain

f:id:amvirosa:20220107185007p:plain

このままコピーして別シートに値のみ貼り付けをすると行番号配列が出来上がる。後でGAS側で読み込むのでシート名もわかりやすくリネームしておく

ついでに結果を書き込む新しいシートも用意しておくと失敗したときに大変なことにならなくて良い(3敗)

2:GoogleAppsScriptで書く

行番号配列row_indexs[i]とrow_index[i+1]の要素が元データでコピーしたい範囲をだいたい示している。

function trimDataToRow(){
 const read_file = SpreadsheetApp.openById("スプレッドシート固有ID");
 const row_indexs = read_file.getSheetByName("行番号配列があるシート名").getRange(/*行番号配列の範囲*/).getValues();
 const read_datas =read_file.getSheetByName("クソデータがあるシート名").getRange(/*転置したいデータがある範囲*/).getValues();

 const set_sheet = read_file.getSheetByName("書き込むシート名");

 for(let i=0;i<row_indexs.length-1;i++){
  let rows = { begin: row_indexs[i], end:row_indexs[i+1] };
  let temp_array = [];
  for(let j=rows.begin; j<rows.end-1; j++){
   temp_array.push(String(read_datas[j]));
  }
 set_sheet.appendRow(temp_array);
 }
}