写真からテキストを取得し、スプレッドシート等にまとめる方法

はじめに

写真からテキストを抽出し、スプレッドシートへまとめる方法について、実例に沿って解説します。
今回の事例のほかにも、医療系事務で紙資料をエクセルにまとめていくといったことにも使えるかもしれません。
既出やん・・・kintoneでもええやん・・・と思われるかもしれませんが、１つのやり方として参考にしていただければと思います。

目次

1. 問題の背景
2. 写真からSpreadSheetまでの流れ
3. SpreadSheet上での処理
4. Google Apps Scriptの機能を使う場合
5. SpreadSheetの関数だけを使う場合
6. 完成
7. 最後に

1. 問題の背景

とある企業の採用へのエントリーした時、困ったことが起きました。
それは「履修履歴データベース」を使って成績をアップロードすることです。

そのデータベースでは講義名を一括で入力して複数登録できるのですが、、、、

• 既卒なので学生用ページに入ることができない。つまり履修データをWEB上で入手し、エクセル等にコピペするのは不可能。
• 紙ベースの成績証明書しか入手できないので、全部手打ちをせざるを得ない。
• データベースサイトの「データ登録」では60分経過すると記入した内容が失われ、一時保存もできないので一括入力しか方法が無い。

紙ベースの資料から一旦全部メモ帳に保存して・・・とやっても良いのですが、写真からテキストを取り出してエクセル等にまとめてみます。

目標

科目名、年度、単位、評価　の４項目でシートにまとめます。

• データ化したい写真
  
  資料では、英単語・漢字・カタカナ・ひらがな・記号()・数字Ⅰ,2 が記載されています。

今回の場合、写真は全体を撮影するのではなく、左半分と右半分の2つに分けて撮影して取り込むと後々処理しやすいです。

---

• 整理したあとのシート
  
  年度には2021や2015などの4桁の数字が入っています。

2. 写真からSpreadSheetまでの流れ

• 写真を撮る
• Google Keepの「メモ」のところへ写真をアップロードする
• GoogleKeep「画像のテキストを抽出」→Ctrl+Aでテキストを全選択&Ctrl+cでコピー
• Google SpreadSheet内に、抽出したテキストをペーストする
  

3. SpreadSheet上での処理

テキストをある程度整える

（いちいち科目名や年度などを伏せたりするのが面倒なので加工を諦めました。）

テキストを見てみると大体の規則として上から順に、科目名・年度・単位数+評価に並んでいるという法則が見て取れます。
しかし、抽出したテキストにはいくつか問題があります。
科目名や年度、単位数+評価が正しい順で並んでいなかったり、単位数+評価のテキスト内に、余計な数字や記号(1や:)が入っていたりします。

この規則を守ったうえで余計な数字や記号さえ入っていないければ、INDEX関数とROW関数を使って処理できそうなので、そこまでは手作業で修正します。
なお、科目名の誤字や、単位数+評価の中にある空白などは一旦無視します。

4. Google Apps Scriptの機能を使う場合

A2セルに修正したテキストをペーストし、下記の処理を実行すると自動的に欲しい結果が得られます。

シート名は"test"の部分を置き換えてください。
また、行のrangeは250としていますが、適宜変更してください。

// スプレッドシートから「test」という名前のシートを取得. 
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test");

javascriptで自動処理する場合のコード

function processData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test");

  var range = sheet.getRange("D2:D250");
  range.setValues(range.getValues().map(function(row, i) {
    return [sheet.getRange(3 * i + 2, 1).getValue()];
  }));

  range = sheet.getRange("E2:E250");
  range.setValues(range.getValues().map(function(row, i) {
    return [sheet.getRange(3 * i + 3, 1).getValue()];
  }));

  range = sheet.getRange("K2:K250");
  range.setValues(range.getValues().map(function(row, i) {
    return [sheet.getRange(3 * i + 4, 1).getValue()];
  }));

  range = sheet.getRange("K2:K250");
  range.copyTo(sheet.getRange("L2:L250"), {contentsOnly:true});

  range = sheet.getRange("L2:L250");
  range.setValues(range.getValues().map(function(row) {
    return [row[0].replace(/\s/g, "").replace(/　/g, "")];
  }));

  range = sheet.getRange("M2:M250");
  range.setValues(range.getValues().map(function(row, i) {
    return [sheet.getRange("L" + (i + 2)).getValue()];
  }));

  var lastRow = sheet.getLastRow(); 
  range = sheet.getRange("F2:F"+lastRow); // 
  range.setValues(range.getValues().map(function(row, i) {
    return [sheet.getRange("M" + (i + 2)).getValue().charAt(0)];
  }));

  range = sheet.getRange("G2:G"+lastRow); // 
  range.setValues(range.getValues().map(function(row, i) {
    return [sheet.getRange("M" + (i + 2)).getValue().substr(1)];
  }));
}

5. SpreadSheetの関数だけを使う場合

A2セルに修正したテキストをペーストするまでは、Javascriptで処理する場合と同じです。

INDEX関数とROW関数を使って項目ごとに分ける

科目名はA列において、2,5,8,11・・・と登場しているので、A2から3行ごとに要素を取り出す。
年度と単位数+評価も、それぞれA3から3行ごと、A4から3行ごとに要素を取り出します。
ただしこの後の処理として、単位数+評価に空白を取り除く処理を実行したいので、ひとまず別の離れた列に格納しています。

D2セル=INDEX(A:A, 3*(ROW()-2)+2)
E2セル=INDEX(A:A, 3*(ROW()-2)+3)
I2セル=INDEX(A:A, 3*(ROW()-2)+4)

入力したらセルの右下にカーソルを合わせて、十字（フィルハンドル）をつかみながら下へドラッグしてください。

SUBSTITUTE関数で空白を取り除く

空白を取り除く処理を行う前に、一度「関数で表現されていない数値」に変換します。
画面上では数値として表示されていますが、実際にセルの中に入っているのは「関数」だからです。

I2セルから下の要素をすべてコピーし、右隣のJ2セルに「値のみを張り付け」を行います。
J2セルに値のみが張り付けられたら、K2セルに以下の関数を入力します。

K2セル=SUBSTITUTE(SUBSTITUTE(J2, CHAR(12288), ""), " ", "")

入力したらセルの右下にカーソルを合わせて、十字（フィルハンドル）をつかみながら下へドラッグしてください。

LEFT関数,RIGHT関数でさらに項目を分ける

今のままでは単位数+評価のセットの状態なので、単位数と評価をそれぞれ切り離して別の列に格納します。
F2セルに対して、K2セルの1文字目を返します。
G2セルに対して、K2セルの2文字目以降を返します。

F2セル=LEFT(K2,1)
G2セル=RIGHT(K2, LEN(K2)-1)

入力したらセルの右下にカーソルを合わせて、十字（フィルハンドル）をつかみながら下へドラッグしてください。

6. 完成

セルの中身が関数状態の科目名や年度の部分を扱いたい場合は、一度コピーして別のセルに「値のみ張り付け」をし、文字化けしている部分を多少修正して終わりです。

単位数合計の確認などを行いたい場合はSUM関数ではなくSUMPRODUCT関数を使うと良いでしょう。

7. 最後に

データの下処理をするセクションで属人的な部分を排除しきれませんでしたが、写真からテキストが取り込まれた状態の精度は結構高かったので、全部手打ちでシートに転記していくよりはマシなのではないかと思います。

後から分かったのですが、「履修履歴データベース」で一括入力できるものはどうやら「科目名」のみで、単位数や評価の一括入力には対応していませんでした。。。。
(結局全部手動でポチポチしました。)

思わぬところで時間がかかってしまったので、これを機に今後の誰かが救われますように。