記事の概要
過去に似たようなことを関数で処理する方法を記事にしましたが、よりイレギュラーに対応しやすくするために、Google Apps Scriptを用いたツールを作ることにしました。
コードとSpreadSheet側の設定、上記の関数版からのアップデートポイントをご紹介します。
コード
function convertDataFormat() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// 元データの取得
const rawData = sheet.getRange("B3").getValue().toString();
if (!rawData) {
Browser.msgBox("B3にテキストを入力してください");
return;
}
// カンマ(,)、改行(\n\r)、読点(、)、スペース(\s)のいずれかが
// 1回以上続く場所を「区切り」として分割
const formattedData = rawData.split(/[,\n\r、\s]+/)
.map(item => item.trim()) // 前後の余計な空白を消す
.filter(item => item !== "") // 空の要素を除外する
.map(item => `'${item}'`) // シングルクォートで囲む
.join(','); // カンマで繋ぐ
// D3に出力
sheet.getRange("D3").setValue(formattedData);
}
拡張機能 > Apps Script でファイルの 無題.gs を編集するか、ファイルの + でスクリプトを追加してください。
SpreadSheet側の設定
- (私はA列も1行目も空ける宗派なので)B3に元データのテキストを入れる場所を用意し、変換ボタンを押すと、D3に結果が出力されるようにしています
- 上記のコードを利用する際は、好きなセルに置き換えてください
-
変換ボタンには、上記のコードを割り当てています
ボタンは、挿入 > 図形描画 で作成できます。
スクリプトの割り当てやボタンの移動は右クリックで行ってください。
スクリプトが割り当てられた状態で左クリックをすると、スクリプトが実行されてしまいます。
関数版からのアップデートポイント
関数版と比べた際のメリットを中心に、アップデートポイントを記載します。
イレギュラーが増えるたびにネストを増やす必要がない
関数版では、 「スペースを消すためにネスト」「読点に対応するためにネスト」「スラッシュに対応するために……」と、イレギュラーが増えるたびに関数が長くなり、可読性も落ちていました。
GAS版では、 split(/[,\n\r、\s]+/ の正規表現により、「どのような区切り文字が来ても配列にバラす」という処理にしました。これより「対応したい文字が増えても、正規表現の [ ] の中にその文字を1文字追加するだけ」になり、メンテナンス性を高めることができます。
「データのノイズ」への対応
関数版では、例えば SUBSTITUTE("A、、B", "、", "','") は、A'',''B のようになってしまい、Redashにそのまま貼り付けるとエラーになる「データのノイズへの弱さ」がありました。
GAS版では、正規表現の +(1回以上の繰り返し)と .filter(item => item !== "") を用いることにより、「カンマを2個連続で打ってしまった」「読点とスペースを両方入れてしまった」という場合でも、そのノイズを吸収できるようにしています。
消したくないスペースを消さない
関数版では、Google Apps Scriptにあるような単語の中に含まれる必要なスペースを消したくない場合、イレギュラー対応を逐一する必要がありました。
GAS版では、 .map(item => item.trim()) (Trim)を採用したことで、「単語と単語の間にある必要なスペースは維持しつつ、区切り文字のまわりにある不要なスペースだけを取り除く」ことができるようになりました。
いちいち関数をコピペしなくて良い
何だかんだ書いてきましたが、結局はこれです。これに尽きます。



