第11回：GASで特定セルの値を全て取得する方法

GAS(Google Apps Script)
2023.02.26

今回はドライブ内のスプレッドシートの全てのシート内からセルを指定して特定セルの値を取得していく方法です。

前回まででドライブ内のスプレッドシートを全て開いて（アクティブ）、シートも全て開くことが出来ました（以下のコード参照）

前回までのコード

前回のコード全部と今回の追加場所を以下に書く

前回までのconsole.logはコメントアウトしてます。

function recipeListCreation() {

  /*--------------------------------------------------------------------------*/
  // Googleドライブの指定フォルダ(らーめん妄想屋レシピフォルダ)内から情報を取得する
  /*--------------------------------------------------------------------------*/
  // GoogleドライブのフォルダId（マイドライブ→レシピ管理→らーめん妄想屋レシピ）
  const folderId = '1uOYBO45AP8FVz3AX2D5XSXZHIiTfJ2nX';

  // Googleドライブ内のフォルダ情報を保持しておく配列
  const filesInDriveArray = [];
  
  // Googleドライブのフォルダ内のすべてのファイル情報を取得
  const files = DriveApp.getFolderById(folderId).getFiles();
  
  // ドライブ内のファイル配列(filesInDriveArray）にデータを入れる
  while(files.hasNext()) {
    var file = files.next();
    filesInDriveArray.push([file.getName(),file.getId(),file.getUrl()]);
  }
  //console.log(filesInDriveArray);

  /*--------------------------------------------------------------------------*/
  // スプレッドシート内の情報を取得する
  /*--------------------------------------------------------------------------*/
  // Googleドライブ内のらーめん妄想屋レシピフォルダ内のスプレッドシート数
  const driveArraryLen = filesInDriveArray.length;
  //console.log("スプレッドシート数：" + driveArraryLen);

  // ②ここに追加

  // filesInDriveArrayに入っているIdをキーにしてスプレッドシートを開く
  for (var i=0; i<driveArraryLen; i++) {
    var nowOpenSpreadSheet = SpreadsheetApp.openById(filesInDriveArray[i][1]);
    var nowOpenSheetLen = nowOpenSpreadSheet.getSheets().length;
    //console.log("今開いているスプレッドシートの名前：" + nowOpenSpreadSheet.getName());
    //console.log("今開いているスプレッドシートのID：" + nowOpenSpreadSheet.getId());
    //console.log("今開いているスプレッドシート内のシート数：" + nowOpenSheetLen);

    // スプレッドシートのシートをアクティブにしてレシピ名や売値などを取得する。
    for (var j=0; j<nowOpenSheetLen; j++) {
      // シートをアクティブにする
      var activeSheet = nowOpenSpreadSheet.getSheets()[j];
      //console.log("activeSheet名：" + activeSheet.getName());

     // ①ここに追加
     // ①-1データ取得セルを指定する
     // ①-2データを取得する

    // ③ここに追加
    }
  }
}

①シート内からrange（範囲）を指定してvalue（値）を取得する

必要なデータが入っているセルは以下の12個なのでこれを全て取得していきたいと思います。

レシピ名(“B2”)
レシピコード(“K2”)
売値(“J5”)
原価/単位(“K5”)
原価率(“L5”)
店舗名(“J8”)
店舗コード(“J11”)
ジャンル(“L8”)
ジャンルコード(“K11”)
製品コード(“L11”)
上がり量(“J14”)
単位(“K14”)

①-1 データを取得するセルを指定する

レシピ名はB2セルにあるのでrange（範囲）を設定します。

range（範囲）からvalue（値）を取得する。

      // データ取得セルを指定する
      var range1 = activeSheet.getRange("B2");

①-2 データを取得する

      // データを取得する
      var value1 = range.getValue();
      console.log("【第11】回レシピ名：" + value1);

実行結果は以下

ちゃんとB2セルのデータを取得出来ました。

全てのスプレッドシート内の全てのシートから取得出来ていることを確認できます。

これを書いて行けばどんどんと取得出来ますね。

他のデータを取得するセルを指定する

      // データ取得セルを指定する
      var range1  = activeSheet.getRange("B2");   // レシピ名("B2")
      var range2  = activeSheet.getRange("K2");   // レシピコード("K2")
      var range3  = activeSheet.getRange("J5");   // 売値("J5")
      var range4  = activeSheet.getRange("K5");   // 原価/単位("K5")
      var range5  = activeSheet.getRange("L5");   // 原価率("L5")
      var range6  = activeSheet.getRange("J8");   // 店舗名("J8")
      var range7  = activeSheet.getRange("J11");  // 店舗コード("J11")
      var range8  = activeSheet.getRange("L8");   // ジャンル("L8")
      var range9  = activeSheet.getRange("K11");  // ジャンルコード("K11")
      var range10 = activeSheet.getRange("L11");  // 製品コード("L11")
      var range11 = activeSheet.getRange("J14");  // 上がり量("J14")
      var range12 = activeSheet.getRange("K14");  // 単位("K14")

他のデータも取得する

      // データを取得する
      var value1  = range1.getValue();
      var value2  = range2.getValue();
      var value3  = range3.getValue();
      var value4  = range4.getValue();
      var value5  = range5.getValue();
      var value6  = range6.getValue();
      var value7  = range7.getValue();
      var value8  = range8.getValue();
      var value9  = range9.getValue();
      var value10 = range10.getValue();
      var value11 = range11.getValue();
      var value12 = range12.getValue();
      //console.log("【第11】回レシピ名：" + value1);
      console.log("【第11】回レシピコード：" + value2);

これでレシピコードが取得出来ればOK

② 配列を作る

セル内の値を全て取得出来たので出力する為に保存しておく箱を作ります。

変数名はレシピ一覧データ配列（recipeListDataArray）

これは二次元配列にして出力するときはまるっと塊で出力する予定

  // レシピ一覧に出力する情報を保持しておく配列
  const recipeListDataArray = [];

③ 配列にデータを入れる

push()で配列に入れていく

[]を付けるのを忘れないようにする。付けないと一次元配列になってしまう。

      // レシピ一覧に出力する情報を保持しておく配列にデータを入れる
      recipeListDataArray.push(
        [value1,value2,value3,value4,value5,value6,
        value7,value8,value9,value10,value11,value12]
        );

配列内データを確認する

for分が終わった段階でrecipeListDataArrayを見てみる。

      // レシピ一覧に出力する情報を保持しておく配列にデータを入れる
      recipeListDataArray.push(
        [value1,value2,value3,value4,value5,value6,
        value7,value8,value9,value10,value11,value12]
        );
    }
  } // ←for分はここで終わり
  console.log("【第11】回レシピ一覧に出力する情報：" + recipeListDataArray[0]);
  console.log("【第11】回レシピ一覧に出力する情報：" + recipeListDataArray[1]);
  console.log("【第11】回レシピ一覧に出力する情報：" + recipeListDataArray[0][0]);
  console.log("【第11】回レシピ一覧に出力する情報：" + recipeListDataArray[1][0]);
}

ちゃんと配列に欲しかったデータが入っているのが確認できました。

あとはレシピ一覧に出力するだけです。

次回

レシピ一覧に二次元配列（recipeListDataArray）をまるっと出力する。

コメント