ClearDataNotFormula with confirmation prompt

Clear Data (Not Formulas) in Multiple Google Sheets with Confirmation

Do you work with Google Sheets and need to clear data across multiple sheets while keeping your formulas intact? This Google Apps Script is a game-changer! It lets you clear non-formula data in a specific range across multiple sheets by entering a single sheet name (e.g., Sheet1), a range of sheets (e.g., Sheet1:Sheet9), or leaving it blank for all sheets. With a confirmation prompt to prevent accidental data loss, it’s safe and efficient. Whether you’re managing budgets, tracking projects, or analyzing data, this script will save you time.

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.
• Confirm your action with a prompt to ensure no accidental data clearing.
• Get feedback on success or errors, making it easy to troubleshoot.

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

The Script

Below is the Google Apps Script that powers this automation. Copy it to start clearing data with confidence.

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();

  // Confirmation prompt
  const sheetList = sheets.length === allSheets.length ? 'all sheets' : 
    sheets.length === 1 ? `sheet: ${sheets[0].getName()}` : 
    `sheets: ${sheets[0].getName()} to ${sheets[sheets.length - 1].getName()}`;
  const confirmResponse = ui.alert(
    'Confirm Clear Data',
    `Are you sure you want to clear data (but not formulas) in range ${rangeInput} on ${sheetList}?`,
    ui.ButtonSet.YES_NO
  );

  // Check if the user clicked Yes
  if (confirmResponse != ui.Button.YES) {
    ui.alert('Cancelled', 'Operation cancelled.', ui.ButtonSet.OK);
    return;
  }

  // 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. If you click Cancel, the script will stop.
7. Confirm the Action:
   • A confirmation prompt will ask if you’re sure you want to clear data in the specified range and sheets (e.g., “Are you sure you want to clear data (but not formulas) in range A1:P100 on sheets: Sheet1 to Sheet9?”).
   • Click Yes to proceed or No to cancel.
8. 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
  • Confirmation: Confirm clearing A1:P100 on sheet: Sheet1
  • Result: Clears data (not formulas) in A1:P100 on Sheet1.
• Clear data in a range of sheets:
  • Input: Sheet1:Sheet3
  • Range: B2:G50
  • Confirmation: Confirm clearing B2:G50 on sheets: Sheet1 to Sheet3
  • 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
  • Confirmation: Confirm clearing A1:Z1000 on all sheets
  • Result: Clears data in A1:Z1000 across all sheets in the spreadsheet.

Comparing with Other Scripts

This script uses a name-based range input (e.g., Sheet1:Sheet9) with a confirmation prompt for safety. Here’s how it compares to other versions:

• Name-Based Range (This Script): Enter a single sheet name (e.g., Sheet1) or a range like Sheet1:Sheet9 to process sheets in tab order, with a confirmation to verify your action. Ideal when your sheets have specific names and you want to process a range of them.
• Comma-Separated Names: Another version uses Sheet1,Sheet2,Sheet3 to target specific sheets by name, not their order. It does not support name-based ranges like Sheet1:Sheet9.
• Position-Based Range: A different version uses SheetX:SheetY (e.g., Sheet6:Sheet24) to process sheets by their position in the tab order (6th to 24th sheets), regardless of names. It does not support name-based ranges.

Use this name-based version with confirmation when you want to select a range of sheets by their names in tab order and ensure no accidental data loss!

Tips and Notes

• Confirmation Prompt: The script asks for confirmation before clearing data, showing the range and sheets to be affected (e.g., “A1:P100 on sheets: Sheet1 to Sheet9”). Click “Yes” to proceed or “No” to cancel.
• 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, with the added safety of a confirmation prompt. It’s especially useful for:

• Data Analysts: Clear old data while keeping calculations ready for new inputs, with confidence that you won’t accidentally delete data.
• Project Managers: Reset tracking sheets across multiple projects without losing formulas, with a confirmation to verify your selection.
• Educators: Clear student data in gradebooks while preserving grading formulas, ensuring no mistakes.

The user-friendly prompts and confirmation 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.
• Custom Confirmation Message: Adjust the confirmation prompt to include additional details, like the number of cells to be cleared.
• Handle Protected Ranges: Add checks for protected sheets or ranges to avoid errors.
• Log Changes: Save a log of cleared sheets to a new sheet for tracking.

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. The confirmation prompt ensures worry-free data clearing. 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