Googleスプレッドシートでアプリの翻訳用データを作る

Artstageで多言語対応を行うにあたって、アプリ内文言を各言語に翻訳したエクセルデータを作る必要がありました。

もちろん私は色んな言語の通訳なんてできませんし、通訳を頼むアテもありません。

よって、Google翻訳を使おうと決めました。

機械翻訳なんかでいいのか？と思われるかもしれませんが、Google翻訳は2年前にディープラーニングを使った高精度な翻訳エンジンに切り替わり、劇的に読みやすい翻訳をしてくれるようになりました。

私はそれまではYahoo翻訳をアテにしましたが、あまりのクオリティ向上に速攻でGoogle翻訳に乗り換えました。

それに海外製のソフトウェアを使ってても、雑な日本語でも無いよりはありがたいと感じますし、そういうノリでやっていきます。

とは言え、文言を一つ一つGoogle翻訳で翻訳してエクセルに打ち込んでいくのは手間がかかって面倒くさいです。

そこでググッてみると、Google スプレッドシートなら一発でセルを翻訳してくれる関数（GOOGLETRANSLATE）があるとの事。

これは素晴らし～と思ったら、なんとこの関数だとディープラーニング高精度翻訳エンジンじゃない翻訳になっちゃうそうです。

ディープラーニング以前のGoogle翻訳と言えばかなり残念な感じの奴です。

困ったな～とググッたら、Google Apps Script の LanguageAppというAPIを使えば高精度な翻訳ができるそうです！

Google Spreadsheet の googletranslate 関数の代わりに LanguageApp を使うワークシート関数を作ってイケてる翻訳ができるようにする

これで英語から日本語とかドイツ語、フランス語に一発翻訳できるようになりました。

やったぜ。

しかし運用しているうちに問題が出てきました。

上記リンクの記事だとスプレッドシートのカスタム関数として実装してますが、この方法だと翻訳結果が保持されないのでスプレッドシートを開きなおしたりするたびに再び関数が実行されます。

そしてLanguageAppのAPIがジャブジャブ叩かれてしまい、すぐに「1日のtranslateの制限回数に達しました」みたいなエラーが出て、丸一日待たないと復帰しない問題に直面しました。

なのでカスタム関数じゃなくてメニューコマンドとして手動で実行して選択中のセルを翻訳する感じのスクリプトを書きました。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // メニューに追加
  ui.createMenu('翻訳')
      .addItem('選択範囲を翻訳', 'translate').addToUi();
}

function translate() {
  
  var translateTargetColumnIndex = 2; //B列が翻訳対象
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeRanges = spreadSheet.getSelection().getActiveRangeList().getRanges();

  for(var i =0; i< activeRanges.length; i++){    
    var activeRange = activeRanges[i];
    
    for(var column = 1; column <= activeRange.getNumColumns(); column++){
      for(var row = 1; row <= activeRange.getNumRows(); row++){
        var cell = activeRange.getCell(row, column);
        
        var rowIndex = cell.getRow();
        var columnIndex = cell.getColumn();
        var originalString = spreadSheet.getActiveSheet().getRange(rowIndex, translateTargetColumnIndex,1,1).getCell(1, 1).getValue();
        
        var translateFrom = spreadSheet.getActiveSheet().getRange(1, translateTargetColumnIndex,1,1).getCell(1, 1).getValue();
        var translateTo = spreadSheet.getActiveSheet().getRange(1, columnIndex,1,1).getCell(1, 1).getValue();
        
        var transStr = LanguageApp.translate(originalString, translateFrom, translateTo);
        cell.setValue(transStr);
        Utilities.sleep(1000); //連続してtranslateを呼び出すと警告が出るので対策
      }
    }
  }
  
}

function onOpen() {

var ui = SpreadsheetApp.getUi();

// メニューに追加

ui.createMenu('翻訳')

.addItem('選択範囲を翻訳', 'translate').addToUi();

}

function translate() {

var translateTargetColumnIndex = 2; //B列が翻訳対象

var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();

var activeRanges = spreadSheet.getSelection().getActiveRangeList().getRanges();

for(var i =0; i< activeRanges.length; i++){

var activeRange = activeRanges[i];

for(var column = 1; column <= activeRange.getNumColumns(); column++){

for(var row = 1; row <= activeRange.getNumRows(); row++){

var cell = activeRange.getCell(row, column);

var rowIndex = cell.getRow();

var columnIndex = cell.getColumn();

var originalString = spreadSheet.getActiveSheet().getRange(rowIndex, translateTargetColumnIndex,1,1).getCell(1, 1).getValue();

var translateFrom = spreadSheet.getActiveSheet().getRange(1, translateTargetColumnIndex,1,1).getCell(1, 1).getValue();

var translateTo = spreadSheet.getActiveSheet().getRange(1, columnIndex,1,1).getCell(1, 1).getValue();

var transStr = LanguageApp.translate(originalString, translateFrom, translateTo);

cell.setValue(transStr);

Utilities.sleep(1000); //連続してtranslateを呼び出すと警告が出るので対策

}

B列に英語の文言が入っていて、C列以降に英語から翻訳した翻訳文章が入る感じです。どの言語に翻訳するかは各列の1行目に国コード（たとえば日本語なら”ja”）を入力して指定します。

こんな感じです。

新しく文言を追加したいだけなのにシート開いただけで全部のセルが再翻訳走った挙句API制限に引っかかって全部のセルがエラー表示になっちゃうみたいな事態を回避できるようになりました。

ただし、手動で更新する形になったので、英語だけ文言を更新したけど翻訳の更新するの忘れたって事が無いように注意が必要です。

ちなみに、完成した翻訳エクセルファイルをUnityアプリで読み込むには、テラシュールブログさんのエクセルインポーターを使わせていただきました。

【Unity】Excel Importer Maker、xlsxに対応

死ぬほど便利です。

後はUnityでApplication.systemLanguageでパソコンの設定言語を取得して、それに応じて文言切り替えとかしてやればＯＫです。

【追記：2019/01/03】スクリプト実行時間6分制限を回避する

上記のスクリプトを運用している内に、また新しい問題が発生しました。

スクリプトの実行時間の最大6分制限に引っかかってしまう問題です。

１箇所翻訳する度に1秒のスリープを挟む必要があるので、360個のセルを翻訳した時点で6分経過してスクリプトが止まってしまいます。

回避策をググッたところ、スクリプトの中で自分自身を定期実行トリガーに登録して5分毎にスクリプトを中断→再開を繰り返す方法があるようです。

Google Apps Script で6分以上の処理をする

こちらの記事を参考にしてスクリプトを改良しました。

function onOpen() { 
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('翻訳')
      .addItem('選択範囲を翻訳', 'firstTrigger').addToUi();
}

//選択範囲をプロパティに保存
function firstTrigger() {
  var properties = PropertiesService.getScriptProperties();
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRanges = activeSheet.getSelection().getActiveRangeList().getRanges();
  properties.setProperty("activeRangeCount", activeRanges.length);
  for(var i=0; i< activeRanges.length; i++)
  {
    properties.setProperty("activeRange" + i, activeRanges[i].getA1Notation());
  }
  
  properties.setProperty("sheetId", SpreadsheetApp.getActiveSpreadsheet().getId());
  properties.setProperty("activeSheetName", SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName());
  
  triggerTranslate();
}

//5分経過毎に止めてトリガーで再起動
function triggerTranslate() {
  var properties = PropertiesService.getScriptProperties();
  var sheetId = properties.getProperty("sheetId");//スプレッドシートのID
  if(!sheetId){
    Logger.log("error sheetId");
    return;
  } 
  var sheetName = properties.getProperty("activeSheetName");//シート名
  if(!sheetName){
    Logger.log("error sheetName");
    return;
  }
  var triggerKey = "trigger";    //トリガーIDを保存するときに使用するkey
  var startTime = new Date();//開始時間
  
  //タイマーで起動するのでgetActiveSheet()などは使えない
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  var startRowKey = "startRow";  //何行目まで処理したかを保存するときに使用するkey
  var startColumnKey = "startColumn";  //何列目まで処理したかを保存するときに使用するkey
  var startActiveRangeIndexKey = "startRangeInex";
  
  var activeRangeCount = parseInt(properties.getProperty("activeRangeCount"));
  if(!activeRangeCount){
    Logger.log("error activeRangeCount");
    return;
  }
  
  //途中から実行した場合、ここに何行目まで実行したかが入る
  var startActiveRangeIndex = parseInt(properties.getProperty(startActiveRangeIndexKey));
  if(!startActiveRangeIndex){
    //初めて実行する場合はこっち
    startActiveRangeIndex = 0;
  }
  
  //途中から実行した場合、ここに何行目まで実行したかが入る
  var startRow = parseInt(properties.getProperty(startRowKey));
  if(!startRow){
    //初めて実行する場合はこっち
    startRow = 1;
  }
  
  //途中から実行した場合、ここに何行目まで実行したかが入る
  var startColumn = parseInt(properties.getProperty(startColumnKey));
  if(!startColumn){
    //初めて実行する場合はこっち
    startColumn = 1;
  }
  
  //メイン処理////////////////////
  var translateTargetColumnIndex = 2; //B列が翻訳対象

  for(var i =startActiveRangeIndex; i< activeRangeCount; i++){
    var activeRangeString = properties.getProperty("activeRange" + i);
    if(!activeRangeString){
      Logger.log("error activeRange" + i);
      return;
    }
    var activeRange = sheet.getRange(activeRangeString);
    
    for(var column = startColumn; column <= activeRange.getNumColumns(); column++){
      for(var row = startRow; row <= activeRange.getNumRows(); row++){
        Logger.log("きてる");
        
        var diff = parseInt((new Date() - startTime) / (1000 * 60));
        if(diff >= 5){
          Logger.log("中断");
          //5分経過していたら処理を中断
          properties.setProperty(startRowKey, row);  //何行まで処理したかを保存
          properties.setProperty(startColumnKey, column);  //何行まで処理したかを保存
          properties.setProperty(startActiveRangeIndexKey, i);  //何行まで処理したかを保存
          setTrigger(triggerKey, "triggerTranslate");          //トリガーを発行
          return;
        }
        
        var cell = activeRange.getCell(row, column);
        
        var rowIndex = cell.getRow();
        var columnIndex = cell.getColumn();
                Logger.log("cell:" + rowIndex + " " + columnIndex);
        var originalString = sheet.getRange(rowIndex, translateTargetColumnIndex,1,1).getCell(1, 1).getValue();
        
        var translateFrom = sheet.getRange(1, translateTargetColumnIndex,1,1).getCell(1, 1).getValue();
        var translateTo = sheet.getRange(1, columnIndex,1,1).getCell(1, 1).getValue();
        
        var transStr = LanguageApp.translate(originalString, translateFrom, translateTo);
        cell.setValue(transStr);
        Utilities.sleep(1000); //連続してtranslateを呼び出すと警告が出るので対策
      }
      startRow = 1;
    }
    startColumn = 1;
  }
  Logger.log("おわり");
  
  //全て実行終えたらトリガーと何行目まで実行したかを削除する
  deleteTrigger(triggerKey);
  properties.deleteProperty(startRowKey);
  properties.deleteProperty(startColumnKey);
  properties.deleteProperty(startActiveRangeIndexKey);
  properties.deleteProperty("activeRangeCount");
  for(var i=0; i< activeRangeCount; i++)
  {
    properties.deleteProperty("activeRange"+i);
  }
}

//指定したkeyに保存されているトリガーIDを使って、トリガーを削除する
function deleteTrigger(triggerKey) {
  var triggerId = PropertiesService.getScriptProperties().getProperty(triggerKey);
  
  if(!triggerId) return;
  
  ScriptApp.getProjectTriggers().filter(function(trigger){
    return trigger.getUniqueId() == triggerId;
  })
  .forEach(function(trigger) {
    ScriptApp.deleteTrigger(trigger);
  });
  PropertiesService.getScriptProperties().deleteProperty(triggerKey);
}
 
//トリガーを発行
function setTrigger(triggerKey, funcName){
  deleteTrigger(triggerKey);   //保存しているトリガーがあったら削除
  var dt = new Date();
  dt.setMinutes(dt.getMinutes() + 1);  //１分後に再実行
  var triggerId = ScriptApp.newTrigger(funcName).timeBased().at(dt).create().getUniqueId();
  //あとでトリガーを削除するためにトリガーIDを保存しておく
  PropertiesService.getScriptProperties().setProperty(triggerKey, triggerId);
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

function onOpen() {

var ui = SpreadsheetApp.getUi();

// Or DocumentApp or FormApp.

ui.createMenu('翻訳')

.addItem('選択範囲を翻訳', 'firstTrigger').addToUi();

}

//選択範囲をプロパティに保存

function firstTrigger() {

var properties = PropertiesService.getScriptProperties();

var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var activeRanges = activeSheet.getSelection().getActiveRangeList().getRanges();

properties.setProperty("activeRangeCount", activeRanges.length);

for(var i=0; i< activeRanges.length; i++)

{

properties.setProperty("activeRange" + i, activeRanges[i].getA1Notation());

}

properties.setProperty("sheetId", SpreadsheetApp.getActiveSpreadsheet().getId());

properties.setProperty("activeSheetName", SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName());

triggerTranslate();

}

//5分経過毎に止めてトリガーで再起動

function triggerTranslate() {

var properties = PropertiesService.getScriptProperties();

var sheetId = properties.getProperty("sheetId");//スプレッドシートのID

if(!sheetId){

Logger.log("error sheetId");

return;

}

var sheetName = properties.getProperty("activeSheetName");//シート名

if(!sheetName){

Logger.log("error sheetName");

return;

}

var triggerKey = "trigger"; //トリガーIDを保存するときに使用するkey

var startTime = new Date();//開始時間

//タイマーで起動するのでgetActiveSheet()などは使えない

var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);

var startRowKey = "startRow"; //何行目まで処理したかを保存するときに使用するkey

var startColumnKey = "startColumn"; //何列目まで処理したかを保存するときに使用するkey

var startActiveRangeIndexKey = "startRangeInex";

var activeRangeCount = parseInt(properties.getProperty("activeRangeCount"));

if(!activeRangeCount){

Logger.log("error activeRangeCount");

return;

}

//途中から実行した場合、ここに何行目まで実行したかが入る

var startActiveRangeIndex = parseInt(properties.getProperty(startActiveRangeIndexKey));

if(!startActiveRangeIndex){

//初めて実行する場合はこっち

startActiveRangeIndex = 0;

}

//途中から実行した場合、ここに何行目まで実行したかが入る

var startRow = parseInt(properties.getProperty(startRowKey));

if(!startRow){

//初めて実行する場合はこっち

startRow = 1;

}

//途中から実行した場合、ここに何行目まで実行したかが入る

var startColumn = parseInt(properties.getProperty(startColumnKey));

if(!startColumn){

//初めて実行する場合はこっち

startColumn = 1;

}

//メイン処理////////////////////

var translateTargetColumnIndex = 2; //B列が翻訳対象

for(var i =startActiveRangeIndex; i< activeRangeCount; i++){

var activeRangeString = properties.getProperty("activeRange" + i);

if(!activeRangeString){

Logger.log("error activeRange" + i);

return;

}

var activeRange = sheet.getRange(activeRangeString);

for(var column = startColumn; column <= activeRange.getNumColumns(); column++){

for(var row = startRow; row <= activeRange.getNumRows(); row++){

Logger.log("きてる");

var diff = parseInt((new Date() - startTime) / (1000 * 60));

if(diff >= 5){

Logger.log("中断");

//5分経過していたら処理を中断

properties.setProperty(startRowKey, row); //何行まで処理したかを保存

properties.setProperty(startColumnKey, column); //何行まで処理したかを保存

properties.setProperty(startActiveRangeIndexKey, i); //何行まで処理したかを保存

setTrigger(triggerKey, "triggerTranslate"); //トリガーを発行

return;

}

var cell = activeRange.getCell(row, column);

var rowIndex = cell.getRow();

var columnIndex = cell.getColumn();

Logger.log("cell:" + rowIndex + " " + columnIndex);

var originalString = sheet.getRange(rowIndex, translateTargetColumnIndex,1,1).getCell(1, 1).getValue();

var translateFrom = sheet.getRange(1, translateTargetColumnIndex,1,1).getCell(1, 1).getValue();

var translateTo = sheet.getRange(1, columnIndex,1,1).getCell(1, 1).getValue();

var transStr = LanguageApp.translate(originalString, translateFrom, translateTo);

cell.setValue(transStr);

Utilities.sleep(1000); //連続してtranslateを呼び出すと警告が出るので対策

}

startRow = 1;

}

startColumn = 1;

}

Logger.log("おわり");

//全て実行終えたらトリガーと何行目まで実行したかを削除する

deleteTrigger(triggerKey);

properties.deleteProperty(startRowKey);

properties.deleteProperty(startColumnKey);

properties.deleteProperty(startActiveRangeIndexKey);

properties.deleteProperty("activeRangeCount");

for(var i=0; i< activeRangeCount; i++)

{

properties.deleteProperty("activeRange"+i);

}

//指定したkeyに保存されているトリガーIDを使って、トリガーを削除する

function deleteTrigger(triggerKey) {

var triggerId = PropertiesService.getScriptProperties().getProperty(triggerKey);

if(!triggerId) return;

ScriptApp.getProjectTriggers().filter(function(trigger){

return trigger.getUniqueId() == triggerId;

})

.forEach(function(trigger) {

ScriptApp.deleteTrigger(trigger);

});

PropertiesService.getScriptProperties().deleteProperty(triggerKey);

}

//トリガーを発行

function setTrigger(triggerKey, funcName){

deleteTrigger(triggerKey); //保存しているトリガーがあったら削除

var dt = new Date();

dt.setMinutes(dt.getMinutes() + 1); //１分後に再実行

var triggerId = ScriptApp.newTrigger(funcName).timeBased().at(dt).create().getUniqueId();

//あとでトリガーを削除するためにトリガーIDを保存しておく

PropertiesService.getScriptProperties().setProperty(triggerKey, triggerId);

}

解説すると、GoogleAppScriptではPropertiesを使って変数を文字列に保存できます。（Unityで言うとPlayerPrefs）

Propertiesにどのセルまで処理が終わったかを保存しておくことで、トリガーでスクリプト再開した時に続きから処理を行うことができます。

面倒なのがトリガーから呼ばれた場合のスクリプトからはgetActiveSheetなどが使えず、選択範囲も取得できません。

なので、最初に選択範囲もPropertiesに保存しておく必要があります。トリガーからアクティブシートを取得できるように、シートのIDやシート名も同様にPropertiesに保存しておきます。

これで360個以上のセルを一度に処理できるようになりましたが、1日のtranslate実行回数制限の制約は依然としてあるので、制限に引っかかった時点でスクリプトは止まってしまいますのでご承知おきを。

ソイソフトウェアではゲームやアプリを作ってます！

Googleスプレッドシートでアプリの翻訳用データを作る

【追記：2019/01/03】スクリプト実行時間6分制限を回避する

関連

umiyuki

Latest posts

Text widget

最近の投稿

アーカイブ

全てのソフト

ソイソフトウェアではゲームやアプリを作ってます！

Googleスプレッドシートでアプリの翻訳用データを作る

【追記：2019/01/03】スクリプト実行時間6分制限を回避する

共有:

関連

umiyuki

Latest posts

Text widget

最近の投稿

アーカイブ

全てのソフト