Google Sheets – Sheet Specific Scripts

google sheetsgoogle-apps-script

I'm currently using the below script to timestamp when an edit is made to column 7 on a sheet named "Sheet 1".

function onEdit(e) {
   if ([7].indexOf(e.range.columnStart) != -1) {
   e.range.offset(0, 7).setValue(new Date()).setNumberFormat("dd.MM.yyyy at HH:mm");
   }  
}

I have a second sheet "Sheet 2" which I need to run the same script on but against column 5.

function onEdit(e) {
   if ([5].indexOf(e.range.columnStart) != -1) {
   e.range.offset(0, 7).setValue(new Date()).setNumberFormat("dd.MM.yyyy at HH:mm");
   }  
}

I've tried .getSheetByName("Sheet1") but just cant seem to get it working correctly.

Best Answer

Please try the following piece of code.

Code

function onEdit(e) {
  var col = e.range.columnStart;
  if(col === 7 || col === 5) {
    var name = e.source.getActiveSheet().getName();
    if((name === 'Sheet1' && col === 7) || (name === 'Sheet2' && col === 5)) {
      e.range.offset(0, 7).setValue(new Date()).setNumberFormat("dd.MM.yyyy at HH:mm");
    }
  }
}

Explained

The code reads as follows:

First check whether we're in column 5 or 7.
If true, only then retrieve the sheet name
If the sheet name and the column match, then execute the command

I think onEdit functions deserve some extra attention to avoid un-necessary calculations. If you were to include the sheet name, if would check the sheet name every time you change a cell. By restricting that to the columns only, you avoid that.

Related Solutions

Google-sheets – How to apply a script to only one specific sheet within a spreadsheet

There are a couple of ways to do that. First, you need to declare the spreadsheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();

Secondly, declare the first sheet like this:

var sh0 = ss.getSheets()[0];

or like this:

var sh0 = ss.getSheetByName("Robin");

Then set the active cell and retrieve the column index:

var editedCell = sh0.getActiveRange().getColumnIndex();

And do the logic for the sort:

var columnToSortBy = 1;
var tableRange = "a2:I30";

if(editedCell == columnToSortBy){   
  var range = sheet.getRange(tableRange);
  range.sort( { column : columnToSortBy } );
}

Complete code, a bit shortened:

function onEdit(){
  var sh0 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var editedCell = sh0.getActiveRange().getColumnIndex();

  if(editedCell == 1) { 
    var range = sh0.getRange("A2:I30");
    range.sort({column: 1});
  }
}

Google-sheets – Change a working function from onOpen() to onEdit()

Yes, if you want a script to be triggered by updates to spreadsheet A, then it should be bound to that spreadsheet. It can still access the other spreadsheets by their URL or Id. For example, this function copies the cells F2:H6 of the present sheet to the cells A1:C5 of another one.

function copyStuff() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('MasterSheet1')
  var ss2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/...');
  var targetSheet = ss2.getSheetByName('Sheet1');
  targetSheet.getRange("A1:C5").setValues(sourceSheet.getRange("F2:H6").getValues());
}