【GASでつまずく】スプレッドシートで特定の行列の値を取得する

みなさん、スプレッドシートを使用して仕事をしていますか?

僕は最近スプレッドシートを使用して、仕事をすることがあります。

何よりもGASと呼ばれるプログラミング言語がとても便利で、よく使用しています。

しかし、ついこの前、とあるAPIを使用してスプレッドシートの値と照らし合わせる機会があったのですが、特定のセルの値の取得方法がわからず、、、

かなりの時間を費やしてしまいました。

そこで、今日はそのアウトプットとして記事を書いていきます。

もし他にいいやり方があれば、是非教えてください。

今日の記事は、

  • セルの取得方法がわからない
  • 行数の取得方法がわからない
  • 列のアルファベットをどうやって取得するか知りたい

こんな方におすすめの記事になっています。

それでは、やっていきましょう。

セルの値を取得するコード

まずは、以下のようにスプレッドシートを用意します。

練習用に真ん中の「りんご」の価格を変更する関数を作りました。

function exec() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
  const item_array = getRow(ss);
  input_price(ss, item_array);
}
function getRow(ss) {
  // 最後の行番号を取得する
  var lastRow = ss.getLastRow();
  // 範囲指定をする
  var itemRange = ss.getRange('A2:' + 'A' + lastRow);
  // 商品名を取得する(2次元配列になっている)
  var items = itemRange.getValues();
  // 再度商品名をいれる配列を用意する
  var item_array = [];
  // 再度商品名のみ配列に入れ直す([ 'いちご', 'りんご', 'くり' ])
  for (i=0; i<items.length; i++) {
    item_array.push(items[i][0]);
  };
  return item_array;
}

function input_price(ss, item_array) {
  // 範囲を取得する
  var priceRange = ss.getRange('A1:B1');
  // 値を取得する
  var priceCol = priceRange.getValues();
  // 値段のある列をアルファベットで取得する
  for (j=0; j<priceCol[0].length; j++) {
    // console.log(priceCol[0][j]);
    if (priceCol[0][j] == '値段') {
      var alpha = ss.getRange(1, j+1).getA1Notation().replace(/\d/,'');
    };
  };
  // 価格を書き換える
  for (i=0; i<item_array.length; i++) {
    if (item_array[i] == 'りんご') {
      var a = ss.getRange(alpha+(i+2)).setValue(1000);
    }
  }
};

アウトプットのために書いたのですが、やっぱりちょっと難しかったです。

遠回りなやり方なのは重々承知ですが、GASは意外と特定の行列を取得するのに手間がかかりますね。

他のサイトとかみてもなかなか見つからなかったので、お役に立てれば幸いです。

それでは、少しだけ解説していきます。

GASで全ての行数を取得する

ここが正直一番びっくりして、とても勉強になりました。

function getRow(ss) {
  // 最後の行番号を取得する
  var lastRow = ss.getLastRow();
  // 範囲指定をする
  var itemRange = ss.getRange('A2:' + 'A' + lastRow);
  // 商品名を取得する(2次元配列になっている)
  var items = itemRange.getValues();
  // 再度商品名をいれる配列を用意する
  var item_array = [];
  // 再度商品名のみ配列に入れ直す([ 'いちご', 'りんご', 'くり' ])
  for (i=0; i<items.length; i++) {
    item_array.push(items[i][0]);
  };
  return item_array;
}

スプレッドシートの最後の行を取得して、そこからループ文で行数を取得するのですね。

正直この発想が、僕には全く思いつかなかったので、

アルゴリズムの勉強をしよう!

と思わされました。

getA1Notation関数でアルファベットを取得

 for (j=0; j<priceCol[0].length; j++) {
    // console.log(priceCol[0][j]);
    if (priceCol[0][j] == '値段') {
      var alpha = ss.getRange(1, j+1).getA1Notation().replace(/\d/,'');
    };
  };

ここは、正直もっと簡単なやり方があると思ったのですが、練習のために書きました。

  1. 特定のセルを範囲指定する(今回は、B1セル)
  2. getA1Notation関数を使用する(「B1」という文字列を取得する)
  3. replace関数で、正規表現を使用して数字を空文字にする(「B」を取得する)

やっぱりこういう時に正規表現て便利ですよね。

正規表現は、かなり苦手意識があります。

が、こういう時にとても便利なので、簡単な正規表現は検索せずに使用できるようになりたいです。

まとめ

  • 行数を取得するために最後の行番号を使用する
  • getA1Notation関数でセルを文字列として取得する
  • replace関数で数字を空文字にする

今日は、僕が実際に業務で書いたコードの復習記事です。

ただ、検索してもあまりヒットしなかったので、誰かのお役に立てれば嬉しいです。

(僕の検索力がなかっただけかもしれません。。。)

最後の参考サイトを載せておきます。

このサイトに救われました。ありがとうございます。

参考:

Google Apps Script試行錯誤 Blog