Delete data Not Keeping Formula From Goole sheet Specified In MessageBox

Clear Data (Except Formulas) in Google Sheets with Custom Sheet and Range

This Google Apps Script clears all data in a user-specified range of a user-specified sheet in your Google Sheet while preserving formulas. Follow these steps to use it:

1. Open your Google Sheet.
2. Click on Extensions > Apps Script in the top menu.
3. In the Apps Script editor, paste the code below.
4. Save the script with a name (e.g., "ClearData").
5. Run the script by clicking the play button in the Apps Script editor. You may need to grant permissions the first time.
6. When prompted, enter the name of the sheet (e.g., MySheet) in the first dialog box.
7. If the sheet name is valid, a second dialog box will prompt you to enter the range (e.g., A1:Z50).
8. Click OK to proceed or Cancel to abort at either prompt.
9. Check your sheet—data will be cleared, but formulas will remain!
10. If an invalid sheet name or range is entered, an error message will appear.

function clearDataNotFormulas() { const ui = SpreadsheetApp.getUi(); // Prompt for sheet name const sheetResponse = ui.prompt( 'Enter Sheet Name', 'Please enter the name of the sheet (e.g., MySheet):', ui.ButtonSet.OK_CANCEL ); // Check if the user clicked OK for sheet name if (sheetResponse.getSelectedButton() != ui.Button.OK) { ui.alert('Cancelled', 'Operation cancelled.', ui.ButtonSet.OK); return; } const sheetName = sheetResponse.getResponseText(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const ws = ss.getSheetByName(sheetName); // Validate sheet name if (!ws) { ui.alert('Error', `Sheet "${sheetName}" not found. Please check the sheet name and try again.`, ui.ButtonSet.OK); return; } // Prompt for range const rangeResponse = ui.prompt( 'Enter Range', 'Please enter the range (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(); try { const range = ws.getRange(rangeInput); const formulas = range.getFormulas(); range.clearContent(); range.setFormulas(formulas); ui.alert('Success', 'Data cleared, formulas preserved!', ui.ButtonSet.OK); } catch (e) { ui.alert('Error', 'Invalid range entered. Please use format like A1:P100.', ui.ButtonSet.OK); } }