Thursday, August 29, 2013

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:
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,

1 comment:

  1. Thanks Kishan. It worked perfectly. I will be in touch.

    Thanks again,
    Matthew Beninato

    ReplyDelete