変換したい文字リストを一括変換できるスプレッドシートのスクリプトが便利！

スプレッドシートで複数の項目を一括で自動置換したいんですけど、いい方法ありますか？

今回はスプレッドシートで検索・置換をする項目がたくさんあるときにそれらをリストにまとめて一括で置換できる方法を紹介します。これ、とっても便利！

こんな人が書いています

Macユーザー歴20年超のめんどくさがりインハウスデザイナー。
Macでも無料で使えるスプレッドシートの使い方
無料で使えるアプリなどを紹介

あれ？なにしてんの？スプシ（スプレッドシート）？

そうなんです。こないだの画像管理が評判良かったんですけど、それが終わったら次はウチの商品台帳のリストを整理してくれって。なんかこういうの手早くていいねって、えらい褒められましたよ。

お〜いいね〜。次の査定が楽しみだね。給料上がったらちょっとおごってよ（笑）

そうっすね。じゃあ先輩も手伝ってくださいよ。

えー、やだよ。

一人だけで手に負えなかったら誰かに応援頼んでいいって言われましたよ。

やだ

まあそういうと思ってたんでいいっすけど、せめて前みたくアドバイスくださいよ！この商品情報のリストなんですけど、下のように表記が定まっていないんです。例えば、商品名の後の色の表記方法が下の表のように統一されてないんです。あくまでこれ一つの例なんですけど

おー、ホントだ。ひでえなこりゃ。。。赤とかRedとかrとか。。。表記がまったく統一されてない。こりゃ大変だな

それで、修正のリストを作って、一つ一つのワードごとに検索置換かけてるんです。そのリストだけで50くらいあって。。。

まあ、50ならちょっと頑張りゃ手作業でいけんだろ？

いやいや、そのファイルが30個くらいあるんですよね。。。

・・・。おー、おー、それはそれは。おつかれ、この話は無かったことに・・・

あ、ずるい！いやいや、待って下さいよ！

ウソだよ。よし！じゃあ、今回もとっておきのスクリプト教えてやるよ。

え、あるんすか？スクリプト！？さすがに今回はあてにしてなかったんすけど。

それがあるんだな！ということで今回はスプレッドシートのリストにまとめた項目を一気に変換するスクリプトを紹介しよう。

あざっす！！！

スクリプトの実行手順

それでは、まずは新たにスプレッドシートを作成し「変換元」「変換表」「変換後」のタブを作成します。下に記載したスクリプトをコピペして使う方はB2タブに変換元のリストを挿入しましょう。

❶続いてこのような変換元タブのB列にリストを改行してまとめます。（一つのセルの中に改行しておさめるイメージ）

❷さらに変換表タブに一括で変換したい表を入力します。
左のA列には変換したいワードを、右のB列には変換後のワードを入力します。

❸スクリプトを設定し実行します。
スクリプトの設定はこちらのスクリプトの記事で紹介しています。

全角を半角に一括で変更するスプレッドシートのスクリプトが便利

2021.05.25

今回のスクリプトも基本的にコピー&ペーストで対応できます。上記の記事で書いたスクリプトの設定と同じような手順になります。

「ツール」＞「スクリプトエディタ」で新しくスクリプトを作成し、下記のテキストをペーストします。

spreadsheet = null;

sheetMap = [];

function translate(){

varsheetFrom = _getSheetByName(‘変換元’);

varsheetTo = _getSheetByName(‘変換後’);

varrangeFrom = sheetFrom.getRange(“B2”);

varrangeTo = sheetTo.getRange(“B2”);

varsheetTable = _getSheetByName(“変換表”);

// 変換表を全て取得

vartableValues = sheetTable.getRange(2,1,sheetTable.getLastRow(),2).getValues();

varfromStr = rangeFrom.getValue();

for (variintableValues) {

fromStr = fromStr.split(tableValues[i][0]).join(tableValues[i][1]);

}

rangeTo.setValue(fromStr);

}

function _getSpreadSheet(){

if ( ! spreadsheet) {

spreadsheet = SpreadsheetApp.getActive();

}

returnspreadsheet;

}

function _getSheetByName(name){

if ( ! sheetMap[name]) {

sheetMap[name] = _getSpreadSheet().getSheetByName(name);

}

returnsheetMap[name];

}

その後、スクリプトの名前をつけて保存をします。名前は任意で大丈夫です。

❹スクリプトを使用する

設定したスクリプトを「実行」をクリックして使用します。

❺変換後フォルダを確認

変換後のフォルダのB2セルに変換後のテキストがまとめられます。

このようにあっという間にリストで設定した複数のワードの変換が一気に完了。これはものすごい便利ですね。

変換したテキストはひとつのセル（B2）にまとまっています。これを改行に合わせて１行に一つのワードに変更したい場合は、MacユーザーであればB2セルのテキストをコピーして「メモ」アプリへテキストをコピー、さらにペーストをします。

これをスプレッドシート に戻せば改行に合わせてセルに区切ったデータになります。

まとめ

今回はスクリプトを使って、変換リストにまとめたワードを自動で、しかも一括で変更するスクリプトを紹介しました。ここで設定したスクリプトは一度設定してしまえば、「変換元」と「変換表」を書き換えることで何度でも使うことができます。データ管理でお悩みの方はかなりの時短効果があるので、ぜひご活用ください。

ちなみに今回の方法はこちらで紹介していた方法を活用させていただいています。

それでは今回はこのへんで。