Auto Populate date in Colum when X colum is edited MultiSheet-CodePudding

i am relatively new to this , i work for a small business and im trying to make a sort of registry to follow Orders and Cheques and auto dating is a huge time saver

ATM i have a working code for the first sheet (see bellow) it auto dates in the first column upon editing the 2nd column and it works wonders, the issue i am getting is it works on every sheet the same, i would need the 2nd and 3rd sheet to Auto Date when the 3rd column is edited instead of the 2nd.

can i adapt this script so it works differently on a different sheet on the same doc ?

//------------------------------------------------------------
 //Auto-Populate date in Column A of when column B is updated 
 //Edited 01/13/16 - MK
 //------------------------------------------------------------

function onEdit(event) {
  var eventRange = event.range;
  if (eventRange.getColumn() == 2) { // 2 == column B

    // getRange(row, column, numRows, numColumns)
    var columnXRange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 1, eventRange.getNumRows(), 1);

    var values = columnXRange.getValues();

    for (var i = 0; i < values.length; i  ) {
      if (!values[i][0]) {  // If cell isn't empty
       values[i][0] = new Date();
      }
    }
    columnXRange.setValues(values);  
  }
}

CodePudding user response：

Try it this way:

function onEdit(e) {
  const sh = e.range.getSheet();
  const sA = ["Sheet1", "Sheet2", "Sheet3"];
  const cA = [2, 3, 3];
  const idx = sA.indexOf(sh.getName());
  if (~idx && e.range.columnStart == cA[idx]) { 
    sh.getRange(e.range.rowStart,1).setValue(new Date());
  }
}

CodePudding user response：

SUGGESTION

To my understanding, you want your script to dynamically adjust per sheet on where you perform an edit:

On your first sheet, once a user edits column 2 or B a timestamp will be added on the adjacent row on Column 1 or A.
On your second & third sheet, once a user edits Column 3 or C a timestamp will be added on the adjacent row on Column 1 or A.

You may try this implementation below, with fewer lines of code & you will no longer need to edit your script to define each column but only the sheets themselves in case you'll add multiple sheets in the future:

Script

function onEdit(e) {
  e.source.getActiveSheet().getRange(1, e.range.getColumn()).getValue().includes("*") && e.range.getRow() != 1 ? //Checks if the cell being edited under a column title contains an identifier, which is an asterisk sign (*)
    e.source.getActiveSheet().getRange(e.range.getRow(), 1).setValue(new Date()) : //If true, then the adjacent row on Column A will add a timestamp
    null; //If false, nothing will happen
}

Demonstration

Add an identifier to a column title on each of your sheets where you would like the script to only take effect.

This sample, an identifier used is an asterisk sign (*) after a column title on row 1. But you can use any special character you want by editing the script on the .includes() line

Once you have saved the sample script on your spreadsheet file, here's the behavior:

When you edit any row under a column title that contains an identifier, a timestamp gets added adjacent to the edited row on Column A as seen here. Otherwise, nothing will be added on A column

This script will also dynamically work on all sheets under your spreadsheet file depending on where you put the identifier, as seen here:

SUGGESTION

Script

Demonstration

References