ホーム ≫ プログラミング ≫

実践・連作障害回避スケジューリングツールを作る③

2021-06-12

GAS ツール プログラミング

B!

限られた農地を上手に活用する方法をプログラム側から考える。
前回はスプレッドシートの準備をしました。

第三回目は実際に中身を書いていこうと思います！
長くなったので何度かに分けてお送りします。

実践・連作障害回避スケジューリングツールを作る②

ゲームプログラマーの農業日誌

目次



ソースファイルの準備

まずは「main」、「define」という名前の.gsファイルを追加していきます。

名前に関しては自分が管理しやすい名前で問題ありません。

私は単純に自分自身が後から見た時にわかりやすい＆メンテナンスしやすくしたいと思っているのでプロジェクトが大きいときは別ファイルにしています。
ごく小さいものならいちいち分けたりしません。

スクリプト導入までの手順はこちらで

【GAS】GAS（Google App Script）の導入手順

Google App Script 導入手順

ゲームプログラマーの農業日誌

新規プロジェクトを開始すると初期状態で「コード.gs」というファイルにmyFunctionが記述された状態になっていると思います。
簡単なものや特に宗教やこだわりがなければこのまま使用してもらって結構です。

私は「main」と「define」が欲しいので、
元から存在するソースファイル「コード.gs」の名前を変更して「main」とし、
「define」を追加します。
追加すると「無題.gs」というファイルが増えるので名前の変更をしておきます。
ついでにファイルの中身を両方ともまっさらにしておきます！
ちなみに中身はこんな感じで記載分けします。（私はね）

「main」…実処理を記入します。
「define」…定数を記入します。

あとは自分がよく使う処理をまとめたファイルなんかを追加して、他プロジェクトで使いまわしたりしてもいいかもしれません。

何度も言いますが、プログラムは「自由」なので、自分が管理するなら自分がわかりやすいものが良いです。
多人数で管理するならちゃんとルールを決めて楽しく計画的に仲良く作りましょう。（何目線…）

今回のソースファイル全文

とりあえず今回解説分までのソースコード全文です。

define

var SheetID                   = '自分のスプレッドシートのID';

// -------------------------------------------------------------------------------------------------------
// シート名.
// -------------------------------------------------------------------------------------------------------
var SheetNameMng              = 'mng';    // .
var SheetNameData             = 'data';   // .
var SheetNameDefine           = 'define'; // .
var SheetNameTmp              = 'tmp';    // .

// -------------------------------------------------------------------------------------------------------
// .
// -------------------------------------------------------------------------------------------------------
var OneYearToWeeks            = 48;       // 1年あたり週数.
var OneYearToMonth            = 12;       // 1年あたり月数.
var OneMonthToWeeks           = 4;        // 1月あたり週数.

// -------------------------------------------------------------------------------------------------------
// mngシート.
// -------------------------------------------------------------------------------------------------------
// 基本設定.
var MngConfigSheetName        = 'D6';     // 編集シート名.
var MngConfigPlace            = 'D7';     // 作物を作る場所.

// 基本情報.
var MngInfoTitle              = 'H5';     // タイトル.
var MngInfoSubject            = 'I6';     // 科目.
var MngInfoSowingSeed         = 'I7';     // 種まき開始時期.
var MngInfoPlanting           = 'I8';     // 定植開始時期.
var MngInfoCropRot            = 'I9';     // 輪作年限.
var MngInfoStartWeek          = 'I10';    // 作業開始週.
var MngInfoGoodX              = 11;       // 相性が良い作物.
var MngInfoGoodY              = 6;        //

// 選択リスト.
var MngSelectStartY           = 17;       // 開始 Y.
var MngSelectNameX            = 3;        // 名前 X.
var MngSelectSubjectX         = 4;        // 科目 X.

// 育てるリスト.
var MngListStartY             = 17;       // 開始 Y.
var MngListIDX                = 8;        // ID X.
var MngListNameX              = 9;        // 作物名 X.
var MngListSubjectX           = 10;       // 科目 X.
var MngListWorkStartX         = 11;       // 作業開始 X.
var MngListWorkEndX           = 12;       // 収穫 X.
var MngListWorkEndWeekX       = 13;       // 全作業終了週ID.
var MngListCropRotX           = 14;       // 輪作年限.
var MngListCropRotRestX       = 15;       // 輪作年限残り.
var MngListIntervalX          = 16;       // 空期間 X.
var MngListIsSowingSeedX      = 17;       // 種まきかどうか.

// -------------------------------------------------------------------------------------------------------
// dataシート.
// -------------------------------------------------------------------------------------------------------
var DataDataStartY            = 3;        // 開始 Y.
var DataNameX                 = 1;        // 名前 X.
var DataVarietyX              = 2;        // 品種 X.
var DataPlaceX                = 3;        // 作る場所.
// 種まき.
var DataSowingSeedStartX      = 4;        // 開始月.
var DataSowingSeedStartWeekX  = 5;        // 開始週.
var DataSowingSeedIntervalX   = 6;        // 期間.
var DataSowingSeedResultX     = 7;        // 週番号計算結果.
// 定植.
var DataPlantingStartX        = 8;        // 開始月.
var DataPlantingStartWeekX    = 9;        // 開始週.
var DataPlantingIntervalX     = 10;       // 期間.
var DataPlantingResultX       = 11;       // 週番号計算結果.
// 収穫.
var DataHarvestStartX         = 12;       // 開始月.
var DataHarvestStartWeekX     = 13;       // 開始週.
var DataHarvestIntervalX      = 14;       // 期間.
var DataHarvestResultX        = 15;       // 週番号計算結果.
// 栽培期間.
var DataCultivationPeriodX    = 16;       // 栽培期間（月）.
var DataCultivationPeriodResultX = 17;    // 栽培期間計算結果（週）.
var DataCultivationPeriodEndX = 18;       // 栽培終了週. 

// -------------------------------------------------------------------------------------------------------
// defineシート.
// -------------------------------------------------------------------------------------------------------
var DefineDataStartY          = 2;        // 開始 Y.
var DefineSubjectX            = 1;        // 科目.
var DefineVarietyX            = 2;        // 品種.
var DefineVarietySubjectX     = 3;        // 品種に対しての科目 X.
var DefinepHX                 = 4;        // pH X.
// 輪作年限.
var DefineCropRotX            = 5;        // 輪作年限.
var DefineCropRotResultX      = 6;        // 期間計算結果（週）.
// 相性.
var DefineGoodX               = 7;        // 相性のよい作物.
var DefineGoodNum             = 6;        // 相性リスト個数.

main

// -------------------------------------------------------------------------------------------------------
// エントリーポイント.
// -------------------------------------------------------------------------------------------------------
// -------------------------------------------------------------------------------------------------------
// 「選択肢リセット」ボタンを押した.
// -------------------------------------------------------------------------------------------------------
function PushReset() {
  
  // シートの取得.
  var mngSheet = SpreadsheetApp.openById( SheetID ).getSheetByName( SheetNameMng );
  var dataSheet = SpreadsheetApp.openById( SheetID ).getSheetByName( SheetNameData );
  var defineSheet = SpreadsheetApp.openById( SheetID ).getSheetByName( SheetNameDefine );
  
  if ( mngSheet === null ) { return; }
  if ( dataSheet === null ) { return; }
  if ( defineSheet === null ) { return; }
  
  // いったんリストをリセット.
  RangeClear( mngSheet, MngSelectNameX, MngSelectStartY, 1, 0 );
  RangeClear( mngSheet, MngSelectSubjectX, MngSelectStartY, 1, 0, );
  
  var dataIdx;

  // 育てるリストで輪作年限が残っている科目を抽出.
  var SubjectArr = new Array();
  dataIdx = GetLastRow( mngSheet, MngListIDX ) + 1;
  for ( var y = MngListStartY; y < dataIdx; ++y ) {
    if ( mngSheet.getRange( y, MngListCropRotRestX ).getValue() === 0 ) { continue; }
    SubjectArr.push( mngSheet.getRange( y, MngListSubjectX ).getValue() );
  }
  
  dataIdx = MngSelectStartY;
  
  var isSuccess = true;
  var subject, place;
  var dataEnd = GetLastRow( dataSheet, DataNameX ) + 1;
  
  for ( var y = DataDataStartY; y < dataEnd; ++y ) {
    
    isSuccess = true;
    
    // ① 育てるリストの輪作年限が残っている科目の作物はリストアップしない.
    subject = GetDataToSubject( dataSheet, defineSheet, y, DefineVarietySubjectX );
    
    for ( var i = 0; i < SubjectArr.length; ++i ) {
      if ( subject !== SubjectArr[ i ] ) { continue; }
      // 一覧にあったのでリストアップしない.
      isSuccess = false;
      break;
    }
    
    if ( isSuccess !== true ) { continue; }
    
    // ② 栽培場所をチェックする.
    place = dataSheet.getRange( y, DataPlaceX ).getValue();
    if ( place !== mngSheet.getRange( MngConfigPlace ).getValue() ) { continue; }
    
    // 選択肢リストに追加.
    mngSheet.getRange( dataIdx, MngSelectNameX ).setValue( dataSheet.getRange( y, DataNameX ).getValue() );
    mngSheet.getRange( dataIdx, MngSelectSubjectX ).setValue( subject );
    
    ++dataIdx;
  }
  
  return;
}

// -------------------------------------------------------------------------------------------------------
// 「調べる」ボタンを押した.
// -------------------------------------------------------------------------------------------------------
function PushSearch() {
  
  // シートの取得.
  var mngSheet = SpreadsheetApp.openById( SheetID ).getSheetByName( SheetNameMng );
  var dataSheet = SpreadsheetApp.openById( SheetID ).getSheetByName( SheetNameData );
  var defineSheet = SpreadsheetApp.openById( SheetID ).getSheetByName( SheetNameDefine );
  
  if ( mngSheet === null ) { return; }
  if ( dataSheet === null ) { return; }
  if ( defineSheet === null ) { return; }
  
  // 作物名が存在するかどうかチェック.
  var dataID = SearchDataSheet( dataSheet );
  if ( dataID === -1 ) { return; }
  
  
  // 基本情報の入力.
  
  var name =  dataSheet.getRange( dataID, DataNameX ).getValue();
  
  // タイトル.
  mngSheet.getRange( MngInfoTitle ).setValue( name + 'の基本情報' );
  // 科目.
  mngSheet.getRange( MngInfoSubject ).setValue(
    GetDataToSubject( dataSheet, defineSheet, dataID, DefineVarietySubjectX ) );
  // 種まき・作業開始週.
  mngSheet.getRange( MngInfoStartWeek ).setValue( '' );
  var num = dataSheet.getRange( dataID, DataSowingSeedStartX ).getValue();
  if ( num !== 0 ) {
    mngSheet.getRange( MngInfoSowingSeed ).setValue( num + '月' );
    // 作業開始週.
    mngSheet.getRange( MngInfoStartWeek ).setValue(
      dataSheet.getRange( dataID, DataSowingSeedResultX ).getValue() + '週目' );
  } else {
    mngSheet.getRange( MngInfoSowingSeed ).setValue( '' );
  }
  // 定植.
  num = dataSheet.getRange( dataID, DataPlantingStartX ).getValue();
  if ( num !== 0 ) {
    mngSheet.getRange( MngInfoPlanting ).setValue( num + '月' );
    // 作業開始週.
    mngSheet.getRange( MngInfoStartWeek ).setValue(
      dataSheet.getRange( dataID, DataPlantingResultX ).getValue() + '週目' );
  } else {
    mngSheet.getRange( MngInfoPlanting ).setValue( '' );
  }
  // 輪作年限.
  mngSheet.getRange( MngInfoCropRot ).setValue(
    GetDataToSubject( dataSheet, defineSheet, dataID, DefineCropRotX ) + '年' );
  // 後に植えると相性の良い作物.
  RangeClear( mngSheet, MngInfoGoodX, MngInfoGoodY, 1, DefineGoodNum );
  for ( var x = 0; x < DefineGoodNum; ++x ) {
  name = GetDataToSubject( dataSheet, defineSheet, dataID, (DefineGoodX + x) );
    if ( name === 'なし' ) { continue; }
    mngSheet.getRange( (MngInfoGoodY + x), MngInfoGoodX ).setValue( name );   
  }

  return;  
}

// -------------------------------------------------------------------------------------------------------
// private.
// -------------------------------------------------------------------------------------------------------
// -------------------------------------------------------------------------------------------------------
// 指定した列のデータが入力されている最終行(Y)を取得する.
// -------------------------------------------------------------------------------------------------------
function GetLastRow( sheet, idx ) {
  return sheet.getRange(sheet.getMaxRows(), idx).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
}
// -----------------------------------------------------------------------------
// 指定した行のデータが入力されている最終行を取得する.
// -----------------------------------------------------------------------------
function GetLastColL( sheet, idx ) {
  return sheet.getRange(idx,sheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
}
// -------------------------------------------------------------------------------------------------------
// アクティブ（選択状態）なセルを取得（シングル）.
// -------------------------------------------------------------------------------------------------------
function GetActiveCell( ) {
  var spreadSheet = SpreadsheetApp.getActive();
  
  if ( spreadSheet === null ) { return null; }
  
  var sheet = spreadSheet.getActiveSheet();
  
  if ( sheet === null ) { return null; }
  
  return sheet.getActiveCell();
}
// -------------------------------------------------------------------------------------------------------
// DataのデータID（y）からDefineの指定データを取得.
// -------------------------------------------------------------------------------------------------------
function GetDataToSubject( dataSheet, defineSheet, dataID, getDefineX ) {
  
  // 品種取得.
  var variety = dataSheet.getRange( dataID, DataVarietyX ).getValue();
  
  // 品種からdefineのYを取得.
  var endIdx = GetLastRow( defineSheet, DefineVarietyX ) + 1;

  var ranges = defineSheet.getRange( DefineDataStartY, DefineVarietyX, endIdx, 1 ).createTextFinder( variety ).findAll();

  if ( ranges.length === 0 ) { return ''; }

  return defineSheet.getRange( ranges[0].getRow(), getDefineX ).getValue();
}
// -------------------------------------------------------------------------------------------------------
// 選択中のセルの作物名をデータシートから探す.
// -------------------------------------------------------------------------------------------------------
function SearchDataSheet( dataSheet ) {
  
  var activeCell = GetActiveCell();
  if ( activeCell === null ) { return -1; }
  
  var dataEnd = GetLastRow( dataSheet, DataNameX ) + 1;
  var ranges = dataSheet.getRange( DataDataStartY, DataNameX, dataEnd, 1 ).createTextFinder( activeCell.getValue() ).findAll();

  if ( ranges.length === 0 ) { return -1; }

  return ranges[0].getRow();
}
// -------------------------------------------------------------------------------------------------------
// 指定範囲をクリアする.
// -------------------------------------------------------------------------------------------------------
function RangeClear( sheet, startX, startY, countX, countY ) {
  
  var rangeX, rangeY;
  
    // X方向.
    if ( countX === 0 ) {
      rangeX = GetLastColL( sheet, startY ) + 1;
    } else {
      rangeX = countX;
    }

    // Y方向.
    if ( countY === 0 ) {
      rangeY = GetLastRow( sheet, startX ) + 1;
    } else {
      rangeY = countY;
    }

    sheet.getRange( startY, startX, rangeY, rangeX ).clearContent();
  
  return;
}

【解説】「選択肢更新」ボタンの処理実装

「mng」シートの「選択肢更新」ボタンを押したときの処理を実装していきます。
処理の流れとしては、
  1. 選択肢をリセット
  2. 育てるリスト（ツール右側のリスト）の「輪作年限」が残っている科目以外の作物を「data」シートからリストアップ
うんうん。言うは易し。

19、20行目

  // いったんリストをリセット.
  RangeClear( mngSheet, MngSelectNameX, MngSelectStartY, 1, 0 );
  RangeClear( mngSheet, MngSelectSubjectX, MngSelectStartY, 1, 0, );
リスト（指定範囲）のクリア処理は何度か使用しそうだったので、「RangeClear」という関数を作成して処理しています。
ところどころxやyが出てくるのはcolとrowがいまだに覚えられない私のためにx（横）y（縦）としていますσ(´ω｀*)

24～30行目

  // 育てるリストで輪作年限が残っている科目を抽出.
  var SubjectArr = new Array();
  dataIdx = GetLastRow( mngSheet, MngListIDX ) + 1;
  for ( var y = MngListStartY; y < dataIdx; ++y ) {
    if ( mngSheet.getRange( y, MngListCropRotRestX ).getValue() === 0 ) { continue; }
    SubjectArr.push( mngSheet.getRange( y, MngListSubjectX ).getValue() );
  }
右側（現在は空白）のリストを精査し、「輪作年限/残り」の週数を見て、0以外の「科目」を配列に保存しています。

38～63行目

  for ( var y = DataDataStartY; y < dataEnd; ++y ) {
    
    isSuccess = true;
    
    // ① 育てるリストの輪作年限が残っている科目の作物はリストアップしない.
    subject = GetDataToSubject( dataSheet, defineSheet, y, DefineVarietySubjectX );
    
    for ( var i = 0; i < SubjectArr.length; ++i ) {
      if ( subject !== SubjectArr[ i ] ) { continue; }
      // 一覧にあったのでリストアップしない.
      isSuccess = false;
      break;
    }
    
    if ( isSuccess !== true ) { continue; }
    
    // ② 栽培場所をチェックする.
    place = dataSheet.getRange( y, DataPlaceX ).getValue();
    if ( place !== mngSheet.getRange( MngConfigPlace ).getValue() ) { continue; }
    
    // 選択肢リストに追加.
    mngSheet.getRange( dataIdx, MngSelectNameX ).setValue( dataSheet.getRange( y, DataNameX ).getValue() );
    mngSheet.getRange( dataIdx, MngSelectSubjectX ).setValue( subject );
    
    ++dataIdx;
  }
「data」シートを上からチェックして、先ほど保存した科目と比較し、

// ① 育てるリストの輪作年限が残っている科目の作物はリストアップしない.
の部分で同じものは除外し、

// ② 栽培場所をチェックする.
で「mng」シートで定義している「施設」「路地」「コンテナ」などの栽培場所の区別をします。

// 選択肢リストに追加.
で選択肢一覧に必要項目を追加していきます。

ここで作成した関数「PushReset」を「選択肢更新」ボタンに呼び出してもらいます。

設定方法はこちら↓

【GAS】実践・ping送信

【GAS】実践・ping送信。図形への関数割り当て、別シートの操作など

ゲームプログラマーの農業日誌

実行結果

結構時間がかかるので、最初は「data」シートの項目を2，3個にしてテストすると良いです。

【解説】「調べる」ボタンの処理実装

71～129行目

// -------------------------------------------------------------------------------------------------------
// 「調べる」ボタンを押した.
// -------------------------------------------------------------------------------------------------------
function PushSearch() {
  
  // シートの取得.
  var mngSheet = SpreadsheetApp.openById( SheetID ).getSheetByName( SheetNameMng );
  var dataSheet = SpreadsheetApp.openById( SheetID ).getSheetByName( SheetNameData );
  var defineSheet = SpreadsheetApp.openById( SheetID ).getSheetByName( SheetNameDefine );
  
  if ( mngSheet === null ) { return; }
  if ( dataSheet === null ) { return; }
  if ( defineSheet === null ) { return; }
  
  // 作物名が存在するかどうかチェック.
  var dataID = SearchDataSheet( dataSheet );
  if ( dataID === -1 ) { return; }
  
  
  // 基本情報の入力.
  
  var name =  dataSheet.getRange( dataID, DataNameX ).getValue();
  
  // タイトル.
  mngSheet.getRange( MngInfoTitle ).setValue( name + 'の基本情報' );
  // 科目.
  mngSheet.getRange( MngInfoSubject ).setValue(
    GetDataToSubject( dataSheet, defineSheet, dataID, DefineVarietySubjectX ) );
  // 種まき・作業開始週.
  mngSheet.getRange( MngInfoStartWeek ).setValue( '' );
  var num = dataSheet.getRange( dataID, DataSowingSeedStartX ).getValue();
  if ( num !== 0 ) {
    mngSheet.getRange( MngInfoSowingSeed ).setValue( num + '月' );
    // 作業開始週.
    mngSheet.getRange( MngInfoStartWeek ).setValue(
      dataSheet.getRange( dataID, DataSowingSeedResultX ).getValue() + '週目' );
  } else {
    mngSheet.getRange( MngInfoSowingSeed ).setValue( '' );
  }
  // 定植.
  num = dataSheet.getRange( dataID, DataPlantingStartX ).getValue();
  if ( num !== 0 ) {
    mngSheet.getRange( MngInfoPlanting ).setValue( num + '月' );
    // 作業開始週.
    mngSheet.getRange( MngInfoStartWeek ).setValue(
      dataSheet.getRange( dataID, DataPlantingResultX ).getValue() + '週目' );
  } else {
    mngSheet.getRange( MngInfoPlanting ).setValue( '' );
  }
  // 輪作年限.
  mngSheet.getRange( MngInfoCropRot ).setValue(
    GetDataToSubject( dataSheet, defineSheet, dataID, DefineCropRotX ) + '年' );
  // 後に植えると相性の良い作物.
  RangeClear( mngSheet, MngInfoGoodX, MngInfoGoodY, 1, DefineGoodNum );
  for ( var x = 0; x < DefineGoodNum; ++x ) {
  name = GetDataToSubject( dataSheet, defineSheet, dataID, (DefineGoodX + x) );
    if ( name === 'なし' ) { continue; }
    mngSheet.getRange( (MngInfoGoodY + x), MngInfoGoodX ).setValue( name );   
  }

  return;  
}
この部分はひたすら検索＆代入。
作成した関数「PushSearch」を「調べる」ボタンに割り当てます。

実行結果



ブログランキング・にほんブログ村へ

Translate

魔王と愉快な仲間

このブログを検索

ラベル

Blogger (7) FX (1) GAS (14) Utility (1) ゲーム (1) ツール (10) プログラミング (20) レポート (2) 移住 (3) 金策 (9) 土壌 (2) 独り言 (5) 読み物 (21) 農業 (3)

ブログ アーカイブ

メッセージ

名前

メール *

メッセージ *

Powered by Blogger.

フリー写真素材ぱくたそ

QooQ