How to Delete Row Based on Cell Value in Google Apps Script

How to Delete Row Based on Cell Value in Google Apps Script? Are you still figuring out the solution to this question? then you are in the right place. Deleting unnecessary rows might be a time-consuming activity, but with the help of a Google Apps script, you can do this task in seconds automatically without manual intervention.

Features

We will add the custom menu button to run script
Script will display the count of no. of rows removed and types cell value removed

Why delete rows based on cell value

There can be multiple reasons for deleting a row based on a cell-specific value, such as if row content in column C has a value other than “By Road,” the script will automatically be removed.

Below are examples.

Cleaning Data: You might want to remove the unwanted row or empty rows.
Filtering Data: Managing the only required data to display.
Managing Lists: You might want to remove the out-of-stock inventory data from Google Sheets.

How to delete rows based on cell value in google apps script?

Let start the writing script to automatically remove the unwanted rows.

Follow the instructions given below :-

Step 1. Open Google Sheets: Open that google sheet in which you want to add a function for automatic unwanted rows deletion.

how to automatically send emails from google sheets — How to open Google sheet

Step 2. Open Apps Script Editor : Navigate the menu bar click on Extensions > Apps Script. Once you clicked the the apps script code editor will open in new tab

how do i automatically send email from google sheets — How to open the apps script editor in google sheet

Step 3. Add a new script file: Click on + sign, new code space will added name it like delete_extra_data

Step 4: Adding Script

Just copy the below code using the copy button, paste it into the app script code, and save it.

Important! : After adding the code, define the column no. in which the values are other than “By Road.” In this sample code, the values are in column C, or Column No. 2, as given in the below code snapshot.

The code script will start validating the values from the bottom and check the C column. If the C column values are not matching “By Road,” then the script will remove those rows.

 const dataRange = sheet.getRange(3, 3, lastRow - 2, 1); // Get only column C from the third row

				
					//How to Delete Row Based on Cell Value in Google Apps Script
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Data Cleaner')
    .addItem('Data clean', 'delete_extra_data')
    .addToUi();
}

function delete_extra_data() {
  const sheetName = 'data';
  const validValues = ['By Road'];

  // Get the sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    SpreadsheetApp.getUi().alert(`Sheet "${sheetName}" not found!`);
    return;
  }

  const lastRow = sheet.getLastRow();
  if (lastRow < 3) { // Ensure there are rows to process
    SpreadsheetApp.getUi().alert("No data found in the sheet beyond row 2.");
    return;
  }

  const dataRange = sheet.getRange(3, 3, lastRow - 2, 1); // Get only column C from the third row
  const data = dataRange.getValues();

  // Initialize category counters
  let categoryCount = {};
  for (let value of validValues) {
    categoryCount[value] = 0;
  }

  // Collect rows to delete
  let rowsToDelete = [];
  for (let i = data.length - 1; i >= 0; i--) { // Start from the bottom
    if (!validValues.includes(data[i][0])) { // Column C is index 0 in the data range
      rowsToDelete.push(i + 3); // Rows in Google Sheets are 1-indexed and we start from the third row
      let category = data[i][0];
      if (categoryCount[category] === undefined) {
        categoryCount[category] = 1;
      } else {
        categoryCount[category]++;
      }
    }
  }

  // Delete rows in reverse order
  for (let i = 0; i < rowsToDelete.length; i++) {
    sheet.deleteRow(rowsToDelete[i]);
  }

  // Display deleted row counts
  let ui = SpreadsheetApp.getUi();
  let message = 'Deleted rows:\n';
  for (let category in categoryCount) {
    if (categoryCount[category] > 0) {
      message += `${category}: ${categoryCount[category]} rows\n`;
    }
  }
  ui.alert(message);
}

Let’s debug the script and understand.

Each part of the script has different functionalities and how they work.

1. Adding a custom button for data cleaning

				
					function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Data Cleaner')
    .addItem('Data clean', 'delete_extra_data')
    .addToUi();
}

onOpen Function: The onOpen is the app-script inbuilt function that automatically starts when Google Sheets gets open. In this app-script, it automatically runs and displays the custom menu “Data Cleaner” in the Google Sheets UI.
createMenu: This method is used to create a custom menu to be added to the Google Sheets UI.
addItem: dd item method helps to create a menu item; in this case, the menu name is “Data Clean,” and this button runs the delete_extra_data function when clicked.

2. Delete rows functions

				
					function delete_extra_data() {
  const sheetName = 'data';
  const validValues = ['By Road'];

  // Get the sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    SpreadsheetApp.getUi().alert(`Sheet "${sheetName}" not found!`);
    return;
  }

  const lastRow = sheet.getLastRow();
  if (lastRow < 3) { // Ensure there are rows to process
    SpreadsheetApp.getUi().alert("No data found in the sheet beyond row 2.");
    return;
  }

  const dataRange = sheet.getRange(3, 3, lastRow - 2, 1); // Get only column C from the third row
  const data = dataRange.getValues();

sheetName and valid Values: In this method, the variable holds the name of the sheet and a list of defined values.
getSheetByName: This method gets the sheet by name.
getLastRow: This method gets the bottom rows and starts validating.
getRange: This method validates and checks the value in the column C.
getValues: This method retrieves the values specified in the array.

3. Deleting Rows Based on Cell Values

				
					  // Initialize category counters
  let categoryCount = {};
  for (let value of validValues) {
    categoryCount[value] = 0;
  }

  // Collect rows to delete
  let rowsToDelete = [];
  for (let i = data.length - 1; i >= 0; i--) { // Start from the bottom
    if (!validValues.includes(data[i][0])) { // Column C is index 0 in the data range
      rowsToDelete.push(i + 3); // Rows in Google Sheets are 1-indexed and we start from the third row
      let category = data[i][0];
      if (categoryCount[category] === undefined) {
        categoryCount[category] = 1;
      } else {
        categoryCount[category]++;
      }
    }
  }

  // Delete rows in reverse order
  for (let i = 0; i < rowsToDelete.length; i++) {
    sheet.deleteRow(rowsToDelete[i]);
  }

Category Counters: This part of the script starts validating the counting of categories whose values are deleted.
Collecting Rows to Delete: This part of the script starts iterating rows from bottom to top; if the values are not matched, delete the array of this data.
Deleting Rows: In this part of the script, start deleting rows in reverse order.

4. Displaying the deleted data categories

				
					  // Display deleted row counts
  let ui = SpreadsheetApp.getUi();
  let message = 'Deleted rows:\n';
  for (let category in categoryCount) {
    if (categoryCount[category] > 0) {
      message += `${category}: ${categoryCount[category]} rows\n`;
    }
  }
  ui.alert(message);
}

Displaying Results: In this code snippet, display a structured message and show the number of rows and categories to delete.

Let’s run the script.

To run the script:

Save the Script: Once the script is done, click on the disk icon or go to File > Save it.
Run the script: To test a script, click on the triangle icon or go to Run > onOpen.
This will add the custom button to the Google Sheets UI.
Custom Menu: Inside the data cleaner button, Data Clean will display. Click on it.

Scheduled script to auto trigger.

Follow the below steps to auto-trigger a script:

Go to Triggers: open the script editor in the Apps script and click the clock icon, or click Edit > Current project's triggers.
Add Trigger: Click on + Add Trigger.
Set trigger settings: Select the function name from the functions list; in this case, the delete_extra_data function.
Select the deployment (head), choose the event source (time-driven), and set the type of time-based trigger (e.g., daily, hourly).

Learn how to trigger function