【GAS】スプレッドシートで特定の条件を満たす行を抽出する

スプレッドシートのデータが多すぎて対応するデータが埋もれてしまうことってありませんか？
完了したデータを別のシートにアーカイブすれば、対応中の案件が見やすくなり業務の効率もアップしますよね！
GAS（Google Apps Script)には6分の壁があるのですが、配列を使用することで、関数の呼び出しを減らしデータ量が多くなっても6分の壁を回避できるよう工夫しました。

#やりたいこと
対応中のシートには、「受付」「準備中」「完了」の３つのステータスが存在します。
このなかで、「完了」となった行はアーカイブのシートに移動させて、それ以外のステータスのみ対応中のシートに反映させてみましょう。
![スクリーンショット 2021-08-29 14.24.04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/569827/9c4ef9b3-4a48-c410-3729-6c8f18be9bb9.png)


#反映イメージ
対応中のシートには「受付」「準備中」のみ
![スクリーンショット 2021-08-29 14.28.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/569827/61e55f27-4d1a-6bf1-d026-134cf63906be.png)
アーカイブシートには「完了」のみ
![スクリーンショット 2021-08-29 14.29.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/569827/0f2682ae-8af9-680e-8c6d-746b4dfc6459.png)

#事前準備
1. スプレッドシートのIDを調べておく
1. 元となるシート名を「対応中」とする
1. 「完了」データを格納するシート名を「アーカイブ」にし、見出しをいれておく

#実装
```js
function archive(){
  //スプレッドシート情報
  const ss_ID = 'あなたのスプレッドシートのIDを入れてください' 
  const sprt_sh_name = '対応中'
  const arch_sh_name = 'アーカイブ'
  
　　　　//配列の初期化
　　　　const sprt_array = [];
  const arch_array = [];

  //ID、シート名を指定してシートを取得
  const ss = SpreadsheetApp.openById(ss_ID);
  const sprt_sh = ss.getSheetByName(sprt_sh_name);
  //シートの行数をA列で数える
  const sprt_values = sprt_sh.getRange('A:A').getValues(); 
  //空白の要素を除いた長さを取得
  const sprt_lastrow = sprt_values.filter(String).length;
  //配列で取得(1行目、1列、A列の長さ、取得する列数)
  const sprt_sh_data = sprt_sh.getRange(1,1,sprt_lastrow,4).getValues();

  //1行目（見出し）をsprt_arrayに追加
  for(let i=0; i<1; i++){
    sprt_array.push(sprt_sh_data[i]);
  }

  //2行目から「完了」かどうかを判定
  for (let i=1; i< sprt_lastrow; i++){

    //「完了」の場合はarch_arrayへ追加
    if(sprt_sh_data[i][3] == '完了'){
      arch_array.push(sprt_sh_data[i]);
    
    //「完了」以外の場合はsprt_arrayへ追加
    }else{
      sprt_array.push(sprt_sh_data[i]);
    }
  }

  //シート名「対応中」にsprt_arrayを書き込む
  sprt_sh.clearContents(); //シートを消す
  const sprt_array_lastcolum = sprt_array[0].length; //カラムの値を取得する
  const sprt_array_lastrow = sprt_array.length; //対応中シートの行数を取得する
  sprt_sh.getRange(1,1,sprt_array_lastrow,sprt_array_lastcolum).setValues(sprt_array); //書き込む

  //シート名「アーカイブ」にarch_arrayを書き込む
  const arch_sh = ss.getSheetByName(arch_sh_name);
  const arch_sh_values = arch_sh.getRange('A:A').getValues(); 
  const arch_sh_lastrow = arch_sh_values.filter(String).length;
  const arch_array_lastcolum = arch_array[0].length; //カラムの値を取得する
  const arch_array_lastrow = arch_array.length; //行の数を取得する
  arch_sh.getRange(arch_sh_lastrow+1,1,arch_array_lastrow,arch_array_lastcolum).setValues(arch_array); //書き込む
}
```

#いくつか補足
シートの行数をA列で数えて空白の要素を省くのは、意図していない行や列にゴミのようなデータが入っている場合、全てを取得してしまうのを防いでいます。

```js
  //ID、シート名を指定してシートを取得
  const ss = SpreadsheetApp.openById(ss_ID);
  const sprt_sh = ss.getSheetByName(sprt_sh_name);
  //シートの行数をA列で数える
  const sprt_values = sprt_sh.getRange('A:A').getValues(); 
  //空白の要素を除いた長さを取得
  const sprt_lastrow = sprt_values.filter(String).length;
  //配列で取得(1行目、1列、A列の長さ、取得する列数)
  const sprt_sh_data = sprt_sh.getRange(1,1,sprt_lastrow,4).getValues();
```
今回、「対応中」のシートはクリアして書き換える前提なので、sprt_arrayに見出し行をpushします。
「アーカイブ」のシートはクリアせず、過去データの一番後ろにデータを追加していくので、見出しはpushしません。

```js
//1行目（見出し）をsprt_arrayに追加
  for(let i=0; i<1; i++){
    sprt_array.push(sprt_sh_data[i]);
  }
```
4列目（配列では[3]）が「完了」かどうかをfor文で１行１行チェックしていきます。
完了の場合は、arch_array、そうでない場合はsprt_array、とステータスに応じた配列をつくります。

```js
//2行目から「完了」かどうかを判定
  for (let i=1; i< sprt_lastrow; i++){

    //「完了」の場合はarch_arrayへ追加
    if(sprt_sh_data[i][3] == '完了'){
      arch_array.push(sprt_sh_data[i]);
    
    //「完了」以外の場合はsprt_arrayへ追加
    }else{
      sprt_array.push(sprt_sh_data[i]);
    }
  }
```
「対応中」のシートを全てクリアにし、sprt_arrayのデータを書き込みます。

```js
//シート名「対応中」にsprt_arrayを書き込む
  sprt_sh.clearContents(); //シートを消す
  const sprt_array_lastcolum = sprt_array[0].length; //カラムの値を取得する
  const sprt_array_lastrow = sprt_array.length; //対応中シートの行数を取得する  sprt_sh.getRange(1,1,sprt_array_lastrow,sprt_array_lastcolum).setValues(sprt_array); //書き込む
```
「アーカイブ」のシートはログとして過去データを残していきたいので、シートのデータ行数を取得し、その次の行から書き込みます。

```js
//シート名「アーカイブ」にarch_arrayを書き込む
  const arch_sh = ss.getSheetByName(arch_sh_name);
  const arch_sh_values = arch_sh.getRange('A:A').getValues(); 
  const arch_sh_lastrow = arch_sh_values.filter(String).length;
  const arch_array_lastcolum = arch_array[0].length; //カラムの値を取得する
  const arch_array_lastrow = arch_array.length; //行の数を取得する
  arch_sh.getRange(arch_sh_lastrow+1,1,arch_array_lastrow,arch_array_lastcolum).setValues(arch_array); //書き込む
}
```
#最後に
このGASを定期的に動かせば、終わった案件はアーカイブされて対応が必要な案件のみ表示されると思います。
変数名が冗長でわかりにくいため、適時修正していただければと思います。