CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-macros
Raymond Douwes
Raymond Douwes

Reputation: 11

Delete part of row if checkbox is checked

I am trying to make a tasklist in Google Sheets where I can check a checkbox (value true/untrue). At the end of the day I want to delete all the tasks in the tasklist that are checkt (marked as true). I know how to delete a whole row if a checkbox is checkt and my code works. But in this case I only need to delete the information from the checkbox from the column D till K. I don't want that any other information from any other column on the same row is beeing deleted.

Like I said, i have a working code. In the example sheet I have a example where I want to delete project Mario. The box is checked and now I want to delete the information from column D till column K.

https://docs.google.com/spreadsheets/d/1EYxtzro_vk-gK1iReyjKQ-WwM5G9Jiu-h-55zuHMWXg/edit?usp=sharing

function deleteCells() { 
var ss = SpreadsheetApp.getActiveSpreadsheet();  
var s = ss.getSheetByName('Blad1');
var r = s.getRange('K:K');    
var v = r.getValues();  
for(var i=v.length-1;i>=0;i--) 
if(v[0,i]=='true')   
s.deleteRow(i+1);
};

Upvotes: 0

Views: 2042

Answers (1)

Tedinoz
Tedinoz

Reputation: 7959

Instead of deleting the row, use range.clear(options) to delete the content and the formatting.
I created var datasetrange = s.getRange(i+1,4,1,7); to identify the range. Note that this is the range from Coluymn D to Column J

  • datasetrange.clear({contentsOnly: true}); - to delete the content
  • datasetrange.clear({formatOnly: true}); - to delete the formatting

Then as a separate exercise:

  • s.getRange(i+1,11).setValue(false); - this is the range for cell "K6", and it changes the value of the checkbox from "true" to "false" (which assumes, BTW, that you used the default values for the checkbox).
 function so5763377101() { 
      var ss = SpreadsheetApp.getActiveSpreadsheet();  
      var s = ss.getSheetByName('Blad1');
      var r = s.getRange('K:K');    
      var v = r.getValues();    
      var dataset = [];
      for(var i=v.length-1;i>=0;i--) 
      if(v[0,i]=='true') {  
        // Logger.log("DEBUG: i:"+i+", checkbox = "+v[0,i]);
        var datasetrange = s.getRange(i+1,4,1,7);
        // Logger.log(datasetrange.getA1Notation());
        datasetrange.clear({contentsOnly: true});
        datasetrange.clear({formatOnly: true});
        s.getRange(i+1,11).setValue(false);    
      }
    }

Upvotes: 2

Related Questions