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);

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.


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.