🟢43Clear Data (Not Formulas) in Specific Google Sheets Ranges with Apps ScriptAdded

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

Do you need to clear data from specific sheets in your Google Sheets while keeping your formulas intact? This Google Apps Script is a perfect solution! It allows you to clear non-formula data in a chosen range across multiple sheets by specifying their names (e.g., Sheet1,Sheet2,Sheet3) or processing all sheets if you leave the input blank. Whether you’re managing budgets, resetting project trackers, or preparing data for a new period, this script will save you time and effort.

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.
• Receive clear feedback on success or errors, making troubleshooting easy.

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

The Script

Below is the Google Apps Script that makes this automation possible. Copy it to start clearing data in your Google Sheets.

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

  // 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: Navigate 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 in a new tab.
3. 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").
4. 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.
5. 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.
6. 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.
7. 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
  • 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
  • Result: Clears data in B2:G50 on sheets named Data, Summary, and Report.
• 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).
• 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. It’s perfect for:

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

The comma-separated input makes it easy to target specific sheets by name, giving you precise control over your spreadsheet.

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.
• Add Confirmation: Include a prompt to confirm before clearing data, especially for large operations.
• 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 workflow? Copy this script, test it in your spreadsheet, and see how it simplifies data clearing. Have questions or run into issues? Leave a comment, and I’ll help you out. Happy automating!

Posted on August 5, 2025