Google Sheets: delete rows containing specified data

Question

I'm new to Java scripting and Google Apps Scripts so i am sorry if this has already been answered. I was not able to find what i was looking for over the last few months of working on this project.

I am working on a variant of the scripts here:
Delete row in Google Sheets if certain "word" is found in cell
AND
Google Sheet Script - Find Value in Col and Delete Row

I want to create a button, or menu, that will allow someone to enter specific data, and have each row in the spreadsheet containing that data deleted.

I have a test sheet here that illustrates the data i'm working with, formulas i'm using, and has the beginning of the script attached to it: https://docs.google.com/spreadsheets/d/1e2ILQYf8MJD3mrmUeFQyET6lOLYEb-4coDTd52QBWtU/edit?usp=sharing

The first 4 sheets are pulling data from the "Form Responses 1" sheet via a formula in cell A:3 in each sheet so the data would only need to be deleted from the "Form Responses 1" sheet to clear it from the rest of the sheets.

I tried working this in but i do not think i am on the right track. https://developers.google.com/apps-script/guides/dialogs

I also posted this on Google Docs Help Forum 60 days ago, but have not received any responses. Any help would be greatly appreciated.

do you want to target a single column for the data search or all columns ? — Karan, Jun 20 '17 at 08:02
i would need to target all columns as the data to be deleted could be in several different columns. — Jason Meier, Jun 21 '17 at 22:59

Nathan Brown · Accepted Answer · 2017-06-20T10:21:11.857

There's a few steps. For usability of UI this takes a little longer code. In concise form:

The user activates a dialog and enters a string.
Rows w/ the string are deleted (with error handling and confirmation)

(Hopefully this gets you started and you can tailor it to your needs)

Function that initiates the menu:

function onOpen(){
  SpreadsheetApp.getUi() 
  .createMenu('My Menu')
  .addItem('Delete Data', 'deleteFunction')
  .addToUi();
}

The main workhorse:

function deleteFunction(){
  //declarations
  var sheetName = "Form Responses 1"; 
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(sheetName);

  var dataRange = sheet.getDataRange();
  var numRows = dataRange.getNumRows();
  var values = dataRange.getValues();

  var delete_string = getUIstring();//open initial UI, save value
  if (delete_string.length < 3) return shortStringError()//UI to protect your document from an accidental entry of a very short string. 

  //removing the rows (start with i=2, so don't delete header row.) 
  var rowsDeleted = 0;
  for (var i = 2; i <= numRows; i++){
    var rowValues = values[i-1].toString();//your sheet has various data types, script can be improved here to allow deleting dates, ect. 

    if (rowValues.indexOf(delete_string) > -1){
      sheet.deleteRow(i - rowsDeleted);//keeps loop and sheet in sync
      rowsDeleted++; 
    }

  }
  postUIconfirm(rowsDeleted);//Open confirmation UI   
}

Isolated UI functions to help make above function more concise:

function getUIstring(){
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt("Enter the target data element for deletion")
  return response.getResponseText()
}

function postUIconfirm(rowsDeleted){
  var ui = SpreadsheetApp.getUi();
  ui.alert("Operation complete. There were "+rowsDeleted+" rows deleted.")  
}

function shortStringError(){
  var ui = SpreadsheetApp.getUi();
  ui.alert("The string is too short. Enter a longer string to prevent unexpected deletion")  
}

Thank you so much! I was not expecting someone to write the whole thing. This is absolutely perfect. I ran it through a few tests and it operates perfectly. It looks like it even addresses the issue i was worried about with deleting the data and leaving a blank row behind (this would have caused the spreadsheet to grow too large for the formulas to handle as they are currently written); the entire row is deleted. I really appreciate this. You saved me countless hours of work. And you broke it down in a way i can follow, so i am learning more than i ever expected to from this. — Jason Meier, Jun 22 '17 at 00:10
I figured it would help you get started. I think that GAS is really easy once you know the right methods for interacting with the spreadsheet, calling UI interfaces ect. It has been helpful for me along the way to see a few specific examples how a multistep dea can be implemented, rather than a struggle to find the way when you're just beginning. — Nathan Brown, Jun 22 '17 at 11:23

score 0 · Answer 2 · edited Jan 19 '22 at 06:15

I'll just show a way to delete the cell value if it matches your search criteria. It's up to you to connect it to buttons ,etc.

You'll loop through a Sheet Range. When you find the word match, delete it using clearContent()

function deleteSpecificData() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheets()[0];
     var range = sheet.getRange("Sheet1!A1:C4");
     var values = range.getValues();
     var numArray = [1,2,3,4,5,6,7,8,9];
  
     var deleteItem = "Garen";

     Logger.log(range);
    
      for(var i=0; i< values.length; i++){
        for(var j=0; j<values[i].length; j++){
          if(values[i][j] == deleteItem){
               var row = numArray[i];
               var col = numArray[j];
               var range = sheet.getRange(row,col).clearContent();

          }
        }
      }
}

Before:

After:

Google Sheets: delete rows containing specified data

2 Answers2

Linked