知識と技術を増やすが吉

スプレッドシートの記入データをGoogle AppsScript（GAS）で取得する方法

Google Apps Script

みなさん、こんにちは！
現役エンジニアの”ます”です。

今回はGoogle Apps Scriptとスプレッドシートの連携でよく使うメソッドを紹介します。

もちろん、Googleが提供する公式ドキュメントに書いてありますが、
さらに分かりやすく、探す手間も省いて説明していきますね！

Google Apps ScriptのSpreadSheetにおけるSheetクラスのリファレンスはこちら

Google Apps Scriptの導入方法についてはこちらで紹介しています。

Google Apps Scriptの導入方法

目次

事前準備① スプレッドシートを取得しよう

GASでスプレッドシートに記入したデータを読み取るには事前にどのスプレッドシートのどのシート名が対象になるのかを判定する必要があります。
また、そのスプレッドシートの取得の仕方も2つありますので、1つずつお話します。

コンテナバインド型スクリプト

スプレッドシートのコンテナに紐づける形でApps Scriptを導入した場合、
紐づくスプレッドシートの取得は非常に簡単に行えます。
関数内でこちらを記入してみてください。

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.getActiveSpreadsheet();
}

非常に簡単な一文でコンテナバインドで紐づくスプレッドシートを読み解くことができます。

スタンドアロン型スクリプト

次に、スタンドアロン型スクリプトでスプレッドシートを取得する場合は、紐づくスプレッドシートはありませんので、いくつかの方法のどれかを使い、スプレッドシートを指定する必要があります。

スプレッドシートのIDから取得する

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.openById("__スプレッドシートのID__");
}

openById()メソッドを使う場合、第一引数に対象のスプレッドシートのIDを入力する必要があります。
スプレッドシートのIDはURLを開くと出てくる下記太字の部分をご利用ください。

https://docs.google.com/spreadsheets/d/1SLdJvOGsGFE1XV0WGQdHQukLlIpSjPe9jqydFxRVz3w/edit

この場合、このように記入することになりますね！

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.openById('<strong>1SLdJvOGsGFE1XV0WGQdHQukLlIpSjPe9jqydFxRVz3w</strong>');
}

スクリプト中でIDのみを取得していることもありますので、この方法であれば簡単にスプレッドシートを取得することができます。

スプレッドシートのURLから取得する

先ほどのIDよりもより直感的で簡単かもしれませんね！
取得したいスプレッドシートのURLを記入するだけで取得する方法もあります。

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1SLdJvOGsGFE1XV0WGQdHQukLlIpSjPe9jqydFxRVz3w/edit')
}

指定のスプレッドシートを手動で入力するときなど、やはりURLの方が直感的で分かりやすいかもしれませんね！

事前準備② 対象のシートを取得しようgetSheetByName()

スプレッドシートが取得できたら、次にデータを抽出したい対象のシートを取得します。
この場合のシートは「シート 1」、「シート 2」のようにスプレッドシート下部にあるタブを表します。

例えば、シート名が「夕飯」の場合、下記のように表示されます。

トリガー（自動実行）を用いてプログラムを起動する場合を除き、基本的にシートは下記のように取得します。

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.getActiveSpreadsheet();

  // シート名を指定して取得
  const SHEET = SS.getSheetByName('__シート名__');
}

シート名が「夕飯」の場合、SS.getSheetByName(‘夕飯’);となるわけですね！

シートに記載されたデータを取得しよう

1つのセルデータを取得する場合getRange().getValue()

getRange()は対象のシートのどのセルを範囲として指定しているかを指定する命令です。

引数の取り方はいくつもありますが、

  1. getRange(a1Notation)
  2. getRange(row, column)
  3. getRange(row, column, numRows)
  4. getRange(row, column, numRows, numColumns)

①の引数は「A1」や「A1:B10」といったスプレッドシートの範囲を記入します。

②rowが行数、columnが列番号です。A列は1、B列が2になります。

③rowが行数、columnが列番号、numRowsはそこから何行分かを指定します。

④rowが行数、columnが列番号、numRowsはrowから何行分か、numColumnsはcolumnから何列分取得するかを指定します。

今回は分かりやすく①の方法で解説を行います。

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.getActiveSpreadsheet();

  // シート名を指定して取得
  const SHEET = SS.getSheetByName('__シート名__');

  // 1つのセルデータを取得する場合
  let cellValue = SHEET.getRange('A1').getValue();
}

getRange()でセルを1つ指定した場合、続けてgetValue()をすることで指定したセルに記入された値を取得することができます。

指定範囲が2つ以上のセルを示す場合はエラーとなってしまうので注意してください。

全てのデータを取得する場合getDataRange().getValues()

getDataRange()というシート内の全てのデータ範囲を取得することができます。
最終行や最終列を自動で取得してくれますので引数を考えなくて済む便利なメソッドになります。

また、その全データの範囲に対してgetValues()で複数のデータを取得することができます。
取得したデータは2次元配列として変換され、1次元目が行、2次元目が列となります。

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.getActiveSpreadsheet();

  // シート名を指定して取得
  const SHEET = SS.getSheetByName('__シート名__');

  // 全てのデータを取得する場合
  let values = SHEET.getDataRange().getValues();
}

指定範囲のデータを取得する場合getRange().getValues()

範囲を指定してデータを抽出することもできます。
その場合はgetRange()関数を上手く活用してください。

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.getActiveSpreadsheet();

  // シート名を指定して取得
  const SHEET = SS.getSheetByName('__シート名__');

  // 全てのデータを取得する場合
  let values = SHEET.getRange(2, 3, 1, 3).getValues();
}

上記の例の場合、2行3列目のC2セルから1行と3列分なのでE3列までの範囲を指定してデータを取得します。
こちらも複数範囲の指定なので2次元配列で取得されます。

配列から取り出すときはindex番号は0からになりますので注意してください。

＜応用＞ 取得したデータを上手く活用しよう！

データを取得する際に、フィルタリングしたいこともあるかと思います。
例えば、A列が空の場合はスキップしたいといったことや、列順を変更したいなどへの対応はJavaScriptのコードで対応できます。

一定の条件下でフィルタリングかけたい場合

JavaScriptの配列におけるfilterメソッドが便利です。

よくあるパターンとして、GASでgetValuesしたデータを空の場合スキップしたいといった処理です。

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.getActiveSpreadsheet();

  // シート名を指定して取得
  const SHEET = SS.getSheetByName('__シート名__');

  // 全てのデータを取得する場合
  let value = SHEET.getDataRange().getValues().filter(x => x[0]); // 各行をxとして取り出し、0個目（A列）が空の場合はスキップします。
}

filterを使うことで返り値がtrueのもののみ配列として格納してくれます。
今回は取得した行の0個目（A列）が空だった場合はfalseがreturnされるため、自動的にスキップされたデータがvalues変数に格納されます。

各行を取り出してデータを処理したい場合

JavaScriptにおけるmapメソッドが便利です。

よくあるパターンとして、B列とC列の順番を変更したい！などの場合、mapメソッドが便利になります。

function myFunction() {
  // コンテナバインドで紐づくスプレッドシートを取得
  const SS = SpreadsheetApp.getActiveSpreadsheet();

  // シート名を指定して取得
  const SHEET = SS.getSheetByName('__シート名__');

  // 全てのデータを取得する場合
  let value = SHEET.getDataRange().getValues().map(x => [x[0],x[2],x[1]); // 各行をxとして取り出し、順番を入れ替えて返す
}

mapを使うことで行ずつに取り出した変数に対して、指定した返り値ががそのまま配列として格納してくれます。
今回は取得した列番号が1（B列）と2（C列）を入れ替えた行データをreturnしているため、順番を変更したデータがvalues変数に格納されます。

まとめ

準備から使い方、応用編までお話してきました。
この応用までできるようになると非常に便利にスプレッドシートを取り扱えるようになると思います。

また、一般的な初学者向けの書籍や解説に出てきにくいfilterやmapですが、実例を用いて活用できるかと思います。
ぜひ基礎だけでなく応用まで手を伸ばしてみてくださいね！

Google Workspaceを活用している場合はGoogle Apps Scriptを覚えると業務の効率化および自動化ができます。
Google Apps Scriptが学べるスクールはコチラで紹介しています。よかったら見てみてください。

Google Apps Scriptを学べるスクール3選

Google Apps Scriptが学べたらコチラの記事で収益化してみてはいかがでしょうか？

Google Apps Scriptのプロが教える！成功するGAS副業完全ガイド

以上、最後までお読みいただきありがとうございました。

最新情報をチェックしよう！

Google Apps Scriptの最新記事8件