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.