Clear Data (Not Formulas) in Multiple Google Sheets with Apps Script

Do you work with Google Sheets and need to clear data across multiple sheets while keeping your formulas intact? Whether you're managing budgets, tracking projects, or analyzing data, this Google Apps Script will save you time by automating the process. In this post, I'll share a powerful script that lets you clear non-formula data in a specific range across multiple sheets, with a user-friendly prompt to guide you.

What Does This Script Do?

This script allows you to:

• Clear data (but not formulas) in a specified range across one or more sheets.
• Choose sheets by entering a single sheet name (e.g., Sheet1), a range of sheets (e.g., Sheet1:Sheet9), or leave it blank to process all sheets.
• Specify a range (e.g., A1:P100) to clear data in that area only.
• Get feedback on success or errors, making it easy to troubleshoot.

Perfect for: Data analysts, project managers, or anyone who needs to reset data in Google Sheets without losing carefully crafted formulas!

The Script

Below is the Google Apps Script that does the magic. It prompts you for a sheet range and a cell range, then clears data while preserving formulas.

function clearDataNotFormulas() {
  const ui = SpreadsheetApp.getUi();
  
  // Prompt for sheet range (e.g., Sheet1:Sheet9 or Sheet1 for single sheet)
  const sheetResponse = ui.prompt(
    'Enter Sheet Range',
    'Enter sheet range (e.g., Sheet1:Sheet9 or Sheet1 for single sheet, or leave blank for all sheets):',
    ui.ButtonSet.OK_CANCEL
  );

  // Check if the user clicked OK for sheet range
  if (sheetResponse.getSelectedButton() != ui.Button.OK) {
    ui.alert('Cancelled', 'Operation cancelled.', ui.ButtonSet.OK);
    return;
  }

  const sheetInput = sheetResponse.getResponseText().trim();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheets = [];

  // Get all sheets in the spreadsheet
  const allSheets = ss.getSheets();

  if (!sheetInput) {
    // If input is blank, process all sheets
    sheets = allSheets;
  } else {
    // Parse sheet range (e.g., Sheet1:Sheet9 or single sheet)
    const sheetRange = sheetInput.split(':').map(name => name.trim());
    let startSheetName, endSheetName;

    if (sheetRange.length === 1) {
      // Single sheet case
      startSheetName = sheetRange[0];
      const singleSheet = ss.getSheetByName(startSheetName);
      if (!singleSheet) {
        ui.alert('Error', `Sheet "${startSheetName}" not found.`, ui.ButtonSet.OK);
        return;
      }
      sheets = [singleSheet];
    } else if (sheetRange.length === 2) {
      // Sheet range case
      startSheetName = sheetRange[0];
      endSheetName = sheetRange[1];

      // Find indices of start and end sheets
      let startIndex = -1, endIndex = -1;
      allSheets.forEach((sheet, index) => {
        if (sheet.getName() === startSheetName) startIndex = index;
        if (sheet.getName() === endSheetName) endIndex = index;
      });

      // Validate sheet names and range
      if (startIndex === -1 || endIndex === -1) {
        ui.alert('Error', `One or both sheets "${startSheetName}" or "${endSheetName}" not found.`, ui.ButtonSet.OK);
        return;
      }
      if (startIndex > endIndex) {
        ui.alert('Error', 'Start sheet must appear before end sheet in the spreadsheet.', ui.ButtonSet.OK);
        return;
      }

      // Collect sheets in the range (inclusive)
      sheets = allSheets.slice(startIndex, endIndex + 1);
    } else {
      ui.alert('Error', 'Invalid sheet range format. Use Sheet1:Sheet9 or Sheet1.', ui.ButtonSet.OK);
      return;
    }
  }

  // Prompt for range
  const rangeResponse = ui.prompt(
    'Enter Range',
    'Please enter the range to clear (e.g., A1:P100):',
    ui.ButtonSet.OK_CANCEL
  );

  // Check if the user clicked OK for range
  if (rangeResponse.getSelectedButton() != ui.Button.OK) {
    ui.alert('Cancelled', 'Operation cancelled.', ui.ButtonSet.OK);
    return;
  }

  const rangeInput = rangeResponse.getResponseText().trim();

  // Process each sheet
  let successCount = 0;
  let errorMessages = [];

  sheets.forEach(sheet => {
    try {
      const range = sheet.getRange(rangeInput);
      const formulas = range.getFormulas();
      range.clearContent();
      range.setFormulas(formulas);
      successCount++;
    } catch (e) {
      errorMessages.push(`Sheet "${sheet.getName()}": Invalid range "${rangeInput}". Please use format like A1:P100.`);
    }
  });

  // Display results
  if (successCount === sheets.length) {
    ui.alert('Success', `Data cleared, formulas preserved in ${successCount} sheet(s)!`, ui.ButtonSet.OK);
  } else if (successCount > 0) {
    ui.alert(
      'Partial Success',
      `Data cleared in ${successCount} sheet(s). Errors occurred:\n${errorMessages.join('\n')}`,
      ui.ButtonSet.OK
    );
  } else {
    ui.alert('Error', errorMessages.join('\n'), ui.ButtonSet.OK);
  }
}
    

How to Use the Script

Follow these simple steps to implement and run the script in your Google Sheets:

1. Open Your Google Sheet: Go to the Google Sheet where you want to clear data.
2. Access Apps Script:
   • Click Extensions > Apps Script in the Google Sheets menu.
   • This opens the Apps Script editor.
3. Paste the Script:
   • Copy the script above.
   • Paste it into the Apps Script editor, replacing any existing code.
   • Click the save icon (or File > Save) and name your project (e.g., "ClearDataNotFormulas").
4. Run the Script:
   • Click the play button (▶) in the Apps Script editor to run the clearDataNotFormulas function.
   • The first time you run it, Google Sheets will ask for permissions. Review and grant them to allow the script to access your spreadsheet.
5. Enter Sheet Range:
   • A prompt will ask for a sheet range. Enter one of the following:
     • Single sheet: Type the sheet name (e.g., Sheet1).
     • Range of sheets: Type a range like Sheet1:Sheet9 to process all sheets from Sheet1 to Sheet9 in the tab order.
     • All sheets: Leave the input blank to process every sheet in the spreadsheet.
   • Click OK. If you click Cancel, the script will stop.
6. Enter Cell Range:
   • Another prompt will ask for the cell range (e.g., A1:P100).
   • Enter the range where you want to clear data (excluding formulas).
   • Click OK.
7. Review Results:
   • If successful, you’ll see a "Success" message indicating how many sheets were processed.
   • If there are errors (e.g., invalid sheet names or ranges), you’ll get a detailed error message.

Example Scenarios

• Clear data in a single sheet:
  • Input: Sheet1
  • Range: A1:P100
  • Result: Clears data (not formulas) in A1:P100 on Sheet1.
• Clear data in a range of sheets:
  • Input: Sheet1:Sheet3
  • Range: B2:G50
  • Result: Clears data in B2:G50 on all sheets from Sheet1 to Sheet3 in the tab order.
• Clear data in all sheets:
  • Input: (blank)
  • Range: A1:Z1000
  • Result: Clears data in A1:Z1000 across all sheets in the spreadsheet.

Tips and Notes

• Sheet Names Are Case-Sensitive: Enter exact sheet names (e.g., Sheet1, not sheet1).
• Tab Order Matters: For a range like Sheet1:Sheet3, the script processes sheets based on their order in the tab bar, from Sheet1 to Sheet3.
• Invalid Ranges: If the cell range (e.g., A1:P100) is invalid for a sheet (e.g., the sheet is too small), the script will skip that sheet and report an error.
• Protected Sheets: If a sheet or range is protected, the script may fail unless you have edit permissions.
• Performance: For large spreadsheets with many sheets or big ranges, the script may take a few seconds to run.

Why Use This Script?

This script is a game-changer for anyone who regularly resets data in Google Sheets but needs to keep formulas intact. It’s especially useful for:

• Data Analysts: Clear old data while keeping calculations ready for new inputs.
• Project Managers: Reset tracking sheets across multiple projects without losing formulas.
• Educators: Clear student data in gradebooks while preserving grading formulas.

Plus, the user-friendly prompts make it accessible even if you’re not a coding expert!

Customize It!

Want to tweak the script? Here are some ideas:

• Clear Entire Sheets: Modify the script to use sheet.getDataRange() instead of a user-specified range to clear all data in each sheet.
• Add a Confirmation Prompt: Add a prompt to confirm before clearing data, especially for large operations.
• Handle Protected Ranges: Add checks for protected sheets or ranges to avoid errors.

Let me know in the comments if you’d like help customizing this script for your needs!

Try It Out!

Give this script a try in your Google Sheets and let me know how it works for you. If you run into issues or have questions, drop them in the comments below, and I’ll be happy to help. Happy scripting!

Posted on August 5, 2025