GASを使ったスプレッドシート上のデータの入出力について

Google Apps Script（以下GAS）を使ってスプレッドシート上の値を取得したり、逆に書き出したりする処理について纏めます。

操作対象シートの取得

シート上のセルからデータを取得する前にまずこちらから。

VBAなどでも、まずは「ThisWorkbook.Worksheets(～)」と書いて『どのブック』の『どのシート』を操作するかいった事を書いていますよね（Excelの場合はいきなりCellsでも動きますが）。スプレッドシートでも同じです。

 

GASでのスプレッドシートの取得は以下の2種類があります。

・SpreadsheetApp.getActiveSpreadsheet();

・SpreadsheetApp.openById(“xxxxxxxxx”)
※「xxx」部分には対象のシートIDが入る

上の処理は「ActiveSpreadsheet」の通り、「アクティブ（現在操作中の）スプレッドシート」を取得する事ができ、後述の処理はID指定で開くスプレッドシートを選択する事ができます。

 

IDについてはスプレッドシートのURLから確認する事ができます。

「･･･/d/****************/edit･･･」

上記「*」部分に相当する部分の文字列がスプレッドシートのIDです。

 

この関数は戻り値として「スプレッドシート」が返って来ます。「シート」と名は付いていますがイメージとしてはエクセルの『ブック』ですね。

 

・SpreadsheetApp.getActiveSpreadsheet();

と似た名前に、

・SpreadsheetApp.getActiveSheet()

という関数が存在しますが、こちらは「スプレッドシート内の」アクティブなシートが取得できます。こちらはそのまま「シート」です。Excelで言う「シート」です。そのままです。

 

一応、分かり易くするために以下のスプレッドシートで、

以下の関数を使って、

function myFunction() {
  var sht = SpreadsheetApp.getActiveSheet();
  var sprSht = SpreadsheetApp.getActiveSpreadsheet();
  Browser.msgBox("SpreadsheetApp.getActiveSheet()=" + sht.getName() + "\\n" + 
    "SpreadsheetApp.getActiveSpreadsheet()=" + sprSht.getName());
}

どの様な値が表示されるかを確認してみましょう。

内容は「SpreadsheetApp.getActiveSheet()」と「SpreadsheetApp.getActiveSpreadsheet()」でそれぞれ取得したオブジェクトに対して「getName()」を使用し、名前を表示させるという処理です。

 

結果は以下の様になりました。

予想通りの結果ですね。

シート上の値を取得して変数に格納する

それでは早速本題に入りましょう。

シート上に以下の様なデータが入力されていると仮定し、その値をGASを使って変数に格納する処理を書いてみます。

値を取得するためにはまず「どの場所から」というのを記述しなければいけません。

そこで「getRange()」関数を使用して位置を指定した後、「getValue()」関数で値を取得します。

getRangeの使い方は全部で4種類あり、それぞれ以下の通りです。

• getRange(row, column)
• getRange(a1Notation)
• getRange(row, column, numRows)
• getRange(row, column, numRows, numColumns)

指定の仕方や動きは実際に見た方が早いので、以下のサンプルプログラムを用意しました。

function GetSheetValueSample(){
  var sht = SpreadsheetApp.getActiveSheet();
  var val = "";
  
  // ①getRange(row, column)
  val += sht.getRange(1, 1).getValue() + "\\n";
  
  // ②getRange(a1Notation)
  val += sht.getRange("A1").getValue() + "\\n";
  
  // ③getRange(row, column, numRows)
  val += sht.getRange(1, 1, 2).getValues() + "\\n";
  
  // ④getRange(row, column, numRows, numColumns)
  val += sht.getRange(1, 1, 2, 2).getValues() + "\\n";
  
  Browser.msgBox(val);
}

実行した結果がこちら。

それぞれ解説します。

①getRange(row, column)

まず①の記述方法では、行と列を数値で指定する方法となります。

一番左上のセルを（1, 1）として、そこから○行目、○列目という数え方です。

②getRange(a1Notation)

続いて②は行列を「A1」などの文字列座標で渡す方法です。

行は良いですが、列は通常アルファベットで表示されているため、列が増えるほど「今何列目だっけ？」となってしまいますよね。列数を意識せずに指定できるため便利です。

③getRange(row, column, numRows)

③は範囲の取得に用いる方法です。

最初の「row, column」は①の記述そのままですが、最後に「numRows」が追加されています。3つめの引数には「始点から数えて何行目までを取得する」という風に記述します。

始点から数えるというのがポイントです。

例えば上図の様に「A7～A10」までの範囲を指定したいと考えた場合、始点から数えて4マス分となるため「getRange(1, 7, 4)」と記述します。

A8、A9、A10だから・・・3行下で3だな！と勘違いし易いので注意です。

 

そしてこの方法で取得した範囲のデータは、行ごとで配列化されて格納されています。

文字がユニコード表記になっていますが、「\u3042」はひらがなの「あ」を指します。

tmp1の値は③、tmp2は次の④で取得した結果です。

 

そしてもう一つ間違えやすいのが、この方法で範囲取得した状態の値を取得する場合、「getValues()」を使います。複数形のValuesです。

「getValue()」を使うと配列の一番最初の値しか取得できません。

④getRange(row, column, numRows, numColumns)

最後に④です。

こちらは③に列の指定が加わっただけで、考え方は一緒ですね。

上図の様なケースで「A7～C15」の範囲を指定する場合は、「getRange(1, 7, 9, 3)」となります。

おまけ

範囲取得のやり方がめんどくさい！

Excelみたいに始点と終点の座標入力する方法無いの！？

 

と言う方向けにこんな記述もご紹介しておきます。

sht.getRange("A1:A2").getValues()

②の変形ですが、「:（コロン）」で区切って左側を始点、右側を終点とすれば「A1～A2」の範囲を取得する事が可能です。

Excelでもお馴染みの書き方ですよね。

変数の値をシート上に出力する

今度はGASで用意した値をシート上に出力する方法です。

関数は、

sht.getRange(1, 1).setValue()

または

sht.getRange(1, 1).setValues()

を使用します。

 

それぞれの使い方は以下のサンプルプログラムをご覧下さい。

function SetSheetValueSample(){
  var sht = SpreadsheetApp.getActiveSheet();
  var test = "Test";
  var testArray = [["arr1", "arr2"],["arr3", "arr4"]];
  
  sht.getRange(1, 1).setValue(test);  // ①

  sht.getRange("B1:B2").setValue(test);  // ②
                   
  // ↓の記述だとエラー
  // sht.getRange(1, 3).setValues(testArray);
  
  // ↓の記述だとエラー
  // sht.getRange("C3:D3").setValues(testArray);      

  sht.getRange("C3:D4").setValues(testArray);  // ③
}

上記処理の結果は次の様になります。

②の様に範囲指定した状態でsetValue()を使用すると、指定範囲全てにその値を入力する事ができます。

そしてsetValues()の方は見てもらえると分かる通り、2次元配列状態となっている値を一気に展開する事が可能です。ループ使って要素を一つずつ出力しなくて済むので便利ですよね。

最もsetValuesの中では何をやっているは知りませんが・・・

 

Valuesの注意点として、出力する配列の大きさが指定した範囲と一致している必要があります。一致しない場合はエラーとなります。

 

また、setValues()には2次元配列の値しか渡す事ができません。

「”Test”」文字列単体や、「[“arr1”, “arr2”]」の様な1次元配列を渡すと、実行時に以下の様なエラーが表示されます。

「ArrayをObject[][]に変換できません。」＝2次元配列として扱えるものしか引数として渡せないよって事です。

おまけ

「setValuesを使って一括出力したいけど、範囲の大きさが処理によって変化するので固定値を入力できない！」

というケースがあると思います。

そんな場合は以下の様に、配列のlengthを取って指定すればOK。

function SetSheetValueSample(){
  var sht = SpreadsheetApp.getActiveSheet();
  var test = "Test";
  var testArray = [["arr1", "arr2"],["arr3", "arr4"]];
  
  sht.getRange(row, column, testArray.length, testArray[0].length).setValues(testArray);
} 

全体の配列の数＝出力行数なので、numRowsには「testArray.length」を。

1配列あたりのデータ数=出力列数なので、numColumnsには「testArray[0].length」をそれぞれセットしています。

行によってデータの数が異なる場合はエラーになるので、必ず1配列あたりのデータ数はそろえましょう。

 

どうしても統一できない場合は・・・for文使って1要素ずつ出力するしか無いですね。

 

 

今回はここまで。ただの入出力でも奥が深いですね！

他の操作方法についても順次解説していきたいと思います。