Search code examples
google-apps-scriptgoogle-sheets

How to allow generation of Timestamp on protected cells using AppsScript for all non-sheet owners

I hope this correspondence finds you well. I recently posted an inquiry on TimeStamp and was grateful to receive helpful feedback that aided me in resolving the matter. However, I have since encountered a new challenge. My current task involves working on a spreadsheet that utilizes Appscript to capture Start and End times. With a script, I have implemented TimeStamp on edit. To ensure security, I have restricted access to certain columns by locking them, which limits access to specific individuals. Specifically, I have locked Columns 26 and 28 for Production Activity and Columns 10 and 12 for Non-production Activity. Regrettably, I have encountered an issue where the timestamp does not generate for users who are restricted from editing the aforementioned columns. The timestamp only appears correctly when the cell protection is removed. Below is the current Apps Script

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == "Production Activity") { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if (r.getColumn() == 25) { //checks the column
      var nextCell = r.offset(0, 1);
      if (nextCell.isBlank()) {
        nextCell.setValue(new Date());
      }
    }

    if (r.getColumn() == 27) { //checks the column
      var nextCell = r.offset(0, 1);
      if (nextCell.isBlank()) {
        nextCell.setValue(new Date());
      }
    }
  }
  if (s.getName() == "Non-Production Activity") { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if (r.getColumn() == 9) { //checks the column
      var nextCell = r.offset(0, 1);
      if (nextCell.isBlank()) {
        nextCell.setValue(new Date());
      }
    }

    if (r.getColumn() == 11) { //checks the column
      var nextCell = r.offset(0, 1);
      if (nextCell.isBlank()) {
        nextCell.setValue(new Date());
      }
    }
  }
}

As a result, I request your guidance on whether there is a feasible solution to generate the timestamp even when cell protection is enabled.

Thank you in advance for your valuable assistance.

Here is the replica of the original spreadsheet where I protected the above-mentioned columns for your reference.

Solution

  • Modification points:

    • I think that the reason for your current issue of Regrettably, I have encountered an issue where the timestamp does not generate for users who are restricted from editing the aforementioned columns. The timestamp only appears correctly when the cell protection is removed. might be due to the simple trigger of OnEdit. In your situation, please use the installable OnEdit trigger.

    When this is reflected in your script, please do the following flow.

    1. Rename function name

    Please rename the function name from onEdit to installedOnEdit.

    Because, when onEdit function is installed as the installable trigger of OnEdit, when a cell is edited, the function onEdit is executed 2 times with an asynchronous process. Ref (Author: me)

    2. Install OnEdit trigger

    In this case, please operate the following install by the owner of Google Spreadsheet. The shared users are not required to do it. Please be careful about this.

    Please install the OnEdit trigger to installedOnEdit. You can see how to manually install OnEdit trigger at https://developers.google.com/apps-script/guides/triggers/installable#manage_triggers_manually

    And, you can see the flow of installing it in the following image.

    enter image description here

    And also, the OnEdit trigger can be also installed by a script as follows.

    function installTrigger() {
  ScriptApp.newTrigger("InstalledonEdit").forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

    When you run this script, the OnEdit trigger is installed to the function InstalledonEdit. When I saw your Spreadsheet, it seemed that the function name was InstalledonEdit. So, I used it. Please be careful about this.

    3. Testing

    Please edit a cell by a shared user who is not the owner of Spreadsheet. By this, the function installedOnEdit is automatically run by the installable trigger.

    Note:

    • From your showing script, when you don't want to use the event object, as another modification, how about the following modification? In this case, please install the OnEdit trigger to installedOnEdit, and edit a cell.

      function installedOnEdit() {
  const obj = {
    "Production Activity": [25, 27],
    "Non-Production Activity": [9, 11],
  };
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveCell();
  const o = obj[sheet.getSheetName()];
  if (o && o.includes(range.getColumn())) {
    const dstRange = range.offset(0, 1);
    if (dstRange.isBlank()) {
      dstRange.setValue(new Date());
    }
  }
}

    References: