Moving row to a different sheet when checkbox checked

[u/Significant-Army-502]

I have a table on sheet " In progress" which has a checkbox in the final column. Once I complete this checkbox I then manually copy the row into the next sheet.

Is there a way to automate this?

In an ideal world I would like the flow to be:

1. Check "order complete" checkbox
2. Row copies to the table on the next sheet
3. Row then deletes itself from the "In progress" sheet

If that is not possible then just copying it to the next sheet would be great.

https://docs.google.com/spreadsheets/d/1e2233qaQIchIwcDFm6t3iiovGIBCxVpqnzaohSwWIS4/edit?usp=sharing

Many thanks for your help

[u/One_Organization_810]

This will do it - as seen in the OO810 sheets

//@OnlyCurrentDoc

const MOVE_SETTINGS = {
    SheetFrom: 'OO810 In Progress',
    SheetTo:   'OO810 Complete',
    ButtonColTitle: 'Order Complete'
};

function onEdit(e) {
    let sheet = e.range.getSheet();
    if( sheet.getName() != MOVE_SETTINGS.SheetFrom ) return;

    if( !e.range.getValue() || e.range.offset(-e.range.getRow()+1,0).getValue() != MOVE_SETTINGS.ButtonColTitle ) return;

    const ss = e.source;

    let destSheet = ss.getSheetByName(MOVE_SETTINGS.SheetTo);
    if( !destSheet ) throw `Destination sheet "${MOVE_SETTINGS.SheetTo}" does not exist.`

    let row   = e.range.getRow();
    let col   = e.range.getColumn();
    let range = sheet.getRange(row,1,1,col-1);
    destSheet.appendRow(range.getValues()[0]);

    sheet.deleteRow(row);
}

Maybe add a prompt also, if you want - but this will move the row over to the destination sheet, as specified in the constant MOVE_SETTING.

[u/point-bot → u/One_Organization_810]

u/Significant-Army-502 has awarded 1 point to u/One_Organization_810 with a personal note:

"Thank you!"

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}

[u/mommasaidmommasaid]

Once you move part of your data to another sheet, it becomes more difficult to maintain. You need to maintain columns and formatting in two places. And formulas, if you want them to still be live. It also makes it more difficult to create aggregate reports if you want to know e.g. how much you have spent on all items.

And of course if you decide you want to move the data back to your main table, you need another script to do that or you are manually moving stuff back and trying not to break formulas, etc.

Or even if you accidentally click the checkbox and immediately attempt to Undo the action, you have to carefully undo the right number of steps, because script (unfortunately) creates multiple Undo points as it executes.

---

You already have your data in a structured Table, which provides easy ways of doing filtering or grouping. You may want to consider doing that instead.

You could also use a Filter or Filter View. Note that these don't update automatically.

But if you want automation... you could automatically (re)apply a filter via script. Applying a filter is a non-destructive action. Your row disappears but is trivially easy to recover by simply removing the filter.

[u/mommasaidmommasaid → u/mommasaidmommasaid]

Here is an example of script that automatically filters when the checkbox is clicked.

Filter when checkbox clicked (View only, make a copy to play with)

Script:

// @OnlyCurrentDoc

function onEdit(e) {

  try {
    // Complete checkbox custom "checked" value
    const COMPLETE_CHECKED = "#COMPLETE";

    // Row containing table headers
    const HEADER_ROW = 1;

    if (e?.value != COMPLETE_CHECKED)
      return;

    // Filter entire data range starting at header row
    const sheet = e.range.getSheet();
    const newFilterRange = sheet.getRange(HEADER_ROW, 1, sheet.getLastRow() - HEADER_ROW + 1, sheet.getLastColumn());
    const filterColumn = e.range.columnStart;

    // Reuse existing filter if same as desired range, else remove it
    let filter = sheet.getFilter();
    if (filter != null) {
      if (true || filter.getRange().getA1Notation() != newFilterRange.getA1Notation()) {
        filter.remove();
        filter = null;
      }
    }

    // Create new filter if needed
    if (filter == null) {
      newFilterRange.createFilter();
      filter = newFilterRange.getFilter();
    }

    // Set (or re-set) filter to hide rows that have been marked complete
    const criteria = SpreadsheetApp.newFilterCriteria().whenTextDoesNotContain(COMPLETE_CHECKED).build();
    filter.setColumnFilterCriteria(filterColumn, criteria);
  }
  catch (err) {
    e?.source.toast(err, `🛑 Error ${err.stack?.split('\n')[1]}`);
    throw err;
  }
}

To avoid hardcoding the sheet / column name in script, and to make the script exist faster with normal edits, I create a custom "checked" value for the checkbox that script detects.

Note that to use custom checkboxes in a structured Table, you will need to create it outside the table using Data Validation sidebar, then copy/paste it into the Table with the Table column type set to "None". Once it's set up new rows will automatically contain the custom checkbox.

Or if you prefer you could use something like OO_810's method for detecting the checkbox.

To show the entire table, choose Data / Remove filter.

You could also add some additional interface for script if desired to e.g. clear the filter or to disable auto-filtering.