0

Situation:

I have the following script, that runs when someone add new rows. This script should be detect the sheet and apply the datavalidation or action only when an specific sheet have new rows.

Problem:

This script only run apply the datavalidation for the sheet called "ISP1", when I add new rows in the sheet "ISP2" re apply the datavalidation in the sheet "ISP1".

Some part of this script should be run for every sheets but somes part only when an specific sheet have new rows.

Function to AddNew Rows: a link

Script:

function initializeTrigger(){ // run this only once to create a trigger if necessary
  var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("dataValidation")
   .forSpreadsheet(sheet)
   .onChange()
   .create();
}

function dataValidation(e){
  Logger.log(e.changeType);
  if(e.changeType=='INSERT_ROW'){
    // do Something

    //DataValidation - In Column B
    var cell1 = SpreadsheetApp.getActiveSheet().getRange('B3:B');
    var rule1 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['Open', 'Closed']).build();
      cell1.setDataValidation(rule1);  

            //DataValidation - In Column C
    var cell2 = SpreadsheetApp.getActiveSheet().getRange('C3:C');
    var rule2 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(true).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['Y','N']).build();
      cell2.setDataValidation(rule2);


      //DataValidation - In Column D & I for ISP1
    var ss = SpreadsheetApp.getActiveSheet();
    if(ss.getName() == "ISP1"){
    var cell3 = ss.getRange('D3:D');
    var rule3 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['DATCO', 'XFR MDA2', 'SCH CALL']).build();
      cell3.setDataValidation(rule3);

    var cell4 = ss.getRange('I3:I');
    var rule4 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['1:00:00', '2:00:00', '12:00:00', '24:00:00']).build();
      cell4.setDataValidation(rule4);

      }

      //DataValidation - In Column D & I for ISP2
    var ss = SpreadsheetApp.getActiveSheet();
    if(ss.getName() == "ISP2"){
    var cell5 = ss.getRange('D3:D');
    var rule5 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['NOC', 'CUS', 'DATCO', 'COMER']).build();
      cell5.setDataValidation(rule5);

    var cell6 = ss.getRange('I3:I');
    var rule6 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['0:15:00', '0:20:00', '1:00:00']).build();
      cell6.setDataValidation(rule6);
      }

    Browser.msgBox('New row(s) added, Data Validation Completed');
  }
}

This script should be run and apply the datavalidation only when someone add new rows into an specif sheets.
-Column B&C Should be run for the active sheet.
-Column D&I Should be run only when someone add new rows for specif sheet ISP1 or ISP2


OTHER SCRIPT TESTED:

Both script run onchange.

function Data_V(){

  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //var ss = event.source.getActiveSheet();
  if (s.getName() == "ISP1") {
  var numRows = s.getMaxRows();
  if(ScriptProperties.getProperty('numberOfRows')){
    var nRows = Number(ScriptProperties.getProperty('numberOfRows'));
    if(nRows<numRows){

      //DataValidation - In Column B
    var cell1 = s.getRange('B3:B');
    var rule1 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['Open', 'Closed']).build();
      cell1.setDataValidation(rule1);

            //DataValidation - In Column C
    var cell2 = s.getRange('C3:C');
    var rule2 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(true).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['Y','N']).build();
      cell2.setDataValidation(rule2);


      //DataValidation - In Column D
    var cell3 = s.getRange('D3:D');
    var rule3 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['DATCO', 'XFR MDA2', 'SCH CALL']).build();
      cell3.setDataValidation(rule3);

      //DataValidation - In Column I
    var cell4 = s.getRange('I3:I');
    var rule4 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['1:00:00', '2:00:00', '12:00:00', '24:00:00']).build();
      cell4.setDataValidation(rule4);

      ScriptProperties.setProperty('numberOfRows',numRows);// update value with current value
    }
    Browser.msgBox('New row(s) added, Data Validation Completed ISP1');
  }
  ScriptProperties.setProperty('numberOfRows',numRows);// create a start value with current value of simply update if the trigger was called for another reason
 }
}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

function Data_V2(){

  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //var ss = event.source.getActiveSheet();
  if (s.getName() == "ISP2") {
  var numRows = s.getMaxRows();
  if(ScriptProperties.getProperty('numberOfRows')){
    var nRows = Number(ScriptProperties.getProperty('numberOfRows'));
    if(nRows<numRows){

      //DataValidation - In Column B
    var cell5 = s.getRange('B3:B');
    var rule5 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['Open', 'Closed']).build();
      cell5.setDataValidation(rule5);

            //DataValidation - In Column C
    var cell6 = s.getRange('C3:C');
    var rule6 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(true).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['Y','N']).build();
      cell6.setDataValidation(rule6);


      //DataValidation - In Column D
    var cell7 = s.getRange('D3:D');
    var rule7 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    .requireValueInList(['NOC', 'CUS', 'DATCO', 'COMER']).build();
      cell7.setDataValidation(rule7);

      //DataValidation - In Column I
    var cell8 = s.getRange('I3:I');
    var rule8 = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false).setHelpText("Seleccione Solo los Valores Indicados")
    ..requireValueInList(['0:15:00', '0:20:00', '1:00:00']).build();
      cell8.setDataValidation(rule8);

      //CopyFormat - In Column H
    //var source = s.getRange('H1');
    //var destination = s.getRange('H3:H');
    //source.copyTo(destination, {formatOnly:true});

      ScriptProperties.setProperty('numberOfRows',numRows);// update value with current value
    }
    Browser.msgBox('New row(s) added, Data Validation Completed ISP2');
  }
  ScriptProperties.setProperty('numberOfRows',numRows);// create a start value with current value of simply update if the trigger was called for another reason
 }
}

Both script run Onchange. When I add rows in ISP1 the script executed is Data_V for ISP1 but when I add news rows in ISP2 the script Data_V2 didn't run but the Data_V run again. I think maybe only we can have one script on change, but when I try to merge both script in one to run one script and detect what sheet was edited didn't work.

Cœur
  • 37,241
  • 25
  • 195
  • 267
LAD Service Desk
  • 289
  • 5
  • 14
  • 27
  • Just get the sheet on the beginning `var s = e.source.getActiveSheet()`. Then and `if( s.getName() == 'ISP1' ) {` ... etc. Makes sense? – Henrique G. Abreu Jun 10 '14 at 02:27
  • Hi, still not working. Ex.: -- I add new rows in ISP1 sheet, the script apply the all datavalidation except ISP2 part. When I add new rows in ISP2 sheet, the script apply the All datavalidation again in ISP1 sheet, and not implement the datavalidation in ISP2 sheet and also not implement the specific part for ISP2. When I add new rows in ISP2, the box appear indicated the script detected the new rows but not implement the datavalidation in that sheet where I add rows. – LAD Service Desk Jun 10 '14 at 04:31
  • I think the issue here the option on change detect where change in the spreadsheet, I split the script in two script one for ISP1 and other for ISP2, When I add new rows in ISP1 run the Script ISP2 when I add new rows in ISP2 run the script in ISP2 but in any time apply the datavalidation. – LAD Service Desk Jun 10 '14 at 05:02

0 Answers0