×

[PR]この広告は3ヶ月以上更新がないため表示されています。
ホームページを更新後24時間以内に表示されなくなります。



>> ユーザーガイド >> データの格納 >> ユーザープロパティ | ScriptDb | スプレッドシート | JDBC


スプレッドシート

Google.Apps.Scriptでデータを格納する最もシンプルな方法は、スプレッドシート内のセルにデータを格納することです。


WWW GoogleStyle


概要

スプレッドシートにバウンドされているスクリプトの場合、データはスプレッドシート内に格納するのが一般的です。なぜなら、これらのスクリプトは、アクティブなスプレッドシートへの組み込み概念を持っているからです。 ここにあるドキュメントの例では、スプレッドシートにバインドされているスクリプトについて述べています。
しかし、スタンドアロンのスクリプトでもスプレッドシート内にデータを格納することができます。 この場合は、スプレッドシートを開いたり使用するためには、スクリプトはSpreadsheetApp.openById()を介してスプレッドシートを明示的に指定しなければなりません。

Google.Spreadsheetsでは、スクリプトデータを格納するためのスプレッドシートの利用の有無を決める際に、考慮すべきいくつかの制限があります。例えば、スプレッドシートはすべてのシートにわたる40万合計セルと1シートあたり256列に制限されています。 制限の完全なリストについては、ヘルプセンターの記事をご覧ください。



基本の理解

スプレッドシート内でデータの保存や取得を行うための最も簡単な方法は、単に自セル内の各データを保存することです。Tools > Script Editorを選択して、スプレッドシートからスクリプトエディタを起動することができます。

下の画像に示すように、スプレッドシートに格納する製品名と製品番号のリストを持っていると仮定します。

ご覧のように、、製品名はA列に、製品番号はB列に格納されています。

スプレッドシートからデータを取得するには、データが格納されているスプレッドシートへのアクセスを取得する必要があり、その上でデータを保持しているスプレッドシート内の範囲を取得してからセルの値を取得します。 以下の例では、製品名や製品番号を取得してログに記録する方法を示しています。


function
printProductInfo() {
var
sheet =
SpreadsheetApp.
getActiveSheet();
var
dataRange = sheet.getDataRange().getValues();
for
(
var
i = 0; i < dataRange.length; i++) {
Logger.
log(
"Product name: "
+ dataRange[i][
0
]);
Logger.
log(
"Product number: "
+ dataRange[i][
1
]); } }

ログに記録されたデータを表示するにはスクリプトエディタからView > Logs...を選択します。

スプレッドシートへあらたな製品名と製品番号のデータを格納するには、スクリプトに次のコードを追加すればできます。


function
addProduct() {
var
sheet =
SpreadsheetApp.
getActiveSheet(); sheet.appendRow([
"Cotton Sweatshirt XL", "css004"
]); }

上のコードで、スプレッドシートの最下部に、指定された値で新しい行を追加しています。この関数を実行すれば追加された新しい行が表示されます。



高度なテクニック

このセクションでは、スプレッドシート内の構造化された方法でデータにアクセスし、それを保管するプロセスを簡素化するためにいくつかの有用な関数を提供します。

スプレッドシートのデータをJavaScriptに読み込む

このセクションでは、スプレッドシートから構造化されたデータを簡単に読み取り、データへのアクセスを容易にするJavaScriptオブジェクトを作成するための手順を説明します。 再利用可能なgetRowsDataメソッドなどのいくつかの有用な関数を提供します。

■ スプレッドシートの設定とサンプルスクリプト

  1. このチュートリアル用のテンプレートを開きます。
  2. 自分用のコピーを作成するためにFile > Make a copyを選択します。これでスプレッドシートとスクリプトの編集が可能になります。
  3. Tools > Script Editorでスクリプトエディタを開きます。
  4. プロジェクトで'reading_data'ファイルが表示されるはずです。
  5. 関数runExampleを実行してください。スプレッドシート内のテーブルにある3人目の雇用者の情報がメッセージボックスで表示されるはずです。

次のセクションでスクリプトがどのようにスプレッドシート内のデータを読み取っているのかを学びましょう。

■ テーブルの行毎データをJavaScriptオブジェクトに読み込む

はじめにスクリプトエディタでrunExample関数の次の部分のコードを探してください。


// Get the range of cells that store employee data.
var
employeeDataRange = ss.getRangeByName(
"employeeData"
);

Googleスプレッドシートでは、ユーザーにセルの名前付き範囲を定義するように推奨しています。この例では、範囲"employeeData"は"E45:A1"の範囲として定義されています。 スプレッドシートのData > Manage Rangesと選択して、'employeeData'からこれを確認できます。

名前付き範囲は、スプレッドシートのスクリプトを書くときに非常に便利で柔軟性があります。そこで、スクリプト内ではハードコーディング範囲の代わりにこれらの利用を検討してください。 ここに名前付き範囲の詳細についての説明があります。


// For every row of employee data, generate an employee object.
var
employeeObjects = getRowsData(sheet, employeeDataRange);

getRowsData関数は、すぐ下のrunExampleを定義し、このサンプル中での動作はほとんどがここで行われています。 これを再利用して、今後のスクリプトをきめ細かく調整することをお勧めします。


var
thirdEmployee = employeeObjects[2];

この時点では、スプレッドシート内のテーブルからすべてのデータがemployeeObjectsに読み込まれています。テーブル内の行ごとにJavaScriptオブジェクトが作成されています。 上記のコードは、テーブル内の3番目の従業員(メアリアン・パッカード)に関する情報を取得します。JavaScriptの配列は0ベースであることに注意してくださ。ですから、最初のエントリはemployeeObjects[0]となります。


var
stringToDisplay =
"The third row is: "
+ thirdEmployee.firstName +
" "
+ thirdEmployee.lastName; stringToDisplay +=
" (id #"
+ thirdEmployee.employeeId +
") working in the ";
stringToDisplay += thirdEmployee.department +
" department and with phone number ";
stringToDisplay += thirdEmployee.phoneNumber;

上記のコードは、従業員オブジェクトからのデータをすべて文字列に生成しているだけです。列名は大文字と小文字が混在したオブジェクトのプロパティ(たとえば、'First Name'のデータはobject.firstNameを使用して読み取ることができます)であることに注意してください。 任意のスプレッドシートでgetRowsData関数を使用することができるはずです。スプレッドシートにはサンプルのrunExampleを除くすべての関数をコピーする必要があります。

getRowsDataの動作を理解する

このセクションでは、getRowsData関数がどのように機能するかを詳細に説明します。Spreadsheetサービスと一般的なJavaScriptの概念の両方をカバーしています。

1. 関数 getRowsData


// getRowsData iterates row-by-row on the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - columnHeadersRowIndex: specifies the row number where the column names are stored.
// This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function
getRowsData(sheet, range, columnHeadersRowIndex) { columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;

関数 getRowsDataは3つの引数を持っています。最後の引数columnHeadersRowIndexは、オプションで、range引数の真上の行の行インデックスを初期値とします。 これは、関数内のコードの最初の行で実装されています。
もし、columnHeadersRowIndexが指定されずに関数が呼ばれた場合は、range.getRowIndex()-1がセットされます。
range.getRowIndex()-1Rangeオブジェクトの最初の行のインデックスを返します。


var
numColumns = range.getEndColumn() - range.getColumn() + 1;
var
headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);

新しいRangeオブジェクトは、関連するすべての列名を持つ単一の行データを含むように作成されています。


var
headers = headersRange.getValues()[
0
];
return
getObjects(range.getValues(), normalizeHeaders(headers));

該当するデータを表示するメッセージボックスを作成することで問題を迅速にデバッグできます。実際に上の例で、ヘッダー変数に格納されている列名のリストを出力するコードを挿入できます。 これを行うためには、JavaScriptの配列をシリアル化するために、標準的なJavaScript関数toSourceを必ず呼び出す必要があります。最終的にコードは次のようになります。


var
headers = headersRange.getValues()[
0
];
Browser.
msgBox(headers.toSource());
return
getObjects(range.getValues(), normalizeHeaders(headers));

getObjectsは次の2つのパラメーターで呼び出されます。

  1. JavaScriptの2次元配列はデータ範囲内のすべての値を含んでいて、たとえば、range.getValues()[0][1]は最初の行の2列目"Berger"を返します。
  2. 正規化された列名の配列。normalizeHeaders関数の分析は後ほど行います。

2. 関数 getObjects


function
getObjects(data, keys) {
var
objects = [];
for (var
i =
0
; i < data.length; ++i) {
var
object = {};
var
hasData = false; for (var j = 0; j < data[i].length; ++j) { var cellData = data[i][j];
if
(isCellEmpty(cellData)) {
continue
; }
object
[keys[
j
]] = cellData; hasData =
true
; }
if
(hasData) { objects.push(
object
); } }
return
objects; }

最初のforループは、2次元配列データ内の各行を反復処理します。2つ目のループは、セルのために与えられた行とプロセスデータ内の列を反復します。 すべての空ではない生成されたオブジェクトは、返されたオブジェクトの配列に追加されます。


object
[keys[j]] = cellData;

これは、名前keys[j]を持つオブジェクトプロパティをcellDataの値にセットするkeyステートメントです。

3. 列名の正規化関数


// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
// - header: string to normalize
// Examples:
// "First Name" -> "firstName"
// "Market Cap (millions) -> "marketCapMillions
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function
normalizeHeader(header) {
var
key =
""
;
var
upperCase = false;
for (var
i =
0
; i < header.length; ++i) {
var
letter = header[i];
if
(letter ==
" "
&& key.length >
0
) { upperCase =
true
;
continue
; }
if
(!isAlnum(letter)) {
continue
; }
if
(key.length ==
0
&& isDigit(letter)) {
continue
;
// first character must be a letter
}
if
(upperCase) { upperCase =
false
; key += letter.toUpperCase(); }
else
{ key += letter.toLowerCase(); } }
return
key; }

この関数は、純粋なJavaScriptです。基本的には、入力ヘッダー文字列を文字ごとに反復処理してすべての非英数字文字を削除します。また、JavaScriptオブジェクトのプロパティを簡単に定義するために使用する大文字と小文字が混在する文字列を返します。


// Returns an Array of normalized Strings.
// Arguments:
// - headers: Array of Strings to normalize
function
normalizeHeaders(headers) {
var
keys = [];
for (var
i =
0
; i < headers.length; ++i) {
var
key = normalizeHeader(headers[i]);
if
(key.length >
0
) { keys.push(key); } }
return
keys; }

この関数は、ヘッダー内の文字列配列で定義された文字列を、ひとつづつすべて正規化します。

■ テーブルの列毎データをJavaScriptオブジェクトに読み込む

getRowsData関数にも慣れたと思うので、行ごとではなく、列ごとにデータを読む方法を説明します。

スプレッドシートの2枚目のシートには、前のセクションと同じデータが縦列で整理されているものが含まれています。

どのようにしてgetColumnsDataのバージョンを実装すればよいでようか?一部に上記コードの再利用をすることでこれを行うことができます。


function runExercise() {
  
var
ss =
SpreadsheetApp.
getActiveSpreadsheet();
var
sheet = ss.getSheets()[
1
];
// Get the range of cells that store employee data.
var
employeeDataRange = sheet.getRange(
"B1:F5"
);
// For every row of employee data, generate an employee object.
var
employeeObjects = getColumnsData(sheet, employeeDataRange);
var
thirdEmployee = employeeObjects[
2
];
var
stringToDisplay =
"The third column is: "
+ thirdEmployee.firstName +
" "
+ thirdEmployee.lastName; stringToDisplay +=
" (id #"
+ thirdEmployee.employeeId +
") working in the "
; stringToDisplay += thirdEmployee.department +
" department and with phone number "
; stringToDisplay += thirdEmployee.phoneNumber; ss.msgBox(stringToDisplay); }
// Given a JavaScript 2d Array, this function returns the transposed table.
// Arguments:
// - data: JavaScript 2d Array
// Returns a JavaScript 2d Array
// Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
function
arrayTranspose(data) {
if
(data.length ==
0
|| data[0].length ==
0
) {
return null
; }
var
ret = [];
for (var i
=
0
; i < data[
0
].length; ++i) {
ret.push
([]); }
for (var i
=
0
; i < data.length; ++i) {
for (var j
=
0
; j < data[i].length; ++j) {
ret
[j][i] = data[i][j]; } }
return
ret; }
// getColumnsData iterates column by column in the input range and returns an array of objects.
// Each object contains all the data for a given column, indexed by its normalized row name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - rowHeadersColumnIndex: specifies the column number where the row names are stored.
// This argument is optional and it defaults to the column immediately left of the range;
// Returns an Array of objects.
function
getColumnsData(sheet, range, rowHeadersColumnIndex) { rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex()
- 1
;
var
headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(),
1
).getValues();
var
headers = normalizeHeaders(arrayTranspose(headersTmp)[
0
]);
return
getObjects(arrayTranspose(range.getValues()), headers); }

runExerciseを実行すると、セクション1のデータと同じ結果が返るはずです。

■ サンプルコードの完成版


function
runExample() {
var
ss =
SpreadsheetApp.
getActiveSpreadsheet();
var
sheet = ss.getSheets()[
0
];
// Get the range of cells that store employee data.
var
employeeDataRange = ss.getRangeByName(
"employeeData"
);
// For every row of employee data, generate an employee object.
var
employeeObjects = getRowsData(sheet, employeeDataRange);
var
thirdEmployee = employeeObjects[
2
];
var
stringToDisplay =
"The third row is: "
+ thirdEmployee.firstName +
" "
+ thirdEmployee.lastName; stringToDisplay +=
" (id #"
+ thirdEmployee.employeeId +
") working in the "
; stringToDisplay += thirdEmployee.department +
" department and with phone number "
; stringToDisplay += thirdEmployee.phoneNumber;
Browser.
msgBox(stringToDisplay); }
function
runExercise() {
var
ss = SpreadsheetApp.getActiveSpreadsheet();
var
sheet = ss.getSheets()[
1
];
// Get the range of cells that store employee data.
var
employeeDataRange = sheet.getRange(
"B1:F5"
);
// For every row of employee data, generate an employee object.
var
employeeObjects = getColumnsData(sheet, employeeDataRange);
var
thirdEmployee = employeeObjects[
2
];
var
stringToDisplay =
"The third column is: "
+ thirdEmployee.firstName + " " + thirdEmployee.lastName; stringToDisplay +=
" (id #"
+ thirdEmployee.employeeId +
") working in the "
; stringToDisplay += thirdEmployee.department +
" department and with phone number "
; stringToDisplay += thirdEmployee.phoneNumber; ss.msgBox(stringToDisplay); }

ライブラリー


// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - columnHeadersRowIndex: specifies the row number where the column names are stored.
// This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function
getRowsData(sheet, range, columnHeadersRowIndex) { columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex()
- 1
;
var
numColumns = range.getEndColumn() - range.getColumn() + 1;
var
headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(),
1
, numColumns);
var
headers = headersRange.getValues()[
0
];
return
getObjects(range.getValues(), normalizeHeaders(headers)); }
// getColumnsData iterates column by column in the input range and returns an array of objects.
// Each object contains all the data for a given column, indexed by its normalized row name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - rowHeadersColumnIndex: specifies the column number where the row names are stored.
// This argument is optional and it defaults to the column immediately left of the range;
// Returns an Array of objects.
function
getColumnsData(sheet, range, rowHeadersColumnIndex) { rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex()
- 1
;
var
headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(),
1
).getValues();
var
headers = normalizeHeaders(arrayTranspose(headersTmp)[
0
]);
return
getObjects(arrayTranspose(range.getValues()), headers); }
// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
// - data: JavaScript 2d array
// - keys: Array of Strings that define the property names for the objects to create
function
getObjects(data, keys) {
var
objects = [];
for (var i
=
0
; i < data.length; ++i) {
var
object = {};
var
hasData = false;
for (var j
=
0
; j < data[i].length; ++j) {
var
cellData = data[i][j];
if
(isCellEmpty(cellData)) {
continue
; }
object
[keys[j]] = cellData; hasData =
true
; } if (hasData) { objects.push(
object
); } }
return
objects; }
// Returns an Array of normalized Strings.
// Arguments:
// - headers: Array of Strings to normalize
function
normalizeHeaders(headers) {
var
keys = [];
for (var i
=
0
; i < headers.length; ++i) {
var
key = normalizeHeader(headers[i]);
if
(key.length >
0
) { keys.push(key); } }
return
keys; }
// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
// - header: string to normalize
// Examples:
// "First Name" -> "firstName"
// "Market Cap (millions) -> "marketCapMillions
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function
normalizeHeader(header) {
var
key =
""
;
var
upperCase =
false
;
for (var i
=
0
; i < header.length; ++i) {
var
letter = header[i];
if
(letter == " " && key.length >
0
) { upperCase =
true
;
continue
; }
if
(!isAlnum(letter)) {
continue
; }
if
(key.length == 0 && isDigit(letter)) {
continue
;
// first character must be a letter
}
if
(upperCase) { upperCase =
false
; key += letter.toUpperCase(); }
else
{
key
+= letter.toLowerCase(); } }
return
key; }
// Returns true if the cell where cellData was read from is empty.
// Arguments:
// - cellData: string
function
isCellEmpty(cellData) {
return typeof
(cellData) ==
"string"
&& cellData ==
""
; }
// Returns true if the character char is alphabetical, false otherwise.
function
isAlnum(char) {
return
char >=
'A'
&& char <=
'Z'
||
char
>= 'a' &&
char
<=
'z'
|| isDigit(
char
); }
// Returns true if the character char is a digit, false otherwise.
function
isDigit(
char
) {
return char
>=
'0'
&&
char
<=
'9'
; }
// Given a JavaScript 2d Array, this function returns the transposed table.
// Arguments:
// - data: JavaScript 2d Array
// Returns a JavaScript 2d Array
// Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
function
arrayTranspose(data) {
if
(data.length ==
0
|| data[
0
].length ==
0
) {
return null
; }
var
ret = [];
for (var i
=
0
; i < data[
0
].length; ++i) { ret.push([]); }
for (var i
=
0
; i < data.length; ++i) {
for (var j
=
0
; j < data[i].length; ++j) { ret[j][i] = data[i][j]; } }
return
ret; }

JavaScriptオブジェクトからのデータをスプレッドシートに書き込む

このセクションでは、スプレッドシートから構造化されたデータを簡単に読み取り、データのさまざまなビューを持つ第2のスプレッドシートを作成して値を代入する手順を案内します。 また、再利用のできる便利なsetRowsData関数を提供します。

■ スプレッドシートの設定とサンプルスクリプト

  1. このチュートリアルのためのテンプレートを開きます。
  2. File > Make a copyと選択して自分用のコピーを作成します。これでシートの編集とスクリプトを書き始めることができます。
  3. Tools > Script Editorでスクリプトエディタを開きます。
  4. 唯一"writing_data"というファイル名を持つプロジェクトが確認できるはずです。

このサンプルのオブジェクトは下のようなものです。