【GAS】 Google Spread Sheetから二次元配列を取得する

はじめに

GASを使って、Google Spread Sheetから二次元配列を取得する機会があったので、その方法を紹介します

今回取得するシート

シートを特定する

以下のコードでシートを特定します

  // シートの特定
  const ssId = '**********************'; 
  const shName = 'array';
  const ss = SpreadsheetApp.openById(ssId);
  const sh = ss.getSheetByName(shName)

ssIdは、google spread sheetを開いた時のurl
https://docs.google.com/spreadsheets/d/********/edit#gid=0の******の部分。
shNameは、シート名。getSheetByName()メソッドでシートを特定できる。

特定した範囲を配列で取得するメソッド

getRange(最初行,最初列,最終行,最終列).getValues()で、配列の取得ができるが、最終行はデータが追加されていくため変動する。
そこで、最終行は以下のように取得する。

var lastRow = sh.getRange(sh.getMaxRows(), targetColumn).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();`

targetColumnは、必ずデータが入る列を指定する。上記のコードで、その列の最終行を取得することができる。
最終行を取得できたので、全体の範囲を指定して配列を取得する。

var arrayData = sh.getRange(startRow,1,lastRow-1,lastColumn).getValues(); 

startRowは見出し部分を除き2行めからとし、その分lastRowを1減らす。列は任意の数値とする。
結果以下のような配列が取得できた。

[[1.0, Taro, Tokyo, Car, 3.0, 2.0, 5.0], [2.0, Hanako, Gifu, Bike, 1.0, 1.0, 2.0], [3.0, Shouhei, Ohsaka, bicycle, 4.0, 2.0, 6.0]]

まとめ

配列が取得できると、配列をもとにいろいろな処理をすることができます。
このデータから他のシートにデータを転記することもできます。一度試してみてください

コード全文

  function getSheetArray() {
  
  // シートの特定
  const ssId = '***********************************'; 
  const shName = 'array';
  const ss = SpreadsheetApp.openById(ssId);
  const sh = ss.getSheetByName(shName)

  // 二次元配列にする範囲を特定
  const targetColumn = 2;    //必ずデータが入る列を指定する
  const startRow = 2;        //見出し部分は除くため2行目から
  const lastColumn = 7;      //最終列
  
  // targetColumn列の最終行を取得
  var lastRow = sh.getRange(sh.getMaxRows(), targetColumn).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  
  // 範囲を指定して二次元配列の取得
  // getRange(最初行,最初列,最終行,最終列)
  var arrayData = sh.getRange(startRow,1,lastRow-1,lastColumn).getValues(); 
  Logger.log(arrayData); 
  
  return arrayData;
    
}