【Googleスプレッドシート】セル内の改行をBR置換でHTML出力!Webへの埋め込み対策

スプレッドシートのデータをWebページに埋め込む際、セル内の改行が無視されてしまうことに困ったことはありませんか。HTMLでは改行を
タグで表現する必要があるため、単純にコピー&ペーストすると改行が消えてしまいます。この記事では、Googleスプレッドシートの関数やスクリプトを使って、セル内の改行を
タグに置き換え、そのままHTMLとして出力する方法を詳しく解説します。

具体的には、SUBSTITUTE関数を使った手軽な方法と、Google Apps Scriptを使った自動化の手法を紹介します。どちらの方法でも、改行情報を保持したままHTMLを生成できるようになります。

セル内改行とHTMLの関係

Googleスプレッドシートのセル内改行は、CHAR(10)という改行コードで表現されます。これはWindowsやMacの標準的な改行(LF)と同じです。一方、HTMLでは改行を
タグで表現するため、プレーンテキストの改行コードはブラウザで無視されます。そのため、スプレッドシートのデータをそのままHTMLに埋め込むと、改行が消えてしまいます。

この問題を解決するには、セル内のCHAR(10)を
に置き換える必要があります。以下では、2つの代表的な方法を紹介します。1つは関数を使った方法、もう1つはGoogle Apps Scriptを使った自動化です。

SUBSTITUTE関数で改行をBRに置き換える手順

最も簡単なのは、SUBSTITUTE関数とCHAR関数を組み合わせる方法です。以下の手順で行います。

1. 変換対象のセルを準備します
   例えば、A1セルに複数行のテキストが入力されているとします。
2. 数式を入力します
   =SUBSTITUTE(A1, CHAR(10), “
   “) という数式をB1セルに入力します。これで、A1内の改行がすべて
   に置き換わります。
3. 結果を確認します
   B1セルには、改行の代わりに
   タグが挿入されたテキストが表示されます。これをコピーしてHTMLに貼り付ければ、改行が正しく反映された状態で表示されます。

応用として、範囲全体に同じ処理を適用したい場合は、ARRAYFORMULAを使います。例えば、=ARRAYFORMULA(SUBSTITUTE(A1:A10, CHAR(10), “
“)) と入力すれば、A1からA10までの各セルに一括で変換を適用できます。

Google Apps Scriptで改行をBRに置き換える手順

大量のデータを定期的に変換する必要がある場合は、Google Apps Scriptを使うと便利です。以下の手順でスクリプトを作成します。

1. スクリプトエディタを開きます
   スプレッドシートのメニューから「拡張機能」→「Apps Script」を選択します。
2. コードを記述します
   以下の関数をエディタに貼り付けます。

function replaceNewlineToBr() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (typeof values[i][j] === 'string') {
        values[i][j] = values[i][j].replace(/\n/g, '<br>');
      }
    }
  }
  
  range.setValues(values);
}

3. スクリプトを実行します
   関数名を選択し、実行ボタンをクリックします。初回は権限の承認が必要です。実行後、アクティブシートの全セル内の改行が
   に置き換わります。

このスクリプトは、文字列型のセルに対してのみ改行置換を行います。数値や日付はそのままです。また、\
のバックスラッシュはエスケープに注意してください。

改行置換時の注意点とよくある失敗

改行コードがCR+LFの場合の対処

一部の環境では、改行がCR+LF(CHAR(13)+CHAR(10))で保存されることがあります。この場合、CHAR(10)だけを置き換えても改行が残ることがあります。対策として、=SUBSTITUTE(SUBSTITUTE(A1, CHAR(13), “”), CHAR(10), “
“) のように、先にCRを削除してから置換します。

連続する改行が1つのBRになってしまう

SUBSTITUTE関数は改行1つにつき1つの
タグを生成するため、連続改行は複数の
になります。特に問題はありませんが、場合によっては

のように空行が入ります。そのままHTMLで表示すると空行になります。

引用符や特殊文字のエスケープ

HTMLでは、< > & " ' などの特殊文字はエスケープする必要があります。改行置換と同時に、SUBSTITUTE関数でこれらの文字も置換しておくと、より安全なHTML文字列が得られます。例えば、=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, “&”, “&”), “<", "<"), ">“, “>”), CHAR(10), “
“), CHAR(13), “”) のように数式をネストします。

SUBSTITUTE関数とApps Scriptの比較

まとめ

この記事では、Googleスプレッドシートのセル内改行をHTMLの
タグに置き換える方法を2つ紹介しました。SUBSTITUTE関数を使えば、数式1つで手軽に変換でき、Google Apps Scriptを使えば大量データも一括処理できます。さらに、特殊文字のエスケープを組み合わせることで、完全なHTML文字列を生成できます。

次に試していただきたいのは、QUERY関数やIMPORTRANGEと組み合わせて、外部データソースから取得したデータをHTML化する応用です。また、スクリプトを時間主導型トリガーに設定すれば、定期的に自動変換できます。ぜひ、自分のワークフローに取り入れてみてください。