スプレッドシートでGASを使用すると色々なことができますが、
どう活用していいかわからない、GASを学びたいけど何から始めればいいかわからない。
そんな悩みがある方もいると思います。
何かやりたいことがあるのであれば、
調べて作成することで活用できたり学べたりしますが、
何が出来るのかわからない状態でやりたいことと言っても難しいですよね。
この記事ではスプレッドシートでGASを使用したタスク管理シートのデータを配布しています。
コピペで使用できるので、使用しながらGASを動かしてみることで、
GASを使うきっかけになれば幸いです！
こちらの記事でもサンプルデータを紹介しています。
スプレッドシート進捗、タスク管理シート
使用しているスプレッドシートは↓こちらからダウンロードできます。
【Copori】タスク管理シート
タスク管理シートの使用方法
1、【Copori】タスク管理シートを開いてコピーしてください。
2、タスク管理シートの下(A30〜A40)にタスクのジャンルを記入してください。
ここに記入したジャンルが選択できるようになります。
3、メニューから「拡張機能」⇨「Apps Script」を開きます。
※複数アカウントでログインしているとエラーで開けないので注意してください。
4、【権限取得用】日付表示用のGASを実行して、権限取得を行なってください。
やり方はこちら
5、権限取得をすると、内容を記入したら日付が自動入力されるようになります。
DAY処理を実行すると、完了タスクが「完了タスクのシート」の最終行に追記されます。
月が変更された場合は、新しい月シートが作成されます。
日付トリガーで自動実行させる方法を参照して、トリガーをセットしてください。
タスク管理シートのGAS解説
タスク管理シートには3つのGASコードが入っています。
・日付自動記入.gs
・【権限取得用】日付表示用.gs
・DAY+Month処理.gs
日付自動記入GAS
日付を自動で入力するGASはシンプルトリガーのonEdit（）で作成しています。
functionにonEdit()を入力すると手動でトリガーをセットしなくても、
スプレッドシートが編集されたら実行されるようになります。
（事前に権限取得は必要です）
実際のコードはこちら
function onEdit(e) {
//onEdit(e)⇦の(e)で編集された場所を取得しています。
//日付を自動表示する対象シート名を変数「sheetName」に格納
var sheetName = "タスク管理シート";
//編集された情報を取得して変数「range」に格納
var range = e.range;
//編集された情報「range」からシート名を取得して変数「sheet」に格納
var sheet = range.getSheet();
Logger.log('編集されたシート：'+sheet.getName());//ログ確認用
//編集された情報「range」から行・列それぞれ変数に格納
var row = range.getRow(); // 行
var col = range.getColumn(); // 列
Logger.log('編集された行：'+row+'、列：'+col);//ログ確認用
//編集されたシートが「タスク管理シート」なら実行
if (sheet.getName() === sheetName) {
//編集された範囲がタスク内容の記入セルだったら実行
if(row >= 2 && row <= 26 && col == 4){
//現在の日付を取得して変数「todayTime」に格納
let todayTime = new Date();
//編集された行の日付セルを取得
let date = sheet.getRange(row,2).getValue();
//日付セルが空白だったら日付をセット
if(date == ''){
sheet.getRange(row,2).setValue(todayTime);
}
}
}
}処理ごとにコメントを書いているので、確認してみてください。
日付自動処理を詳しく知りたい場合はこちら
【GAS】スプレッドシートで日付を自動で入力！日付更新・曜日自動サンプル【Google Apps Script】
【関数】スプレッドシートで日付を自動入力！日付関連の関数のまとめとサンプルデータ
GAS権限取得用コード
このコードは権限を取得するためだけに使用するGASです。
function test() {
//アクティブシートを取得
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
//現在の日付を取得して変数「todayTime」に格納
var todayTime = new Date();
Logger.log(todayTime);//ログ確認用
}シートを読み込んで、日付を取得してログ表示するだけのコードです。
自動で最終行追加、自動シート作成GAS
このGASは、日付が変更されたら完了済みのタスクを自動で月シートの最終行に追記するコードと、月が変わったら新しくシートを作成するコードが入っています。
全体のGASコードはこちら
function daydata() {
//このGASは時間トリガーで毎日0〜1時に実行をセットすると便利です。
//方法はこちらを参照してください「https://coporilife.com/242/#trigger」
//手動で実行することでも実行可能です。
//タスク内容のデータ範囲
var taskRange = 'D2:D26';
//現在の日時データを取得して変数dateに格納
var date = new Date();
//dateから月を取得
var month = date.getMonth()+1;
//dateの日付を-1して前日の月を取得
date.setDate(date.getDate()-1);
var lastMonth = date.getMonth()+1;
//「年＋月」のシート名を取得
var year = date.getFullYear();
var monthShtName = year+'0'+lastMonth;
//アクティブスプレッドシートを取得
var sheet = SpreadsheetApp.getActiveSpreadsheet();
//アクティブシート（【Copori】タスク管理シート）の「タスク管理シート」を取得
var taskSheet = sheet.getSheetByName('タスク管理シート');
//アクティブシート（【Copori】タスク管理シート）の今月のシートを取得
var monthSheet = sheet.getSheetByName(monthShtName);
//前日と本日の月が変わってたら月次処理
if(month !== lastMonth){
//新しい「年＋月」のシート名を変数に格納
var monthShtNameN = year+'0'+month;
//前月のシートをコピー
let newSheet = monthSheet.copyTo(sheet);
//コピーしたシートのシート名を新しい「年＋月」に変更
newSheet.setName(monthShtNameN);
//A11の値をクリア
newSheet.getRange('A11').clearContent();
//A12:J787の値をクリア
newSheet.getRange('A12:J787').clearContent();
//罫線をクリア
newSheet.getRange('A12:J787').setBorder(null,null,null,null,null,false);
//月シートの月次完了数の表示箇所を配列変数に格納
let monthData = ['E2','F2','G2','H2','I2','J2','E4','F4','G4','H4','I4','J4']
//前月の完了数を新しい月シートにコピー
newSheet.getRange(monthData[lastMonth-1]).setValue(monthSheet.getRange(monthData[lastMonth-1]).getDisplayValue());
//新しい月シートの完了数表示の関数をセット
newSheet.getRange(monthData[month-1]).setValue('=B2');
//新しい月シートをアクティブシートに
sheet.setActiveSheet(newSheet);
//新しい月シートをシートの2番目の位置に移動
sheet.moveActiveSheet(2);
Logger.log(monthShtNameN+'シート作成完了');//ログ確認用
}
//月シートの最終行を取得
var lastRow = monthSheet.getLastRow();
//タスク管理シートのチェックボックスの値を取得
let checkBox = taskSheet.getRange('A2:A26').getValues();
//取得したチェックボックスの値を1次元配列に変更
checkBox = checkBox.flat();
//取得したチェックボックスの値の中のtrueの数を取得
let checkTrue = checkBox.indexOf(true);
//タスク管理シートのチェックボックスにtrueがあれば実行
if(checkTrue >= 0){
//メモ欄の値を取得してクリア
var memo = taskSheet.getRange('E7:E26').getValues();
taskSheet.getRange('E7:E26').clearContent();
//月シートの最初の行にデータが入っているか確認
let data = monthSheet.getRange('A12').getValue();
//月シートの最初の行にデータが入っていたら、最終行に見出しを追加
if(data !== ''){
monthSheet.getRange('A11:J11').copyTo(monthSheet.getRange(lastRow+1,1));
}else{lastRow--;}
//月シートの見出し部分に現在の日付を追加
monthSheet.getRange(lastRow+1,1).setValue(new Date());
//タスク管理シートの内容を1行ずつコピペ処理
for(let i=2; i<=26; i++){
//チェックボックスの値を取得
let check = taskSheet.getRange(i,1).getValue();
//チェックボックスがtrueなら実行
if(check === true){
//月シートの最終行を取得
let lastRows = monthSheet.getLastRow();
lastRows++;
//コピーする範囲を取得
let range = 'B'+i+':D'+i;
//タスク内容を取得
let values = taskSheet.getRange(range).getValues();
//タスク内容をクリア
taskSheet.getRange(range).clearContent();
//月シートの最終行に「完了」を入力
monthSheet.getRange(lastRows,1).setValue('完了')
//月シートの最終行に取得したタスク内容を追記挿入
monthSheet.getRange(lastRows,2,1,3).setValues(values);
}
}
//メモ欄の値を最終行に追記挿入
monthSheet.getRange(lastRow+2,5,20,1).setValues(memo);
//タスク管理シートのチェックボックスを全て外す
taskSheet.getRange('A2:A26').setValue(false);
//タスク管理シートの残っているデータ（未完了）を全て取得
var value = taskSheet.getRange('B2:D26').getValues();
//取得した配列データの空の行を削除
var value2 = value.filter(v => v[0]);
//データが入っている配列内のデータ数を取得
var count = value2.length;
//タスク管理シートのデータをクリア
taskSheet.getRange('B2:D26').clearContent();
//タスク管理シートの未完了データの記入範囲を取得
count++;
let range = 'B2:D'+count;
//タスク管理シートの未完了データを追記挿入
taskSheet.getRange(range).setValues(value2);
Logger.log(new Date()+'：タスク移動完了');//ログ確認用
}else{Logger.log('trueデータなし')}//ログ確認用
}完了タスクを月シートの最終行に移動させるGASコード詳細
//タスク内容のデータ範囲
var taskRange = 'D2:D26';タスク内容が記入されているセルを取得
//現在の日時データを取得して変数dateに格納
var date = new Date();
//dateから月を取得
var month = date.getMonth()+1;現在の日付を取得するには、new Date()メソッドを使用します。
「date.getMonth()+1」で、dateに格納した日付データから月のみ取得しています。
月のデータは、0〜11の数値で取得することができ、
1月＝0、12月＝11、
という取得方法になっているため、「＋1」しています。
//dateから月を取得
var month = date.getMonth()+1;
//dateの日付を-1して前日の月を取得
date.setDate(date.getDate()-1);
var lastMonth = date.getMonth()+1;
//「年＋月」のシート名を取得
var year = date.getFullYear();
var monthShtName = year+'0'+lastMonth;日付が変わってからトリガーで実行する場合を想定しているので、
前日の日付を取得して、月を取得しています。
年データを合わせて「年＋月」のシート名を取得できるように変数に格納しています。
月変更による自動シート作成は後ほど説明しますので、62行まで飛ばします。
//月シートの最終行を取得
var lastRow = monthSheet.getLastRow();
//タスク管理シートのチェックボックスの値を取得
let checkBox = taskSheet.getRange('A2:A26').getValues();
//取得したチェックボックスの値を1次元配列に変更
checkBox = checkBox.flat();
//取得したチェックボックスの値の中のtrueの数を取得
let checkTrue = checkBox.indexOf(true);最終行を取得するためにgetLastRow()メソッドを使用しています。
チェックボックスの値取得以降は、
trueが入っている完了タスクがあるかないかの判断をするために行なっている処理です。
完了タスク（true）があれば実行していきます。
//メモ欄の値を取得してクリア
var memo = taskSheet.getRange('E7:E26').getValues();
taskSheet.getRange('E7:E26').clearContent();メモ欄の値を取得してセルのデータを削除しています。
削除するには、clearContent()を使用します。
書式も削除する場合は、clear()メソッドを使いますが罫線も削除されるので注意。
//月シートの最初の行にデータが入っているか確認
let data = monthSheet.getRange('A12').getValue();
//月シートの最初の行にデータが入っていたら、最終行に見出しを追加
if(data !== ''){
monthSheet.getRange('A11:J11').copyTo(monthSheet.getRange(lastRow+1,1));
}else{lastRow--;}この処理がないと、月初は見出しが2重で記入されてしまうため、
データが既にあるかないかの判断をして、データがある場合のみ見出しを挿入するようにしています。
見出しが2重で記入されるとこうなります↓
//月シートの見出し部分に現在の日付を追加
monthSheet.getRange(lastRow+1,1).setValue(new Date());いつのタスクかわかるように見出しに日付を入れています。
getRange()メソッドで最終行を使用する場合は、
getLastRow()で取得した最終行はデータが入っている行なので、
「＋1」をして、次の行からデータを入力するように範囲指定しています。
最終行に自動入力する詳細はこちら
【GAS】スプレッドシートの最終行に追加、コピー、自動追加する方法まとめ【Google Apps Script】
//タスク管理シートの内容を1行ずつコピペ処理
for(let i=2; i<=26; i++){
//チェックボックスの値を取得
let check = taskSheet.getRange(i,1).getValue();
//チェックボックスがtrueなら実行
if(check === true){完了タスクのみを取得して月シートの最終行に移動したいので、
1行ずつ完了（true）かどうかを判断して実行するようにしています。
//月シートの最終行を取得
let lastRows = monthSheet.getLastRow();
lastRows++;上でも書いた通り、getLastRow()メソッドはデータのある最終行を取得するので、
「lastRows++」で「＋1」をして空白行を取得しています。
//コピーする範囲を取得
let range = 'B'+i+':D'+i;
//タスク内容を取得
let values = taskSheet.getRange(range).getValues();
//タスク内容をクリア
taskSheet.getRange(range).clearContent();for文で1行ずつ取得しているので、
for文で使用している変数を使って範囲を指定しています。
//月シートの最終行に「完了」を入力
monthSheet.getRange(lastRows,1).setValue('完了')
//月シートの最終行に取得したタスク内容を追記挿入
monthSheet.getRange(lastRows,2,1,3).setValues(values);月シートの完了したタスクは、チェックボックスではなく「完了」と表示させるために、完了を入力してから内容を追記しています。
//メモ欄の値を最終行に追記挿入
monthSheet.getRange(lastRow+2,5,20,1).setValues(memo);先にメモ欄を月シートに取得してしまうと、タスク内容の記入の時に最終行が変動してしまうため、タスク内容の移動が終わってから、メモを記入するようにしています。
ちなみに、メモを先に入力してしまうとこうなります↓
変数「lastRow」は一番最初に取得している最終行で、
その後見出しを追加しているのでここでは「＋2」をしています。
//タスク管理シートのチェックボックスを全て外す
taskSheet.getRange('A2:A26').setValue(false);GASで一括でチェックボックスを外すには、該当セルにfalseを入れるだけです。
//タスク管理シートの残っているデータ（未完了）を全て取得
var value = taskSheet.getRange('B2:D26').getValues();
//取得した配列データの空の行を削除
var value2 = value.filter(v => v[0]);
//データが入っている配列内のデータ数を取得
var count = value2.length;月シートに移動したタスク内容は、取得の時に削除するようにしているので、空白行として残っています。
これを上から詰めて表示したい↓
空白行を詰めて未完了タスクを表示させるため、
一度取得して変数に格納し、空白行を削除して、残っているデータ数（行）を数えるということをしています。
//タスク管理シートのデータをクリア
taskSheet.getRange('B2:D26').clearContent();データは既に変数に格納しているので、タスク管理シートのデータを消去しています。
//タスク管理シートの未完了データの記入範囲を取得
count++;
let range = 'B2:D'+count;
//タスク管理シートの未完了データを追記挿入
taskSheet.getRange(range).setValues(value2);setValue()メソッドを使用するには、getRange()で範囲を指定する必要があるので、
データの数を数えて範囲指定するようにしています。
配列を使用して行う方法もありますが、
配列は混乱しやすいため今回はこの方法をとっています。
Logger.log(new Date()+'：タスク移動完了');//ログ確認用
}else{Logger.log('trueデータなし')}//ログ確認用Logger.log()はログを書き出すためのメソッドです。
今回のGASは自動トリガーで動くことを想定して作成しているので、
後からログで確認ができるようにしています。
完了タスクがない場合は、「trueなし」をログに出力しています。
自動シート挿入、月シートを自動で作成するGASコード詳細
自動シート挿入で使用しているメインコードは、32行〜60行です。
自動挿入GASコード全体はこちら
//前日と本日の月が変わってたら月次処理
if(month !== lastMonth){
//新しい「年＋月」のシート名を変数に格納
var monthShtNameN = year+'0'+month;
//前月のシートをコピー
let newSheet = monthSheet.copyTo(sheet);
//コピーしたシートのシート名を新しい「年＋月」に変更
newSheet.setName(monthShtNameN);
//A11の値をクリア
newSheet.getRange('A11').clearContent();
//A12:J787の値をクリア
newSheet.getRange('A12:J787').clearContent();
//罫線をクリア
newSheet.getRange('A12:J787').setBorder(null,null,null,null,null,false);
//月シートの月次完了数の表示箇所を配列変数に格納
let monthData = ['E2','F2','G2','H2','I2','J2','E4','F4','G4','H4','I4','J4']
//前月の完了数を新しい月シートにコピー
newSheet.getRange(monthData[lastMonth-1]).setValue(monthSheet.getRange(monthData[lastMonth-1]).getDisplayValue());
//新しい月シートの完了数表示の関数をセット
newSheet.getRange(monthData[month-1]).setValue('=B2');
//新しい月シートをアクティブシートに
sheet.setActiveSheet(newSheet);
//新しい月シートをシートの2番目の位置に移動
sheet.moveActiveSheet(2);
Logger.log(monthShtNameN+'シート作成完了');//ログ確認用
}このGASコードの手前で月の取得を行なっていますが、
こちらのコードは先に書いた、
「完了タスクを月シートの最終行に移動させるGASコード詳細」で書いているので、
この後から解説していきます！
//前日と本日の月が変わってたら月次処理
if(month !== lastMonth){
//新しい「年＋月」のシート名を変数に格納
var monthShtNameN = year+'0'+month;
//前月のシートをコピー
let newSheet = monthSheet.copyTo(sheet);
//コピーしたシートのシート名を新しい「年＋月」に変更
newSheet.setName(monthShtNameN);先に書いた通り、日付が変わってからトリガーで自動実行することを想定に作成しているため、
前日の月を取得して、月が変わっているかの判断をしています。
変わっていたら、新しい「年＋月」シートを作成するため、
前月の月シートをコピーして、名前を変更しています。
シート名はこんな感じ↓
コピーはcopyTo()メソッド、
シート名の変更はsetName()メソッドを使用します。
//A11の値をクリア
newSheet.getRange('A11').clearContent();
//A12:J787の値をクリア
newSheet.getRange('A12:J787').clearContent();
//罫線をクリア
newSheet.getRange('A12:J787').setBorder(null,null,null,null,null,false);コピーした月シートの最初の見出しに記入されている日付(A11)を削除して、
完了タスクが記入される範囲を全て消去しています。
clearContent()メソッドで消去すると値だけが消去され、
このままだと見出しに使用していた罫線が残ってしまうため、
setBorder()メソッドで罫線を消しています。
罫線についての記事はこちら
【GAS】スプレッドシート罫線・枠線の自動設定、太さスタイル変更まとめサンプルコード【Google Apps Script】
ちなみに、罫線を消さないとこんな感じで残ってしまいます。
//月シートの月次完了数の表示箇所を配列変数に格納
let monthData = ['E2','F2','G2','H2','I2','J2','E4','F4','G4','H4','I4','J4']ここは月シートのE1:J4の月の完了数を入力しているセルです。
範囲を配列番号で指定して入力するために、配列を使用しています。
monthData[0] ＝'E2' 〜monthData[11]='J4'
という感じで、「月-1」の数値で範囲を拾えるようにしています。
//前月の完了数を新しい月シートにコピー
newSheet.getRange(monthData[lastMonth-1]).setValue(monthSheet.getRange(monthData[lastMonth-1]).getDisplayValue());
//新しい月シートの完了数表示の関数をセット
newSheet.getRange(monthData[month-1]).setValue('=B2');ちょっとややこしいのですが、getRange()で先月の完了数が入っているセルの値を取得して、新しいシートに記入しています。
新しいシートでは、その月の完了数を取得できるようにするために「=B2」とB2の値を引っ張ってくる関数を入力しています。
getDisplayValue()は表示されている数値・文字等を取得するメソッドです。
ここでgetValue()を使用してしまうと、入力されている関数が取得されてしまいます。
表示されている完了数を取得したいため、getDisplayValue()を使用しています。
//新しい月シートをアクティブシートに
sheet.setActiveSheet(newSheet);
//新しい月シートをシートの2番目の位置に移動
sheet.moveActiveSheet(2);
Logger.log(monthShtNameN+'シート作成完了');//ログ確認用ここではシートの移動を行なっています。
新しく作成した月シートは、タスク管理シートの次のタブに置きたいので移動させているのですが、シートを移動させるmoveActiveSheet()メソッドは、アクティブになっているシートにしか使えないため、
setActiveSheet()メソッドを使用して、新しく作成した月シートをアクティブシートにしています。
一番最後にある「202206」のタブを管理シートのすぐ右に移動させたい↓
以上がタスク管理シートのGAS詳細です。
初心者の人でもわかるように解説したつもりですが、
実際に触ったり動かしてみないと読んだだけではさっぱり理解できないと思います。
このサイトではサンプルデータを配布して、
実際に動かして学べるように記事を作成しているので、
他の記事も参考にして動かせるようにしてみてください。
ありがとうございました！
コメント