What is the process to create a Google Sheets script that clears certain cells while setting others to a value of zero?

Other Questions Google Sheets
1

I have developed a data input sheet for gathering information. I also have a script that transfers the data to a collection sheet and another that resets the input sheet by removing all entries. This method works well; however, it would be more efficient if specific cells were set to zero instead of being cleared completely while others are left blank.

Currently, I have the following script that successfully clears the required fields:

function clearData() {
    var inputSheet = SpreadsheetApp.getActive().getSheetByName('Data Input');
    var cellsToClear = ["C12", "C13", "C15", "C16", "C17", "C23", "C24", "C25", "C26", "C27", "C28", "C29", "C30", "H15", "H16", "H27", "H28", "H29", "H30", "C35", "C36", "C37", "H35", "H36", "H37", "C40", "C41", "H40", "H41", "C44", "H44", "H45"];
    for (var j = 0; j < cellsToClear.length; j++) {
        inputSheet.getRange(cellsToClear[j]).clearContent();
    }
}

I attempted to incorporate the following code to set certain cells to zero, but it didn’t function as expected. Only a few cells received a zero value, while the intended cells were not affected:

var inputSheet = SpreadsheetApp.getActive().getSheetByName('Data Input');
var cellsToZero = ["C23", "C24", "C25", "C26", "C27", "C28", "C29", "C30"];
for (var j = 0; j < cellsToZero.length; j++) {
    inputSheet.getRange(cellsToClear[j]).setValue(0);
}

I’d greatly appreciate any assistance with this issue!

2

It looks like you’re using the “cellsToClear” array instead of “cellsToZero” for setting value, which is likely causing the issue. You should iterate over “cellsToZero” when assigning zeros to the cells. Here’s a corrected version of your script with this change:

var inputSheet = SpreadsheetApp.getActive().getSheetByName('Data Input');
var cellsToZero = ["C23", "C24", "C25", "C26", "C27", "C28", "C29", "C30"];
for (var j = 0; j < cellsToZero.length; j++) {
    inputSheet.getRange(cellsToZero[j]).setValue(0);
}

Make sure that the ranges in “cellsToZero” correspond to those you indeed want to reset to zero.

3

When you’re working with Apps Script and manipulating ranges or setting cell values, it’s important to double-check the exact cells you want to update. If you are using separate actions for clearing some fields and setting others to zero, consider wrapping these actions in functions for better maintainability. Also, if you find performance issues or if this script grows, consider batch operations such as setting multiple cell values simultaneously using range arrays, which can be more efficient than looping through each cell individually.