はじめに
当エントリでは、Google Apps Script(GAS)からGoogleスプレッドシートを扱う際のメソッドを記述しています。
(他の記事の前フリとして作成しておりましたが、長くなりすぎたので切り出しました)
Google Apps Scriptのはじめかたは以下を参照ください。
1.スプレッドシートへのアクセス
A. 親(コンテナ)スプレッドシートへのアクセス
インストール型(スプレッドシートから作成した)GASの場合は、SpreadSheetApp.getActive() で、親(コンテナ)のスプレッドシートを取得可能です。
let spreadSheet = SpreadSheetApp.getActive();
B. スプレッドシートのID指定でアクセス
単体として作成したGASの場合や、親スプレッドシートとは別のスプレッドシートにアクセスする場合はSpreadSheetApp.OpenById("Spreadsheet ID")で取得可能です。(勿論自身の親のスプレッドシートも取得できます)
引数として指定するSpreadsheet ID はスプレッドシートのURLの下記部分が該当します。
https://docs.google.com/spreadsheets/d/[ Spreadsheet ID ]/edit
let spreadSheet = SpreadSheetApp.openById("Spreadsheet ID")
2. シートへのアクセス
A. シート名を指定してアクセス
スプレッドシートから.getSheetByName("シート名") でアクセスします。
let sheet = spreadSheet.getSheetByName("シート名")
B. シート配列を取得して添字でアクセス
.getSheets()でシートの配列を取得し、添字にてアクセスします。
let sheets = spreadSheet.getSheets(); //シートの配列が取得される
let sheet = sheets[0]; //シートの1番目を取得
let sheet = spreadSheet.getSheets()[0]; //この記述でも取得可能
3. セルの範囲選択
シートから.getRange()でアクセスします。
パラメータの指定方法は下記の5種があり、用途により使い分けが可能です。
シートの原点となる"A1"は[1,1]である(0オリジンでない) ことに注意が必要です。
| 指定方法 | 取得対象 | 使用例・備考 |
|---|---|---|
| (行番号,列番号) | 単一セル | (1,1)でA1 |
| (行番号,列番号,行数) | 1列を複数行 | (1,1,3)でA1～A3 |
| (行番号,列番号,行数,列数) | 範囲 | (1,1,3,4)でA1～D3 |
| ("A1形式") | 単一～範囲 | ("A1")で単体、("A1:C3")で範囲を選択可能 |
| ("R1C1形式") | 単一～範囲 | ("R1C1")で単体、("R1C1:R4C3")で範囲を選択可能 |
"A1形式"と"R1C1形式"については、スプレッドシートからシート名を含めた範囲を選択することも可能です。
| 指定方法 | 範囲 | 使用例・備考 |
|---|---|---|
| ("A1形式") | 単一～範囲 | ("シート名!A1")で単体、("シート名!A1:C3")で範囲を選択可能 |
| ("R1C1形式") | 単一～範囲 | ("シート名!R1C1")で単体、("シート名!R1C1:R4C3")で範囲を選択可能 |
let rangeA = sheet.getRange(1,1); //A1セル単体
let rangeB = sheet.getRange(4,3,4); //C4～C7セル
let rangeC = sheet.getRange(4,3,4,2); //C4～D7セル
let rangeD = sheet.getRange("C4"); //C4セル
let rangeE = sheet.getRange("C4:D7"); //C4～D7セル
let rangeF = spreadSheet.getRange("シート1!C4:D7"); //シート1のC4～D7セル
let rangeG = sheet.getRange("R1C1"); //A1セル単体
let rangeH = sheet.getRange("R1C1:R3C4"); //A1～D3セル
let rangeI = spreadSheet.getRange("シート2!R1C1:R3C4"); //シート2のA1～D3セル
4. セルの値取得
選択範囲がセル単体の場合はgetValue()で取得が可能です。
選択範囲が複数セルの場合はgetValues()で二次元配列array[行][列]として取得されます。
getRange("A1")で選択したセル単体に対しgetValues()を用いた場合でも、array[1][1]の二次元配列として返却されます。
getValues()メソッドは、getRange()で選択した範囲に関わらず、返却値は必ず二次元配列となる事に注意が必要です。
let rangeA = sheet.getRange(1,1); //A1セル選択
let valueA = rangeA.getValue(); //A1セルの値取得
let rangeB = sheet.getRange(4,3,4); //C4～C7セル選択
let valuesB = rangeB.getValues(); //C4～C7セルの値をarray[4]配列に格納
let rangeC = sheet.getRange("C4:D7"); //C4～D7セル選択
let valuesC = rangeC.getValues(); //C4～D7セルの値をarray[4][2]配列に格納
5. セルへの値挿入
選択範囲がセル単体の場合はsetValue(value)で挿入が可能です。
選択範囲が複数セルの場合はsetValues(array)で一括挿入が可能です。
setValues(data)で指定する引数は、getValues()同様二次元配列である必要があります。選択範囲が単一セルの場合でも二次元配列array[1][1]を指定しなければエラーとなります。
let rangeA = sheet.getRange(1,1); //A1セル選択
rangeA.setValue(34); //A1セルに34を挿入
let insertsB = [8, 5, 3, 2];
let rangeB = sheet.getRange(4,3,4); //C4～C7セル選択
rangeB.setValues(insertsB); //C4～C7セルにそれぞれ8,5,3,2を挿入
let insertsC = [[2,4],[3,2],[8,1][5,3]]
let rangeC = sheet.getRange("C4:D7"); //C4～D7セル選択
rangeC.setValues(insertsC); //C4～D7セルにそれぞれ[2,4],[3,2],[8,1],[5,3]を挿入
6. データがあるセルの範囲すべてを選択する
シートに対しgetDataRange()を使うことで、データのある範囲をすべて選択する事ができます。
情報を一括で操作したい場合や、データが入っている行の次の行番号を取得したい場合などに有用です。
「データがあるセルの範囲」は、原点となるA1セルから、データが存在する最後の行及び列までが対象となることに注意が必要です。仮にC4以降にのみデータが存在していても、getDataRange()はA1から取得してしまいます。
let dataRange = sheet.getDataRange(); //データのある範囲を選択
let values = dataRange.getValues(); //データのある範囲の値を配列として取得する
//下記でも可
let valuesB = sheet.getDataRange().getValues();
7. データがあるセルの末尾の行数、列数を取得する
行数の場合はgetLastRow()を、列数の場合はgetLastColumn()を呼ぶ事で、データがあるセルの末尾を呼び出す事が可能です。
getRange(1,1,getLastRow(),getLastColumn())と指定した場合は、上記のgetDataRange()と同じ範囲を選択することになります。
let row = sheet.getLastRow(); //データの最終行数を取得
let col = sheet.getLastColumn(); //データの最終列数を取得
//A1からデータの最終行、最終列までを選択(=getDataRange())
let dataRange = sheet.getRange(1,1,getLastRow(),getLastColumn());
//C4からデータの最終行、最終列までを選択
let limitedRange = sheet.getRange(4,3,getLastRow(),getLastColumn());
//E列について1行目を除いた最終行までを選択
let rangeE = sheet.getRange(2,5,getLastRow()-1);
おわりに
今回はGASの基礎としてのスプレッドシート操作をまとめました。
スプレッドシートへのアクセスは、ExcelにたいすrVBAや.NetでのMicrosoft.Office.Interop.Excel とほぼ同様の手順かと思います。Interopと違いメモリ解放を気にしなくていいので楽ですね。
Rangeを指定する際は A1が[1,1] である事が個人的に最重要なポイントでした。
メソッドチェーンについて
Google Apps ScriptはJavaScriptをベースとしているので、メソッドチェーンでの記述も可能です。
getDataRange().getValues()などはセットで使用される場合が多いため、メソッドチェーンで積極的に記述を省略するのも有用かと思われます。
//シートを取得する
let sheet = SpreadSheetApp.getActive().getSheetsByName("シート1");
//データを取得する
let data = sheet.getRange(1,1,6,7).getValues();
//別シートのデータ範囲にデータを一括設定する
SpreadSheetApp.getActive().getRange("シート2!A3:G8").setValues(data);
//親スプレッドシートの1番目のシートのデータ部分の値をすべて取得する
let values = SpreadSheetApp.getActive().getSheets()[0].getDataRange().getValues();
参考
以下は参考にさせて頂いたエントリ・サイトになります。