CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tester
Tester

Reputation: 1

How to use same script for multiple tabs in Google Sheet

I am using an auto-timestamp script in Google Sheets. Where when you input something in a particular cell, the script will automatically add a timestamp in a particular cell. But the problem is that I want to use the same script for multiple instances in multiple tabs of google sheets. But it is only working for 1st instance.

I'd like to include news tabs (I alread knows thats a limited numbers of page). Those others names are: "Natal", "Joao Pessoa", "Novo1", "novo2"

function onEdit(e)
{
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() == "CAICO") //"order data" is the name of the sheet where you want to run this script.
  {
    var actRng = sheet.getActiveRange();
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf("Alt") + 1;
    var orderCol = headers[0].indexOf("Data") + 1;
    if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
    {
      sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "dd-MM-yyyy"));
    }
  }
}

Upvotes: 0

Views: 1172

Answers (2)

Diego
Diego

Reputation: 9571

You should create an array of sheet names to include and then check if the trigger is coming from one of those sheets. You can check this using .indexOf(), which will return -1 if the value is not found (note that this is case-sensitive).

var sheets = ["CAICO", "Natal", "Joao Pessoa", "Novo1", "novo2"];
if (sheets.indexOf(sheet) != -1) {
  // do something
}

I would also highly encourage you to change the way you're getting the triggering sheet. Right now, you're using e.source.getActiveSheet(), which is not necessarily the sheet where the edit is. For example, if you make an edit in "Sheet1" and then quickly open "Sheet2", if the script executes slowly, the .getActiveSheet() call could actually return "Sheet2".

This is an unlikely scenario, but you can easily avoid it by writing e.range.getSheet(). From the documentation, we know that e.range represents "the cell or range of cells that were edited". So, this guarantees that the sheet we're considering is the sheet that was edited.

Similarly, and I may be mis-reading your code here, but I think you should make a similar change to your actRng declaration. 

function onEdit(e)
{
  var sheets = ["CAICO", "Natal", "Joao Pessoa", "Novo1", "novo2"]; // Sheet names that will trigger execution. Case-sensitive.
  var sheet = e.range.getSheet();
  if (sheets.indexOf(sheet.getName()) != -1) // Check if the edit was made in an applicable sheet
  {
    var actRng = e.range; // The edited range
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf("Alt") + 1;
    var orderCol = headers[0].indexOf("Data") + 1;
    if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
    {
      sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "dd-MM-yyyy"));
    }
  }
}

Upvotes: 2

CodeCamper
CodeCamper

Reputation: 6980

Why not just add an or to your function?

function onEdit(e)
{
  var sheet = e.source.getActiveSheet();
  var n = sheet.getName();
  if (n == "CAICO" || n == "Natal" || n== "Joao Pessoa" || n == "Novo1" || n== "novo2") //"order data" is the name of the sheet where you want to run this script.
  {
    var actRng = sheet.getActiveRange();
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf("Alt") + 1;
    var orderCol = headers[0].indexOf("Data") + 1;
    if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
    {
      sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "dd-MM-yyyy"));
    }
  }
}

Upvotes: 0

Related Questions