Google-sheets – Add a new row and move data to that row when box is checked

google sheetsgoogle-appsgoogle-apps-script

I currently have a Google spreadsheet that keeps track of all orders and the progress:

  • Column 1: Order Form Sent
  • Column 2: Order Form Received
  • Column 3: Delivered to Pick Up Site
  • Column 4: Picked Up

For those columns, I have entered checkboxes with data values of "true" or "false." On my sheet, there are two separate sections for complete and waiting.

Is there any way to create a new row when an column 3 is checked and move it to that new row?

EDIT: I've supplied the script that I've implemented that adds a new row under completed orders using the onEdit() trigger:

function onEdit(event) {

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

if(s.getName() == "Orders" && r.getColumn() == 17 && r.getValue() == true) {

var num_row = s.getLastRow(); 
var num_column = s.getLastColumn();  
var s_values = s.getSheetValues(1, 1, num_row-1, num_column);

for (var j = 0; j<num_row; j++){
  if(s_values[j][0] == 'WAITING'){
    s.insertRowAfter(j);
    break;
  }
}

So the issue I'm facing now is that when I add this script:

var currentrow = r.getRow();
var target = s.getRange(j+1, 1); 
s.getRange(currentrow,1,1,num_column).moveTo(target);
s.deleteRow(currentrow);

It no longer adds a row nor moves it into the newly created row. Can anyone help me with this?

Thanks!

Best Answer

One easy solution would be to keep all the data in the same sheet and use filter views to decide which rows to show at a time. This feature lets multiple simultaneous users sort, filter and edit the sheet without disturbing each other.

For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.

To answer your question, you can use an onEdit(e) simple trigger to run Apps Script code that moves rows around. See the moveRowsFromSpreadsheetToSpreadsheet_ script for one example.

Related Topic