GASでのスプレッドシートのデータ取得・書き込みについて
GoogleAppsScriptで扱うスプレッドシートのデータ取得・書き込みについて解説します。
概要
GASでGoogleスプレッドシートを利用する場合、シートのセルを選択してデータを取得したり書き込んだりする場面が多々あるかと思います。
前回の記事では範囲選択の方法について解説しました。
今回は、データの取得・書き込み方法とデータ形式について解説します。
使用するGAS及びスプレッドシートは前回のものを使用したいと思います。
スプレッドシートには適当なデータを入れておき、シート名はそのまま「シート1」としています。
1．基本的なデータの取得方法
スプレッドシート上のセル内のデータ取得には色々な種類があります。
まずは基本の範囲オブジェクトに対して、データの取得を行うものについて解説します。
1-1. １つのセルのデータを取得 getValue()
getValue()では１つのセルに入力されたデータを取得します。
つまり、複数のセル（範囲）を選択した範囲オブジェクトに対しては実行できません。注意しましょう。
【例】A2セルのデータを取得する場合：「りんご」
function getValue() {
// スプレッドシート＆シートオブジェクトを取得
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A2セルを選択
var range = sheet.getRange('A2');
// セルの値を取得
var value = range.getValue();
// 取得したデータを実行ログに表示
console.log(value);
}
取得したデータはそのままのデータ型で取得されます。
数値が入力されたセルであれば数値型データ、日付が入力されたセルであればDate型データで取得されます。
今回の場合は文字列型データが取得されています。
入力された内容をデータ型問わず表示された文字の形で取得したい場合については別途後述します。
1-2. １つ以上のセルのデータを取得 getValues()
getValues()では１つ以上のセルに入力されたデータを取得します。
１つ以上のセルからデータを取得する都合上、取得したデータの格納方法が少し複雑で、2次元配列という形になります。
2次元配列とは配列の中に配列が格納されているもののことです。
実際の例を見ながら段階的に解説します。
【例１】A2セルのデータを取得する場合：「りんご」
function getValues() {
// スプレッドシート＆シートオブジェクトを取得
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A2セルを選択
var range = sheet.getRange('A2');
// １つ以上のセル（今回は１つのセル）の値を取得
var values = range.getValues();
// 取得したデータを実行ログに表示
console.log(values);
}
実行結果が[['りんご']]となりました。
これは取得した範囲の中の１行目１列目に’りんご’というデータが入っていることを表しています。
図解すると以下のようになります。
データの取り出し方は以下のようになります。
function getValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A2セルを選択
var range = sheet.getRange('A2');
var values = range.getValues();
console.log('1行目 1列目 = ' + values[0][0]);
}
まだ少しわかりにくいと思いますので、次に取得する列を増やして見てみましょう。
【例２】A2～C2セルのデータを取得する場合：「りんご」「くだもの」「赤」
function getValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A2～C2セルを選択
var range = sheet.getRange('A2:C2');
var values = range.getValues();
console.log(values);
}
実行結果が[['りんご', 'くだもの', '赤']]となりました。
これは取得した範囲の中の１行目の１列目～３列目にデータが入っていることを表しています。
図解すると以下のようになります。
内の配列の要素が増えています。
データの取り出し方は以下のようになります。
function getValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A2～C2セルを選択
var range = sheet.getRange('A2:C2');
var values = range.getValues();
console.log('1行目 1列目 = ' + values[0][0]);
console.log('1行目 2列目 = ' + values[0][1]);
console.log('1行目 3列目 = ' + values[0][2]);
}
次は取得する行を増やして見てみましょう。
【例３】A2～C2セルのデータを取得する場合：「りんご」「くだもの」「赤」、「にんじん」「野菜」「オレンジ」
function getValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A2～C3セルを選択
var range = sheet.getRange('A2:C3');
var values = range.getValues();
console.log(values);
}
実行結果が[['りんご', 'くだもの', '赤'], [ 'にんじん', '野菜', 'オレンジ' ]]となりました。
これは取得した範囲の中の１行目の１列目～３列目と２行目の１列目～３列目にデータが入っていることを表しています。
図解すると以下のようになります。
行が増えると内の配列（=外の配列の要素）が増えます。
データの取り出し方は以下のようになります。
function getValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A2～C3セルを選択
var range = sheet.getRange('A2:C3');
var values = range.getValues();
console.log('1行目 1列目 = ' + values[0][0]);
console.log('1行目 2列目 = ' + values[0][1]);
console.log('1行目 3列目 = ' + values[0][2]);
console.log('2行目 1列目 = ' + values[1][0]);
console.log('2行目 2列目 = ' + values[1][1]);
console.log('2行目 3列目 = ' + values[1][2]);
}
また、指定した範囲内にデータのないセルがあった場合は下記のようになります。
データのないセルは空白の文字列''として扱われ、配列の要素数は保持されます。
2．直接指定でのデータの取得方法
先ほどの例では範囲オブジェクトに対してデータの取得を行いましたが、今度は範囲オブジェクトを用いない方法を紹介します。
１つ以上のセルのデータを取得 getSheetValues(startRow, startColumn, numRows, numColumns)
getSheetValues(startRow, startColumn, numRows, numColumns)では取得範囲の指定をしながら１つ以上のセルに入力されたデータを取得します。
startRowは範囲の開始行：2行目からなら2 startColumnは範囲の開始列：A列からなら1 numRowsは範囲の行数：2行分なら2 numColumnsは範囲の列数；3列分なら3
取得するデータは前項と同じく2次元配列となります。
【例】A2～C2セルのデータを取得する場合：「りんご」「くだもの」「赤」、「にんじん」「野菜」「オレンジ」
function getSheetValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A2～C3セルを選択
var values = sheet.getSheetValues(2, 1, 2, 3);
console.log(values);
console.log('1行目 1列目 = ' + values[0][0]);
console.log('1行目 2列目 = ' + values[0][1]);
console.log('1行目 3列目 = ' + values[0][2]);
console.log('2行目 1列目 = ' + values[1][0]);
console.log('2行目 2列目 = ' + values[1][1]);
console.log('2行目 3列目 = ' + values[1][2]);
}
3．表示文字列データの取得方法
ここまでの例では、スプレッドシートが自動的に処理したデータ型のままでデータの取得を行っていました。
とはいえこれまでの例は全て文字列型データだったのでピンと来ないと思います。
下記の例を試してみましょう。
【例】データ型での取得
スプレッドシートに「シート3」を作成し、このようにデータを入力します。
getValues()で取得してみましょう。
function testGetValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート3');
// A1～B4セルを選択
var range = sheet.getRange('A1:B4');
var values = range.getValues();
console.log(values[0][0] + ' : ' + values[0][1]);
console.log(values[1][0] + ' : ' + values[1][1]);
console.log(values[2][0] + ' : ' + values[2][1]);
console.log(values[3][0] + ' : ' + values[3][1]);
}
文字列や数値型のデータはそのままでも問題なさそうですが、日付やパーセントはスプレッドシート上と明らかに表示が変わってしまっています。
※パーセントはデータ型ではなくスプレッドシートによる変換です。
日付などはDate型データとなるため、日数計算に使用する場面では実はとても便利です。
しかし、スプレッドシート上に表示された文字列のまま使いたい場面では逆に不便になります。
そんな際にはgetDisplayValue()とgetDisplayValues()を使用します。getDisplayValue()とgetDisplayValues()はスプレッドシート上に表示された文字列のままデータを取得してくれます。
getDisplayValue()は１つのセルに対して用い、getDisplayValues()は１つ以上のセルに対して用います。
それぞれの使い分けは
| データ型で取得 | 表示文字列で取得 | |
|---|---|---|
| １つのセル | getValue() | getDisplayValue() |
| １つ以上のセル | getValues() | getDisplayValues() |
【例】表示文字列での取得
getDisplayValues()を試してみます。
function testGetValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート3');
// A1～B4セルを選択
var range = sheet.getRange('A1:B4');
var values = range.getDisplayValues();
console.log(values[0][0] + ' : ' + values[0][1]);
console.log(values[1][0] + ' : ' + values[1][1]);
console.log(values[2][0] + ' : ' + values[2][1]);
console.log(values[3][0] + ' : ' + values[3][1]);
}
スプレッドシート上での表示と同じ形で取得できました。
4. データの書き込み方法
データの書き込みにはsetValue(value)とsetValues(values)の2種類があります。
違いは取得の際と同じで、setValue(value)は１つのセルに対してのみ、直接値を書き込み、setValues(values)は１つ以上の１つ以上のセルに対して、2次元配列の形で値を書き込みます。
それぞれ試してみましょう。
【例１】１つのセルに書き込み
「シート1」のA7セルに「コーヒー」と書き込みます。
function setValue() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A7セルを選択
var range = sheet.getRange('A7');
var values = range.setValue('コーヒー');
}
【例２】１つ以上のセルに書き込み
「シート1」のA7～C8セルに「コーヒー」「飲み物」「黒」、「抹茶」「飲み物」「緑」と書き込みます。
function setValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
// A7セルを選択
var range = sheet.getRange('A7:C8');
var values = range.setValues([['コーヒー', '飲み物', '黒'], ['抹茶', '飲み物', '緑']]);
}
配列の要素数が指定した範囲と一致していない場合などにはエラーとなってしまうので注意してください。
また、setValues(values)の場合は１つのセルに書き込む場合でも[['コーヒー']]のようにする必要があることにも注意が必要です。
まとめ
GASで行うスプレッドシートの操作で肝心となるデータの取得・書き込みについてご紹介しました。getValues()やsetValues(values)は扱うデータが2次元配列となる点で、初心者には最初の難関になるかと思いますが、
スプレッドシートからデータを取得する回数・書き込む回数が少なければ少ないほど処理にかかる時間は短縮できるので、
複数のセルのデータを取得する際や書き込む際には是非活用してみてください。
