Google script to select rows in Google sheets and clear contents

Question

I want to try and clear rows ready to delete that contain a specific word within google sheets, the problem i have is the script i have gone with delete lines 1 by 1 which can take a long time if you have a lot of row to search through, where if i could just highlight all those rows and clear the data if the row cantains the specific word then that would be a lot quicker as i could sort the data afterwards

function deleteRows() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var s = ss.getSheetByName('Copy of TBC Sort');
 var r = s.getRange('C3:C400');
var v = r.getValues();
for(var i=v.length-1;i>=0;i--)
 if(v[0,i]=='Delete Row')
  s.deleteRow(i+1);
 };

It is difficult to understand what your problem is. Please try to structure your question and, please, use dots in your text. — pascalpuetz, Jul 17 '19 at 20:51
Is this thread useful for your situation? https://stackoverflow.com/q/54643065/7108653 — Tanaike, Jul 17 '19 at 23:38

score 2 · Answer 1 · answered Jul 17 '19 at 19:58

2

You can use clearContent() instead. For more details check the Apps Script website here.

The example shown is

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var range = sheet.getRange("A1:D10");
range.clearContent();

answered Jul 17 '19 at 19:58

Mario R.

659
5
11

OK but how would i select all rows that contain the specfic text so i can clear them? – Sam Gidman Jul 17 '19 at 20:20

ross · Answer 2 · 2019-07-18T07:50:17.373

Goal:

Delete rows if data contains "Delete Row".

Script:

function findAndDeleteRow() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of TBC Sort');
  var r = s.getRange('C3:C400');
  var find = r.createTextFinder('Delete Row').findAll().reverse();
  if (find.length > 0) {
    for (i = 0; i < find.length; i++) {
      s.deleteRow(find[i].getRow());
    }
  }
}

I've used createTextFinder() to find every row that contains your string "Delete Row" (this is faster than looping through an array of values to compare each and every one). This pushes all of the ranges to an array in var find. Then we use reverse() to reverse the array so we're deleting rows from the bottom up. I've included an if statement just to check that there are items in the array to process, then we have a for loop to loop through each of the items in the array, passing the row to deleteRow() as we go.

Note: make sure your var r is a valid range in the spreadsheet or the createTextFinder will hang.

References:

score 1 · Answer 3 · answered Jul 17 '19 at 21:03

Building on what mario said, the fastest thing is to copy the data, clear the range, and paste back the data you want

function deleteRows() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var s = ss.getSheetByName('Copy of TBC Sort');
var data = s.getDataRange().getValues();
var results = [];
for(var i=v.length-1;i>=0;i--)
 if(data[0,i]=='Delete Row') continue;//skips these rows
 result.push(data[i]);
 };
s.getDataRange().clear();
s.getRange(1, 1,results.length, results[0].length).setValues(results);
}

Delete row just takes too long

Google script to select rows in Google sheets and clear contents

3 Answers3

Goal:

Script:

References: