Need help with Google Sheets script: Clear some cells, set others to zero

Other Questions Google Sheets
1

Hey everyone! I’m working on a Google Sheets project and I’m stuck. I’ve got a data entry sheet and I want to reset it after saving the data. Right now, my script clears all the cells, but I’d like to change it so some cells are cleared while others are set to zero. Here’s what I’ve tried:

function resetSheet() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Input');
  var clearCells = ['A1', 'B2', 'C3', 'D4'];
  var zeroCells = ['E5', 'F6', 'G7', 'H8'];

  clearCells.forEach(cell => sheet.getRange(cell).clearContent());
  zeroCells.forEach(cell => sheet.getRange(cell).setValue(0));
}

This almost works, but it’s not setting the right cells to zero. Any ideas on how to fix this? I’m pretty new to Google Sheets scripts, so any help would be awesome. Thanks!

2

I’ve dealt with similar issues in my Google Sheets projects. Your approach is on the right track, but here’s a suggestion to make it more flexible:

Instead of hardcoding cell references, consider using ranges. This way, you can easily adjust the areas you want to clear or set to zero. For example:

function resetSheet() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Input');
  sheet.getRange('A1:D10').clearContent();  // Clear a larger area
  sheet.getRange('E1:H10').setValue(0);     // Set a range to zero
}

This method is more efficient and easier to maintain. You can adjust the ranges as needed without modifying the core logic. Just ensure you’re not overwriting any important data outside your input area.

3

hey there! your script looks pretty good actually. the issue might be with the cell ranges you’re using. double-check that ‘E5’, ‘F6’, etc. are the exact cells you want to set to zero. if they’re not, just update those in the zeroCells array. also, make sure you’re running the function after saving data. hope this helps!