【コピペで使える】GASでスプレッドシートの値を読み / 書きしてみる

GASでスプレッドシートの値を読み取り / 書き込みする

GASを使って業務の自動化をする場合、スプレッドシートの操作をする場合が多いと思います。スプレッドシートの操作とは、主に以下の2つの処理が多いかと思います。

今回は、GASで標準でスプレッドシートの操作をするためのSpreadsheetAppで読み / 書きをするための一連のコードをご紹介します。

まずはGASで利用するSpreadsheetAppの基本構造から図解して説明します。

上記のように、スプレッドシートには基本構造があり、SpreadsheetAppでは、大きい塊から順に取得していきます。

スプレットシートで一番大きな塊はスプレッドシートファイルとなります。
次に大きな塊はシート、その次にセル、そして値となります。

記事で紹介するメソッド一覧

スプレッドシートで使えるSpreadsheetAppには、非常に数多くのコードが存在するため、本記事でご紹介するものが全てではないですが、まずは上記の基本的な使い方をご紹介しようと考えています。

open(file)サンプルコード

引数に入れる値

• file（ファイルオブジェクト） — スプレッドシートファイルを指定

サンプルコード

//DriveAppで取得したファイルを元にスプレッドシートを取得するサンプルコード1
function sampleCodeForOpenFile1(){
  let files = DriveApp.searchFiles('starred = true and mimeType = "' + MimeType.GOOGLE_SHEETS + '"');
  while (files.hasNext()) {
    let file = files.next();
    let ss = SpreadsheetApp.open(file);
    let name = ss.getName();
    console.log(name);
  }
}

//DriveAppで取得したファイルを元にスプレッドシートを取得するサンプルコード2
function sampleCodeForOpenFile2() {
  let fileId = "1kQSSciqJ_43r43p6InGz4XZPLFAROApK0YvTQsMfZPo";
  let file = DriveApp.getFileById(fileId); //ファイルIDでファイルを取得してみる
  let ss = SpreadsheetApp.open(file);
  let name = ss.getName();
  console.log(name);
}

変数の説明

files = 検索条件に一致したファイル
file = 取得されたファイル
ss = スプレッドシートファイル
name = スプレッドシート名

DriveAppを使ってファイルを取得し、そのファイルを使ってスプレッドシートを開くやり方です。

サンプルコード①については、スター付きのファイル＋スプレッドシートファイルという条件でファイルを取得し、繰り返し処理をしてファイルを取り出しています。

サンプルコード②については、ファイルIDを使ってDriveAppから対象のファイルを取得し、そのファイルを使ってスプレッドシートを開いています。

どちらのパターンも処理が遅くなる傾向がありますので、このメソッドを使う頻度はあまり高くないと思います。

openById(id)サンプルコード

引数に入れる値

• id（文字列） — スプレッドシートIDを指定

サンプルコード

//スプレッドシートIDを元にスプレッドシートを取得するサンプルコード
function sampleCodeForOpenSheetById(){
  let id = "1kQSSciqJ_43r43p6InGz4XZPLFAROApK0YvTQsMfZPo";
  let ss = SpreadsheetApp.openById(id);
  let name = ss.getName()
  console.log(name);
}

変数の説明

id = スプレッドシートID
ss = スプレッドシートファイル
name = スプレッドシート名

スプレッドシートIDを使ってダイレクトにスプレッドシートを開く方法です。

シンプルでわかりやすく、無駄な処理もないため、処理速度も早いです。

次に紹介するopenByUrlと似たような使い方になりますが、処理速度はそれほど変わりませんので、好みに合わせて使い分ければいいと思います。

個人的にはスクリプトエディタから参照元のスプレッドシートの内容を確認するためにファイルを開く事が多いので、openByUrlを使用することが多いです。

openByUrl(url)サンプルコード

引数に入れる値

• url（文字列） — スプレッドシートURLを指定

サンプルコード

//スプレッドシートURLを元にスプレッドシートを取得するサンプルコード
function sampleCodeForOpenSheetByUrl(){
  let url = "https://docs.google.com/spreadsheets/d/1kQSSciqJ_43r43p6InGz4XZPLFAROApK0YvTQsMfZPo";
  let ss = SpreadsheetApp.openByUrl(url);
  let name = ss.getName()
  console.log(name);
}

変数の説明

url = スプレッドシートURL
ss = スプレッドシートファイル
name = スプレッドシート名

スプレッドシートURLを使ってダイレクトにスプレッドシートを開く方法です。

シンプルでわかりやすく、無駄な処理もないため、処理速度も早いです。

getActiveSpreadsheet()サンプルコード

引数に入れる値

引数なし

サンプルコード

//ユーザーが開いているスプレッドシートを取得するコード
function sampleCodeFogetActiveSpreadsheet(){
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let name = ss.getName();
  console.log(name);
}

変数の説明

ss = ユーザーが開いているスプレッドシートファイル
name = スプレッドシート名

getActiveSpreadsheetは次のような場面で利用することができます。

トリガーについて知りたい場合は下記の記事も参考にしてください。

あわせて読みたい

GASでスプレッドシートの編集時トリガーでフォルダを作成してみる 以前の記事でトリガーを利用しスプレッドシートのセルを編集する毎にアラートを出すスクリプトを紹介しました。  【GAS】 スプレッスプレッドシートのセルを編集す…

getActiveSpreadsheetはスタンドアロンのスクリプトでは基本的には利用できない関数となりますのでご注意ください。

スプレッドシートを操作するユーザーがいて初めて利用が可能になります。

getActiveSpreadsheetはスタンドアロンスクリプトでは利用ができない

getActiveSheet()サンプルコード

引数に入れる値

引数なし

サンプルコード

//ユーザーが開いているスプレッドシートを取得するコード
function sampleCodeFogetActiveSpreadsheet(){
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let name = sheet.getName();
  console.log(name);
}

変数の説明

ss = ユーザーが開いているスプレッドシートファイル
sheet = ユーザーが選択しているシート
name = シート名

こちらもgetActiveSpreadsheetと同じように次の条件下で利用できます。

getActiveSheetはスタンドアロンのスクリプトでは基本的には利用できない関数となりますのでご注意ください。

スプレッドシートを操作するユーザーがいて初めて利用が可能になります。

getActiveSheetはスタンドアロンスクリプトでは利用ができない

getSheetByName(name)サンプルコード

引数に入れる値

• name（文字列） — ファイル名を指定

サンプルコード

//シート名を指定してシートを取得するコード
function sampleCodeForOpenFile1(){
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let name = "シート1";
  let sheet = ss.getSheetByName(name);
}

変数の説明

ss = スプレッドシートファイル
name = スプレッドシート名

getSheetByNameはその名の通り、シート名を使ってシートを取得する方法です。

対象のシートがない場合はnullが返されることになります。

getSheets()サンプルコード

引数に入れる値

引数なし

サンプルコード

//スプレッドシートのすべてのファイルを取得するコード
function sampleCodeForOpenFile1(){
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheets = ss.getSheets();
  let i;
  for (i in sheets) {
    let sheet = sheets[i];
    let sheetName = sheet.getName();
    console.log(sheetName);
  }
}

変数の説明

ss = スプレッドシートファイル
sheets = スプレッドシートにあるすべてのシートが配列に入る
sheet = 単一のシート
sheetName = スプレッドシート名

getSheetsでは、スプレッドシートにある全てのシートを一括で取得します。

取得する順番は、スプレッドシート上で見えている一番左のシートから順番に配列でシートが格納されます。

シートを取り出す際は、ループ処理を使って一つずつのシートに対して処理をしていきます。

この場合、シート名を取得して条件分岐をして特定のシートに対してのみ処理を行うというような事を組み合わせることが多いかと思います。

getRange(row, column, numRows, numColumns)サンプルコード

引数に入れる値

• row（数値） — 指定したい行の数値を代入
• column（数値） — 指定したい列の数値を代入
• numRows（数値） — rowから何行を取得したいかを数値で代入（単一セルを取得する場合は必要ない）
• numColumns（数値） — columnから何列を取得したいかを数値で代入（単一セルを取得する場合は必要ない）
  

スプレッドシートでは、行（Row）と列（Column）という呼び方をするのですが、横一行のことを行と呼び、縦一列のことを列と呼びます。

上記のスクリーンショットでは、

ハイライトされている5行目のような1行を行と呼ぶ
ハイライトされているC列のような1列を列と呼ぶ

読者の方と共通認識を持つためにまずは言葉の定義を致しました。

サンプルコード

//A5セルを指定するプログラム
function sampleCodeForGetRangeA5() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let row = 5;
  let column = 1;
  let range = sheet.getRange(row, column);
  sheet.setActiveRange(range);
}

//C3セルを指定するプログラム
function sampleCodeForGetRangeC3() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let row = 3;
  let column = 3;
  let range = sheet.getRange(row, column);
  sheet.setActiveRange(range);
}

//A1:C3セルを指定するプログラム
function sampleCodeForGetRangesA1toC3() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let row = 1;
  let column = 1;
  let numRows = 3;
  let numColumns = 3;
  let range = sheet.getRange(row, column, numRows, numColumns);
  sheet.setActiveRange(range);
}

//C4:F6セルを指定するプログラム
function sampleCodeForGetRangesC4toF6() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let row = 4;
  let column = 3;
  let numRows = 3;
  let numColumns = 4;
  let range = sheet.getRange(row, column, numRows, numColumns);
  sheet.setActiveRange(range);
}

変数の説明

ss = スプレッドシートファイル（Spreadsheetオブジェクト）
sheet = 単一のシート（Sheetオブジェクト）
row = セルの行番号
column = セルの列番号
numRows = 起点の行番号から何行目までかを指定する数値
numColumns = 起点の列番号から何列目までかを指定する数値
range = 指定されたセル範囲（Rangeオブジェクト）

動作を確認するためのスプレッドシートを用意しました。

サンプルファイルの上部メニューには、セルを指定というカスタムメニューを用意しました。

一番最初に実行するときだけスクリプトに許可を求められますので、下記記事の手順に従ってGASに実行許可の付与をお願いします。

あわせて読みたい

無料のアカウントでGASに初回の実行許可を与える方法【超入門】 今回と次回の投稿では、Google Apps Scriptでその他のG Suiteサービスの操作を行う際に必要なアクセス許可について説明します。Googleアカウントには無料のGoogleアカウ…

サンプルのコードでは、単一セルを指定する方法とセル範囲を指定する方法をサンプルとして用意しています。

スプレッドシートで値を読み込んだり、書き込んだりする場合は、このようにセルを指定することから始まります。

使い方は非常にわかりやすいと思いますので、何度か試してみて使い方を身に着けてしまいましょう。

上記の方法で指定する場合、行と列を数字で指定することになります。

A1を指定するときには行番号 = 1、列番号 = 1となります。

getRangeByName(name)サンプルコード

引数に入れる値

• name（文字列） — 名前付きの範囲名を指定

サンプルコード

//単一の名前付きセルを指定するプログラム
function sampleCodeForGetRangeByName() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let name = "単一の名前付きセル";
  let range = ss.getRangeByName(name);
  ss.setActiveRange(range);
}

//単一の名前付きセルを指定するプログラム
function sampleCodeForGetRangesByName() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let name = "名前付きのセル範囲";
  let range = ss.getRangeByName(name);
  ss.setActiveRange(range);
}

変数の説明

ss = スプレッドシートファイル（Spreadsheetオブジェクト）
name = 名前をつけた範囲名
range = 指定されたセル範囲（Rangeオブジェクト）

名前をつけた範囲を取得する方法です。

名前付きの範囲については、スプレッドシートで一元的に管理されていますので、シート名を指定せずにファイルを範囲を取得することが可能です。

ただし、処理速度はそれほど速くない傾向があります。

このメソッドの使いやすい点は2つあります。

このような強みもありますので、行や列が追加 / 削除される可能性がある場合やシート名が編集される可能性がある場合に使ってみてください。

getActiveRange()サンプルコード

引数に入れる値

引数なし

サンプルコード

//ユーザーが選択しているセルを取得
function sampleCodeForGetActiveRange() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let range = sheet.getActiveRange();
}

変数の説明

ss = ユーザーが開いているスプレッドシートファイル
sheet = ユーザーが選択しているシート
range = ユーザーが選択しているセル範囲

こちらもgetActiveSpreadsheetやgetActiveSheetと同じように次の条件下で利用できます。

getActiveRangeはスタンドアロンのスクリプトでは基本的には利用できない関数となりますのでご注意ください。

スプレッドシートを操作するユーザーがいて初めて利用が可能になります。

getActiveRangeはスタンドアロンスクリプトでは利用ができない

getValue()サンプルコード

引数に入れる値

引数なし

サンプルコード

//単一セルの値を取得するコード
function sampleCodeForGetValue() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let range = sheet.getActiveRange();
  let value = range.getValue();
  console.log(value);
}

変数の説明

ss = ユーザーが開いているスプレッドシートファイル
name = ユーザーが選択しているスプレッドシート名
range = ユーザーが選択しているセル範囲
value = セルの値

単一セルから値を取得したい場合に利用するのがgetValueです。

取得できる値は基本的には文字列として取得できます。

対象のセルに関数が入っている場合でも関数の出力結果を文字列として取得されます。

表示形式の設定によって、期待する値が取得できないケースがあります。

処理でエラーが発生する場合や、条件に期待通り一致しないなどがあれば表示形式をまずは疑ってみましょう。

表示形式の設定によって文字列ではなく、数値や日付オブジェクトとして取得される場合がある。

getValues()サンプルコード

引数に入れる値

引数なし

サンプルコード

//複数のセルから一括で値を取得するコード
function sampleCodeForGetValues() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let range = sheet.getActiveRange();
  let values = range.getValues();
  console.log(values);
}

変数の説明

ss = ユーザーが開いているスプレッドシートファイル
name = ユーザーが選択しているスプレッドシート名
range = ユーザーが選択しているセル範囲
values = 複数セルの値の二次元配列

複数セルから一括で値を取得する方法です。

例えば、A1:B2の値をgetValuesで取得した場合は次のような形で値が取得されます。

[
    ["A1の値", "B1の値"],
    ["A2の値", "B2の値"]
]

一般的にgetValueを多用して複数のセルからデータを取得するよりも、getValuesを使って一括で値を取得するほうが処理速度が早い傾向があります。
※もちろん、データ量やその他の要因によってはgetValueの方が早い場合もあります。

setValue(value)サンプルコード

引数に入れる値

• value（文字列 or 数値 or 日付オブジェクト） — 名前付きの範囲名を指定

サンプルコード

//単一のセルに値を出力するコード
function sampleCodeForSetValue) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let range = sheet.getActiveRange();
  let value = "テスト";
  range.setValue(value);
}

変数の説明

ss = ユーザーが開いているスプレッドシートファイル
sheet = ユーザーが選択しているシート
range = ユーザーが選択しているセル範囲
value = 出力する値

setValueは単一のセルに値を出力する方法です。

基本的には1つのセルを取得して使用するのですが、複数のセル範囲に対しても実行することは可能です。

その場合は選択されたすべてのセルに同じ値が出力されます。

setValues(values)サンプルコード

引数に入れる値

• values（二次元配列） — 二次元配列で複数の値を格納

サンプルコード

//複数のセルに値を一括出力するコード
function sampleCodeForGetRangeByName() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let range = sheet.getRange(1, 1, 2, 2);
  let values = [
    ["テスト1", "テスト2"],
    ["テスト3", "テスト4"]
  ];
  range.setValue(values);
}

変数の説明

ss = ユーザーが開いているスプレッドシートファイル
sheet = ユーザーが選択しているシート
range = ユーザーが選択しているセル範囲
values = 出力する値の二次元配列

setValuesは複数のセルに対して一括して値を出力する方法です。

このメソッドであれば、少ない処理回数でスピーディに値の出力が可能です。

ただし、注意点としては選択したセルの範囲と二次元配列の値の数が同じでなければなりません。

A1:C2の範囲を指定しているにもかかわらず、値の二次元配列が下記のような場合はエラーになります。

[
    ["A1の値", "B1の値"],
    ["A2の値", "B2の値"]
]

また、配列の各要素の長さは同じにしなければエラーになります。

例えば、下記のような配列をsetValuesで一括処理しようとした場合

[
    ["A1の値", "B1の値", "C1の値"],
    ["A2の値", "B2の値"]
]

1つ目の要素数 = 3、2つ目の要素数 = 2となり、うまく値を出力することができません。

上記のような場合は、下記のようにして配列の長さを合わせます。

[
    ["A1の値", "B1の値", "C1の値],
    ["A2の値", "B2の値", ""]
]

値を出力しないセルに対しては””を指定して空白を出力するようにします。

まとめ

今回の記事では、

1. スプレッドシートの取得
2. シートの指定
3. セルの指定
4. 値の取得 / 出力

に使えるコードをご紹介しました。

このページにあるメソッドを使えば、スプレッドシートで行うことができる基本的な操作は実行できるようになると思います。

繰り返し使って使い方を覚えてしまいましょう。