Google SpreadsheetのA列に文を記入するごとに、翻訳結果をB列に自動で出力する。ただしワークシート関数を使わずに、Google Apps Script の onEdit で。

英文を翻訳するときに、Google Spreadsheetで行単位で原文とGoogle翻訳の結果を比較しながら作業したいけど、ワークシート関数をいちいちペーストするのはイケてないと思う

突然ですが、Google翻訳、便利ですよね。外国語の情報を理解したいときに、さっと使える。しかもいろんな方法で使える。多分、利用者の多い順に並べると、概ねこういう順番になると思います。

1. https://translate.google.com/ で単体で使う
2. Google Chrome で表示中のページを翻訳する
3. ブラウザ版のGmailに組み込まれた翻訳機能で外国語のメールを読む
4. スマートフォンでGoogle翻訳アプリを使う
5. Google Document の翻訳機能により表示中の文書を一括翻訳し、別の文書として新規生成する
6. Google Spreadsheet の googletranslate 関数を使う

しかし、機械翻訳は一部の表現が翻訳から漏れていたり、あるいは翻訳自体が適切に行われていないことが多々ありますよね。だから、私がそれなりの精度で日本語への翻訳を行いたい時には、原文とGoogle翻訳の下訳を比較しつつ、最終的な日本語を作文する、という方法をとります。

この作業は Google Spreadsheet のA列に原文、B列に翻訳文という形で表示させれば、翻訳文をざっくり読みつつ、原文も読んで内容を確認できます。こういう場合にB列に =googletranslate(A1,"en", "ja") のような計算式を記入しておけばA列に記入した原文は自動でB列に出力できます。

でも googletranslate 関数は翻訳精度が微妙に良くないので、Google Spreadsheet の googletranslate 関数の代わりに LanguageApp を使うワークシート関数を作ってイケてる翻訳ができるようにする のような方法で LanguageApp を使うのもありです。

とは言え、このような方法はB列でコピペが必要なので、これはこれで微妙にめんどくさいです。事前にワークシート関数をB列に貼っておいても良いかもしれないけど、なんかイケてない気がします。

そこでA列の書き換えを検出したら、B列に翻訳された日本語を自動で出力するような処理を Google Apps Script で実装してみました。

実際にやってみたらこうなった

こんな仕様で作ります

• 1行目には翻訳設定を記述します。A1 には原文の言語、B1には翻訳言語を指定します。
• Google Apps Script の onEdit でA列が編集されたことを検知したら、B列に翻訳結果を出力します。

以下のスクリーンショットは実際の動作例です。A列に英文を配置するとB列に翻訳文が自動で差し込まれます。またA列を削除するとB列の翻訳文も自動的に消去されています。これだけなら googletranslate 関数等をセルに書いておいても実現できますが、今回の方法ではB列にワークシート関数を設定していません。

このようにA列の編集結果に応じて自動的に変換ができるにもかかわらず、セルに関数を配置せずに使えるので、自分にとっては大変使いやすいものになりました。

ちなみにこの文章は Sencha Ext JS の Software License Agreement の最初のほうの文章を貼り付けてみました。Sencha Ext JS は商用の JavaScript フレームワークで、多くのコンポーネントを備えていて表現力豊かなシングルページアプリケーションを作成でき、しかもデータの取扱にすぐれたコンポーネントがたくさん使えます。

必要な要件を考えてみる

いろんな要件があるとは思うのですけど、今回は２つの機能を必須要件にしてみます。

A列に原文を記入したら、B列に自動で翻訳文が書き込まれるようにしたい

スクリーンショットで紹介しているように、A列に原文を記入したらB列に自動で翻訳文が作成されるような仕組みにしてみます。翻訳は A1, B2 に翻訳言語が指定されている場合だけ動作するようにしてみます。

全件の再翻訳もできるようにしたい

前述の機能をプロトタイプ的に実装してみたら、大量の原文を一度に貼り付けたときに都度翻訳がうまく行かない場合が出ました。そこで、全件を強制的に再翻訳する処理も欲しくなりました。

今回のスクリーンショットを良く見てみると、メニューの中に「Google翻訳」というメニューが追加されていることが分かりますが、このカスタムメニューで再翻訳できるようにしています。

この機能を使う場合は、A1, B2 を一旦は空欄のままにして原文を貼り、準備ができたタイミングで一括変換を実行する、ということが可能になります。

設計のサマリ

翻訳には LanguageApp を用いる

Google Apps Script では LanguageApp クラスで翻訳が行えます。
https://developers.google.com/apps-script/reference/language/language-app

というか、Google Apps Script での翻訳は他の選択肢は多分無いと思う。

翻訳の言語設定はA1, B1で行えるようにする

翻訳時の言語指定はシート内のA1, B1で翻訳指定できるようにします。そうすれば、任意の言語の翻訳が容易に行えます。たとえば英語→日本語、日本語→英語の２パターンが欲しい場合は、Google Spreadsheet に２つのシートを作成し、A1, B2 にそれぞれ en, ja と ja, en を入力しておくだけで英語→日本語、日本語→英語の翻訳用シートとして動作させることができます。

なお、一番最初のプロトタイプ実装ではタブ名で翻訳指定できるようにしてみたのですが、これは案外使いづらかったので現在の実装に落ち着きました。

onEdit() でA列の書き換えを検知して処理を行うようにする

スプレッドシートのGoogle Apps Script に onEdit() 関数を作成しておくと、Spreadsheet の編集が発生したタイミングでこの関数が呼ばれます。

今回のようにA列の書き換えだけに限って動作する処理は、以下のような実装で編集された範囲を検知できます。

function onEdit(e) {
　// A列以外の編集はスルー
  if ( e.range.columnStart != 1 ) return;

  // 編集が発生した行の範囲を取得
  var rowStart = e.range.rowStart;
  var rowEnd   = e.range.rowEnd;
}

Spreadsheet のセルの値を取得したり、書き換えたりしたい

Spreadsheet のセルは次の実装で参照や書き換えができます。

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(rowStart, 1, rowEnd,2)

var originalValue = range.getCell(loop,1).getValue();
var translateValue = LanguageApp.translate(originalValue, translateFrom, translateTo);
range.getCell(loop,2).setValue(translateValue);

この処理を先程の onEdit() に組み込んでA列の更新範囲の値を取得して TranslateApp で翻訳して結果をB列に書き込むことで、自動的に翻訳を実行できます。

ただし Spreadsheet を操作する処理は案外重い（遅い）ため、大量の書換えが行われると Spreadsheet の更新が処理のボトルネックになります。時間がかかる理由をあえて推測するとしたら「Google Spreadsheetは同じシートを複数人で同時に開いての閲覧や編集ができ、その結果は同じシートを開いている全員の表示に反映される」という機能が影響しているのかもしれません。

またスクリプトは実行可能時間に上限がありますので、処理に時間がかかりすぎると処理が途中で強制終了してしまいます。

このため、Google Spreadsheetからセルを1つづつ読んで翻訳結果を書き込むような実装は避けるべきです。

そこで編集範囲をまとめて取得、翻訳し、結果もまとめて更新するようにします。実際の実装では range.getValues() で指定範囲を配列で取得して操作した後、range.setValues() で配列を Spreadsheet に書き込むようにしています。

でもこの方法には別の欠点もあります。多くの行を一度に翻訳するならこれで良いのですが、せいぜい数行程度をコピペした場合に、進捗状況が分かりづらくなります。そこで処理行数が少ない時は翻訳結果の書き込みを都度実行し、行数が多いときは先に翻訳だけを実施して結果を配列に保存し、それを一度に書き出すようにしてみます。

全件翻訳のメニューを追加したい

Google Spreadsheetを開く際に、カスタムメニューを追加することにします。これで全件翻訳をメニューから選んで実行できるようになります。

このための実装はこんな感じです。

function onOpen() {
  // Google Spreadsheet のオープン時にメニューを追加します。
  var entries = [
    { name : "A列全件を翻訳してB列に出力", functionName: "translateAll" }
  ];

  SpreadsheetApp.getActiveSpreadsheet().addMenu("Google翻訳",entries);
}

実際のコード

こんな実装になりました。説明済みの内容に加えていくつかの例外処理などを加えています。逐次翻訳とバッチ翻訳は処理が似ているので本当は纏めてしまってもよいのですが、ここではそこまでの最適化を行わず、別々の関数で実装しています。

実装したらSpreadsheet の「ツール」メニューから「スクリプトエディタ」を選んでコードを貼り付ければ利用できますが、貼り付け後に1回だけ transfer_all_column_A をスクリプトエディタで実行してください。実行時は A1 = en, B1 = ja のように翻訳指定をあらかじめ記入し、A2 には適当な英文 (Hello world. など、なんでも構いません。）を記入しておいてください。transfer_all_column_A を実行すると各種権限に関する確認が表示されますので、権限を付与すれば以後は問題なく動くはずです。

// Google Spreadsheet で一度に追加された行がこの数値を超えたらバッチ翻訳に切り替える
var SequentialTranslationLimit = 20;

// Google Spreadsheet のオープン時にメニューを追加する処理
function onOpen() {
  var entries = [
    { name : "A列全件を翻訳してB列に出力", functionName: "transfer_all_column_A" }
  ];

  SpreadsheetApp.getActiveSpreadsheet().addMenu("Google翻訳",entries);
}

// A列全件を無条件に翻訳する
function transfer_all_column_A() {
  var rowStart = 2;
  var rowEnd   = SpreadsheetApp.getActiveSheet().getLastRow();

  translate_between(rowStart, rowEnd);
}

// Spreadsheet 書き換えが発生したら行う処理
function onEdit(e) {
  // A列以外の書き換えは無視
  if ( e.range.columnStart >= 2 ) return;

  // 翻訳範囲はA列で書き換えが発生した範囲だけを対象に行う
  var rowStart = e.range.rowStart;
  var rowEnd   = e.range.rowEnd;

  translate_between(rowStart, rowEnd);
}

// 翻訳処理（逐次翻訳とバッチ処理をディスパッチする）
function translate_between(rowStart, rowEnd) {
  var sheet = SpreadsheetApp.getActiveSheet();

  // 翻訳言語設定を取得する
  var translateConfigRange = sheet.getRange(1,1, 1,2);

  var translateFrom = translateConfigRange.getCell(1,1).getValue();
  var translateTo   = translateConfigRange.getCell(1,2).getValue();

  // 翻訳言語指定が不完全な場合は何もしない
  if ( translateFrom.length * translateTo.length == 0 ) return;

  Logger.log(translateFrom);
  Logger.log(translateTo);

  if ( rowStart == 1 ) rowStart = 2;
  var numRows = rowEnd - rowStart;

  // フォームにデータがない場合は何もしない
  if ( numRows < 0 ) return;

  // 対象行数が少ない場合は逐次翻訳して表示、多い場合は全部翻訳してから表示する
  // Google Spreadsheet の操作は案外重いので、更新行数が指定値以上ならバッチ変換する
  if ( numRows <= SequentialTranslationLimit )
    translate_Sequential_between(sheet, rowStart, rowEnd, translateFrom, translateTo );
  else
    translate_Batch_between     (sheet, rowStart, rowEnd, translateFrom, translateTo);
}

// 逐次翻訳の実施
function translate_Sequential_between(sheet, rowStart, rowEnd, translateFrom, translateTo) {
  var range = sheet.getRange(rowStart, 1, rowEnd,2)  

  // A列に記述されている文章を翻訳してB列に書き出す
  for ( var loop = 1 ; loop <= rowEnd-rowStart+1 ; loop++ ) {
    // Spreadsheet を参照して値を取得する処理：頻繁に行うと遅い処理です。
    var originalText = range.getCell(loop,1).getValue();

    // 原文があれば翻訳するが、なければ翻訳結果のセルは空にする
    if ( originalText != "" ) {
      try {
        // 翻訳の実行
        var translatedText = LanguageApp.translate(originalText, translateFrom, translateTo);

        // Spreadsheet への書き込み：これも遅い処理。
        range.getCell(loop,2).setValue(translatedText);
      } catch (e) {
        Browser.msgBox( e + "\\nA1, B1には翻訳前、翻訳後の言語を2文字で記述する必要があります (en, ja, ...)",Browser.Buttons.OK);
        return;
      }
    }
    else {
      range.getCell(loop,2).setValue("");
    }
  }
}

// バッチ翻訳の実施
function translate_Batch_between(sheet, rowStart, rowEnd, translateFrom, translateTo) {
  var range = sheet.getRange(rowStart, 1, rowEnd,2);

  // Spreadsheet から翻訳対象範囲をまとめて配列で取得しておく
  // このほうが処理が高速になる
  var rangeValue = range.getValues();

//  var rangeDestIndex = 0;

  // A列に記述されている文章を翻訳してB列に書き出す
  // 処理範囲は配列 rangeValue に取得したものを使うので、１行目 = 0、A列 = 0 であることに注意が必要。
  for ( var loop = 0 ; loop <= rowEnd-rowStart ; loop++ ) {
    var originalText = rangeValue[loop][0];
//    Logger.log("%s: %s", loop, originalValue );

    // 原文があれば翻訳するが、なければ翻訳結果のセルは空にする
    if ( originalText != "" ) {
      try {
        // 翻訳結果は配列に書く
        var translatedText = LanguageApp.translate(originalText, translateFrom, translateTo);
        rangeValue[loop][1] = translatedText;

        // LanguageApp は単位時間あたりの実行回数制限があるので実行しすぎないように時間調整を入れてみた
        Utilities.sleep(10);
      } catch (e) {
        Browser.msgBox( e + "\\nA1, B1には翻訳前、翻訳後の言語を2文字で記述する必要があります (en, ja, ...)",Browser.Buttons.OK);
        return;
      }
    }
    else {
      rangeValue[loop][1] = "";
    }
  }
　// 全件の翻訳が終わったら結果を一括で Spreadsheet に戻す
  range.setValues(rangeValue);
}

使用上の注意

• あまりたくさんの翻訳をさせようとすると、Google Apps Script の実行可能時間内に終了しないおそれがあります。
• LanguageApp などのサービスは１日あたりの quota がありますので使いすぎに注意しましょう。