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

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

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

##操作するスプレッドシートをgasに教えてあげる
まずはこれですね。

```javascript:test.gas
function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
}

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

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

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

###スプレッドシートのidで指定
```javascript:test.gas
function myFunction() {
  const spreadSheet = SpreadsheetApp.openById(スプレッドシートのid);
}

```
idには下記を指定します。
>https\://docs.google.com/spreadsheets/d/<font color="Red">xxxxxxxxx</font>/edit

###URLで指定
```javascript:test.gas
function myFunction() {
  const spreadSheet = SpreadsheetApp.openByUrl(スプレッドシートのURL);
}

```
##操作したいスプレッドシートのシートを指定
次はシートを指定しましょう！ここまでは必須の処理になるかなと思います。

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

```

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

##範囲指定と値の取得
このスプレッドシートの値を取得してみましょう。
![スクリーンショット 2021-10-24 10.37.14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/590579/0b6d1552-e501-eb39-161a-ccb4d7be65f7.png)


**対象シート.getRange(セル)**でセルを選択できます。
そこから**getValue()**で値を取得です。
 
```javascript:test.gas
function myFunction() {
  // 操作するスプレッドシートを取得
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadSheet.getSheetByName("シート1");
  // A1セルから値を取得し、出力
  const value = sheet.getRange("A1").getValue();
  console.log(value);// 出力結果:テスト
}

```

上記ではセル指定していますが、範囲選択も出来ます！
例えばこんな風に範囲指定する場合は
![スクリーンショット 2021-09-19 15.56.29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/590579/2b00a2c1-bb67-350f-fb28-3af6eaa195b8.png)

このように指定します。

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

```
番号で指定することも可能です。
>getRange(開始位置行、開始位置列、終了行、終了列)

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

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

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

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

```javascript: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[行][列]みたいに指定して値を取り出してください。

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

さっきの表があります。
![スクリーンショット 2021-09-19 15.56.29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/590579/0a3b57fa-657e-14c2-d6ad-1ba2399b9818.png)

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

```javascript: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 ]]
}

```

##値の書き込み
新しい名前を追加してみましょう！
![スクリーンショット 2021-09-20 15.11.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/590579/53cdee9b-ecaa-22b6-7dc1-ae43e5236d42.png)

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

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

}

```

スプレッドシートを開くと、、、追加されました！！
![スクリーンショット 2021-09-20 15.30.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/590579/2df0220f-00cf-0c45-9a3d-d4f72422603e.png)

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

```javascript: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));

}

```

追加されました！
![スクリーンショット 2021-09-23 11.58.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/590579/f8b981f5-34bd-8ed7-49ce-02a1886010c4.png)