概要

先日GoogleAppSheetで、本を何巻まで読んだか管理するアプリ（並べ替え,最近読んだ,書籍検索）が出来たので投稿した・・・ところが、実際に使ってみたら思わぬところで不具合が見つかり、修正したのでご報告。

先日の投稿：【GAS】AppSheetで本を何巻まで読んだか管理するアプリを作成（並べ替え,最近読んだ,書籍検索）

不具合とは

まずこのアプリだけども、新しく読んだ本と既読巻数などを登録すると、その本のGoogle書籍検索URL、読んだ日付を自動で生成し、スプレッドシートの一覧に追加。自動でタイトル順に並べ替えを行う。

AppSheet側には、タイトル順にソートしたビューと、最近読んだ順にソートしたビューがある。

で、不具合と言うのが、読み終わった本を一覧から削除したところ、スプレッドシートの並び替えの際、削除したことによって出来た空欄に移動してきた本に対して、書籍検索URLと読んだ日付が更新されてしまい、AppSheetの読んだ順の並びが間違えた並びになってしまったorz

原因

そもそもの動作は、セルの変更をトリガーに、変更があった書籍に対して[書籍検索URL生成][更新日時入力][並べ替え]が行われる。

新しい書籍が登録された場合の並べ替えはセルの変更と判定されていないようだが、書籍削除によってできた空欄を詰める並び替えだと、空欄に入った書籍が変更のあった書籍と判定されてしまう模様。

そのため書籍を削除すると、空欄を詰める形で並び変わった書籍に対して[書籍検索URL生成][更新日時入力][並べ替え]が行われてしまった。

対策

書籍が減った場合だけ不具合が起こる。更に、書籍が減って並び替えが行われる所までは期待通りの動作で、並び変わった後に余計な日付更新があるのが良くない。

スプレッドシート視点で考えると、空欄が詰まった時に余計な日付変更があるので、並べ替え前の最終行より並べ替え後の最終行が少なくなった場合（空欄が詰まった場合）、日付変更を行わないようにしたい。

具体的には、並べ替えが行われる度に最終行を記録し、スクリプトが実行される度に前回の最終行を参照し、最終行が減っていたら動作を終了すればOK！

と言うわけで、最終行を書き出すためのシート「シート2」を作成し、コードを追記。変更後のコードがコチラ！

function onChange(e) {

  //更新されたセルを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
  var fs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート2');
  var ActCell = ss.getActiveCell();

  //今回の最終行と前回の最終行を取得
  var oftimeRow = ss.getLastRow();
  var previousRow = fs.getRange(1, 1).getValue();
  
  //行が減っていたら終了＆最終行を上書き
  var dis = oftimeRow - previousRow;
  if(dis<0){
    fs.getRange(1, 1).setValue(oftimeRow);
    return;
  }

  //セルの行列を取得
  var CellRow = ActCell.getRow();
  var CellCol = ActCell.getColumn();

  //セルの内容を格納
  var Cell = ss.getRange(CellRow, CellCol).getValue();

  //自動実行させたくない処理をif文で回避
  if(Cell){ if(CellRow!=1){ if(CellCol<4){

    //A列の文字列でグーグル書籍検索するURLを生成 E列に入力
    var title = ss.getRange(CellRow, 1).getValue();
    title = title.replace(" ", "%20");
    ss.getRange(CellRow, 5).setValue('https://www.google.com/search?q='+title+'&tbm=bks');

    //F列に更新日時を入力
    var date = new Date();
    var today = Utilities.formatDate(date, 'JST', 'yyyy/MM/dd');
    ss.getRange(CellRow, 6).setValue(today);

  } } }

  //B列で並べ替え 行を削除した時も並べ替えたいのでifの外
  var range = ss.getRange("A2:H");
  range.sort([{column:2, ascending:true}]);

  //実行後の最終行をシート2,A1セルに入力
  var lastRow = ss.getLastRow();
  fs.getRange(1, 1).setValue(lastRow);

}

素人が作ったアプリだけあって、こういった不具合はまだありそう^^;

関連ページ

【GAS】AppSheetで本を何巻まで読んだか管理するアプリを作成（並べ替え,最近読んだ,書籍検索）

【GAS】AppSheetからスプレッドシートを操作するとonEditが動かない問題の対処法」

おしまい