CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
redpandasuit
redpandasuit

Reputation: 63

Limiting script variables to specific sheets in Google Sheets

I have a functioning script working within Google Sheets. The document has multiple sheets. As such, I wish to be able to have one script that is capable of automating a time stamp on all of the sheets.

I have managed to get the script running on all sheets... however I wish to be able to have two separate variable columns and have them act independently of each other. For instance, var cols would affect SHEET 1 and var colx would affect SHEET 2. Would this be possible?

function onEdit(e) {
var s = e.source.getActiveSheet().getName();
var cols = [8];
var colx = [4, 9, 14];
var curDate = Utilities.formatDate(new Date(), "GMT-5", "M/d HH:mm:ss ") 
var r = e.source.getActiveRange();

if (s !== "SHEET 1" && s !== 'SHEET 2' && s !== 'SHEET 3'|| cols.indexOf(e.range.columnStart) ==-1 && colx.indexOf(e.range.columnStart)    ==-1|| 
!e.range.getValue()) return;
 e.range.offset(0,1).setValue(curDate); 

 };

Novice scripter here, thanks for your patience. I was thinking that it would involve specifying the Sheet names with var cols and var colx but am unsure if this is the right approach.

Thanks in advance for any help!!!!!

Upvotes: 0

Views: 146

Answers (1)

Eric Dauenhauer
Eric Dauenhauer

Reputation: 759

I would recommend changing your if statement to be a little more clear in what you're trying to accomplish. It seems like you are either trying to

  1. Apply a timestamp when any column except cols or colx is matched, or
  2. Apply a timestamp only when the columns defined in cols or colx are matched.

Scenario 1: Apply timestamp except when column is matched

if (s == "SHEET 1" && cols.indexOf(e.range.columnStart) == -1) {
    e.range.offset(0,1).setValue(curDate); 
} else if (s == "SHEET 2" && colx.indexOf(e.range.columnStart) == -1) {
    e.range.offset(0,1).setValue(curDate); 
} else {
    return;
}

Scenario 2: Apply timestamp only when column is matched

if (s == "SHEET 1" && cols.indexOf(e.range.columnStart) != -1) {
    e.range.offset(0,1).setValue(curDate); 
} else if (s == "SHEET 2" && colx.indexOf(e.range.columnStart) != -1) {
    e.range.offset(0,1).setValue(curDate); 
} else {
    return;
}

Scenario 3: Skip the timestamp if the cell is empty

if (s == "SHEET 1" && cols.indexOf(e.range.columnStart) != -1 && !e.range.getValue()) {
    e.range.offset(0,1).setValue(curDate); 
} else if (s == "SHEET 2" && colx.indexOf(e.range.columnStart) != -1 && !e.range.getValue()) {
    e.range.offset(0,1).setValue(curDate); 
} else {
    return;
}

I hope that helps.

Upvotes: 1

Related Questions