1

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 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?

1
  • Welcome to Web Applications Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    May 21, 2021 at 1:50

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.