Google Spreadsheet Script to move row from one sheet to another and have it time stamp a specific cell in that row

Question:

( by Matthew Beninato )

Below is my current script. What I'm trying to add to this is a function that will add a time stamp to another cell within the same row once the cell is marked completed. Can you help please???

function onedit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var targetSheetName = "Completed";

  var sourceSheetName = "Eligibility & Pre-Auths"

  var targetSheet = ss.getSheetByName(targetSheetName);

  var sourceSheet = ss.getActiveSheet();

  var sourceRow = sourceSheet.getActiveRange().getRow();

  var targetRow = targetSheet.getLastRow() + 1;

  

  //var rows = SpreadsheetApp.getActiveSheet().getCurrentRow();

  var cell  = SpreadsheetApp.getActiveSheet().getRange(sourceRow, 26);  

  var value = cell.getValue().toString();

  

  if (sourceSheet.getName() == sourceSheetName) {

    if (value=='Yes') { 

      if (targetRow > targetSheet.getMaxRows()) targetSheet.insertRowAfter(targetRow - 1);

      sourceSheet.getRange(sourceRow, 1, 1, sourceSheet.getLastColumn()).copyTo(targetSheet.getRange(targetRow, 1));

      sourceSheet.deleteRow(sourceRow);

    };

  };

}

Solution:

Try the following code:

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var targetSheetName = "Completed";

  var sourceSheetName = "Eligibility & Pre-Auths"

  var targetSheet = ss.getSheetByName(targetSheetName);

  var sourceSheet = ss.getActiveSheet();

  var sourceRow = sourceSheet.getActiveRange().getRow();

  var targetRow = targetSheet.getLastRow() + 1;

  

  //var rows = SpreadsheetApp.getActiveSheet().getCurrentRow();

  var cell  = SpreadsheetApp.getActiveSheet().getRange(sourceRow, 26);  

  var value = cell.getValue().toString();

  

  if (sourceSheet.getName() == sourceSheetName) {

    if (value=='Yes') { 

      if (targetRow > targetSheet.getMaxRows()) targetSheet.insertRowAfter(targetRow - 1);

      sourceSheet.getRange(sourceRow, 1, 1, sourceSheet.getLastColumn()).copyTo(targetSheet.getRange(targetRow, 1));

      targetSheet.getRange(targetRow, sourceSheet.getLastColumn()+1).setValue(new Date());

      sourceSheet.deleteRow(sourceRow);

    };

  };

};

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive