How to Auto Insert Timestamp in Google Sheet. Multi-purpose Script.

Hello from 2020! Good news: I’ve fully rebuilt the solution of timestamps in Google Sheets. My new script should do more and faster than one described in this post.

The current script works fine, but if you like to try the new one, please visit:

https://sheetswithmaxmakhrov.wordpress.com/2020/09/14/timestamps/

I was wondering how to make a timestamp in Google Sheets and also to make the script that is:

1. Easy to reuse
2. Easy to set
3. Easy to program for different purposes.

Here’s my try.

Photo by Ales Krivec on Unsplash

Sample Sheet

Background

Sample sheet shows 2 timestamps on the sheet called Sales:

Notes:

• Rule #1. When the data changes in columns B:C, or columns starting from F, the new timestamp appears in column A.
• Rule #2. When the data changes in column D, the new timestamp appears in column F.
• Basic rule: do not overwrite. When the timestamp is already set, the trigger won’t overwrite it.

——–>	BTW. I’ve recently found one non-standard usage of timestamps:
	Suppose, you have multiple workbooks for different users. All users have access to their file only. And what if you need to gether all the data in a single file. The timestamp is a great option to make a unique ID for each individual row of data. Of course, there’s a possibility that 2 or more users will enter the row in one second. But this possibility is unlikely, so the risk worth it.

My next thoughts were: how to deal with settings. Here’re 2 options:

1. All settings on a sheet. Good for a user, bad for a script. The script needs to store and then read these settings, and reading them back will cost execution time. I wanted the script to execute as fast as possible because it runs on each edit of a user, and I left this option.
2. All settings in a script. Good for a script, bad for a user. A user feels uncomfortable with understanding arrays, variables, and other script stuff. I declined this option, too.

I came up with a different solution.

Installation

You may want to make a copy of my file in order to be able to edit your own copy.

I decided to store all settings in a script, but help a user to get all changeable settings. I stored settings that users can change on a separate sheet.

Notes:

• Settings on Sheet. The sheet called onEdit_Ini stores all the settings needed for the first launch
• Columns are labeled by letter (A, B, C). Ranges are named the same as ranges on a sheet, using colon “A:C”. Ranges are joined with delimiter = semicolon ;
• Infinite ranges are allowed.  Range “F:” means start from column F and end at the end of a Sheet.

And then I’ve added a custom menu for a user to get the code.

Notes:

• Authorization needed! After the first launch, you need to pass the current authorization process of Google.
• Copy the code you get. The script gives you the code in a message box. Copy it!

• Open the editor. Then open Script Editor. Go to menu Tools > Script Editor…
• Paste the code. And paste it at the very beginning of a script (replace current code)
• Congratulations! You are already using automated timestamps.

The whole code is here:

	/*
	|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
	*/
	/*
	_____ _ _ _ __
	| __ \ | | | | | | \ \
	| |__) |_ _ ___| |_ ___ | |__| | ___ _ __ ___ \ \
	| ___/ _` / __| __/ _ \ | __ |/ _ \ '__/ _ \ \ \
	| | | (_| \__ \ || __/ | | | | __/ | | __/ \ \
	\__,_|___/\__\___| |_|\___|_| \___| \_\
	*/
	///////////// 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;
	}

view raw onEdit-TimeStamp-Lab.js hosted with ❤ by GitHub

Notes about the code:

• The new constant, not mentioned in a sheet is:  C_TRIGGER_TYPES = [1, 1];
• C_TRIGGER_TYPES is used to edit the logic of the script, all the logic is stored in a function: getTimeStamp_
• Please read my comments to the code to see more helpful options.
• Have fun =)

FAQ

How can I set the timestamp to be the latest whenever the data is changed?

The timestamp will update only one time by the code. When you update data, the timestamp will not update again. This is the default behavior.

If you want to update the timestamp every time, please do the following modifications:

Delete the condition ( replace only empty value ) in the function getTimeStamp_. To do this you’ll need

1. delete 2 lines of code (55, 56)
2. replace the code from these lines to return getReplaceValue_();

How to insert unique stamps

When user inserts multiple lines of date at once, the timestamp will be the same. You may create unique stamp this way. First, convert values into numbers. Sample function:

function getReplaceValue_()
{
  // milliseconds between January 1, 1970 and now
  return Number(new Date()); 
}

Second, create the counter and add +1 for each next value:

var counter_for_unique_values = 0;
function getReplaceValue_()
{
  // milliseconds between January 1, 1970 and now
  return Number(new Date()) + counter_for_unique_values++; 
  // Note: ++ adds 1 to the counter
}

Now you’ve got unique values. 1 millisecond shift is a bulletproof for ids when user inserts values up to 100 rows at once.

How to eliminate inserting stamps on empty values with filled values:

In this case you may need not to insert stamps in when the cell of inserted data is blank. Find function called runWithSet_ and replace it with this modified version:

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 = [];  
  // modification [ 1 ] for multiple rows
  if (height > 1)
  {
    var original_values = range.getValues().map(function(rowArray) { return rowArray.join(''); });
  }
  // end of modification [ 1 ]
  // 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];    
    // modification [ 2 ] for multiple rows
    if (height > 1)
    {
      // replace old val with dummy text to prevent new stamp
      if (original_values[i] === '') { oldVal = '[filled]'; }
    }
    // end of modification [ 2 ]    
    newVal = getTimeStamp_(type, oldVal); 
    // modification [ 3 ] for multiple rows
    if (newVal == '[filled]') { 
      // reset vals
      newVal = '';
      oldVal = ''; // to prevent running when all cells are empty
    } 
    // end of modification [ 3 ]
    
    if (newVal !== oldVal) { boolWriteValues = true; }
    newValues[i] = [];
    newValues[i][0] = newVal;
  }
  if (boolWriteValues) { rangeStamp.setValues(newValues); } 
}

In this case the stamp will not show if blank rows are inserted. Note: the code will still allow stamp when the only blank row pasted.