CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Seyfertt
Seyfertt

Reputation: 37

Run script depending on active sheet

I'm trying to execute a script based on an active sheet the user is. So, the menu code is the following:

function onOpenInstalledTrigger(){
  app.createMenu('Benefits')
    .addItem('Generate benefits', 'setBenefits')
    .addToUi();
}

And then, to execute the script only when the sheet VT is active I wrote:

function setBenefits(){
  if (SpreadsheetApp.getActiveSpreadsheet().getSheetByName() == 'VT'){
    benefitsValue()
    discountValue()
    netValue()
  } else {
    app.alert('It must be active on sheet VT')
  }
}

But when I add this condition, I get an error:

Exception: The parameters () don't match the method signature for SpreadsheetApp.Spreadsheet.getSheetByName.

How can I only execute the code when the user is at the right sheet?

Upvotes: 1

Views: 745

Answers (3)

NightEye
NightEye

Reputation: 11184

Use getActiveSheet().getName() to get the name of the current active sheet.

function setBenefits(){
  if (SpreadsheetApp.getActiveSheet().getName() == 'VT'){
    benefitsValue()
    discountValue()
    netValue()
  } else {
    app.alert('It must be active on sheet VT')
  }
}

You have it confused with getSheetByName.

getSheetByName needs a parameter (sheet name) and it returns the sheet.

getName returns the name of the spreadsheet/sheet depending on where you use it.

Output:

output output

Reference:

EDIT:

  • The issue "Exception: The number of rows in the range must be at least 1." is caused by the commands outside the functions trying to access the active sheet's range that isn't available.

FIX:

  • There are many ways to do this, but the easiest modification you can do is access VT directly. Since you only need to do this in VT sheet, access the VT sheet instead.

Modification:

var spread = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('VT');

Output:

output

Upvotes: 2

Marios
Marios

Reputation: 27348

You should use the onSelectionChange(e) trigger:

To execute a code when switching between different sheets you can instead use onSelectionChange(e).

The following example will execute some code when you switch from a different sheet to VT:

function onSelectionChange(e) {       
   const as = e.source.getActiveSheet();
   if (as.getName() == 'VT'){ 
          benefitsValue();
          discountValue();
          netValue();
   }
}

Upvotes: 1

Rilves
Rilves

Reputation: 156

try this instead :

function setBenefits(){
  if (SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName() == 'VT'){
    benefitsValue()
    discountValue()
    netValue()
  } else {
    app.alert('It must be active on sheet VT')
  }
}

Your function has two problems :

Upvotes: 1

Related Questions