Code to track the changes made to a specific cell in Google Sheets

Question

Code to track the changes made to a specific cell in Google Sheets

I am currently collaborating on a file with multiple individuals and I would like to create a script that can identify the person who last modified a specific cell, along with the timestamp of when the modification occurred. All I need is the email address of the user who made the most recent change and the corresponding time stamp example here,

So far, I have managed to retrieve the email address of the user but I am unable to capture the timestamp.

function history_version() { 
  var thisSS = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet1 = thisSS.getSheetByName('Data'); 
  var specialCell = sheet1.getRange("A1:A5"); 
  Logger.log(htmlEmail = "Cell " + specialCell.getA1Notation() +  " edited by " + Session.getActiveUser().getEmail())

}

Thank you for your assistance!

javascript excel google-apps-script google-sheets

Answer 1

Answer №1

Solution:

If you want to capture the date and time when an edit is made in Google Sheets using an onEdit trigger, here's a code snippet that can help:

function onEdit() {
  var thisSS = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = thisSS.getSheetByName('Data');
  var specialCell = sheet1.getRange("A1:A5");
  var now = new Date();
  Logger.log("Edited by " + Session.getEffectiveUser().getEmail() + " at " + now);
}

Please Note:

I utilized getEffectiveUser instead of getActiveUser since the former may not be available in certain contexts (simple triggers). Another approach would be to install the trigger for accessing services requiring authorization.

Answer 2

Solution:

If you want to capture the date and time when an edit is made in Google Sheets using an onEdit trigger, here's a code snippet that can help:

function onEdit() {
  var thisSS = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = thisSS.getSheetByName('Data');
  var specialCell = sheet1.getRange("A1:A5");
  var now = new Date();
  Logger.log("Edited by " + Session.getEffectiveUser().getEmail() + " at " + now);
}

Please Note:

I utilized getEffectiveUser instead of getActiveUser since the former may not be available in certain contexts (simple triggers). Another approach would be to install the trigger for accessing services requiring authorization.

Code to track the changes made to a specific cell in Google Sheets

Answer №1

Solution:

Please Note:

Similar questions

Renew the php blade foreach loop using jQuery to update data live

Enhancing User Experience with AJAX Page Loading and Progress Tracker

How do I reduce the size of a WinJS image file

Implement a function to delete an item from an array within a React object by utilizing the UseState hook

Creating responsive images in a navbar with HTML and CSS

What could be causing the hover style to not take effect on the leaf nodes in jQuery Treeview?

Tips for extracting data from a state-based object store in React?

Having trouble converting the file to binary format in order to send it to the wit.ai api through node.js

Exploring ways to access an element's background color through JavaScript

Utilize PHP to transform JSON data into JavaScript

How to retrieve email input using SweetAlert2 in PHP?

Using TypeScript to call Node.js functions instead of the standard way

Challenges with pjax/ajax and handling the browser's back button

What is the best way to create a function that shifts a musical note up or down by one semitone?

Using Node.js and Express with MySQL to store documents (outcomes)

Filtering a user list array in JavaScript based on tags using regular expressions

Dynamically load highcharts with a dynamic configuration setting

Selenium and Python: Dealing with the ElementNotInteractableException when trying to input a value in a td element

The functionality of alpine.js x-for update is not functioning as intended

Verifying the URL to determine if it includes the specific string needed to modify the Jade theme in