Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
7
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

Organization

Googleスプレッドシート + GASで別シートの変換表に基づいた文字列置換

はじめに

プロジェクトのデバッガーの人に、「デバッグ画面で出る出力が英語ばかりで分かりにくいから、日本語に変換するツールが欲しい。英語と日本語の対応表は用意するから」と言われたので、Googleスプレッドシートを使って実現してみました。

動作

このように動作します。

文字列変換シート - Google スプレッドシート 2019-05-12 20-40-24.png
変換元シートに文字列を入力し(緑の部分)、実行(青いボタン)を押すと

文字列変換シート - Google スプレッドシート 2019-05-12 20-40-59.png
このような変換表シートを元に

文字列変換シート - Google スプレッドシート 2019-05-12 20-41-22.png
文字列置換して出力します。

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];
}

シートの全てのデータを取得

シートの最後の行、列がそれぞれgetLastRowgetLastColumn関数で取得できます。
今回は列数は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などのプログラミング環境を用意せずとも、ブラウザだけでこれだけのことができるのは便利だなと感じました。
みなさんも使ってみてください!

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
7
Help us understand the problem. What are the problem?