How to create a Google apps script array list of sheet names and use it in if statement?-CodePudding

I am trying to get my Google sheet to fill in the date in column A when I fill in column B and have that function work on multiple sheets within the spreadsheet.

So far I have a working script that works on my first sheet. I have tried several things like making an array out of the variable (but that makes only the last array name work), trying an OR statement with 2 variables (but that fucked up the column it should watch), ...

Can you guys help me with this?

this is the one that works for 1 sheet name:

function onEdit() {
  // writes the current date to the cell to the right on the row when a cell in a specific column is edited
  // adjust the following variables to fit your needs

  var sheetNameToWatch = "2021";
  var columnNumberToWatch = /* column A */ 2; // column A = 1, B = 2, etc.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();
  var val=sheet.getActiveCell().getValue()

  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && val!= "" /* I changed this var value so if I input anything, it will update the date */) {
    var targetCell = sheet.getRange(range.getRow(), range.getColumn()-1 /* changed this to "-1" so it will input to the left column */
                               );
    targetCell.setValue(""   Utilities.formatDate(new Date(), "CET", "dd/MM/yyyy"));
  }
}

Demo can be found here: https://docs.google.com/spreadsheets/d/1mLBJrx3VCCwtcfUk-q_1DhwZFdA1cdrqFcySfC0cAi0/edit?usp=sharing

CodePudding user response：

Just a minor edit on the code you provided:

function onEdit() {
      // writes the current date to the cell to the right on the row when a 
    cell in a specific column is edited
      // adjust the following variables to fit your needs
   
      var columnNumberToWatch = /* column A */ 2; // column A = 1, B = 2, etc.
    
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = SpreadsheetApp.getActiveSheet();
      var range = sheet.getActiveCell();
      var val=sheet.getActiveCell().getValue()
    
      if (range.getColumn() == 
      columnNumberToWatch && val!= "") {
      var targetCell = sheet.getRange(range.getRow(), range.getColumn()-1 
    
                                   );
        targetCell.setValue(""   Utilities.formatDate(new Date(), "CET", "dd/M

M/yyyy"));
  }
}

The following code snippet has been removed:

var sheetNameToWatch = "2021";

sheet.getName() == sheetNameToWatch &&

Technically your code is already good, we just need to remove the condition based on your IF Statement since SpreadsheetApp.getActiveSheet(); will detect the current sheet you are working on.