Google

Google Sheets | Moving a row if cell contains specific text

Moving data to another tab on the sheet your working on can be such a help with organisation. The way I use it is to move a complete row to a completed tab to avoid clutter. The below image shows my working sheet where jobs would come in:

Please note: This is only, for example, a business helpdesk system would deal with this sort of scenario.

Once the completed column has an ✓ in the cell, the row will be copied to a Completed tab and remove from this working tab:

This process is carried out through the use of a script within Google Sheets. To insert the script click:

  1. Tools
  2. Script Editor

It may ask you to allow permission to access your sheets within Google Drive. Once open copy the below script into the center pane:

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Overview" && r.getColumn() == 17 && r.getValue() == "✓") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Make sure to change the following to it matches the layout of your Sheet:

  • if(s.getName() == “Overview”     <— Change this to the name of your working tab
  • r.getColumn() == 17     <— Change this to the column number which the text will be specified
  • r.getValue() == “✓”) {     <— Change this to text that needs to be specified

Hit save and give the script a name. You do not need to run the script manually as it runs on edit and will fail within the Script Editor feature.

LEAVE A RESPONSE

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.