【GAS】importrangeを使ったSpreadsheetにgetDataRange()した時、空白の行まで取得してしまう。

概要

getDataRange()

• sheetオブジェクトの関数
• 値が入っている全てのセルを持つRangeオブジェクトを取得できる

IMPORTRANGE

• 指定したスプレッドシートからセルの範囲を読み込む。
• 行番号を指定しないばあい、空白の行も含め一番下の行まで読み込む。

importrangeでクローンしたスプレッドに対してgetDataRange()すると、空白の行まで値が入っているとみなし、取得してしまう。

→ query関数で空白の行を表示しないことで解決できる。

事象確認

以下のデータをJSON形式に変換するGASで事象を確認してみよう。

データを読み込んでいる

importrange("https://docs.google.com/spreadsheets/d/[シートID]/edit","[範囲]")

▼データを読み込んだあとのSpreadsheet

さっそくimportrangeでクローンしたスプレッドに対してgetDataRange()してみる。

JSONに変換するGAS

function getData(id, sheetName) {
  var sheet = SpreadsheetApp.openById('[スプレッドのID]').getSheetByName('[シート名]');
  var rows = sheet.getDataRange().getValues();
  var keys = rows.splice(0, 1)[0];
  
  return rows.map(function(row) {
    var obj = {}
    row.map(
      function(item, index) {
        obj[keys[index]] = item;
    });
    // ログ表示
    Logger.log(obj);
  });
}

▼ログ。空白行が取得されてしまい、無駄なデータが大量に生成されている。

解決法

importrangeで取得したデータから空白の行を取り除く。

以下のようにquery関数でキー値がNULLでないものを指定してあげる。

query関数を適用

query(importrange("https://docs.google.com/spreadsheets/d/[シートID]/edit","[範囲]"),"where Col1 IS NOT NULL")

▼ログ。空白行を除いてJSON形式になっている。