How do I run Google Sheets App Script on all sheets but EXCEPT for three tabs in a Google Sheet? - Stack Overflow

admin管理员组

文章数量:1122846

I am trying to make the following script run on all the sheets(tabs) EXCEPT for three tabs in my Google Sheet. Or if I want to run code selected tabs only.

Here is my app script code.

function dropdown() {
    var activeCell=SpreadsheetApp.getActiveSheet().getActiveRange();
    var activeRow=activeCell.getRow()
    var activeCol=activeCell.getColumn()
    var activeValue=activeCell.getValue()
    var activeSheet=activeCell.getSheet()
    if(activeSheet.getName()=="Umesh_K" && activeRow>1 && activeCol==9){
        var worksheet=SpreadsheetApp.getActiveSpreadsheet();
        var spreadsheet=worksheet.getSheetByName("Data")
        var data=spreadsheet.getDataRange().getValues();
        var list=data.filter(row=>row[0]==activeValue).map(row=>row[1])
        var validation=SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid( false).build()
        activeCell.offset(0,1).setDataValidation(validation)
    }
}

I am trying to make the following script run on all the sheets(tabs) EXCEPT for three tabs in my Google Sheet. Or if I want to run code selected tabs only.

Here is my app script code.

function dropdown() {
    var activeCell=SpreadsheetApp.getActiveSheet().getActiveRange();
    var activeRow=activeCell.getRow()
    var activeCol=activeCell.getColumn()
    var activeValue=activeCell.getValue()
    var activeSheet=activeCell.getSheet()
    if(activeSheet.getName()=="Umesh_K" && activeRow>1 && activeCol==9){
        var worksheet=SpreadsheetApp.getActiveSpreadsheet();
        var spreadsheet=worksheet.getSheetByName("Data")
        var data=spreadsheet.getDataRange().getValues();
        var list=data.filter(row=>row[0]==activeValue).map(row=>row[1])
        var validation=SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid( false).build()
        activeCell.offset(0,1).setDataValidation(validation)
    }
}

• google-sheets
• google-apps-script

Share Improve this question Follow edited Nov 21, 2024 at 11:48 Umesh Kharediya asked Nov 21, 2024 at 10:58 Umesh KharediyaUmesh Kharediya 111 bronze badge 8

• Welcome to Stackoverflow! Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this anonymous sheet maker to create your reproducible example without your email being exposed. – 4thAnd1 Commented Nov 21, 2024 at 11:22
• May I confirm if the code you posted is already working and you just need to run it on all sheets except for 3 tabs? – PatrickdC Commented Nov 21, 2024 at 11:42
• Yes Please you can go through my code. because the Google app script is not showing an error. – Umesh Kharediya Commented Nov 21, 2024 at 11:53
• What does your code do in the first place? Can you add a description of your code in your post? – PatrickdC Commented Nov 21, 2024 at 12:07
• First, In "Umesh_K" sheet, I choose my subject, on the contrary, it brings me the dropdown, which I have mentioned in the "data" sheet which it is available my content in two headers (Subject, Topics) – Umesh Kharediya Commented Nov 21, 2024 at 12:30

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 0

You can use Array.includes to set a condition that a given value(sheet name) is included in the given array or not:

//...
const includeArray = ["Umesh_K", "Umesh_Z"]
if(includeArray.includes(activeSheet.getName()) && activeRow>1 && activeCol==9){
//rest of the code

Alternatively, to exclude,

//...
const excludeArray = ["DO_NOT_INCLUDE_sheet", "Sheet15"]
if(!excludeArray.includes(activeSheet.getName()) && activeRow>1 && activeCol==9){
// ! <-- Note the NOT operator above 
//rest of the code

本文标签：