Top 10 Google Sheets Time-Saving Scripts You Can Build in an Hour

Do you find yourself performing the same tedious tasks in Google Sheets day after day? Whether it’s formatting data, sending email notifications, or generating reports, Google Apps Script can automate these workflows and save you hours every week.

In this article, I’ll walk you through 10 powerful yet simple Google Sheets scripts that even coding beginners can implement in under an hour.

1. Auto-Format New Data

Tired of manually formatting new entries? This script automatically applies your preferred formatting to new rows as they’re added.

function autoFormatNewData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  
  // Format the newest row
  const range = sheet.getRange(lastRow, 1, 1, 5); // Last row, columns A-E
  
  // Apply formatting
  range.setFontWeight("normal");
  range.setFontFamily("Arial");
  range.setFontSize(10);
  
  // Format currency in column C
  sheet.getRange(lastRow, 3).setNumberFormat("$#,##0.00");
  
  // Format date in column D
  sheet.getRange(lastRow, 4).setNumberFormat("yyyy-mm-dd");
}

Set this to run on a form submit trigger or time-based trigger, and never worry about inconsistent formatting again!

2. Email Notifications for Updated Cells

Get notified when specific cells change—perfect for collaborative sheets or tracking important values.

function notifyOnCellChange() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const watchRange = sheet.getRange("B2"); // The cell to monitor
  const currentValue = watchRange.getValue();
  
  // Get the previous value from Properties Service
  const properties = PropertiesService.getScriptProperties();
  const previousValue = properties.getProperty("lastValue");
  
  // If value changed, send an email
  if (currentValue != previousValue && previousValue != null) {
    MailApp.sendEmail({
      to: "your.email@example.com",
      subject: "Cell Value Changed in Spreadsheet",
      body: `The watched cell changed from ${previousValue} to ${currentValue}.`
    });
  }
  
  // Store current value for next check
  properties.setProperty("lastValue", currentValue);
}

3. Automatic Data Cleanup

Remove duplicates, trim whitespace, and standardize case with this handy cleanup script.

function cleanupSheetData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const uniqueValues = [];
  
  // Process each row
  for (let i = 0; i < values.length; i++) {
    let row = values[i];
    
    // Skip header row
    if (i === 0) {
      uniqueValues.push(row);
      continue;
    }
    
    // Trim whitespace and standardize case in each cell
    for (let j = 0; j < row.length; j++) {
      if (typeof row[j] === 'string') {
        row[j] = row[j].trim();
        
        // Standardize case for specific columns (e.g., column B)
        if (j === 1) row[j] = row[j].toUpperCase();
      }
    }
    
    // Check for duplicates
    const isDuplicate = uniqueValues.some(uniqueRow => 
      uniqueRow[0] === row[0] && uniqueRow[1] === row[1]
    );
    
    if (!isDuplicate) uniqueValues.push(row);
  }
  
  // Clear and repopulate sheet with clean data
  sheet.clear();
  sheet.getRange(1, 1, uniqueValues.length, uniqueValues[0].length)
    .setValues(uniqueValues);
}

4. Weekly Summary Report Generator

This script creates a summary sheet with key metrics from your data—perfect for weekly reporting.

function generateWeeklySummary() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Data");
  
  // Create or get Summary sheet
  let summarySheet = ss.getSheetByName("Weekly Summary");
  if (!summarySheet) {
    summarySheet = ss.insertSheet("Weekly Summary");
  } else {
    summarySheet.clear();
  }
  
  // Set up headers
  summarySheet.getRange("A1:D1").setValues([["Metric", "This Week", "Last Week", "Change %"]]);
  summarySheet.getRange("A1:D1").setFontWeight("bold");
  
  // Calculate some example metrics (customize for your data)
  const dataRange = dataSheet.getDataRange().getValues();
  
  // Example: Count of rows
  const totalRows = dataRange.length - 1; // Subtract header
  
  // Example: Sum of values in column C
  let totalValue = 0;
  for (let i = 1; i < dataRange.length; i++) {
    totalValue += Number(dataRange[i][2]) || 0;
  }
  
  // Set some example metrics (replace with your own)
  summarySheet.getRange("A2:B3").setValues([
    ["Total Entries", totalRows],
    ["Total Value", totalValue]
  ]);
  
  // Format the summary sheet
  summarySheet.autoResizeColumns(1, 4);
}

5. Automatic Data Backup

Keep weekly snapshots of your data for safekeeping.

function backupData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  
  // Create new sheet with timestamp
  const now = new Date();
  const backupName = `Backup ${now.toISOString().slice(0,10)}`;
  
  // Check if a backup with today's date already exists
  try {
    ss.getSheetByName(backupName);
    // If no error is thrown, sheet exists
    Browser.msgBox("Backup already exists for today.");
    return;
  } catch (e) {
    // Sheet doesn't exist, proceed with backup
  }
  
  // Create new backup sheet
  const backupSheet = ss.insertSheet(backupName);
  
  // Copy data
  const data = sheet.getDataRange().getValues();
  backupSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
  
  // Copy formatting (basic)
  sheet.getDataRange().copyFormatToRange(
    backupSheet, 
    1, 
    sheet.getLastColumn(), 
    1, 
    sheet.getLastRow()
  );
  
  // Add timestamp note
  backupSheet.getRange("A1").setNote(`Backup created on ${now.toLocaleString()}`);
}

6. Custom Menu Buttons

Add a custom menu to your spreadsheet with your most-used scripts.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  
  // Create custom menu
  ui.createMenu('Automation')
    .addItem('Clean Data', 'cleanupSheetData')
    .addItem('Generate Summary', 'generateWeeklySummary')
    .addItem('Create Backup', 'backupData')
    .addSeparator()
    .addSubMenu(ui.createMenu('Formats')
      .addItem('Format as Currency', 'formatAsCurrency')
      .addItem('Format as Date', 'formatAsDate'))
    .addToUi();
}

// Example format functions
function formatAsCurrency() {
  const range = SpreadsheetApp.getActiveRange();
  range.setNumberFormat("$#,##0.00");
}

function formatAsDate() {
  const range = SpreadsheetApp.getActiveRange();
  range.setNumberFormat("yyyy-mm-dd");
}

7. Auto-Import Data from Another Sheet

Pull data from another Google Sheet automatically.

function importFromAnotherSheet() {
  // ID of the source spreadsheet
  const sourceId = '1234567890abcdefghijklmnopqrstuvwxyz'; // Replace with actual ID
  
  // Get source and destination sheets
  const sourceSheet = SpreadsheetApp.openById(sourceId).getSheetByName("Data");
  const destSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get data from source
  const sourceData = sourceSheet.getDataRange().getValues();
  
  // Clear destination and import data
  destSheet.clear();
  destSheet.getRange(1, 1, sourceData.length, sourceData[0].length)
    .setValues(sourceData);
    
  // Add import timestamp
  destSheet.getRange(1, sourceData[0].length + 1)
    .setValue("Last Import")
    .setFontWeight("bold");
    
  destSheet.getRange(2, sourceData[0].length + 1)
    .setValue(new Date())
    .setNumberFormat("yyyy-mm-dd HH:mm:ss");
}

8. Conditional Email Alerts

Send emails based on specific conditions in your data.

function conditionalAlerts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getDataRange().getValues();
  
  // Skip header row
  for (let i = 1; i < dataRange.length; i++) {
    const row = dataRange[i];
    
    // Example: Check if value in column C is below threshold
    const value = row[2]; // Column C
    const threshold = 100;
    
    if (value < threshold) {
      // Send alert email
      MailApp.sendEmail({
        to: "your.email@example.com",
        subject: "Low Value Alert in Spreadsheet",
        body: `Row ${i+1} contains a value (${value}) below the threshold of ${threshold}.
        
Item: ${row[0]}
Category: ${row[1]}
Value: ${value}

Please review this entry.`
      });
    }
  }
}

9. Data Validation Rules Generator

Automatically set up data validation rules based on reference data.

function setupDataValidation() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Example: Set up dropdown from values in another range
  const categoriesRange = sheet.getRange("Categories!A2:A20"); // Adjust to your range with categories
  
  // Apply validation to column B
  const targetRange = sheet.getRange("B2:B100"); // Apply to column B
  
  // Create validation rule
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(categoriesRange)
    .setAllowInvalid(false)
    .build();
    
  targetRange.setDataValidation(rule);
}

10. Interactive Spreadsheet Dashboard

Create a simple dashboard that updates with real-time data.

function updateDashboard() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Data");
  const dashboardSheet = ss.getSheetByName("Dashboard");
  
  // Ensure dashboard sheet exists
  if (!dashboardSheet) {
    dashboardSheet = ss.insertSheet("Dashboard");
    setupDashboard(dashboardSheet); // Function to set up initial layout
  }
  
  // Get data
  const data = dataSheet.getDataRange().getValues();
  
  // Calculate metrics
  let totalSales = 0;
  let categoryCounts = {};
  
  // Skip header
  for (let i = 1; i < data.length; i++) {
    const category = data[i][1]; // Column B has categories
    const value = Number(data[i][2]) || 0; // Column C has values
    
    totalSales += value;
    
    // Count by category
    if (!categoryCounts[category]) {
      categoryCounts[category] = {count: 0, value: 0};
    }
    categoryCounts[category].count += 1;
    categoryCounts[category].value += value;
  }
  
  // Update dashboard
  dashboardSheet.getRange("B2").setValue(totalSales);
  dashboardSheet.getRange("B3").setValue(data.length - 1); // Total entries
  
  // Update last refresh time
  dashboardSheet.getRange("B4").setValue(new Date()).setNumberFormat("yyyy-mm-dd HH:mm:ss");
  
  // Update category breakdown
  let row = 7; // Starting row for category breakdown
  for (let category in categoryCounts) {
    dashboardSheet.getRange(row, 1).setValue(category);
    dashboardSheet.getRange(row, 2).setValue(categoryCounts[category].count);
    dashboardSheet.getRange(row, 3).setValue(categoryCounts[category].value);
    row++;
  }
}

function setupDashboard(sheet) {
  // Set up static elements of dashboard
  sheet.getRange("A1:B1").setValues([["Dashboard", ""]]);
  sheet.getRange("A1:B1").merge().setFontSize(16).setFontWeight("bold");
  
  sheet.getRange("A2:B2").setValues([["Total Sales", ""]]);
  sheet.getRange("A3:B3").setValues([["Total Entries", ""]]);
  sheet.getRange("A4:B4").setValues([["Last Updated", ""]]);
  
  sheet.getRange("A6:C6").setValues([["Category", "Count", "Value"]]);
  sheet.getRange("A6:C6").setFontWeight("bold");
  
  // Format columns
  sheet.autoResizeColumns(1, 3);
}

How to Implement These Scripts

1. Open your Google Sheet
2. Click Extensions → Apps Script
3. Paste the script you want to use
4. Save the project (give it a name)
5. Run the function manually, or set up triggers for automation:
   • Click on Triggers (clock icon)
   • Add Trigger
   • Choose function, event source, and timing

Beyond the Basics

Once you’re comfortable with these scripts, you can:

• Combine multiple scripts into more complex workflows
• Add error handling for more robust solutions
• Create custom dialogs for user input
• Connect to external APIs for deeper integrations

Conclusion

These 10 Google Sheets scripts demonstrate the power of automation for everyday tasks. Even with basic coding knowledge, you can save hours of repetitive work and reduce the risk of manual errors.

Which script will you implement first? Do you have a specific Google Sheets workflow you’d like to automate? Share in the comments below!

---

Stay tuned for our next article on how to build a complete email newsletter system using Google Sheets and Apps Script.