スプレッドシートのセル内の改行に悩まされたことありませんか?

なぜ改行なのかと、下の行に記入してはダメだったのかと…!
スプレッドシートを見栄え上で使っていると、利用のことは意外と考えられていないですよね。
セル内の改行をバラバラにするGAS作ったので、改行に悩まされるぜひ皆さん使って下さい。コードを使って何か問題が起きたとしても狸ブログは一切責任は取りませんので自己責任でお願いします。

スプレッドシートの改行を分割するコード
下記は全体のコードです。
function myFunction() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = 0; i < data.length; i++) {
var rowValues = data[i];
var maxLineBreaksCount = 0;
for (var j = 0; j < rowValues.length; j++) {
var cellValue = String(rowValues[j]);
if (cellValue.includes("\n")) {
var lineBreaksCount = (cellValue.match(/\n/g) || []).length;
if (lineBreaksCount > maxLineBreaksCount) {
maxLineBreaksCount = lineBreaksCount;
}
}
}
if (maxLineBreaksCount > 0) {
sheet.insertRowsAfter(i + 1, maxLineBreaksCount);
for (var j = 0; j < rowValues.length; j++) {
var cellValue = String(rowValues[j]);
var valuesArray = cellValue.split("\n");
if (valuesArray.length > 1) {
for (var k = 0; k < valuesArray.length; k++) {
sheet.getRange(i + 1 + k, j + 1).setValue(valuesArray[k]);
}
}
}
}
i += maxLineBreaksCount;
data = sheet.getDataRange().getValues();
}
}
コードについてご説明する前にプログラミングにおける「改行」についてご説明しますね。
改行は特殊文字
実はスプレッドシートの改行は見えていませんが、データ上では「/n」で表示されています。例えば、「こんにちは 狸さん」という文章があったとます。「こんにちは」と「狸さん」の間に改行を入れたい場合は「こんにちは/n狸さん」とすれば良いのですね。ログで確認していましょう。
function myFunction() {
var text = "こんにちは\n狸さん";
console.log(text);
}

このように改行されていることがわかりました。改行の仕組みについてわかったところで、今回のコードについてご説明します。
各行で最も多く改行されたセルを確認
最初にsheetでアクティブなスプレッドシートを取得します。次にdataでシートの全てのデータを二次元配列で取得します。
function myFunction() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
// 各行の処理
for (var i = 0; i < data.length; i++) {
var rowValues = data[i];
var maxLineBreaksCount = 0;
// 各セルの処理
for (var j = 0; j < rowValues.length; j++) {
var cellValue = String(rowValues[j]);
次にfor (var i = 0; i < data.length; i++) で各行づつ処理するようにします。for文は繰り返し処理と呼ばれるもので()に記された条件を満たすまで{}の中の処理を繰り返します。
2回目の for (var j = 0; j < rowValues.length; j++) もiとjでアルファベットが違いますが機能としては1回目のforと同じです。ただし1回目は各行を、2回目はその中の各セルを繰り返し処理してます。
そして、var cellValue = String(rowValues[j]);でセル内のデータを強制的に文字列に変換。これによってデータの中身が日付でも数字でも処理できるようになりました。
またこのコードでは行内で一番多く改行されたセルの改行数がキーポイントなので、各セルの改行数をカウントするためにmaxLineBreaksCountの初期値を0にして、以降のコードで更新していきます。
一番多く改行されたセルを見つける
次に行内で一番多く改行されたセルを見つけ出します。
if (cellValue.includes("\n")) {
var lineBreaksCount = (cellValue.match(/\n/g) || []).length;
if (lineBreaksCount > maxLineBreaksCount) {
maxLineBreaksCount = lineBreaksCount;
}
}
プログラムでは改行は\nとなるので、matchメソッドを使いセル内の改行を探します。そしてlengthで改行数をカウントして、その数をlineBreaksCountの変数に格納。
次のif (lineBreaksCount > maxLineBreaksCount)で、lineBreaksCount
より多くの改行を持ったセルがあれば、maxLineBreaksCount
の値を更新するように設定。この処理によって各行内で最も多くの改行文字を含むセルの改行数を見つけることができます。
行の下に最大改行数の空白行を追加する
処理中の行の下に空白行を追加する処理です。
if (maxLineBreaksCount > 0) {
sheet.insertRowsAfter(i + 1, maxLineBreaksCount);
IFを使い改行数が0以上であれば、insertRowsAfterメソッドを使い空白行を挿入します。挿入する行数はmaxLineBreaksCount
が持っている数です。ここでいう、行内で最も改行されているセルの改行数。最大の改行数でなければ、次の行に干渉します。
またinsertRowsAfterは挿入する行の場所と行数を渡してあげなければなりません。行の場所は現在処理しているdata[i]の1つ下ですのでi + 1を、行数は最大改行数を持っているmaxLineBreaksCountを渡します。
セル内の改行ごとにデータを分割し、空白行に転写する
これはinsertRowsAfterで追加した空白行に、セルがもつ改行を分割して転写する処理です。転写する作業が必要のない(最大改行数分の空白行を追加したい)方は下記のコードを省いても大丈夫です。
// 各セルの処理
for (var j = 0; j < rowValues.length; j++) {
var cellValue = String(rowValues[j]);
// 各セルの処理
var valuesArray = cellValue.split("\n");
if (valuesArray.length > 1) {
for (var k = 0; k < valuesArray.length; k++) {
sheet.getRange(i + 1 + k, j + 1).setValue(valuesArray[k]);
}
}
}
splitメソッドを使いセル内の改行ごとにデータを分割。改行ごとに分割されたデータはvaluesArrayに配列とした格納されます。
そして、配列の数(改行数)が0よりも多ければ、for (var k = 0; k < valuesArray.length; k++) の繰り返し処理を行い転写処理を行います。
getRange(i + 1 + k, j + 1)は転写する行の番号と列を指定しています。そしてsetValueメソッドでデータの転写。既存のデータの上に書き込みします。
最後にdata[i]の位置を調整する
改行されたため、 for (var i = 0; i < data.length; i++) のdata.lengthの値を更新しなければなりません。そうでなければ、空白行を追加する前のデータ範囲で処理が終わってしまいます。
i += maxLineBreaksCount;
data = sheet.getDataRange().getValues();
とはいえ、挿入して転写した行はもう処理する必要がないので+=を使って[i]の数字に挿入した行数を追加。そして、現在の範囲を再びsheet.getDataRange().getValues(); を使いdataに再び代入してあげます。これにより、空白行が挿入されても最後まで処理されるようになりました。
まとめ
以上、セル内の改行をバラバラにするコードでした。筆者以外に悩まされている人いるのかは知りませんけども是非参考にしてください。また、コードについてのアドバイスなどもお待ちしております。
きちんとGASについて学びたい人は「詳解! Google Apps Script完全入門 [第3版]」もおすすめです。クリックするとAmazonに移動します。
コメント