時短ワザ大全 GAS・自動化の時短ワザ(18) Google Apps Script で、毎回の手作業をボタン1つに。コピペで動くコードつき。
学年のフォームの回答を、組ごとにコピーして担任に渡している。
見出しが「組」の列を見て、同じ組の行を「1組」「2組」…という名前のシートに書き出します。毎回作り直すだけなので何度実行しても大丈夫。トリガーで「フォーム送信時」に動かせば、回答が届くたびに自動で振り分けられます。
function splitByClass() {
const ss = SpreadsheetApp.getActive();
const src = ss.getSheetByName('フォームの回答 1');
const values = src.getDataRange().getValues();
const head = values.shift();
const col = head.indexOf('組'); // 見出しが「組」の列
if (col < 0) throw new Error('「組」という見出しの列が見つかりません');
const groups = {};
values.forEach(function (r) {
const key = String(r[col]).trim();
if (!key) return;
(groups[key] = groups[key] || []).push(r);
});
Object.keys(groups).forEach(function (key) {
const sh = ss.getSheetByName(key) || ss.insertSheet(key);
sh.clearContents();
sh.getRange(1, 1, 1, head.length).setValues([head]);
sh.getRange(2, 1, groups[key].length, head.length).setValues(groups[key]);
});
}
回答のシート名が違うときは「フォームの回答 1」を書き換えます。組のシートは毎回中身を消して書き直すので、そこに手で書き込まないでください。
年間の行事予定を、カレンダーに1件ずつ手で入れている。
A列に日付、B列に行事名を並べたシートから、終日の予定としてまとめて登録します。登録した行の C列に「登録済み」と書くので、2回実行しても二重にはなりません。学年や部活のカレンダーに入れたいときは、そのカレンダーの ID を指定します。
function addEvents() {
const sh = SpreadsheetApp.getActiveSheet();
const cal = CalendarApp.getDefaultCalendar();
const rows = sh.getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
const [date, title, done] = rows[i]; // A:日付 B:行事名 C:登録済み
if (!date || !title || done) continue;
cal.createAllDayEvent(title, new Date(date));
sh.getRange(i + 1, 3).setValue('登録済み');
}
}
自分のカレンダー以外に入れるなら CalendarApp.getCalendarById('カレンダーID') に置き換えます(ID は Google カレンダーの設定の「カレンダーの統合」にあります)。
「金曜17時締切」のフォームを、その時刻に閉じ忘れてしまう。
フォームを閉じる関数を作り、トリガーで「特定の日時」に実行するように設定します。締切の瞬間に受付が止まり、閉じたあとの案内文も自動で出ます。自分がその時間に授業中でも大丈夫です。
コードを貼り、フォームの ID を入れて保存 左の時計のアイコン(トリガー)→「トリガーを追加」 実行する関数「closeForm」、イベントのソース「時間主導型」、種類「特定の日時」を選んで日時を入力 保存する(初めてのときは権限の確認が出る)
function closeForm() {
const form = FormApp.openById('ここにフォームのID');
form.setAcceptingResponses(false);
form.setCustomClosedFormMessage('締め切りました。変更があるときは担任まで連絡してください。');
}
フォームの ID は、編集画面の URL の /d/ と /edit の間の文字列です。
欠席連絡が届いたら、その行に色を付けたり別のシートに写したりしたい。
回答先のスプレッドシートの GAS で、トリガーのイベントの種類を「フォーム送信時」にすると、回答が届くたびに関数が動きます。e.namedValues['質問のタイトル'] で回答の中身が、e.range でその回答の行が取れます。
function onSubmit(e) {
// e.namedValues は { 質問のタイトル: [回答] } の形
const kubun = e.namedValues['区分'][0];
if (kubun === '欠席') {
e.range.setBackground('#ffe0e0'); // 欠席の行に色を付ける
}
}
関数名を onFormSubmit にしても自動では動きません。必ずトリガーの画面で「スプレッドシートから」「フォーム送信時」を設定します。エディタの「実行」で試すと e が無いためエラーになりますが、それで正常です。
個別のお知らせメールを、一人ずつコピーして作っている。
シートの宛先・名前・内容から、Gmail の下書きを人数分まとめて作ります。送信はしないので、下書きフォルダで1通ずつ目で確かめてから送れます。宛先の間違いをその場で止められるのが「下書き方式」のいちばんの良さです。
function makeDrafts() {
const rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
const [to, name, item] = rows[i]; // A:宛先 B:名前 C:未提出の物
if (!to) continue;
const body = name + 'さんの保護者様\n\n' + item + 'の提出がまだのようです。\nご確認をお願いいたします。';
GmailApp.createDraft(to, '提出物のお知らせ', body);
}
}
宛先のデータは学校のルールに従って扱ってください。本番の前に、自分の宛先だけの表で試すと安心です。1日に作れる・送れる数には上限があります。
ポートフォリオ用のフォルダを、40人分手で作っている。
A列の名前を読み、親フォルダの中に同じ名前のフォルダを作ります。すでにある名前は飛ばすので、転入生を足してもう一度実行しても大丈夫。「01_原田」のように番号を先に付けておくと、フォルダが名簿の順に並びます。
function makeFolders() {
const parent = DriveApp.getFolderById('親フォルダのID');
const names = SpreadsheetApp.getActiveSheet()
.getRange('A2:A').getValues().flat().filter(String);
names.forEach(function (n) {
if (!parent.getFoldersByName(n).hasNext()) parent.createFolder(n);
});
}
親フォルダの ID は、ドライブでそのフォルダを開いたときの URL の最後の文字列です。
提出用フォルダに誰のファイルがあるか、一つずつ開いて確かめている。
指定したフォルダの中のファイル名・リンク・更新日時をシートに一覧にします。名簿と XLOOKUP で突き合わせれば、まだ出していない人がすぐ分かります。年度末のファイル整理にも使えます。
function listFiles() {
const it = DriveApp.getFolderById('フォルダのID').getFiles();
const out = [['ファイル名', 'URL', '更新日時']];
while (it.hasNext()) {
const f = it.next();
out.push([f.getName(), f.getUrl(), f.getLastUpdated()]);
}
const sh = SpreadsheetApp.getActiveSheet();
sh.clearContents();
sh.getRange(1, 1, out.length, 3).setValues(out);
}
実行すると、開いているシートの中身を消して一覧を書き込みます。専用の空のシートで実行してください。
同じ長い式を何度も書くのが面倒で、もっと短く書きたい。
GAS で関数を作って保存すると、セルで =NENDO(A2) のように普通の関数として使えます。学校独自のルール(年度・学期・当番の順番など)を1つの名前にまとめられ、式が短く読みやすくなります。
/** 日付から年度を返す 例:=NENDO(A2) */
function NENDO(d) {
if (!(d instanceof Date)) return '';
return d.getMonth() < 3 ? d.getFullYear() - 1 : d.getFullYear();
}
カスタム関数の中では、メール送信など権限のいる処理はできません。範囲を渡すと二次元の配列で届きます。スプレッドシートとスクリプトのタイムゾーンは両方「日本」にしておきます。
動いたり止まったりして、どこが悪いのか分からない。
①1回の実行は6分まで。セルを1つずつ読み書きせず、getValues/setValues でまとめて扱うと何十倍も速くなります。②権限の確認が出たら、自分で確かめたコードかを見てから許可。③うまくいかないときは console.log で途中の値を表示し、左の「実行数」で記録を見ます。
// 遅い:1セルずつ書く
for (let i = 0; i < data.length; i++) sh.getRange(i + 1, 1).setValue(data[i][0]);
// 速い:まとめて1回で書く
sh.getRange(1, 1, data.length, data[0].length).setValues(data);
校務の締切を忘れないよう、毎朝一覧表を開いて確かめている。
「締切」シートに締切日・仕事・済(チェックボックス)を並べておくと、締切まで3日以内で済んでいない行を集めて、自分あてにメールします。トリガーで毎朝7時台に動かせば、出勤前に今日やるべきことが届きます。送り先は実行している自分のアドレスだけです。
function remindDeadlines() {
const sh = SpreadsheetApp.getActive().getSheetByName('締切');
const rows = sh.getDataRange().getValues().slice(1); // A:締切日 B:仕事 C:済
const today = new Date();
today.setHours(0, 0, 0, 0);
const soon = rows.filter(function (r) {
if (!(r[0] instanceof Date) || r[2] === true) return false;
return (r[0] - today) / 86400000 <= 3; // 過ぎたものも含む
});
if (soon.length === 0) return;
const body = soon.map(function (r) {
return Utilities.formatDate(r[0], 'Asia/Tokyo', 'M/d') + ' ' + r[1];
}).join('\n');
MailApp.sendEmail(Session.getEffectiveUser().getEmail(), '締切3日以内の仕事(' + soon.length + '件)', body);
}
// 1回だけ実行して、毎朝7時台のトリガーを作る
function setDailyTrigger() {
ScriptApp.newTrigger('remindDeadlines').timeBased().everyDays(1).atHour(7).create();
}
setDailyTrigger は1回だけ実行します(何度も実行するとトリガーが重なり、メールが何通も届きます)。atHour(7) は7時ちょうどではなく7時台のどこかで動きます。プロジェクトの設定のタイムゾーンを「日本」にしておきます。
面談のお知らせや表彰の文面を、一人ずつ名前を書き換えて保存している。
Google ドキュメントでひな形を作り、名前などを入れる所に《名前》《日時》のような目印を書いておきます。スクリプトはシートの行ごとにひな形を複製し、目印を置き換えて保存します。一人ずつ別のファイルになるので、個別に共有したり印刷したりできます。
function makeDocs() {
const template = DriveApp.getFileById('ひな形ドキュメントのID');
const folder = DriveApp.getFolderById('保存先フォルダのID');
const rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
const [name, when] = rows[i]; // A:名前 B:日時
if (!name) continue;
const w = when instanceof Date
? Utilities.formatDate(when, 'Asia/Tokyo', 'M月d日 H:mm')
: String(when);
const copy = template.makeCopy(name + '_面談のお知らせ', folder);
const doc = DocumentApp.openById(copy.getId());
const body = doc.getBody();
body.replaceText('《名前》', name);
body.replaceText('《日時》', w);
doc.saveAndClose();
}
}
目印の文字は正規表現として扱われるので、{ } や ( ) ではなく《》のような記号にしておくと安全です。まず2〜3行だけの表で試します。名前の入ったファイルの保存先と共有の範囲は、学校の規程に従ってください。
毎週の学年だよりや当番表を、手で PDF に書き出してドライブに上げている。
getAs(MimeType.PDF) で、ドライブのファイルを PDF に変えて保存できます。ファイル名の先頭に日付を付けて決まったフォルダに入れるので、配った版が日付順に残ります。トリガーで毎週金曜に動かせば、配信用の PDF が自動でそろいます。
function saveAsPdf() {
const file = DriveApp.getFileById('PDFにしたいドキュメントのID');
const folder = DriveApp.getFolderById('保存先フォルダのID');
const stamp = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMMdd');
const pdf = file.getAs(MimeType.PDF).setName(stamp + '_' + file.getName() + '.pdf');
folder.createFile(pdf);
}
スプレッドシートにも使えますが、用紙の向きや余白などが思いどおりにならないことがあるので、最初に出来上がりを確かめます。細かい印刷の設定が必要な物は、手で PDF に書き出すほうが確実です。
学年で決めた20問のアンケートを、フォームに1問ずつ打ち込んでいる。
シートの A列に質問、B列に選択肢を「、」区切りで書いておくと、新しいフォームを作って選択式の質問を順に足します。B列が空の行は記述式にします。質問の文の確認をシートの上でまとめてできるので、学年会での打ち合わせもしやすくなります。
function makeForm() {
const rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues().slice(1); // A:質問 B:選択肢
const form = FormApp.create('学年アンケート(下書き)');
rows.forEach(function (r) {
const q = String(r[0]).trim();
if (!q) return;
const choices = String(r[1]).split('、')
.map(function (s) { return s.trim(); }).filter(String);
if (choices.length) {
form.addMultipleChoiceItem().setTitle(q).setChoiceValues(choices);
} else {
form.addParagraphTextItem().setTitle(q);
}
});
Logger.log(form.getEditUrl()); // 編集用の URL が実行ログに出る
}
できたフォームは自分のマイドライブに入ります。メールアドレスの収集や回答の制限などの設定は、できたあとに画面で確かめて決めます。
共有の成績表をだれかが壊してしまったとき、戻せるかがいつも不安。
スプレッドシートを丸ごと複製して、日付つきの名前でバックアップ用のフォルダに保存します。トリガーで毎週金曜の夕方に動かせば、手間をかけずに週ごとの控えが残ります。変更履歴よりも探しやすく、学期末の確定版を残すのにも使えます。
function backup() {
const ss = SpreadsheetApp.getActive();
const folder = DriveApp.getFolderById('バックアップ用フォルダのID');
const stamp = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMMdd');
DriveApp.getFileById(ss.getId()).makeCopy(stamp + '_' + ss.getName(), folder);
}
// 1回だけ実行して、毎週金曜18時台のトリガーを作る
function setWeeklyTrigger() {
ScriptApp.newTrigger('backup').timeBased()
.everyWeeks(1).onWeekDay(ScriptApp.WeekDay.FRIDAY).atHour(18).create();
}
コピーは増え続けるので、学期ごとなどに古いものを整理します。バックアップのフォルダも、元のファイルと同じように共有の範囲を絞ってください。成績など個人情報の控えをどこにいつまで残すかは、学校の規程に従います。
毎週の当番表や提出チェックのチェックを、週明けに一つずつ外している。
Range の uncheck() を使うと、範囲の中のチェックボックスをまとめてオフにできます。外す前に、先週チェックが入っていた数を「記録」シートに書き残します。トリガーで毎週月曜の朝に動かせば、週の始めにはいつも空の表が用意されています。
function resetChecks() {
const ss = SpreadsheetApp.getActive();
const range = ss.getSheetByName('今週').getRange('C2:G41'); // チェックボックスの範囲
const count = range.getValues().flat().filter(function (v) { return v === true; }).length;
ss.getSheetByName('記録').appendRow([new Date(), count]);
range.uncheck();
}
シート名と範囲は自分の表に合わせます。トリガーは画面から「時間主導型」「週ベースのタイマー」「毎週月曜日」と時間帯を選んで作れます。
提出された「IMG_0123.jpg」のようなファイル名を、一つずつ開いて付け直している。
まず listWithId でフォルダの中のファイルの ID と今の名前を書き出し、C列に新しい名前(例:2-1-05_理科レポート.pdf)を書きます。renameByList を実行すると、一覧のとおりに名前が変わります。名前順に並べるだけで番号順になり、採点や返却が楽になります。
function listWithId() {
const it = DriveApp.getFolderById('フォルダのID').getFiles();
const out = [['ファイルID', '今の名前', '新しい名前']];
while (it.hasNext()) {
const f = it.next();
out.push([f.getId(), f.getName(), '']);
}
const sh = SpreadsheetApp.getActiveSheet();
sh.clearContents();
sh.getRange(1, 1, out.length, 3).setValues(out);
}
function renameByList() {
const sh = SpreadsheetApp.getActiveSheet();
const rows = sh.getDataRange().getValues(); // A:ID B:今の名前 C:新しい名前
for (let i = 1; i < rows.length; i++) {
const [id, , newName] = rows[i];
if (!id || !newName) continue;
DriveApp.getFileById(id).setName(String(newName));
sh.getRange(i + 1, 4).setValue('変更済み');
}
}
listWithId は開いているシートの中身を消してから書き込むので、専用の空のシートで実行します。名前を変えられるのは編集の権限があるファイルだけ。拡張子(.pdf など)も新しい名前に書いておきます。ファイル名に実名を入れるかどうかは学校の決まりに合わせてください。
転入生が来るたびに、フォームのプルダウンの名前を手で足している。
フォームの「名前」という題のプルダウン(リスト)の質問を、シートの名簿の列から作り直します。名簿を直してこのスクリプトを実行するだけで、選択肢が最新の名簿と同じになります。名前を選ばせる形にすると、書き間違いや表記のゆれがなくなります。
function updateNameList() {
const form = FormApp.openById('フォームのID');
const names = SpreadsheetApp.getActive().getSheetByName('名簿')
.getRange('B2:B').getValues().flat().map(String)
.filter(function (s) { return s.trim(); });
const item = form.getItems(FormApp.ItemType.LIST)
.filter(function (it) { return it.getTitle() === '名前'; })[0];
if (!item) throw new Error('「名前」という題のプルダウンの質問が見つかりません');
item.asListItem().setChoiceValues(names);
}
選択肢の名前は、そのフォームを開ける人全員に見えます。校内のアカウントだけが開ける設定にするか、番号を選ばせる方法も考えてください。名前の扱いは学校の規程に従い、外部のサービスには入れないでください。