Automatically move data from one sheet to another in google docs

Question

i have a spreadsheet that i keep track of tasks i need to do, once complete i enter a date in the last column. What i want is for that completed task to be moved to sheet 2.

At present i have sheet 1 named SUD_schedule and i want the completed row of data to be moved to sheet 2 named SUD_archive. I've looked through the forum posts already and i've tried a variation of scripts but so far no luck. The closest i have come is this script:

function onEdit() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();//Original sheet
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];//target sheet
   // to act on only one sheet, check the sheet name here:
  //If it si not first sheet, it will do nothing
  if (sheet1.getSheetName() != "SUD_schedule") {
    return; 
  }
  //Get Row and column index of active cell.
  var rowIndex = sheet1.getActiveRange().getRowIndex();
  var colIndex =  sheet1.getActiveRange().getColumnIndex();

  //If the selected column is 10th and it is not a header row
  if (colIndex == 16 && rowIndex > 1) {
    //Get the data from the current row
   var data = sheet1.getRange(rowIndex,1,1,9).getValues();
   var lastRow2; 
    (sheet2.getLastRow()==0)?lastRow2=1:lastRow2=sheet2.getLastRow()+1;
    //Copy the data to the lastRow+1th row in target sheet
    sheet2.getRange(lastRow2,1,1,data[0].length).setValues(data);
  }
}

Column P (16) is the task complete date, row 1 is frozen and contains column headers.

Can anybody help show where i'm going wrong.

Kind regards Den

Answer 1

Your code is not generic and you are more complicating your objective. Below will work out your need.

    function onEdit(){

     var ss = SpreadsheetApp.getActiveSpreadsheet();

     var sheet1 = ss.getSheetByName('SUD_schedule');
     var sheet2 = ss.getSheetByName('SUD_archive');

     var dateColumn = "16";
     var array = []

     var range = sheet1.getRange(1, 1, sheet1.getLastRow(), dateColumn); 

      for (var i = 2; i <= sheet1.getLastRow(); i++) //i iterates from 2 as you say R1 is header
      {
        if(isValidDate(range.getCell(i, dateColumn).getValue()) == true) //checking if any values on column16 is valid date
        {
          data = sheet1.getRange(i, 1, 1, dateColumn).getValues(); //Getting the range values of particular row where C16 is date

          for (var j = 0; j < dateColumn; j++) //Adding the row in array
          {
          array.push(data[0][j]);
          }

        }
        if(array.length > 0)
        {
        sheet2.appendRow(array); //Appending the row in sheet2
        array = [];
        sheet1.deleteRow(i); //deleting the row in sheet as you said you want to move, if you copy remove this and next line
        i=i-1; //managing i value after deleting a row.
        }

      }


    }

//Below function return true if the given String is date, else false

    function isValidDate(d) {
      if ( Object.prototype.toString.call(d) !== "[object Date]" )
        return false;
      return !isNaN(d.getTime());
    }

Answer 2

I am not sure that the syntax you have as used below is entirely correct.

(sheet2.getLastRow()==0)?lastRow2=1:lastRow2=sheet2.getLastRow()+1;
sheet2.getRange(lastRow2,1,1,data[0].length).setValues(data);

What I know will work for certain is if you omit the variable lastRow2 all together and use this instead.

sheet2.getRange(getLastRow+1,1,1,data[0].length).setValues(data);

Answer 3

To complement Joachin's answer, here is how you can adapt that code if you don't have the date in the last row. In the below shown part of the code replace Lastcolumnumber with your last column.

//Getting the range values of particular row where C16 is date
data = sheet1.getRange(i, 1, 1, LASTCOLUMNNUMBER).getValues(); 

//Adding the row in array
for (var j = 0; j < LASTCOLUMNNUMBER; j++)