onEdit-TimeStamp-Lab.js

/*
 |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*/


/*
  _____          _         _    _                __     
 |  __ \        | |       | |  | |               \ \    
 | |__) |_ _ ___| |_ ___  | |__| | ___ _ __ ___   \ \   
 |  ___/ _` / __| __/ _ \ |  __  |/ _ \ '__/ _ \   \ \  
 | |  | (_| \__ \ ||  __/ | |  | |  __/ | |  __/    \ \ 
 |_|   \__,_|___/\__\___| |_|  |_|\___|_|  \___|     \_\
                                                        
                                                                                                                                                                                
*/
///////////// Change ///////////////
C_SHEETS = ["Sales", "Sales"];
C_FIRST_ROWS = [3, 3];
C_STAMP_COLS = ["A", "E"];
C_CHANGE_COLS = ["B:C;F:", "D"];
C_TRIGGER_TYPES = [1, 1];
///////////// Change ///////////////

/*
                                                     __
                                                    / /
                                                   / / 
                                                  / /  
                                                 / /   
                                                /_/    
                                                    
*/


function onEdit(e)
{  
  makeTimeStamps_(e); // add more functions with onEdit if needed    
}



/* 
  in        
  triggerType   type stored in C_TRIGGER_TYPES

  returns:  
  value         leave original value
  timestamp     set timestamp
  
*/
function getTimeStamp_(triggerType, value) // add more valiables if needed
{
  switch (triggerType) {
  case 1:
     if (value === '') { return getReplaceValue_(); }
     else { return value; }
  case 2:
    // add some logic if needed
    return null;
  case 3:
    // add some logic if needed
    return null;
  }

}



// get timestamp OR any value if needed
function getReplaceValue_()
{
  return new Date(); // change if needed
}


/*
 |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*/



/*
   _____               
  / ____|              
 | |     ___  _ __ ___ 
 | |    / _ \| '__/ _ \
 | |___| (_) | | |  __/
  \_____\___/|_|  \___|
                                              

*/

function makeTimeStamps_(e)
{
  var range = e.range;
  var sheet = range.getSheet();
  var sheetName = sheet.getName();

  var indexSets = C_SHEETS.indexOf(sheetName);
  
  if (indexSets === -1) { return -1; } // wrong sheet
  
  // loop sets
  C_SHEETS.forEach(
    function (sheetNameTrigger, index)
    {
      if (sheetNameTrigger === sheetName) { return runWithSet_(index, range, sheet); }    
    }  
  );
}

function runWithSet_(index, range, sheet)
{
  var row = range.getRow();
  if (row < C_FIRST_ROWS[index]) { return -1; } // wrong row
  
  // get Column intersections
  var columnIntersactoins = getMatchRangeWithColumns_(sheet, range, C_CHANGE_COLS[index]);
  if (columnIntersactoins.length === 0) { return -2; } // wrong column
  
  // split range by rows
  var height = range.getHeight();
  var letter = C_STAMP_COLS[index];
  var rangeStamp = sheet.getRange(letter + row + ':' + letter + (row + height - 1))
  var oldValues = rangeStamp.getValues();
  var newValues = [];

  // make a decision
  var boolWriteValues = false;
  var oldVal = '';
  var newVal = '';
  var type = C_TRIGGER_TYPES[index]; 
  for (var i = 0; i < height; i++)
  {
    oldVal = oldValues[i][0];
    newVal = getTimeStamp_(type, oldVal);
    if (newVal !== oldVal) { boolWriteValues = true; }
    newValues[i] = [];
    newValues[i][0] = newVal;
  }

  if (boolWriteValues) { rangeStamp.setValues(newValues); } 

}

/*
  columnsCheck: "B:C;F:"
  range:        {range}
  
  return:        common column numbers
*/
function getMatchRangeWithColumns_(sheet, range, columnsCheck)
{
  var columnsInRange = getColumnsInRange_(range);
  var columnsInCheck = getColumnsInCheck_(columnsCheck, sheet);
  
  var result = [];
  
  var col = 0;
  for (var i = 0, l = columnsInRange.length; i < l; i++)
  {
    col = columnsInRange[i];
    if (columnsInCheck.indexOf(col) > -1) { result.push(col); }  
  }    
  return result;
}

/*
  range:        {range}
  
  return:       [2,3,4,5]
*/
function getColumnsInRange_(range)
{
  var col = range.getColumn();
  var width = range.getWidth();  
  var result = [];  
  for (var i = col; i < col + width; i++) { result.push('' + i); }  // convert to string for match
  return result;
}

/*
  check:        "B:C;F:"
  sheet:        {sheet}
  
  return:       [2,3,4,5]
*/
function getColumnsInCheck_(check, sheet)
{
  var list = check.split(';');
  
  var finalList = {}; // object to prevent duplicates
  
  var maxCols = sheet.getMaxColumns();

  list.forEach(
    function(elt)
    {
      // check if range ends with : => up to the end
      var elts = elt.split(':');     
      var start = sheet.getRange(elts[0] + "1").getColumn();     
      var end = 0;
      if (elt.substr(elt.length - 1) === ':') { end = maxCols; }
      else if (elts.length === 2) { end = sheet.getRange(elts[1] + "1").getColumn(); }
      else { end = start; }     
      for (var i = start; i <= end; i++) { finalList[i] = ''; }
    }  
  );
  return Object.keys(finalList);
}




/*

   _____      _       
  / ____|    | |      
 | (___   ___| |_ ___ 
  \___ \ / _ \ __/ __|
  ____) |  __/ |_\__ \
 |_____/ \___|\__|___/
                      
                      

*/

var C_ONEDIT_SHEET = 'onEdit_Ini';


// *************************************************** //
function onOpen()
{
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('TimeStamp')
      .addItem('Get the code!', 'logTheCode')
      .addToUi();

}



function logTheCode()
{
  Browser.msgBox(getTheCode_());
}

function getTheCode_() {
  var data = getDataFromSheet_(C_ONEDIT_SHEET);
  
  var sheets = [];
  var firstRows = [];
  var timeStampColumns = [];
  var changeColumns = [];
  var triggerTypes = [];
  
  data.forEach(
    function(elt)
    {
      sheets.push(elt[0]);
      firstRows.push(elt[1]);
      timeStampColumns.push(elt[2]);
      changeColumns.push(elt[3]);
      triggerTypes.push(1);
    }  
  );
  
  var result = '';
  
  result += 'C_SHEETS = ["' + sheets.join('", "') + '"];\\n'
  result += 'C_FIRST_ROWS = [' + firstRows.join(', ') + '];\\n'
  result += 'C_STAMP_COLS = ["' + timeStampColumns.join('", "') + '"];\\n'
  result += 'C_CHANGE_COLS = ["' + changeColumns.join('", "') + '"];\\n'
  result += 'C_TRIGGER_TYPES = [' + triggerTypes.join(', ') + '];'
  
  return result;
  
}

function getDataFromSheet_(sheetName)
{
  var file = SpreadsheetApp.getActive();
  var sheet = file.getSheetByName(sheetName); 
  var data = sheet.getDataRange().getValues();
  data.shift();  
  return data;
}