Webアプリでスプレッドシートのデータを操る

Webアプリにスプレッドシートのデータを表示したい。Webアプリからのデータをスプレッドシートに書き込みたい。

Webアプリのデータベースとして、スプレッドシートを使用することができます。

GASを使用して、スプレッドシートと連携する方法をまとめました。

スプレッドシートと連携する２つのスクリプト
1. Container Bound Scriptの作成
2. Standalone Scriptの作成
スプレッドシートの指定
シートを指定する
セルを指定する
データを取得する
1. 特定のセルの値を取得する
2. 複数のセルの値を取得する
データを書き込む
1. 特定のセルに値を書き込む
2. 複数のセルに値を書き込む

スプレッドシートと連携する２つのスクリプト

GASでスプレッドシートと連携するスクリプトには、２つの作り方があります。

Container Bound Script：スプレッドシートなどのファイルに紐づいて存在
Standalone Script：単独で存在

Container Bound Scriptの作成

Container Bound Scriptを作成するには、スプレッドシートを開き「ツール」→「スクリプトエディタ」をクリックします。

スクリプトエディタが開き、このスクリプトはスプレッドシートに紐づけられています。

Container Bound Scriptの場合、スクリプトのファイルはGoogleドライブに表示されません。

Standalone Scriptの作成

Standalone Scriptを作成するには、Googleドライブの「＋新規」→「Google Apps Script」をクリックします。

スプレッドシートの指定

GASでスプレッドシートと連携する場合は、どのスプレッドシートかを指定する必要があります。

Container Bound Scriptの場合は、スプレッドシートと紐づいているので以下のように指定します。

const spreadsheet = SpreadsheetApp.getActiveSheet();

次のように書くこともできます。どちらも同じです。

const spreadsheet = SpreadsheetApp.getActive();

Standalone Scriptの場合は、スプレッドシートと紐づいてないので、「ID」や「URL」で指定する必要があります。

スプレッドシートのIDは、https://docs.google.com/spreadsheets/d/ここがID/edit#gid=0の「ここがID」という部分です。

const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

URLで指定する場合は、次のように書きます。

const spreadsheet = SpreadsheetApp.openByUrl("スプレッドシートのURL");

Container Bound Scriptはスプレッドシートに紐づいているので、getActiveSpreadsheet()メソッドを使用することで、わざわざスプレッドシートのIDを指定する必要がありません。

シートを指定する

先ほど、GASでスプレッドシートを指定しました。

GASでスプレッドシートを操作するには、

スプレッドシートを指定
シートを指定
セルを指定

という感じで、順番に指定していく必要があります。

次は、シートを指定しましょう。シートの名前で指定します。

//まずはスプレッドシートを指定
const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

//次にシートを指定
const sheet = spreadsheet.getSheetByName("シートの名前");

シートは、左から何番目という指定の仕方も可能です。この場合、一番左のシートが[0]になることに注意してください。

//スプレッドシートを指定
const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

//一番左のシートを指定
const sheet = spreadsheet.getSheets()[0];

セルを指定する

シートまで指定できたので、次はセルを指定します。

//まずはスプレッドシートを指定
const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

//次にシートを指定
const sheet = spreadsheet.getSheetByName("シートの名前");

//次にセルを指定
const cell = sheet.getRange("A1");

getRange(“セル”)は、行番号と列番号でも指定ができます。

//スプレッドシートを指定
const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

//シートを指定
const sheet = spreadsheet.getSheetByName("シートの名前");

//セルを指定 getRange(行番号,列番号)
const cell = sheet.getRange(1,1);

データを取得する

セルの指定までできました。セルのデータを取得してみましょう。連携するスプレッドシートは次のような入力があります。

特定のセルの値を取得する

1つの値を取得する場合はgetValue()を使用します。

const getSheetValue = () => {
  const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

  const sheet = spreadsheet.getSheetByName("シート1");

  const cell = sheet.getRange("C2");  
  //または const cell = sheet.getRange(2,3);

  const value = cell.getValue();
};

コードを実行すれば、変数valueにはC2のセルのC200が入ります。

複数のセルの値を取得する

複数のセルの値を取得するときは、getRange()で「”A1：D3”」のように範囲を指定し、getValues()を使用します。

getRange(1,1,3,4)のように指定することも可能です。getRange(行番号,列番号,行数,列数)となっています。

const getSheetValues = () => {
  const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

  const sheet = spreadsheet.getSheetByName("シート1");

  const cell = sheet.getRange("A1:D3");  
  //または const cell = sheet.getRange(1,1,3,4);

  const values = cell.getValues();
};

変数のvaluesは、次のような2次元配列になります。

[
  [A100, B100, C100, D100],
  [A200, B200, C200, D200],
  [A300, B300, C300, D300]
]

この値を使うときは、values[0][0]だと”A100”、values[1][2]だと”C200”となります。

すべてのセルの値を取得するときは、getDataRange()を使うこともできます。

const getSheetValue = () => {
  const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

  const sheet = spreadsheet.getSheetByName("シート1");

  const values = sheet.getDataRange().getValues();
};

データを書き込む

次は、スプレッドシートに値を書き込みます。

特定のセルに値を書き込む

セルに値を書き込むときはsetvalue()を使用します。

const setSheetValue = () => {
  const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

  const sheet = spreadsheet.getSheetByName("シート2");

  const cell = sheet.getRange("B1");  
  //または const cell = sheet.getRange(1,2);

  cell.setValue("書き込み成功");
};

実行すれば、シート2のB1に「書き込み成功」と書き込まれます。

複数のセルに値を書き込む

複数のセルに値を書き込むときは、getRange()で「”A1：D3”」のように範囲を指定し、setValues()を使用します。セルの範囲と書き込む配列の大きさが同じになるようにします。

const setSheetValues = () => {
  const spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");

  const sheet = spreadsheet.getSheetByName("シート2");

  const cell = sheet.getRange("A1:C3");  
  //または const cell = sheet.getRange(1,1,3,3);

  //書き込む値
  const values = [
    ["A1","B1","C1"],
    ["A2","B2","C2"],
    ["A3","B3","C3"]
  ];
  cell.setValues(values);
};