0

I am trying to enter the date and time when a certain query gets resolved. I am using the below script for entering the create-date timestamp in column T, however I would also like to add another script which will enter the current date timestamp if the value in column O changes to 'Resolved' or 'Force Resolved'. This is so that I can measure how many hours it took for the query t be resolved since it was created. Thanks in advance, im pretty new to to scripts..

Screengrab

The current script:

/**
* Creates a Date Stamp if a column is edited.
*/

//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 1;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,19];
// Sheet you are working on
var SHEETNAME = 'New CRM'

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //checks that we're on the correct sheet.
  if( sheet.getSheetName() == SHEETNAME ) { 
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) { 
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      }
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.