limpeza-automatica-planilhas
Script para Google Sheets que automatiza a limpeza de planilhas e a restauração de fórmulas.
AI Summary
Automated Formula Protection for Google Sheets
A Google Apps Script that monitors specified cells in a shared Google Sheet and automatically restores the original formulas when they are overwritten or deleted.
Target Users
Intermediate Google Apps Script users who manage shared Google Sheets in teams and need to keep critical formulas intact in internal dashboards.
Problems Solved
In multi-editor Google Sheets, formulas are often accidentally replaced with static values, breaking data integrity, and there is no native feature to prevent this.
Tags
Main Features
Cell-level formula monitoring
Stores the original formulas of given sheets/ranges and compares them on every edit event to detect unauthorized changes.
Automatic formula restoration
When a formula is replaced by a value or different expression, the script immediately writes back the original formula, preserving data integrity.
Custom menu & trigger support
Adds a spreadsheet menu for manual execution and supports on-open or time-based triggers for automated runs.
Simple configuration file
Installation only requires listing sheet names and ranges in a configuration section at the top of the script.
Usage Examples
Minimal setup example
/**
* List the sheets and ranges you want to keep safe.
*/
const PROTECTED_RANGES = [
{ sheet: 'Dashboard', range: 'A1:F50' },
{ sheet: 'RawData', range: 'G1:G' }
];
/**
* Add a custom menu when the spreadsheet is opened.
*/
function onOpen() {
SpreadsheetApp.getActiveSpreadsheet().addMenu('🛡️ Fórmulas', [
{ name: 'Check & Restore Formulas', functionName: 'corrigeFormulas' } // corrigeFormulas is implemented in the library files
]);
}
/**
* You can also call the library on a time-based trigger.
* This helper creates a trigger that runs every hour.
*/
function createHourlyTrigger() {
ScriptApp.newTrigger('corrigeFormulas')
.timeBased()
.everyHours(1)
.create();
}The corrigeFormulas function—defined in the imported library files—reads PROTECTED_RANGES, checks each cell for unauthorized changes, and restores the original formula if necessary. Developers can run it manually from the custom menu or automatically via the hourly trigger.