Google Apps Script(Gas)でスプレッドシートの操作

皆さまこんにちは。
gasはgoogle提供サービスとの連携が得意だそうです！
今回はgasを用いてのスプレッドシート操作の基本について簡単にまとめました。

1.操作するスプレッドシートをgasに教えてあげる
2.範囲指定と値の取得
3.最終行(列)番号取得
4.値の書き込み

操作するスプレッドシートをgasに教えてあげる

まずはこれですね。

test.gas

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
}

SpreadsheetApp.getActiveSpreadsheet()でアクティブなスプレッドシートを取得します。
取得したスプレッドシートに対して、色々な処理を行っていく事になります。

アクティブなスプレッドシートとはスクリプトと紐づいているスプレッドシートのことです。
スプレッドシートからスクリプトエディタを開いてあげれば、紐づいているという事になります！

紐づいていないスプレッドシートを操作する場合には、URLまたはidで指定が可能です。

スプレッドシートのidで指定

test.gas

function myFunction() {
  const spreadSheet = SpreadsheetApp.openById(スプレッドシートのid);
}

idには下記を指定します。

https://docs.google.com/spreadsheets/d/xxxxxxxxx/edit

URLで指定

test.gas

function myFunction() {
  const spreadSheet = SpreadsheetApp.openByUrl(スプレッドシートのURL);
}

操作したいスプレッドシートのシートを指定

次はシートを指定しましょう！ここまでは必須の処理になるかなと思います。

test.gas

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadSheet.getSheetByName("シート1");
}

取得したスプレッドシート.getSheetByName("シート名")でシートが取得出来ます！
これに対して、色々処理をしていきましょう！

範囲指定と値の取得

このスプレッドシートの値を取得してみましょう。

対象シート.getRange(セル)でセルを選択できます。
そこからgetValue()で値を取得です。

test.gas

function myFunction() {
  // 操作するスプレッドシートを取得
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadSheet.getSheetByName("シート1");
  // A1セルから値を取得し、出力
  const value = sheet.getRange("A1").getValue();
  console.log(value);// 出力結果:テスト
}

上記ではセル指定していますが、範囲選択も出来ます！
例えばこんな風に範囲指定する場合は

このように指定します。

test.gas

// A1セル〜D4までを範囲選択
const range = sheet.getRange("A1:D4");

番号で指定することも可能です。

getRange(開始位置行、開始位置列、終了行、終了列)

先ほどのデータを指定する場合は下記になります。

test.gas

// A1セル〜D4までを範囲選択
const range = sheet.getRange(1,1,4,4);

列はアルファベットではなく、左から1,2,3....で指定します。

複数値を取得する際に範囲選択を使用しますが、その場合に使用する関数がgetValues()となります。

test.gas

function myFunction() {
  // 操作するスプレッドシートを取得
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadSheet.getSheetByName("シート1");
  // A1~D4から値を取得し、出力
  const values = sheet.getRange(1,1,4,4).getValues();
  console.log(values);// 出力結果:[ [ 'ID', '名前', '性別', '年齢' ],
                      // [ 1, 'Aさん', '女', 21 ],
                      // [ 2, 'Bくん', '男', 19 ],
                      // [ 3, 'Cくん', '男', 20 ]]
}

getValues()は二次元配列を返します。
ループさせたり、values[行][列]みたいに指定して値を取り出してください。

最終行(列)番号取得

スプレッドシートのデータが増減する場合はあると思います。
値を範囲指定して取得する際に、ここからここまで！みたいな指定だとメンテナンス面倒です。
一般的には最終列、最終行を取得してそれを利用します。

さっきの表があります。

最終行はgetLastRow()を使用、最終列はgetLastColumn()を使用します！
それをgetRange()に渡してあげれば解決です！

test.gas

function myFunction() {
  // 操作するスプレッドシートを取得
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadSheet.getSheetByName("シート1");
  // 最終行の取得
  const lastRow = sheet.getLastRow();
  // 最終列の取得
  const lastColumn = sheet.getLastColumn();
  // A1セルから値を取得し、出力
  const values = sheet.getRange(1, 1, lastRow, lastColumn).getValues();
  console.log(values);// 出力結果:[ [ 'ID', '名前', '性別', '年齢' ],
                      // [ 1, 'Aさん', '女', 21 ],
                      // [ 2, 'Bくん', '男', 19 ],
                      // [ 3, 'Cくん', '男', 20 ]]
}

値の書き込み

新しい名前を追加してみましょう！

値の書き込みにはsetValue()を使います。

test.gas

function myFunction() {
  // 操作するスプレッドシートを取得
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadSheet.getSheetByName("シート1");
  // 新しく"Dさん"を追加してみよう
  sheet.getRange("B5").setValue("Dさん");

}

スプレッドシートを開くと、、、追加されました！！

データは増減するので、現在のデータの次の行に追加するようにするのが良いと思います！

test.gas

function myFunction() {
  // 操作するスプレッドシートを取得
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadSheet.getSheetByName("シート1");
  // 新規に追加する行
  const newRow = sheet.getLastRow() + 1;
  // リクエスト情報と仮定
  const valArray = [4, "Dさん", "女", 20];
  // 新規で追加
  valArray.forEach((val, index) => sheet.getRange(newRow, index + 1).setValue(val));

}

追加されました！