【GASの始め方】for文とif文でデータ抽出して配列を生成しよう

みなさんこんにちは！GASおじさんです。

「GASでスプレッドシートを自由自在に操るためのスキル習得講座」の第9回です。

前回の記事はこちら。

前回はシートに入力された値から配列を自動生成する方法について解説しました。

今回はシートに値が入力されていない場合はどうしたらいいかを考えていきます。

当シリーズはUdemyで動画解説をしています。

動画で学びたい方は以下からご購読ください。Udemyでは月2〜3回セールが開かれますので、セール期間中にご購入いただくのがオススメです。

GASでスプレッドシートを自由自在に操るためのスキル習得講座【Google Apps Script入門】

GoogleAppsScriptの専門ブログ・Youtubeを運営するGASおじさんが、日本一わかりやすいGASの授業をお届けします！スプレッドシートの各シートから売上を自動集計するプログラムを作成し、プログラミングの基礎知識を習得しよう！

応用問題レベル3
前回の振り返り
シート名から配列を自動生成する
検証しながらコードを微調整する
追加検証: シートを増やして実行してみる
まとめ
おまけ
連載目次: GASでスプレッドシートを自由自在に操るためのスキル習得講座
Udemy動画解説

応用問題レベル3

まずは以下のスプレッドシート「GASをはじめよう！応用問題レベル3」を開いてコピーを作成してください。

GASをはじめよう！応用問題レベル3（コピー用）

前回のレベル2では、集計シートのA列からデータを引っ張って配列を自動生成しました。

今回のレベル3では、A列に何も入力されていない場合はどうしたらいいかを考えていきます。

前回の振り返り

まずは前回作成したコードを振り返りましょう。

function setTotalSales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([getTotalSales(company)]);
  }
  sheet.getRange(2, 2, values.length, values[0].length).setValues(values);
}

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const lastRow = sheet.getLastRow();
  const values = sheet.getRange(2, 1, lastRow-1, 1).getValues().flat();
  return values;
}

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow, 2).getValue();
}

前回は集計シートのA列から配列を生成するgetCompanies関数を作成しました。

この関数で、

const companies = ['A社', 'B社', 'C社', 'D社', 'E社'];

という会社名の配列を生成しました。

しかしこの関数は、「集計シートのA列にすでに会社名が入力されている」という状況が前提となっている場合のみ有効な関数となっています。

今回のレベル3では、A列にそもそも値が入力されていないので、この関数は丸々使えないということになります。

ということで一旦、getCompanies関数の中身をクリアして、再度、配列を生成するためのロジックを考えていくことにしましょう。

function setTotalSales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([getTotalSales(company)]);
  }
  sheet.getRange(2, 2, values.length, values[0].length).setValues(values);
}

function getCompanies() {

}

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow, 2).getValue();
}

シート名から配列を自動生成する

見通しを立てる

さて、集計シートのA列に会社名がない場合、どこから会社名を引っ張ればいいでしょうか。

あらためてスプレッドシートを観察してみます。

すると、ありますね。

スプレッドシートのフッター部分にあるシート一覧。

この部分からシート名を取得することができれば、会社名の配列を生成できるのではないでしょうか。

その際、先頭の「集計」シートは対象外なので、「集計シート以外のシート名をかきあつめる」というやり方でやっていけばよさそうです。

すべてのシートを取得するgetSheets()

ということでやっていきましょう。

まずはすべてのシートオブジェクトを取得するgetSheets()メソッドについて紹介します。

スプレッドシートオブジェクトssに対してgetSheets()メソッドを使うと、すべてのシートオブジェクトを配列形式で取得することができます。

その結果をsheetsという変数に格納して、console.log(sheets.length);でシートの数を確認してみましょう。

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  console.log(sheets.length);
}

getCompanies関数を実行してみます。

すると、sheets.lengthは「6」であることがわかりました。

これはつまり、「集計」「A社」「B社」「C社」「D社」「E社」の6枚のシートがありますよ、ということです。

sheetsをfor文で回す

それでは次に、配列sheetsをfor文で回していきます。

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  for(const sheet of sheets){
    
  }
}

for-of文、覚えているでしょうか？

こうすると、配列sheetsから1個ずつ要素を取り出して、変数sheetに代入してくれるんでしたね。

いま配列sheetsには6つの要素があるので、このループは6周することになります。

では、実際にループできているかどうか確認してみましょう。

for文の中でsheetに対して、getName()メソッドを使うと、シート名を取得することができます。

その結果をsheetNameという変数に格納して、ログ出力してみます。

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    console.log(sheetName);
  }
}

こちら実行してみましょう。

すると、6回分の出力がされ、ループが6周していることが確認できました。

また、それぞれのシート名を取得できていることも確認できました！

if文で「集計」シートを除外する

現状は「集計」「A社」「B社」「C社」「D社」「E社」の6枚のシート名を取得していますが、今回欲しいのは「集計」以外のシート名です。

そこで、「集計」シートを除外するためのロジックを記述します。

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    if(sheetName != '集計'){
      console.log(sheetName);
    }
  }
}

注目は6〜8行目。

こちらで「if文」という条件分岐の制御構文が使われています。

本講座でif文が出てくるのはここが初めてですね。

if文は以下のように書きます。

if(条件式){
  // 条件式がTrueなら実行される
}

if文はこのように( )の中に条件式を記述し、その条件式が正しければ(Trueならば)、{ }の中の処理が実行されるという制御構文です。

if文はfor文に並ぶ超重要な基礎文法ですので、もう少し詳しくelse ifやelseについても説明したいところですが、これについて説明し始めると長くなってしまうので、今回はこれくらいに留めておきます。

さて、そのうえであらためて以下のコードを見てみます。

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    if(sheetName != '集計'){
      console.log(sheetName);
    }
  }
}

今回の条件式は(sheetName != '集計')となっています。

!=は「ノットイコール」を表す比較演算子です。

つまりこの条件式は、「sheetNameが『集計』じゃないならば」を意味しています。

sheetNameが「集計」じゃないならば、console.log(sheetName);を実行してくださいね、というif文となっているので、再度getCompaniesを実行してみると、以下のような結果となります。

無事、「集計」シート以外のシート名を抽出して出力することができました！

あとはこのデータを配列形式に加工しなおしてあげればいいですね。

抽出したデータを配列にする

では、for文開始前に空の配列companiesを用意し、そこにsheetNameをpushしていきましょう。

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const companies = [];
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    if(sheetName != '集計'){
      companies.push(sheetName);
    }
  }
  console.log(companies);
}

4行目で空の配列companiesを定義
8行目でcompaniesにsheetNameをpush
11行目でcompaniesをログ出力

こちらを実行してみると、以下のような結果となります。

無事、目標物である['A社', 'B社', 'C社', 'D社', 'E社'];という配列を生成することができました！

配列をreturnする

あとは最後の仕上げです。この配列をreturnしてあげましょう。

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const companies = [];
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    if(sheetName != '集計'){
      companies.push(sheetName);
    }
  }
  return companies;
}

これでgetCompanies関数の完成です！

検証しながらコードを微調整する

それでは、できあがったコードを実際に動かして検証してみましょう。

function setTotalSales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([getTotalSales(company)]);
  }
  sheet.getRange(2, 2, values.length, values[0].length).setValues(values);
}

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const companies = [];
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    if(sheetName != '集計'){
      companies.push(sheetName);
    }
  }
  return companies;
}

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow, 2).getValue();
}

実行する関数を「setTotalSales」に戻して実行してみます。

こちらを実行してスプレッドシートを確認すると…

無事、B列に各社の売上を集計できました！

…が、しかし、A列に会社名がないので、どの売上がどの会社のものかわからないですね。

そこで、setTotalSales関数で生成する二次元配列に修正を加えましょう。

二次元配列valuesにpushする内容を、

[getTotalSales(company)]

から

[company, getTotalSales(company)]

に修正します。

function setTotalSales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([company, getTotalSales(company)]);
  }
  sheet.getRange(2, 2, values.length, values[0].length).setValues(values);
}

変数companyは配列companiesの1個1個の要素なので、'A社'などの文字列データですよね。

したがって、生成される二次元配列valuesは以下のようになりますよね。

values = [
  ['A社', 60000],
  ['B社', 75000],
  ['C社', 100000],
  ['D社', 120000],
  ['E社', 150000],
];

これなら会社名と売り上げをまとめてsetValuesできます。

では、これであらためてsetTotalSales実行してみます。

すると…

惜しい！

無事、会社名と売上をまとめてsetValuesすることはできましたが、位置が1列ずれてしまっていますね。2行2列目からsetValuesされてしまっています。

さあ、これを修正するにはどこを修正したらいいかわかりますか？

そう、最後の1行ですね。getRangeの範囲指定を「2行1列目から」に修正してあげます。

function setTotalSales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([company, getTotalSales(company)]);
  }
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

これで再度実行してみましょう。

その前に、先ほど記録されてしまったデータを一旦削除します。

では、実行してみます。

すると…

無事、集計できました！これでやっと完成です！

あらためて、完成系のコードを載せておきますね。

function setTotalSales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([company, getTotalSales(company)]);
  }
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const companies = [];
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    if(sheetName != '集計'){
      companies.push(sheetName);
    }
  }
  return companies;
}

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow, 2).getValue();
}

追加検証: シートを増やして実行してみる

せっかくなので、シートを増やしても動くか検証してみましょう。

追加で5枚のシートを作ってみました。中身のデータは適当に書き換えています。

これであらためてsetTotalSales関数を実行すると…

無事、10社分集計できました！

シートを増やしても問題なく動作することが確認できましたね。

追加検証も無事成功しましたので、これにてレベル3クリアとします。お疲れ様でした！

まとめ

以上、シート名から配列を自動生成する方法について解説しました。

まず空の配列を用意する
for文の中でif文を書いて条件指定する
if文の中で配列にデータをpushする

という手順で配列を生成する方法は、プログラミングでは頻繁に使う手法となります。

特に大事なポイントは、if文の条件式ですね。

今回は「集計シートじゃないならば」という比較的シンプルな条件でプログラムを組むことができたので、簡単な内容に思えたかもしれませんが、実際の実務の中ではもっと複雑な条件を組むことも多々あると思います。

そのようなときに役立つ知識として、以下にキーワードを提示しておきます。

真偽値、ブール値、ブーリアン値
True, False
==, >=, <=, !=のような「比較演算子」
&&, ||のような「論理演算子」

この辺についての解説はまた別の機会にしようと思いますが、気になる人はGoogle検索してみたり、ChatGPTやGeminiなどのAIに聞いてみるといいでしょう。

次回は応用問題レベル4に挑戦です。TextFinderで行と列を特定する方法について解説します！

おまけ

おまけです。

getCompanies関数は以下のように書くこともできます。

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets().filter(sheet => sheet.getName() != '集計');
  const companies = sheets.map(sheet => sheet.getName());
  return companies;
}

これは上級者向けの書き方で、forとifの組み合わせで書くよりも短くスマートに書くことができます。

これについて気になる人は、

filter
map
アロー関数

について調べてみてください。

連載目次: GASでスプレッドシートを自由自在に操るためのスキル習得講座

Udemy動画解説

当シリーズはUdemyで動画解説をしています。

動画で学びたい方は以下からご購読ください。Udemyでは月2〜3回セールが開かれますので、セール期間中にご購入いただくのがオススメです。

GASでスプレッドシートを自由自在に操るためのスキル習得講座【Google Apps Script入門】