Web Tool Help

Clear Data (Not Formulas) in Google Sheets with Comma-Separated Names and Confirmation

Need to clear data from specific sheets in your Google Sheets while keeping formulas intact? This Google Apps Script is the perfect tool! It lets you clear non-formula data in a chosen range across multiple sheets by entering their names as a comma-separated list (e.g., Sheet1,Sheet2,Sheet3) or leaving the input blank for all sheets. Plus, it includes a confirmation prompt to prevent accidental data loss, making it safe and user-friendly. Whether you’re resetting budgets, clearing project trackers, or preparing for a new period, this script will streamline your workflow.

What Does This Script Do?

This script enables you to:

Clear data (but not formulas) in a specified range (e.g., A1:P100) across one or more sheets.
Select sheets flexibly:
- Specific sheets: Enter comma-separated sheet names (e.g., Sheet1,Sheet2,Sheet3).
- All sheets: Leave the input blank to process every sheet in the spreadsheet.
Confirm your action with a prompt to ensure you don’t accidentally clear data.
Receive clear feedback on success or errors for easy troubleshooting.

Perfect for: Data analysts, project managers, educators, or anyone who needs to safely reset data in specific sheets by name while preserving formulas!

The Script

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


function clearDataNotFormulas() {
  const ui = SpreadsheetApp.getUi();
  
  // Prompt for sheet names (comma-separated for multiple sheets)
  const sheetResponse = ui.prompt(
    'Enter Sheet Names',
    'Please enter the sheet names (e.g., Sheet1,Sheet2,Sheet3 or leave blank for all sheets):',
    ui.ButtonSet.OK_CANCEL
  );

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

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

  // If no sheet names provided, process all sheets
  if (!sheetInput.trim()) {
    sheets = ss.getSheets();
  } else {
    // Split input into array of sheet names and trim whitespace
    const sheetNames = sheetInput.split(',').map(name => name.trim());
    sheets = sheetNames.map(name => ss.getSheetByName(name)).filter(sheet => sheet !== null);

    // Validate that at least one valid sheet was found
    if (sheets.length === 0) {
      ui.alert('Error', 'No valid sheets found. Please check the sheet names and try again.', 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 === ss.getSheets().length ? 'all sheets' : `sheet(s): ${sheets.map(sheet => sheet.getName()).join(', ')}`;
  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 steps to set up and run the script in your Google Sheets:

Open Your Google Sheet: Navigate to the Google Sheet where you want to clear data.
Access Apps Script:
- Click Extensions > Apps Script in the Google Sheets menu.
- This opens the Apps Script editor in a new tab.
Paste the Script:
- Copy the script above.
- Paste it into the Apps Script editor, replacing any default code.
- Save the project by clicking the save icon or File > Save, and name it (e.g., "ClearDataNotFormulas").
Run the Script:
- In the Apps Script editor, click the play button (▶) to run the clearDataNotFormulas function.
- The first time you run it, Google will prompt you to authorize the script. Review and grant the necessary permissions.
Enter Sheet Names:
- A prompt will ask for sheet names. You can enter:
  - Specific sheets: Comma-separated names (e.g., Sheet1,Sheet2,Sheet3).
  - All sheets: Leave the input blank to process every sheet.
- Click OK. If you click Cancel, the script will stop.
Enter Cell Range:
- A second 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.
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 sheet(s): Sheet1, Sheet2, Sheet3?”).
- Click Yes to proceed or No to cancel.
Check the Results:
- If successful, a “Success” message will confirm how many sheets were processed.
- If there are issues (e.g., invalid sheet names or ranges), you’ll see a detailed error message.

Example Scenarios

Clear data in specific sheets:
- Input: Sheet1,Sheet2,Sheet3
- Range: A1:P100
- Confirmation: Confirm clearing A1:P100 on Sheet1, Sheet2, Sheet3
- Result: Clears data (not formulas) in A1:P100 on sheets named Sheet1, Sheet2, and Sheet3.
Clear data in custom-named sheets:
- Input: Data,Summary,Report
- Range: B2:G50
- Confirmation: Confirm clearing B2:G50 on Data, Summary, Report
- Result: Clears data in B2:G50 on sheets named Data, Summary, and Report.
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 unique comma-separated input for selecting sheets by name, with an added confirmation prompt for safety. Here’s how it compares to other versions:

Comma-Separated Names (This Script): Enter specific sheet names like Sheet1,Sheet2,Sheet3 to target only those sheets, with a confirmation to verify your action. Ideal when you know the exact sheet names and want precise control.
Position-Based Range: Another 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 comma-separated names or include a confirmation prompt.
Name-Based Range: An earlier version uses Sheet1:Sheet9 to process sheets from Sheet1 to Sheet9 in tab order, requiring those exact names. It also does not support comma-separated names or include a confirmation prompt.

Use this comma-separated version with confirmation when you want to select specific sheets by name 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 sheet(s): Sheet1, Sheet2”). Click “Yes” to proceed or “No” to cancel.
Sheet Names Are Case-Sensitive: Enter exact sheet names (e.g., Sheet1, not sheet1).
Comma-Separated Input: Use commas to separate sheet names (e.g., Sheet1,Sheet2). Spaces after commas are okay, as the script trims them.
Invalid Sheet Names: If a sheet name doesn’t exist, the script skips it and processes valid sheets, reporting errors for invalid ones.
Invalid Ranges: If the cell range (e.g., A1:P100) is invalid for a sheet, the script skips that sheet and reports an error.
Protected Sheets: If a sheet or range is protected, the script may fail unless you have edit permissions.
Performance: Processing many sheets or large ranges may take a few seconds.

Why Use This Script?

This script is ideal for anyone who needs to reset data in specific Google Sheets tabs while keeping formulas intact, with the added safety of a confirmation prompt. It’s perfect 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 for specific 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 comma-separated input and confirmation prompt make it easy and safe to target specific sheets by name.

Customize It!

Want to enhance the script? Here are some ideas:

Clear Entire Sheets: Replace sheet.getRange(rangeInput) with sheet.getDataRange() to clear all data in each sheet.
Custom Confirmation Message: Modify 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.

Drop a comment below if you need help customizing this script for your needs!

Try It Out!

Ready to streamline your Google Sheets tasks with added safety? Copy this script, test it in your spreadsheet, and see how the confirmation prompt ensures worry-free data clearing. Have questions or run into issues? Leave a comment, and I’ll help you out. Happy automating!

Posted on August 5, 2025

Webtoolhelp

Tuesday, August 5, 2025

Delete data not formula (Enter comma-separated sheet names (e.g., Sheet1,Sheet2,Sheet3).