変換リストを一気に置換するスプレッドシートのスクリプトが便利!

変換リストを一気に置換するスプレッドシートのスクリプトが便利!
新人デザイナー
スプレッドシートで複数の項目を一括で自動置換したいんですけど、そんな方法ありますか?

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

 本記事の内容

・スプレッドシートで複数の項目を一括・自動で置換する方法

こんなデザイナーが記事を書いています


 

ねこひいきデザイン部
あれ?なにしてんの?スプレッドシート?

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

ねこひいきデザイン部
おー、デザイナー以外の仕事をするのもいいじゃん。でもまああんまり便利に使われてるなよ。

新人デザイナー
そうですよね。じゃあ先輩も手伝ってくださいよ。

ねこひいきデザイン部
いやだよ。

新人デザイナー
お前だけで手に負えなかったら応援頼んでいいって言ってましたよ。上が。

ねこひいきデザイン部
あ、また「上」を引き合いに出しやがった。

新人デザイナー
せめてアドバイスください!この商品情報のリストなんですけど、下のように表記がぶれまくってるんです。

例えば、商品名の後の色の表記が

みたいに統一されてないんですよ。あくまでこれ一例なんですけど。

ねこひいきデザイン部
おー、ホントだ。いろいろ表記が統一されてない。こりゃ大変だ。。。

新人デザイナー
それで、修正のリストを作ったんですけど、一つ一つのワードごとに検索置換かけてます。そのリストだけで50くらいあって。。。

ねこひいきデザイン部
まあ、50ならちょっと頑張りゃ手作業でいけるだろ?

新人デザイナー
いやいや、そのファイルが10個あるんですよ。

ねこひいきデザイン部
・・・。おー、おつかれ。さて、帰るとするか・・・

新人デザイナー
あ、ずる!いやいや、待って下さいよ!

ねこひいきデザイン部
ウソだよ。よし!じゃあ、今回もとっておきのスクリプト教えてやるよ。

新人デザイナー
え、あるんすか?スクリプト。さすがに今回はあてにしてなかったすけど。

ねこひいきデザイン部
それがあるんだな!ということでスプレッドシートのリストにまとめた項目を一気に変換するスクリプトを紹介しよう。

 

今回はスプレッドシートでリストにまとめた項目を一括で置換できるスクリプトを紹介します。

まずは、具体的な使用イメージです。スプレッドシートで変換したいリストをまとめて、変換したいテキストを記入します。そしてスクリプトを使用することで、変換したいリストを変換が一気に実行されます。テキストだけでは、わかりにくいので、順を追って手順を紹介していきましょう。

スクリプトの実行手順

新たにスプレッドシートを作成し「変換元」「変換表」「変換後」のタブを作成します。

 

❶続いてこのような変換元タブに、変更したいデータをまとめます。

変換表

❷さらに変換表タブに一括で変換したい表を入力します。
左の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)にまとまっています。これを改行に合わせて1行に一つのワードに変更したい場合は、MacユーザーであればB2セルのテキストをコピーして「メモ」アプリへテキストをコピー、さらにペーストをします。

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

まとめ

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

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

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

ABOUTこの記事をかいた人

現場のグラフィックデザイナーに有用なリアルな技術やスキルアップの方法などを発信しています。 デザイン制作会社に8年→現在は都内にある日用品メーカーのグラフィック系インハウスデザイナーとして7年(総デザイナー歴15年)。そのかたわらフリーでデザインをしています。ラクにできるところはラクに!という時短デザインを推奨しています