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.

ales-krivec-2859_timeStamp
Photo by Ales Krivec on Unsplash

Sample Sheet

Background

Sample sheet shows 2 timestamps on the sheet called Sales:

time_stamp1

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.

time_stamp2

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.

time_stamp3

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!
time_stamp4
  • 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;
}

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:

[image with some blank rows inserted]

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.

45 thoughts on “How to Auto Insert Timestamp in Google Sheet. Multi-purpose Script.

  1. Pingback: How to insert Timestamp in Google Sheets - SheetsInfo

  2. Hi, sorry, I figured out my last question about the date format. What I’m struggling with, however, is when I delete the timestamp in the modified row and or the timestamp row, it puts a timestamp back in there. I would like it to leave the cell blank if I deleted the modified cell or the timestamp cell. Any suggestions appreciated. Thanks

    Liked by 1 person

    • Thank you Jody!

      This is how the current version of the script works. I’ve found some other drawbacks in my script too. So I decided to get back here later with a newer version. My first attempt was to create a general solution for easy to reuse. Now I see a lot of people are questioning me for more possibilities and I want to rewrite the code.

      I’ll also create another post about the update, so you could subscribe and see the update. Thank you!

      Like

    • Hi Jody!

      Thank you for the comment!
      I see you figured this out. In case someone else needs:

      // This formats the date as Greenwich Mean Time in the format
      // year-month-dateThour-minute-second.
      var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
      Logger.log(formattedDate);
      

      The code is from this ref: https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format

      There are also handy date functions in Javascript one could use:
      getFullYear() Get the year as a four digit number (yyyy)
      getMonth() Get the month as a number (0-11)
      getDate() Get the day as a number (1-31)
      getHours() Get the hour (0-23)
      getMinutes() Get the minute (0-59)
      getSeconds() Get the second (0-59)
      getMilliseconds() Get the millisecond (0-999)
      getTime() Get the time (milliseconds since January 1, 1970)
      getDay() Get the weekday as a number (0-6)
      Date.now() Get the time. ECMAScript 5.

      Found them here https://www.w3schools.com/js/js_date_methods.asp and use them constantly.

      Like

    • Hey. thank you for the comment!

      This could help:

      range.setNote('Last modified: ' + new Date());

      The line 142 is:

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

      The modified code will be like this:

      if (boolWriteValues) { 
         rangeStamp.setValues(newValues); 
        SpreadsheetApp
          .getActive()
          .getSheetByName('Sheet1').getRange(letter + row + ':' + letter + (row + height - 1))
          .setNote('Last modified: ' + new Date());
      }
      

      Change “Sheet1” to your sheet’s name, letter to your column latter: ‘A’, ‘B’, ‘C’

      Please let me know if it doesn’t work.

      Like

    • Hi Alex!

      Thank you for the comment!

      You may insert timestamps in several places at a time.

      Modification points:

      Edit line 142 of the original code.
      Add more ranges according to your needs to insert timestamps.

      The line 142 is:

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

      The modified code will be like this:

      if (boolWriteValues) { 
         rangeStamp.setValues(newValues); 
        SpreadsheetApp
          .getActive()
          .getSheetByName('Sheet2').getRange(letter + row + ':' + letter + (row + height - 1))
          .setValues(newValues);
      }
      

      Change “Sheet1” to your sheet’s name.

      Please let me know if it doesn’t work.

      Like

  3. Your code is going to make life much easier at work … Once I can get past line 95. Whenever I run the script, I receive “TypeError: Cannot read property “range” from undefined. (line 95, file “Code”)”
    This is my first experience with code so I don’t know where to start. Thank you!

    Like

  4. Thanks for the code, really helpful!
    I use for my sheet, but it seems no use when I update the data in C_CHANGE_COLS (already have data in it, and I change it).
    How can I change the code? Thanks!

    Like

    • Hi chunwei.chang!
      Thank you for the question.

      If you want to add more rules, you’ll need to modify all constants:

      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];

      Modified version:

      C_SHEETS = [“Sales”, “Sales”, “Sales”];
      C_FIRST_ROWS = [3, 3, 3];
      C_STAMP_COLS = [“A”, “E”, “E”];
      C_CHANGE_COLS = [“B:C;F”, “D”, “Z:”];
      C_TRIGGER_TYPES = [1, 1, 1];

      Liked by 1 person

      • Thank you for the Answer!
        I think I should clarify my question.
        I mean : It seems the timestamp will update only one time by the code, isn’t it?
        When I update data, the timestamp will not update again.
        If I want to update the timestamp every time, how should I modify it ?
        Thanks a lot again!

        Liked by 1 person

    • I’ve got the question now. I’m sorry for the delay, the answer is:

      You’ll need to delete the condition ( replace only empty value ) in the function getTimeStamp_. To do this you’ll need to replace 2 lines of code (55, 56) with this:
      return getReplaceValue_();

      Liked by 1 person

  5. This is amazing, really useful! Question about a funky workaround though. Is there a way to timestamp a second edit? Where I have this placed, inserted rows also copy cells’ data validations and that’s counting as the timestamp but I want to capture when cells are actually edited, not the data validation copy. Is that possible?

    Are there other trigger types other than 1?

    Like

    • I think the better question is there a way to ignore data validations and only record when actual content is typed in the cell? Thanks, this is an incredible piece of code that is super, super helpful! (if only I can get it to work for me!)

      Like

    • Iris, nice question!

      My thoughts: onEdit triggers both changes from user side: values and formatting. The only thing one can do is to capture these changes with a script and take some action.

      Conditional formatting may change by some different rules and those rules may depend on other sheets or calculations. onEdit will run only when the user changes the sheet, not the formula.

      Like

  6. @maximmakhrov

    Hi again!

    1) The script for the timestamp is the only script within the spreadsheet.
    2) Nothing obvious other than timestamps not changing despite several edits.

    Here is a link to a copy of your script (slightly modified). Note: I modified line 55,56 per your reply to replace date with later edits & line 115 to limit the range of rows affected.

    https://docs.google.com/spreadsheets/d/1dw6g0ORiiTmSs3hCyf-Qn3MNYvzj4KIdlnR7NOMW87w/edit?usp=sharing

    I made sure it worked in the link above before replacing the script in my other spreadsheet. The script is currently not working for me in either spreadsheet.

    Like

    • The correct code is for lines 115-116:
      var ends = C_FIRST_ROWS[index].split(‘:’);
      if (row > parseInt(ends[1]) || row < parseInt(ends[0])) { rerutn -1; } // wrong row

      For line 18:
      C_FIRST_ROWS = ['4:26', '4:26'];

      Like

      • Works like a charm. Thank you for your help and your quick response!

        On Thu, Feb 28, 2019 at 9:21 AM Sheets with Max Makhrov wrote:

        > maximmakhrov commented: “The correct code is for lines 115-116: var ends = > C_FIRST_ROWS[index].split(‘:’); if (row > parseInt(ends[1]) || row parseInt(ends[0])) { rerutn -1; } // wrong row For line 18: C_FIRST_ROWS = > [‘4:26’, ‘4:26’];” >

        Like

  7. @maximmakhrov

    Excellent write-up! This worked perfectly for me.

    One question: Is there any way to limit the script to a Range as far as Rows are concerned? I have mine set to begin at Row 4, and I would like for it to end at Row 26 if possible. I tried editing line 18 as such “C_FIRST_ROWS = [4:26, 4:26];”. As I’m sure you already know it did not work!

    Thanks!

    Like

  8. Hi Nathan, thanks for the code.

    i have 2 questions,
    1. how can i set the timestamp to be the latest whenever the data is changed?
    2. how can I set only the first column to register the timestamp whenever other data in the row is changed.

    hope you can help ! thanks

    Ang

    Like

    • wk ang,

      1. You’ll need to delete the condition ( replace only empty value ) in the function getTimeStamp_. To do this you’ll need to replace 2 lines of code (55, 56) with this:
      return getReplaceValue_();

      2. In the sheet onEdit_ini set Column with a timestamp to A and Columns Change… to B:

      Like

  9. Thank you so much for this! Even though I don’t know how to write code, your script was documented so well that I managed to tweak it a bit to fit my needs even better.

    Liked by 1 person

  10. I have a question- I copied your sheet and I’m psyched except that the time stamp is 7 hours ahead. I assume it has something to do with the code and the time zone. I am in EST- what portion of the code should I adjust?

    Like

    • Hi Kristi,

      You may change the code from line 72, inside of function getReplaceValue_.

      Change it to:

      return Utilities.formatDate(new Date(), “EST”, “yyyy-MM-dd HH:mm:ss”);

      Thanks for the comment! Please ask again if it not works.

      Like

      • First of all, thank you! Creating the spreadsheet with the easy edits was SO HELPFUL! I’m having the same issue as Kristi and when I try to use your code above, it says there is an illegal character in the field – any ideas? It’s likely something very obvious to fix, but I am a total novice! Thanks again for this helpful post!

        Like

  11. Thanks for the code, really helpful! I was hoping to get it to work by inserting more sheet titles in C_Sheets or is there another way to do so?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s