【GAS】Google Apps Script 活用事例　 Spreadsheet全体をまるっと一括翻訳するスクリプト

「Google Slideの翻訳のヤツ.....シートでも出来ない？」

時間の問題だと思っていました。こういう日がいつか来ると.....
ネットで拾ったスクリプトで1列のみの翻訳は使っていましたが、シート全体では試してみた事がありませんでした。

function convertToJapanese() {
 
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('和訳');
 const range       = sheet.getDataRange();
 const values      = sheet.getDataRange().getValues();
 const lastRow   = sheet.getLastRow();
 const results      = [];
 
 
 /*見出しを省くため*/
 for(let i = 1; i < values.length; i++){
   const sourceColumn  = values[i][0];
   const exportColumn  = LanguageApp.translate(sourceColumn, "en", "ja");
   
   results.push([exportColumn]);
 }//for_i
 
 console.log(results);
 
 /*行番号は、配列番号と異なる事に注意が必要 行幅の自動調節*/
 sheet.getRange(2, 2, lastRow-1).setValues(results);


 sheet.autoResizeRows(2, lastRow);
 /*フォントを均一に直し、セルを折り返しにする。*/
 range.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP)
 .setFontFamily('Meiryo').setFontSize(10)
 .setVerticalAlignment('middle');
 
 console.log(values);
 
}

for(let i = 0; i < values.length; i++)　毎度お馴染みの、for文だと、1行目、2行目、3行目といった具合に縦方向に進んでいきます。そして、翻訳対象の単語を取得して、配列に入れて2列目に書き出して上げるという感じです。

つまり、上記のスクリプトだと、縦方向に存在するデータしか翻訳が出来ません。そのため、横方向の検索が加わるとハードルがぐっと上がります。values[0].length;1行目の配列数 = 列数　という事で、for文を入れ子にして、2重に回していく必要があります。 

そこで考えた！！縦方向の翻訳を列の数だけ繰り返せばいいんじゃね？　3列なら3回繰り返す！！

なんて短絡的な.....シートが図のように3列あったら、3列分同じ処理を繰り返せば、上手くいくのでは？と考えました。

確かに、この方法でも出来なくはない。が、しかし、汎用性が全く無い。列の数だけ空の配列を用意しなきゃいけないのと、switch文の分岐が増えるからです。

function translateJapanese() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('シート1');
 const values      = sheet.getDataRange().getValues();
 
 console.log(values);
 
 let results       = [[],[],[]];//３列分の空の配列を作成する。
 
 for (let i = 0; i < values.length; i++) {
   for (let j = 0; j < values[0].length; j++) {
     if(values[i][0] === ''){continue}
     
     switch(j){
       case 0: results[j].push(LanguageApp.translate(values[i][j], "ja", "en"));
         break;
       case 1: results[j].push(LanguageApp.translate(values[i][j], "ja", "en"));
         break;
       case 2: results[j].push(LanguageApp.translate(values[i][j], "ja", "en"));
         break;
     }
   }//for_j
 }//for_i
 
 console.log(results);

 const sheet2         = spreadsheet.getSheetByName('シート2');
 const _              = Underscore.load();
 const arrayTranspose = _.zip.apply(_, results);
 sheet2.getRange(1, 1, arrayTranspose.length, arrayTranspose[0].length).setValues(arrayTranspose);
}//end


/*
自力解決がヘビーなので、既存のライブラリを使う。
underscore for GAS
リソース > ライブラリ
project key
M3i7wmUA_5n0NSEaa6NnNqOBao7QLBR4j
*/
 

getDataRange()で取得出来る配列は、横方向

[['東証上場一覧','コード','銘柄名','市場','商品区分'.......]]

上記の図であれば、データが1行ずつ横並びになっています。配列は横方向で取得しているのに、翻訳が縦方向でしか出来ない事に気づきました。上記スクリプトでは j 列が、1列目だったら、2列目だったらみたいな感じで処理Switch文で分岐しています。

列数が変わったら、スクリプトを直接編集して、もし仮に8列あったら、Switch文の分岐を8回書かなくてはいけません......。イケてない。あまりにもイケてない。

そこに救いの手を差し伸べてくれたのが、etauさん。mapを使ったこんなのはどう？と提案してくれました。

流石、etauさん！！

function sheetTranslation () {
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('テストデータ');
 const values       = sheet.getDataRange().getValues();
 let newValues      = [];
 
 for (const record of values) {
   if(record === ''){continue}
   newValues.push(record.map(element => LanguageApp.translate(element, "ja","en")));
   
 }//for
 console.log(newValues);
 const targetSheet = spreadsheet.getSheetByName('翻訳結果');
 targetSheet.getRange(1, 1, newValues.length, newValues[0].length).setValues(newValues);
 
}//end

すっすげー......。

2020.04.24 追記　やっている事はコレと一緒

function myFunction() {
 const array   = ['犬','猫','赤ちゃん'];
 let newValues = [];
 for(let i = 0; i < array.length; i++){
   const value = LanguageApp.translate(array[i], 'ja', 'en');
   newValues.push(value);
 }
 console.log(newValues)
}

一次元配列の中身を取り出して、一個一個翻訳していき、それを新たな配列に追加していく。

mapを使いこなせるようになりたい

多分、配列操作で、出来る事がめっちゃ増えるなぁ.....。

Array.prototype.map() - JavaScript | MDN

Googleフォームの質問の選択肢をスプレッドシートのデータから生成するGAS関数の作り方GASでGoogleフォームを一気に作成するツールを作成しています。今回はGoogleフォームの質問の選択肢をスプレッドシtonari-it.com