Can I run multiple scripts for multiple sheets?

[Mark Pyper]

I have multiple sheets in doc.  I have script that hides rows based on cell entry, easy stuff.  However, I added the same script for another sheet in the doc and it will only work on one of the sheets.  I have tried it adding it separately as a file, tried modifying the original file by adding the script to it while changing the sheet name.  I can make each work, individually but not together.  What am I doing wrong?

 

Individually:

//**GLOBALS**

// Sheet the data is on.

var SHEET = "Road Notes";

// The value that will cause the row to hide.

var VALUE = "x";

// The column we will be using

var COLUMN_NUMBER = 7

 

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var activeSheet = ss.getActiveSheet();

  

  //Ensure on correct sheet.

  if(SHEET == activeSheet.getName()){

    var cell = ss.getActiveCell()

    var cellValue = cell.getValue();

    

    //Ensure we are looking at the correct column.

    if(cell.getColumn() == COLUMN_NUMBER){

      //If the cell matched the value we require,hide the row.

      if(cellValue == VALUE){

        activeSheet.hideRow(cell);

      };

    };

  };

}

Individually:

/**GLOBALS**

// Sheet the data is on.

var SHEET = "Road Notes";

// The value that will cause the row to hide.

var VALUE = "x";

// The column we will be using

var COLUMN_NUMBER = 7

 

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var activeSheet = ss.getActiveSheet();

  

  //Ensure on correct sheet.

  if(SHEET == activeSheet.getName()){

    var cell = ss.getActiveCell()

    var cellValue = cell.getValue();

    

    //Ensure we are looking at the correct column.

    if(cell.getColumn() == COLUMN_NUMBER){

      //If the cell matched the value we require,hide the row.

      if(cellValue == VALUE){

        activeSheet.hideRow(cell);

      };

    };

  };

}

Combined Script:

/**

* TITLE:

*     Hide a row if a value is inputted.

*/

 

//**GLOBALS**

// Sheet the data is on.

var SHEET = "Shop Notes";

// The value that will cause the row to hide.

var VALUE = "x";

// The column we will be using

var COLUMN_NUMBER = 6

 

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var activeSheet = ss.getActiveSheet();

  

  //Ensure on correct sheet.

  if(SHEET == activeSheet.getName()){

    var cell = ss.getActiveCell()

    var cellValue = cell.getValue();

    

    //Ensure we are looking at the correct column.

    if(cell.getColumn() == COLUMN_NUMBER){

      //If the cell matched the value we require,hide the row.

      if(cellValue == VALUE){

        activeSheet.hideRow(cell);

      };

    };

  };

}

/**

* TITLE:

*     Hide a row if a value is inputted.

*/

 

//**GLOBALS**

// Sheet the data is on.

var SHEET = "Road Notes";

// The value that will cause the row to hide.

var VALUE = "x";

// The column we will be using

var COLUMN_NUMBER = 7

 

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var activeSheet = ss.getActiveSheet();

  

  //Ensure on correct sheet.

  if(SHEET == activeSheet.getName()){

    var cell = ss.getActiveCell()

    var cellValue = cell.getValue();

    

    //Ensure we are looking at the correct column.

    if(cell.getColumn() == COLUMN_NUMBER){

      //If the cell matched the value we require,hide the row.

      if(cellValue == VALUE){

        activeSheet.hideRow(cell);

      };

    };

  };

}

[Edward Ulle]

There can be only one function named onEdit().  You can have multiply on edit functions but each has to have a unique name. I’m not saying that’s what you should do.  You can have one onEdit(e) and depending what sheet you are editing do something. If editing a different sheet do something different. The event object “e” tells you what sheet.

[Mark Pyper]

I'm fairly new to this, meaning I have no formal training and this is my first attempt at App Script.  

So what would you suggest changing?  Do I take the onEdit out of the second script in the code?  

[CBMServices Web]

If you want the exact same functionality on all sheets, then just delete the extra onEdit functions and just keep one. Then change the if statement to check against all the sheets that you care about, or remove that check if you want it to operate against all sheets. 

If only one set of sheets, do the if statement this way:

const sheetName == activeSheet.getName();

const sheets =["Road Notes","Shop Notes"];  // list all sheets here

if (sheets.includes(sheetName))

{....

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5f7c6579-8515-4915-a353-85143c481513n%40googlegroups.com.

[Mark Pyper]

Thanks for the response.  I think I understand but there are different criteria on each sheet (one is COLUMN_NUMBER=6 the other is =7).  How would that script work or need to be changed?

Humbly His servant,

 

Mark A. Pyper

616.914.7650

 

"Ye who have sold for naught

  Your heritage above

  Shall Have it back unbought

  The GIFT of Jesus love"

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3UaWZtXFjEPg_rSz24vCtAyLwE5MGnTKoRK0-wQ4DpWmQ%40mail.gmail.com.

[CBMServices Web]

There are several ways for you to be able to have different criteria for each sheet. The variable sheets is an array and you can have another array that contains the column number. Just ensure the order of the items in the two arrays match. As this:

const cols = [7,6]; // 7 is for Road Notes, 6 is for Shop Notes, etc..

const sheetName == activeSheet.getName();

const sheets =["Road Notes","Shop Notes"];  // list all sheets here

const sheetIndex = sheets.indexOf(sheetName);

if (sheetIndex >= 0)

{....

Then on Column check use:

if(cell.getColumn() == cols[sheetIndex]){

This would check the column to ensure you are checking against corresponding column number for that sheet.

Hope this help.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAGhk4pBp0GivXt1Q9f335qktfj7suO66fNHRR-vwSP%3DbO7rwRg%40mail.gmail.com.

[Mark Pyper]

Well, I can successfully "run" and "Execute" the Script.  It doesn't actually hide the rows though when entering an "x" in the corresponding column.  Any thoughts?

I am very interested in learning and understanding AS, as I have found it useful in the very minute amount that I have played with it.  It is a little over my head to say the least.

Humbly His servant,

 

Mark A. Pyper

616.914.7650

 

"Ye who have sold for naught

  Your heritage above

  Shall Have it back unbought

  The GIFT of Jesus love"

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3XK4mD9x%2BsUENhE_dKWsQVnW5v4ocDKsynZPCRDH8p89Q%40mail.gmail.com.

[Mark Pyper]

/**

* TITLE:

*     Hide a row if a value is inputted.

*/

 

//**GLOBALS**

// Sheet the data is on.

var SHEET = ["Copy of Shop Notes","Copy of Road Notes"];

// The value that will cause the row to hide.

var VALUE = "x";

// The column we will be using

var COLUMN_NUMBER = [6,7]

 

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var activeSheet = ss.getActiveSheet();

  

  //Ensure on correct sheet.

  if(SHEET == activeSheet.getName()){

    var cell = ss.getActiveCell()

    var cellValue = cell.getValue();

    

    //Ensure we are looking at the correct column.

    if(cell.getColumn() == cols[sheetIndex]){

      //If the cell matched the value we require,hide the row.

      if(cellValue == VALUE){

        activeSheet.hideRow(cell);

      };

    };

  };

}

Humbly His servant,

 

Mark A. Pyper

616.914.7650

 

"Ye who have sold for naught

  Your heritage above

  Shall Have it back unbought

  The GIFT of Jesus love"

[CBMServices Web]

For these type of script, it is best to use the env variable e that is passed in. This does mean you will not be able to execute it manually because e is only passed in when triggered by Google.

But here is what you do:

Get row number out of the e variable:

const row = e.range.getRow();

Then in hideRow use row number (not cell range).

activeSheet.hideRow(row);

This should work.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAGhk4pCsXcQuJD6fu83hj_T9mKycESnCNERq0%3DKEJpxG%2Bcvy4w%40mail.gmail.com.

[cbmserv...@gmail.com]

Try this:

 

/**

* TITLE:

*     Hide a row if a value is inputted.

*/

 

//**GLOBALS**

// Sheet the data is on.

var SHEET = ["Copy of Shop Notes","Copy of Road Notes"];

// The value that will cause the row to hide.

var VALUE = "x";

// The column we will be using

const COLUMN_NUMBER = [6,7];

 

 

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var activeSheet = ss.getActiveSheet();

  const sheetName = activeSheet.getName();

  const sheetIndex = SHEET.indexOf(sheetName);

  

  //Ensure on correct sheet. (sheetIndex is 0 or higher if active sheet is in array SHEET)

  if(sheetIndex >=0 ){

    var cell = ss.getActiveCell();

    var row = cell.getRow();

    var col = cell.getColumn();

    var cellValue = cell.getValue();

    

    //Ensure we are looking at the correct column.

    if(col == COLUMN_NUMBER[sheetIndex]){

      //If the cell matched the value we require,hide the row.

      if(cellValue == VALUE){

        activeSheet.hideRow(row);

      };

    };

  };

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAGhk4pCsXcQuJD6fu83hj_T9mKycESnCNERq0%3DKEJpxG%2Bcvy4w%40mail.gmail.com.

[Mark Pyper]

First of all, thank you for your time and help.  I tried the script changes you suggested and provided.  It still doesn't work.  I am struggling, as this seems like such a simple thing.  I was able to figure out the Script initially to work on one sheet, this stuff gets confusing!

Thanks again for your time and willingness to help.  

MP

Humbly His servant,

 

Mark A. Pyper

616.914.7650

 

"Ye who have sold for naught

  Your heritage above

  Shall Have it back unbought

  The GIFT of Jesus love"

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/JLIvAC5dzfs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/006e01d8a05f%24e432a050%24ac97e0f0%24%40gmail.com.

[Keith Andersen]

Mark, 

Were you able to share a workbook? 

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAGhk4pCfLAoi4XO53%2Bp7DqyQGJEXmFheAwFDnYT29-DOZz5LTw%40mail.gmail.com.

[Mark Pyper]

https://docs.google.com/spreadsheets/d/1SSuvWMjn66A_KGH_BZstjwQFPPfpbXt8IgpID_6Mdv0/edit?usp=sharing

Humbly His servant,

 

Mark A. Pyper

616.914.7650

 

"Ye who have sold for naught

  Your heritage above

  Shall Have it back unbought

  The GIFT of Jesus love"

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4w1wuA9f7acaAiTXbWOO-do-9GXHM429EdLyCbNANq3qg%40mail.gmail.com.

[Edward Ulle]

Here is how I would do it.  I tested it on my test sheets.  A few notes first:

 

I always wrap my code in a try{} catch(){} block to catch any errors that occur.  I can direct to console, Logger or even the Spreadsheet as shown.

Next I always use the event object (e) rather than getActive to control the flow of my onEdit.

Here are several reference you should look at:

Simple Triggers  Event Objects  Array.find()  Range Object  Sheet Object

function onEdit(e) {

  try {

    // I would use a 2D array of rows [sheet_name,column_number]

    // You can add additional sheets by adding a row

    let watch = [["Shop Notes",6],["Road Notes",7]];

    // I prefer to use the event object (e) to control the onEdit

    // Get the name of the active sheet

    let sheet = e.range.getSheet().getName();

    // See if the sheet is in the list to be watched

    let found = watch.find( item => item[0] === sheet );

    if( found === undefined ) return; // not found

    // We are in one of the watched sheets now are we in the right column

    if( e.range.getColumn() !== found[1] ) return;  // Not in the right column

    // Now lets check the value of cell that was edited

    if( e.value !== "x" ) return; // Nope no x

    // Get the current row number

    let row = e.range.getRow();

    e.range.getSheet().hideRows(row);

  }

  catch(err) {

    SpreadsheetApp.getUi().alert(err);

  }

}