はじめに
プロジェクトのデバッガーの人に、「デバッグ画面で出る出力が英語ばかりで分かりにくいから、日本語に変換するツールが欲しい。英語と日本語の対応表は用意するから」と言われたので、Googleスプレッドシートを使って実現してみました。
動作
このように動作します。
変換元シートに文字列を入力し（緑の部分）、実行（青いボタン）を押すと
このような変換表シートを元に
文字列置換して出力します。
1. スプレッドシートを作る
上記の画像のように以下の3つのシートを作ります。
「変換元」シート
置換したい元の文字列を入力するシート
「変換後」シート
置換した後の文字列を出力するシート
（変換元と同じでもよいかもです。レイアウトに悩んだ結果分けました）
「変換表」シート
文字列置換する際の置換前、置換後の文字列の組み合わせの表
2. GASスクリプト作成
Google App Scriptでメニューの「ツール」→「スクリプトエディタ」からスクリプトを作ります。
スクリプトは以下のような感じ。
spreadsheet = null;
sheetMap = [];
function translate(){
var sheetFrom = _getSheetByName('変換元');
var sheetTo = _getSheetByName('変換後');
var rangeFrom = sheetFrom.getRange("B3");
var rangeTo = sheetTo.getRange("B3");
var sheetTable = _getSheetByName("変換表");
// 変換表を全て取得
var tableValues = sheetTable.getRange(2,1,sheetTable.getLastRow(),2).getValues();
var fromStr = rangeFrom.getValue();
for (var i in tableValues) {
fromStr = fromStr.split(tableValues[i][0]).join(tableValues[i][1]);
}
rangeTo.setValue(fromStr);
}
function _getSpreadSheet(){
if ( ! spreadsheet) {
spreadsheet = SpreadsheetApp.getActive();
}
return spreadsheet;
}
function _getSheetByName(name){
if ( ! sheetMap[name]) {
sheetMap[name] = _getSpreadSheet().getSheetByName(name);
}
return sheetMap[name];
}
ポイントを絞って解説します。
API関数のメモ化
GASについて調べていたらAPIを呼び出す回数が多いほど処理が重くなるとあったので、スプレッドシート の情報を取得する関数をメモ化し、同じ処理を2回目以降に行う場合はAPIを呼ばないようにしています。
function _getSpreadSheet(){
if ( ! spreadsheet) {
spreadsheet = SpreadsheetApp.getActive();
}
return spreadsheet;
}
function _getSheetByName(name){
if ( ! sheetMap[name]) {
sheetMap[name] = _getSpreadSheet().getSheetByName(name);
}
return sheetMap[name];
}
シートの全てのデータを取得
シートの最後の行、列がそれぞれgetLastRow、getLastColumn関数で取得できます。
今回は列数は2列で固定なので、2〜最後の行と1〜2列の範囲を取得します。
// 変換表を全て取得
var tableValues = sheetTable.getRange(2,1,sheetTable.getLastRow(),2).getValues();
文字列変換
文字列変換にreplaceという関数があるのですが、これだと置換元の文字列が複数ある場合に初めの一つしか変換してくれないので、以下の方法で置換します。これだと全て置換されます。
for (var i in tableValues) {
fromStr = fromStr.split(tableValues[i][0]).join(tableValues[i][1]);
}
3. シートから簡単にスクリプトを呼べるようにする
こちらの手順（「スプレッドシートで図形を作成する」、「図形にスクリプトを関連付ける」）にしたがってボタンを設置します。
終わりに
今回初めてスプレッドシートをGASで操作してみましたが、javascriptやphpなどのプログラミング環境を用意せずとも、ブラウザだけでこれだけのことができるのは便利だなと感じました。
みなさんも使ってみてください！
参考
- GASでSpreadsheetを操作する自分的ベストプラクティス
- Google Apps Script シートの全ての値を別のシートにコピーする
- 【JavaScript】検索文字列を全置換する方法【replaceAll】
- スプレッドシートに図形でボタンを作成しGASを実行＆トリガーを使ったメニューの追加
2023/03/16 追記
実行時間が遅いとコメントをいただきました。
Sheets APIなる高速なAPIがリリースされていたので、そちらを使って実装し直してみました。
これから実装する人はこちらを使う方が高速なのでおすすめです。
2023/04/03 追記
置換後の文字列が空白の場合にエラーになっていたので処理追加
function translate(){
//このファイルのIDを取得
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ssid = ss.getId();
// 変換表を取得
const tableValues = Sheets.Spreadsheets.Values.batchGet(
ssid,
{ranges:
[
"変換表!A2:A",
"変換表!B2:B"
]
}
);
let beforeWords = tableValues.valueRanges[0].values;
let afterWords = tableValues.valueRanges[1].values ?? [];
// 変換前のデータを取得
const beforeValues = Sheets.Spreadsheets.Values.batchGet(
ssid,
{ranges:
[
"変換元!B3:B"
]
}
).valueRanges[0].values;
// 変換表に基づいてデータを変換
for (const i in beforeValues) {
if (beforeValues[i].length == 0) {
continue;
}
for (const j in beforeWords) {
beforeValues[i][0] = beforeValues[i][0].replaceAll(beforeWords[j], afterWords[j] ?? "");
}
}
//シートに書き出しオプション
let option = {
valueInputOption: 'USER_ENTERED',
data: [
{
range: '変換後!B3:B',
values: beforeValues,
},
]
};
//シートに一括書き出し
Sheets.Spreadsheets.Values.batchUpdate(
option, ssid
);
}