GASでスプレッドシートの関数自動生成
こんにちは、GASおじです。今回は「GASでスプレッドシートの関数自動生成」というテーマで、中小企業の経営者やフリーランスの皆さんに役立つ内容をお届けします。日々の作業効率化を狙いつつ、既存データは絶対に壊さない、かつ安全に使えるコードを紹介しますよ。
なぜ関数自動生成が必要なのか？
スプレッドシートでよく使う関数、例えばSUM、VLOOKUP、IFなど。これらを毎回手打ちするのは面倒だし、入力ミスも起こりやすい。なにより、複雑なシートになると関数の管理も大変です。
GAS（Google Apps Script）を使えば、そんな関数の自動生成が可能。しかも既存のシートを直接いじらず、新しい日時付きシートや新規ファイルに結果を出力するので、元データを安全に保護できます。
ちなみに、GASはGoogleアカウントの利用枠内で動きます。追加の専用SaaS料金は不要ですが、Apps Scriptのquotaや実行時間上限はありますので、そこは理解しておきましょう。
初期設定と必要権限
まずはGASエディタを開いて新規プロジェクトを作成してください。スプレッドシートにアクセスするためには、スクリプトに「スプレッドシートの閲覧・編集権限」が必要です。初回実行時に認証ダイアログが出ますので、指示に従って許可してください。
また、個人情報の取扱いには十分注意しましょう。スクリプトは基本的に自分のGoogle環境内で動きますが、外部送信やログに個人情報を含めないことが安全です。
installableトリガーを使えば、例えば毎日決まった時間に関数自動生成を実行することも可能です。必要に応じて設定してください。
コード例：関数自動生成＋安全な書き込み＋バックアップ
以下は、元データの関数を新しい日時付きシートに自動生成するサンプルコードです。入力検証やバッチ書き込みも含みます。
function generateFunctionsSafely() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName('元データ');
if (!sourceSheet) {
throw new Error('元データシートが見つかりません');
}
// バックアップ用の新規シートを作成（日時付き）
const timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd_HHmmss');
const backupSheetName = 'バックアップ_' + timestamp;
const backupSheet = ss.insertSheet(backupSheetName);
// 元データ範囲の取得
const dataRange = sourceSheet.getDataRange();
const dataValues = dataRange.getValues();
// バックアップにデータコピー（値のみ）
backupSheet.getRange(1, 1, dataValues.length, dataValues[0].length).setValues(dataValues);
// 新規シート作成・日時付きで関数生成用に利用
const formulaSheetName = '関数生成_' + timestamp;
const formulaSheet = ss.insertSheet(formulaSheetName);
// 入力検証：元データのA列に数値が入っているかチェック（例）
const firstColumn = dataValues.map(row => row[0]);
if (firstColumn.some(cell => typeof cell !== 'number')) {
throw new Error('元データのA列に数値以外の値が含まれています');
}
// 関数自動生成例：B列にA列の合計を出すSUM関数を行ごとにセット
// ここでは新規シートのB2以降にSUM関数を入れてみます
const numRows = dataValues.length;
const formulas = [];
for (let i = 1; i < numRows; i++) { // 1行目はヘッダー想定
// 例：=SUM(元データ!A2:A10)
// ここは単純にA列を範囲指定してSUMした値をB列に表示する形
// 実務ではもっと複雑な関数自動生成も可能
formulas.push([`=SUM('元データ'!A2:A${numRows})`]);
}
// ヘッダー行は空白にしておく
formulas.unshift(['合計']);
// バッチで関数セット（1列分）
formulaSheet.getRange(1, 2, formulas.length, 1).setFormulas(formulas);
// A列には元データのA列をコピー（値のみ）
const aColumnValues = dataValues.map(row => [row[0]]);
formulaSheet.getRange(1, 1, aColumnValues.length, 1).setValues(aColumnValues);
Logger.log('関数自動生成が完了しました。新しいシート名：' + formulaSheetName);
}
ポイント解説
- 既存の「元データ」シートは一切書き換えません。新しいシートにバックアップと関数生成結果を出力。
- 入力検証でA列に数値以外がある場合は処理を中断。無駄なエラーを防止します。
- バッチ書き込みでAPIコール回数を減らし効率的。
- 削除系APIは使わず、シートの追加のみで済ませています。
導入後のコスト削減イメージ
例えば、手作業で毎日30分かかっていた関数入力をGASで自動化できた場合を考えましょう。
- 時間単価：2000円/時間
- 30分 = 0.5時間 × 2000円 = 1000円/日
- 月20営業日なら：1000円 × 20日 = 20,000円/月
- 年間なら約24万円の時間コスト削減効果（あくまで仮定）
もちろん実際の効果は状況によりますが、こうした自動化で人件費のムダを減らすのは大切です。
復元手順とトラブル対策
- 新規シートにデータ・関数を出力しているため、元シートは安全そのもの。
- バックアップシートは消さずに残しておけば、何かあったときに元データを復元可能。
- もし誤って生成シートを消しても、元データは変わらないので安心。
- トリガーやスクリプトの変更は逐次テスト環境で試すことをおすすめします。
いかがでしたか？GASを使えばスプレッドシートの関数自動生成も安全かつ効率的にできます。これで無駄な入力ミスも減り、時間も節約できますよ。
もしもっと多彩なツールを使いこなしたいなら、ぜひGASおじラボのメンバーシップもチェックしてくださいね。