Google App ScriptとGoogle Sheetで英語学習の環境を作った

2022-05-12

GAS

はじめに

英語の問題集を解くときに適当な紙に回答だけ書いて終わったら回答の答え合わせをするようなやり方をやっていました。繰り返し回答を埋めてやっていたのですが、答え合わせをするのが面倒になりまして……

紙に書かずともキーボードで打ち込むほうが楽だという結論に達しました。そして、PC、スマホ、iPadから使えるGoogle Sheetで使う方法について考えていました。

いくつか案を示して置きます。もし、より良いやり方をご存知の方はぜひともコメントください。

問題の自動採点

スマホと問題集があればとりあえず繰り返し解けるようにしたいなと思って、このやり方を試しました。

正答率がわかってしまいますがこんな感じになってます。

1回目の答え合わせする際に、正解列を埋めておけば繰り返せるようにしておきます。

あとは、簡単な数式を正否の所に埋めてやることで問題を解いたら即答え合わせができるようにしています。

=IF(B2=C2,"○","×")

あとは、試行回数を増やすたびに数式をコピーして少し手を加えれば何度でも使い回すことが出来る環境ができました。最初の準備は慣れれば数秒で終わるのでその手間さえ惜しまなければいいんでないでしょうか。

英単語の記録

改めてやり始めると、恥ずかしながらわからん単語が結構あるのでそれを記録していきます。高校生の頃は、わからん単語で単語帳を作って裏表に日本語と英語を書いたものですが、Google Sheetを使うと英語だけ打ち込めば日本語も得られます。これは地味に便利で気にいいってます。

問題集を解いて、少しでも迷ったらとりあえず単語を埋めていきました。後半は複数単語でもちゃんと変換されるのかなとか試してましたが、結構行けそうです。

C列にはこの数式を入れています。

=GOOGLETRANSLATE(A3)

なんとなく察しが付くと思いますが、Google翻訳がGoogle Sheetから使えます。これで英単語を入力すれば、日本語訳が得られます。

ココまでやると、品詞の情報も欲しくなってきました。

英単語の品詞の取得

Google App Script使って、品詞を取得していきましょう。

形態素解析すれば、品詞も得られるはずなのでそれをやっていきます。 Google Sheet単体ではそこまではできないため、ココからはGASを使っていきます。

GAS Hello World

まずはHello WorldとしてGASから書き込みを出来ることを確認します。関数はこんな感じにしました。

function myFunction() {
  var s = SpreadsheetApp.getActiveSheet();
  var cell = s.getRange(2, 2);
  cell.setValue("Hello World");
}

実行前

実行後

cellが更新されたら自動発火するようにする

続いて、単語が入力されたら自動で関数を叩いて値取得出来るようにしました。単語入力するたびに、実行するのは大変ですからね。

onEdit(e)という名称で関数を作ります。 getActiveCell()で選択されているCellを取得して、その値をgetValue()で取得します。

どこのCellを更新して発火しても困るので、if文でＢ列の更新に絞っています。

function onEdit(e) {
  var s = SpreadsheetApp.getActiveSheet();
  var cell = s.getActiveCell();
  var g = cell.getValue();

  if(cell.getColumn()==2){ //B列が更新されたらTriggerをかける
    cell.setValue(g + "更新しました");
  }
}

実行結果

B列を更新したら「更新しました」と付与しています。動画にしたのでYoutubeにあげておきました。

Google Cloud Platform を使った形態素解析

Cloud Natural LanguageをGASから呼び出す

Google Cloud を開いて登録します。

https://cloud.google.com/natural-language/docs

Cloud Natural Language APIを有効にしておきます。

プロジェクトの作成

DASHBOARD こちらから、新規でプロジェクトを作ります。認証情報を左メニューから選びAPI KEYを作成します。

認証情報を作成をクリックして準備完了です。

API キーが作れますのでその値をコピーします。

Google App Scriptで品詞取得関数を作成する

Google SheetからApps Scriptを開きます。

コード編集画面に到達したら準備完了です。

コード全文

せっかちな人のためにひとまず動かしているコードはこんな感じです。シート名を単語に設定して、A列に英単語を入れたらB列に表示されるようになっています。

API_KEYの所に、取得したキーを入れます。今は適当なやつ入れてます。

// Google Cloud Platform API KEY
var API_KEY = "APIajhasjkfhajbscjkasjhgfajkhfakjh";

function onChange() {
  Logger.log("LoggerLog start");
  var s = SpreadsheetApp.getActiveSheet();
  var cell = s.getActiveCell();
  var g = cell.getValue();

  Logger.log(cell);
  Logger.log(g);
  Logger.log(s.getName());

  if (s.getName() === '単語' && cell.getColumn() === 1) {
      var cell = s.getRange(cell.getRow(),4);
      cell.setValue(String(g));
      var cells = s.getRange(cell.getRow(),2);
      cells.setValue(analyzeText(g));
  }
}

function replacePartOfSpeech(str){
  console.log(str);

  var data = {
        "NOUN": "名詞(一般名詞 or 固有名詞)",
        "VERB": "動詞",
        "ADJ": "形容詞",
        "ADV": "副詞",
        "PRON": "代名詞",
        "CONJ": "接続詞",
        "DET": "限定詞",
        "ADP": "接置詞",
        "UNKNOWN": "不明",
        "NUM": "数詞",
        "PRT": "接辞",
        "AFFIX": "接辞?",
        "PUNCT": "句読点",
        "X": "その他",
  }

console.log(data[str]);
return data[str]
}

function analyzeText(g) {
  var text = String(g);
  var url = ['https://language.googleapis.com//v1/documents:analyzeSyntax?key=', API_KEY].join(
    ''
  );

  var data = {
    document: {
      language: 'en',
      type: 'PLAIN_TEXT',
      content: text,
    },
    encodingType: 'UTF8',
  };

  var params  = {
    contentType: 'application/json',
    method: 'post',
    payload: JSON.stringify(data),
  };

  Logger.log(text);

  var response = UrlFetchApp.fetch(url, params);
  Logger.log(response);

  var data = JSON.parse(response);
  Logger.log(data);

  var tag = data["tokens"][0]["partOfSpeech"]["tag"]
  Logger.log(tag);

  return replacePartOfSpeech(tag);
}

ハマりポイント

UrlFetchApp.fetchを呼び出す権限がありません

ログの見方全然わからずハマりました。

基本ですが画像のように、左のメニューから実行結果のログを一覧で見ることができます。

2022/05/13 0:51:50  エラー Exception: UrlFetchApp.fetch を呼び出す権限がありません。必要な権限: https://www.googleapis.com/auth/script.external_request
    at analyzeText(コード:73:30)
    at onEdit(コード:18:22)

このエラーはマニフェストファイルを編集することで解決できます。マニフェストファイルは左メニューの歯車から選んで、エディタに表示するようにしたら見れます。

チェックを入れると、appsscript.jsonが追加されているので、下記のように修正します。

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "MYSELF"
  },
   "oauthScopes": [
   "https://www.googleapis.com/auth/script.external_request"
  ]
}

ちなみに、onEdit()関数がよくTriggerに使われるのですが、UrlFetchAppが動かないので自前でトリガーを作る必要があります。