スプレッドシートのデータが多すぎて対応するデータが埋もれてしまうことってありませんか？
完了したデータを別のシートにアーカイブすれば、対応中の案件が見やすくなり業務の効率もアップしますよね！
GAS（Google Apps Script)には6分の壁があるのですが、配列を使用することで、関数の呼び出しを減らしデータ量が多くなっても6分の壁を回避できるよう工夫しました。
やりたいこと
対応中のシートには、「受付」「準備中」「完了」の３つのステータスが存在します。
このなかで、「完了」となった行はアーカイブのシートに移動させて、それ以外のステータスのみ対応中のシートに反映させてみましょう。
反映イメージ
対応中のシートには「受付」「準備中」のみ
アーカイブシートには「完了」のみ
事前準備
- スプレッドシートのIDを調べておく
- 元となるシート名を「対応中」とする
- 「完了」データを格納するシート名を「アーカイブ」にし、見出しをいれておく
実装
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列で数えて空白の要素を省くのは、意図していない行や列にゴミのようなデータが入っている場合、全てを取得してしまうのを防いでいます。
//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しません。
//1行目（見出し）をsprt_arrayに追加
for(let i=0; i<1; i++){
sprt_array.push(sprt_sh_data[i]);
}
4列目（配列では[3]）が「完了」かどうかをfor文で１行１行チェックしていきます。
完了の場合は、arch_array、そうでない場合はsprt_array、とステータスに応じた配列をつくります。
//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_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); //書き込む
}
最後に
このGASを定期的に動かせば、終わった案件はアーカイブされて対応が必要な案件のみ表示されると思います。
変数名が冗長でわかりにくいため、適時修正していただければと思います。