なぜスプレッドシートのスクリプトが動かないのか？原因特定から解決まで完全網羅

try {
fn();
const duration = (new Date() - startTime) / 1000;
console.log(`${functionName} completed in ${duration}s`);

} catch (error) {
const errorInfo = {
functionName: functionName,
errorMessage: error.message,
stackTrace: error.stack,
timestamp: new Date().toISOString(),
spreadsheetUrl: SpreadsheetApp.getActiveSpreadsheet()?.getUrl() || 'N/A'
};

// スプレッドシートにエラーログを記録
logErrorToSheet(errorInfo);

// Slack通知（WebhookURLは環境に合わせて設定）
sendSlackNotification(errorInfo);

// エラーを再スロー（トリガーのエラーメールも送られる）
throw error;
}
}

function logErrorToSheet(errorInfo) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let logSheet = ss.getSheetByName('ErrorLog');

if (!logSheet) {
logSheet = ss.insertSheet('ErrorLog');
logSheet.appendRow);
}

logSheet.appendRow[
errorInfo.timestamp,
errorInfo.functionName,
errorInfo.errorMessage,
errorInfo.stackTrace,
errorInfo.spreadsheetUrl
]);
}

function sendSlackNotification(errorInfo) {
const webhookUrl = PropertiesService.getScriptProperties().getProperty('SLACK_WEBHOOK_URL');

if (!webhookUrl) {
console.warn('Slack Webhook URL not configured');
return;
}

const payload = {
text: `\u\U0001f6a8 GASエラー発生`,
attachments: [{
color: 'danger',
fields: [
{ title: '関数名', value: errorInfo.functionName, short: true },
{ title: '発生時刻', value: errorInfo.timestamp, short: true },
{ title: 'エラー内容', value: errorInfo.errorMessage, short: false },
{ title: 'スプレッドシート', value: errorInfo.spreadsheetUrl, short: false }
]
}]
};

getElapsedSeconds() {
return (new Date() - this.startTime) / 1000;
}

getRemainingSeconds() {
return (this.maxMilliseconds - (new Date() - this.startTime)) / 1000;
}

shouldStop() {
return (new Date() - this.startTime) >= this.maxMilliseconds;
}

logProgress(processedCount, totalCount) {
const elapsed = this.getElapsedSeconds().toFixed(1);
const remaining = this.getRemainingSeconds().toFixed(1);
const percent = ((processedCount / totalCount) * 100).toFixed(1);
console.log(`Progress: ${processedCount}/${totalCount} (${percent}%) | Elapsed: ${elapsed}s | Remaining: ${remaining}s`);
}
}

/**
* バッチ処理のサンプル実装
* 処理位置を保存し、次回実行時に続きから再開
*/
function batchProcessWithResume() {
const BATCH_SIZE = 100;
const timer = new ExecutionTimer(5);

const props = PropertiesService.getScriptProperties();
const lastProcessedRow = parseInt(props.getProperty('lastProcessedRow') || '1');

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();

let currentRow = lastProcessedRow;

while (currentRow <= lastRow && !timer.shouldStop()) { const endRow = Math.min(currentRow + BATCH_SIZE - 1, lastRow); const range = sheet.getRange(currentRow, 1, endRow - currentRow + 1, sheet.getLastColumn()); const data = range.getValues(); // ここで実際の処理を行う data.forEach((row, index) => {
processRow(row, currentRow + index);
});

currentRow = endRow + 1;
props.setProperty('lastProcessedRow', currentRow.toString());

timer.logProgress(currentRow - 1, lastRow);
}

if (currentRow > lastRow) {
props.deleteProperty('lastProcessedRow');
console.log('All rows processed successfully!');
} else {
console.log(`Stopped at row ${currentRow}. Will resume on next execution.`);
}
}

for (let attempt = 0; attempt <= maxRetries; attempt++) { try { return fn(); } catch (error) { lastError = error; // リトライ不可能なエラーは即座に再スロー if (isNonRetryableError(error)) { throw error; } if (attempt < maxRetries) { const delay = initialDelay * Math.pow(2, attempt); console.log(`Attempt ${attempt + 1} failed. Retrying in ${delay}ms...`); Utilities.sleep(delay); } } } throw new Error(`All ${maxRetries + 1} attempts failed. Last error: ${lastError.message}`); } function isNonRetryableError(error) { const nonRetryablePatterns = [ 'Invalid argument', 'Permission denied', 'Not found', 'Bad request' ]; return nonRetryablePatterns.some(pattern =>
error.message.toLowerCase().includes(pattern.toLowerCase())
);
}

// 使用例
function callExternalApi() {
const result = retryWithBackoff(() => {
const response = UrlFetchApp.fetch('https://api.example.com/data', {
muteHttpExceptions: true
});

const code = response.getResponseCode();
if (code === 429) {
throw new Error('Rate limited');
}
if (code >= 500) {
throw new Error(`Server error: ${code}`);
}
if (code !== 200) {
throw new Error(`Non-retryable error: ${code}`);
}

return JSON.parse(response.getContentText());
}, 3, 2000);

let sheet = ss.getSheetByName('TriggerInventory');
if (sheet) {
sheet.clear();
} else {
sheet = ss.insertSheet('TriggerInventory');
}

// ヘッダー行
sheet.appendRow[
'トリガーID',
'関数名',
'イベントタイプ',
'実行頻度',
'ソースタイプ',
'次回実行予定',
'最終更新者'
]);

triggers.forEach(trigger => {
const handlerFunction = trigger.getHandlerFunction();
const eventType = trigger.getEventType();
const triggerSource = trigger.getTriggerSource();

let frequency = 'N/A';
if (eventType === ScriptApp.EventType.CLOCK) {
// 時間ベースのトリガーの場合
frequency = getTimeTriggerFrequency(trigger);
}

sheet.appendRow[
trigger.getUniqueId(),
handlerFunction,
eventType.toString(),
frequency,
triggerSource.toString(),
'', // 次回実行予定は直接取得できないため空欄
Session.getActiveUser().getEmail()
]);
});

// 書式設定
sheet.getRange(1, 1, 1, 7).setFontWeight('bold').setBackground('#4285f4').setFontColor('white');
sheet.autoResizeColumns(1, 7);

console.log(`Exported ${triggers.length} triggers to TriggerInventory sheet`);
}

function getTimeTriggerFrequency(trigger) {
// トリガーの種類を判定（完全な情報は取得できないため推定）
try {
const source = trigger.getTriggerSource();
if (source === ScriptApp.TriggerSource.CLOCK) {
return '時間ベース（詳細は設定画面で確認）';
}
} catch (e) {
return '不明';
}
return 'N/A';
}

/**
* 不要なトリガーを一括削除（関数名で指定）
* @param {string} functionName - 削除するトリガーの関数名
*/
function deleteTriggersByFunctionName(functionName) {
const triggers = ScriptApp.getProjectTriggers();
let deletedCount = 0;

triggers.forEach(trigger => {
if (trigger.getHandlerFunction() === functionName) {
ScriptApp.deleteTrigger(trigger);
deletedCount++;
}
});

console.log('=== スプレッドシート健全性診断 ===');
console.log(`ファイルサイズ目安: ${getApproximateFileSize(ss)}`);
console.log(`シート数: ${sheets.length}`);
console.log('');

let totalCells = 0;
let totalFormulas = 0;

sheets.forEach(sheet => {
const name = sheet.getName();
const lastRow = sheet.getLastRow();
const lastCol = sheet.getLastColumn();
const maxRows = sheet.getMaxRows();
const maxCols = sheet.getMaxColumns();

const usedCells = lastRow * lastCol;
const totalSheetCells = maxRows * maxCols;
const usagePercent = ((usedCells / totalSheetCells) * 100).toFixed(1);

// 数式の数をカウント（大きいシートでは時間がかかる）
let formulaCount = 0;
if (lastRow > 0 && lastCol > 0 && lastRow * lastCol < 100000) { const formulas = sheet.getRange(1, 1, lastRow, lastCol).getFormulas(); formulaCount = formulas.flat().filter(f => f !== '').length;
}

totalCells += usedCells;
totalFormulas += formulaCount;

// 警告判定
let warnings = ;
if (lastRow > 50000) warnings.push('⚠️ 行数が多い');
if (maxRows - lastRow > 10000) warnings.push('\u\U0001f4a1 未使用行が多い');
if (formulaCount > 10000) warnings.push('⚠️ 数式が多い');

console.log(`【${name}】`);
console.log(` 使用範囲: ${lastRow}行 x ${lastCol}列 (${usedCells.toLocaleString()}セル)`);
console.log(` シートサイズ: ${maxRows}行 x ${maxCols}列`);
console.log(` 使用率: ${usagePercent}%`);
console.log(` 数式数: ${formulaCount.toLocaleString()}`);
if (warnings.length > 0) {
console.log(` 警告: ${warnings.join(', ')}`);
}
console.log('');
});

console.log('=== サマリー ===');
console.log(`総使用セル数: ${totalCells.toLocaleString()}`);
console.log(`総数式数: ${totalFormulas.toLocaleString()}`);

if (totalCells > 500000) {
console.log('\u\U0001f6a8 データ量が多いです。シートの分割を検討してください。');
}
if (totalFormulas > 50000) {
console.log('\u\U0001f6a8 数式が多いです。値として貼り付けることを検討してください。');
}
}

function getApproximateFileSize(ss) {
// 正確なファイルサイズは取得できないため、概算
const sheets = ss.getSheets();
let totalChars = 0;

if (!sheet) {
throw new Error(`Sheet "${sheetName}" not found`);
}

const cutoffDate = new Date();
cutoffDate.setDate(cutoffDate.getDate() - daysToKeep);

const lastRow = sheet.getLastRow();
const lastCol = sheet.getLastColumn();

if (lastRow <= 1) { console.log('No data to archive'); return; } const data = sheet.getRange(2, 1, lastRow - 1, lastCol).getValues(); const headers = sheet.getRange(1, 1, 1, lastCol).getValues(); const rowsToArchive = ; const rowsToKeep = ; data.forEach((row, index) => {
const dateValue = row;
if (dateValue instanceof Date && dateValue < cutoffDate) { rowsToArchive.push(row); } else { rowsToKeep.push(row); } }); if (rowsToArchive.length === 0) { console.log('No rows to archive'); return; } // アーカイブファイルを作成または開く const archiveFileName = `${ss.getName()}_Archive_${Utilities.formatDate(new Date(), 'JST', 'yyyyMM')}`; let archiveFile = getOrCreateArchiveFile(archiveFileName); let archiveSheet = archiveFile.getSheetByName(sheetName); if (!archiveSheet) { archiveSheet = archiveFile.insertSheet(sheetName); archiveSheet.appendRow(headers); } // アーカイブに追記 const archiveLastRow = archiveSheet.getLastRow(); archiveSheet.getRange(archiveLastRow + 1, 1, rowsToArchive.length, lastCol).setValues(rowsToArchive); // 元シートを更新 sheet.getRange(2, 1, lastRow - 1, lastCol).clearContent(); if (rowsToKeep.length > 0) {
sheet.getRange(2, 1, rowsToKeep.length, lastCol).setValues(rowsToKeep);
}

console.log(`Archived ${rowsToArchive.length} rows. Kept ${rowsToKeep.length} rows.`);
console.log(`Archive file: ${archiveFile.getUrl()}`);
}

function getOrCreateArchiveFile(fileName) {
const files = DriveApp.getFilesByName(fileName);

if (files.hasNext()) {
return SpreadsheetApp.open(files.next());
}

try {
// 最大30秒待ってロック取得を試みる
const hasLock = lock.tryLock(30000);

if (!hasLock) {
console.log('Could not obtain lock. Another process is running.');
return;
}

// ここに実際の処理を記述
processEdit(e);

} catch (error) {
console.error('Error in handleEditWithLock:', error);
throw error;

} finally {
// 必ずロックを解放
lock.releaseLock();
}
}

function processEdit(e) {
const sheet = e.source.getActiveSheet();
const range = e.range;

// バックアップ用フォルダを取得または作成
const backupFolderName = 'GAS_Backups';
let backupFolder = getOrCreateFolder(backupFolderName);

// プロジェクト固有のサブフォルダ
let projectFolder = getOrCreateSubFolder(backupFolder, projectName);

// 現在のコードを取得（boundスクリプトの場合）
const scriptId = ScriptApp.getScriptId();
const timestamp = Utilities.formatDate(new Date(), 'JST', 'yyyyMMdd_HHmmss');

// 注意: 実際のソースコード取得にはApps Script APIの有効化が必要
// ここでは簡易的にスクリプトIDと更新日時を記録
const backupInfo = {
scriptId: scriptId,
projectName: projectName,
backupDate: timestamp,
spreadsheetUrl: ss.getUrl(),
note: 'Full code backup requires Apps Script API'
};

const fileName = `backup_${timestamp}.json`;
projectFolder.createFile(fileName, JSON.stringify(backupInfo, null, 2), MimeType.PLAIN_TEXT);

console.log(`Backup created: ${fileName}`);

// 古いバックアップを削除（30日以上前のもの）
cleanupOldBackups(projectFolder, 30);
}

function getOrCreateFolder(folderName) {
const folders = DriveApp.getFoldersByName(folderName);
if (folders.hasNext()) {
return folders.next();
}
return DriveApp.createFolder(folderName);
}

function getOrCreateSubFolder(parentFolder, folderName) {
const folders = parentFolder.getFoldersByName(folderName);
if (folders.hasNext()) {
return folders.next();
}
return parentFolder.createFolder(folderName);
}

function cleanupOldBackups(folder, daysToKeep) {
const cutoffDate = new Date();
cutoffDate.setDate(cutoffDate.getDate() - daysToKeep);

const files = folder.getFiles();
let deletedCount = 0;