google-sheetsgoogle-apps-scriptsetvalue

How can I write a Google Sheets script that will clear some cells while setting others to zero?


I have created an input sheet to collect data. I have written a script that saves the data to a data collection sheet and then another which resets my data entry sheet by clearing data from all the entry cells. This works perfectly but I have found it would be much easier if some of the cells could be set to zero for the users instead of cleared while others must be cleared.

Here is the current script I am running that works fine to clear all the desired fields:

  function clear1() {
    var sheet = SpreadsheetApp.getActive().getSheetByName('Data Entry');
    var rangesToClear = ["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 i=0; i<rangesToClear.length; i++) {
      sheet.getRange(rangesToClear[i]).clearContent();
}
}

I tried adding the following in but it didn't work correctly. It set a couple of cells to 0 but none the ones I wanted.

Note: I removed the cells below from the list above when I added this

var sheet = SpreadsheetApp.getActive().getSheetByName('Data Entry');
var rangesToSetToZero = ["C23", "C24", "C25", "C26", "C27", "C28", "C29", "C30"];
for (var i=0; i<rangesToSetToZero.length; i++) {
  sheet.getRange(rangesToClear[i]).setValue(0);

Appreciate any and all help!


Solution

  • Using getRange()

    The issue I have seen is that you are getting the range of the variable rangesToClear, instead of rangesToSetToZero to set the value of the cells to 0. There is also some inefficiency such that if your goal is to set the value of a range to 0, it is not necessary to include them in your method of deleting the values first. Rather, it would be best to directly setting the values to 0.

    Modified code:

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

    Sample output:

    enter image description here

    The ranges that I have set for this sample output are just dummy ranges to demonstrate how the code works. The first four ranges are set to be deleted, while the next four are set to change the value to 0.

    Reference: getRange()