GoogleAppsScriptとスプレッドシートで英単語の一括翻訳

こんにちは、アドカレ二日目です。

Google Apps Scriptとは

Google Apps Script(GAS)とは、Googleが提供するJavaScriptライクな言語でGoogleの様々なサービスと連携できるプラットフォームです。
GAS向けに様々なAPIが提供されており、それにより自動でGoogleDriveを操作したり、スプレッドシートとカレンダーを連携させて予定表を作成したり、様々なことができます。

GASとスプレッドシートで英単語の一括翻訳

翻訳したいセルを選択し、メニューバーの操作のみで簡単に翻訳できるようにします。

GASをスプレッドシートを連携して作成する

GoogleDriveで扱うGASには主に、スプレッドシート等のアプリケーションに連携されたプロジェクトと独立したプロジェクトの二種類のタイプがあります。
独立したプロジェクトとしてGASを使用すると、特定のスプレッドシート等を取得する際に、そのアイテムのIDが必要となります。アプリケーションと連携した状態でGASを作成すると、現在使用しているシートといった指定で取得することできます。

スプレッドシートとGASを連携した状態で作成するには、連携したいスプレッドシートを開いて、拡張機能->Apps Scriptと操作します。

すると自動的に現在のスプレッドシートと連携した状態でGASが生成されます。

選択された範囲のセルを取得する

SpreadsheetApp.getActiveSheet()で現在開いているシートを取得できます。
sheet.getActiveRange()でシートから選択されているセルの範囲を取得できます。

// 現在開いているシートを取得
const sheet = SpreadsheetApp.getActiveSheet();
// シートから選択範囲を取得
const ramge = sheet.getActiveRange();

range.getCell(row, column)でシートからセルを取得できます。
cell.getValue()でセルの値を取得できます。

// 選択範囲の行ループ
for(let i = 1; i <= range.getNumRows(); i++ ){
    // 選択範囲の列ループ
    for(let j = 1; j <= range.getNumColumns(); j++ ){
        // セルの中身を表示
        console.log(range.getCell(i, j).getValue());
    }
}

セルの値を更新する

range.setValues()で範囲のセルを更新できます。
注意点として、選択範囲とセットするセルのサイズは同じである必要があります。
また、セットする範囲が縦一列であってもサイズを合わせる必要があるため、[["一行目"], ["二行目"], ["三行目"]]のように要素1の配列の多次元配列を作る必要があります。

values = [["1-1", "1-2"], ["2-1", "2-2"]];
range.setValues(values);

GASでGoogle翻訳

GASでGoogle翻訳を利用するのはとても簡単で、提供されているLanguageApp.translateメソッドを叩くだけです。

// LanguageApp.translate(翻訳したい文章, 翻訳前の言語, 翻訳後の言語）
LanguageApp.translate("こんにちは", "Ja", "En"); // hello

メニューにボタンを追加

スプレッドシートの上部のメニューに画像のようなボタンを追加します。

スプレッドシートを開いた際に処理を実行する際には、function onOpen()を定義するだけです。その中にメニューを追加する処理を記述していくことになります。

ボタンを追加するには、ui.createMenu()でメニューを作成し、menu.addItem()でボタンを作成します。
最後に、menu.addToUi()でメニューをUIに適応させ、スプレッドシートにメニューを表示することができます。
また、menu.addItem()はmenuを返すので、menu.addItem().addItem()のようなクールな記述をすることができます。
menu.addItem()は第一引数にボタンの表示名、第二引数にボタンが押された際にコールバックする関数名を指定します。

function onOpen() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu("翻訳").addItem("日本語->英語", "onClickJ2E").addItem("英語->日本語", "onClickE2J").addToUi();
}
function onClickJ2E(){
    console.log("日本語->英語 ボタンが押されました。");
}
function onClickE2J(){
    console.log("英語->日本語 ボタンが押されました。");
}

まとめ

最後にコード全体を載せておきます

// スプレッドシートを開いた際に実行される
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('翻訳').addItem('日本語->英語', 'onClickJ2E').addItem('英語->日本語', 'onClickE2J').addToUi();
}
function onClickJ2E(){
  selectTranslate("Ja","En");
}
function onClickE2J(){
  selectTranslate("En","Ja");
}

// 選択範囲を翻訳する
function selectTranslate(fromLang, toLang) {
  // 選択された範囲
  const sheet = SpreadsheetApp.getActiveSheet();
  const selectedRange = sheet.getActiveRange();
  
  // 翻訳結果の配列
  const translatedList = [];

  // 選択範囲の行ループ
  for(let i = 1; i <= selectedRange.getNumRows(); i++ ){
    // セルの中身を取得
    const cellValue = selectedRange.getCell(i, 1).getValue();
    if (cellValue !== "") {
      // 翻訳
      translatedList.push([LanguageApp.translate(cellValue, fromLang, toLang).toLowerCase()])
    }else{
      translatedList.push([""]);
    }
  }

  // セルを翻訳結果で更新
  sheet.getRange(selectedRange.getRow(), selectedRange.getColumn() + 1, selectedRange.getLastRow() - selectedRange.getRow() + 1).setValues(translatedList);
}