Goal:

I created a submission form for work. Ideally it allows for submission to 3 separate forms depending on input values. I am utilizing the .isChecked corresponding to a celling with check box to determine if cell inputs will be shared to multiple forms. So to sum up, X amount of cells are inputted which submits to 1 form. If the box is checked then that same info plus more is submitted to for 1 and 2. Ill submit the code below.

Problem:

Regardless if the checkbox is checked it will input to both forms. My if statement must be wrong as once I place the code in the if statement the 2nd form wont accept inputs. Also my submitData2 function is greyed out, im assuming that means not active

Im also super new to coding so help me understand if possible.

The code below shows it as 2 functions, one for inputting to 1 sheet and another to the last if .isChecked==true

function submitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Req. Form"); //User Form
var datasheet = ss.getSheetByName("Defect List"); //Database




//Input Values
var values = [[formSS.getRange("E7").getValue(),
formSS.getRange("E9").getValue(),
formSS.getRange("E11").getValue(),
formSS.getRange("E13").getValue(),
formSS.getRange("E15").getValue(),
formSS.getRange("E17").getValue(),
formSS.getRange("E19").getValue(),
formSS.getRange("E21").getValue(),
formSS.getRange("E23").getValue()]];

var blankRow=datasheet.getLastRow() 1;



datasheet.getRange(blankRow,3).setValue(formSS.getRange("E7").getValue());
datasheet.getRange(blankRow,4).setValue(formSS.getRange("E9").getValue());
datasheet.getRange(blankRow,5).setValue(formSS.getRange("E11").getValue());
datasheet.getRange(blankRow,6).setValue(formSS.getRange("E13").getValue());
datasheet.getRange(blankRow,1).setValue(formSS.getRange("E23").getValue());



function submitData2() {
var xx = SpreadsheetApp.getActiveSpreadsheet();
var formXX = xx.getSheetByName("Req. Form");
var downTime = xx.getSheetByName("Downtime List");

var values2 = [[fromXX.getRange("E7").getValue(),
formXX.getRange("E9").getValue(),
formXX.getRange("E11").getValue(),
formXX.getRange("E17").getValue(),
formXX.getRange("E19").getValue(),
formXX.getRange("E21").getValue()]];

var blankRow2=downTime.getLastRow() 1;

if(downTime.getRange("E15").isChecked()==true){
downTime.getRange(blankRow2,1).setValue(formXX.getRange("E9").getValue());  
downTime.getRange(blankRow2,2).setValue(formXX.getRange("E7").getValue());
downTime.getRange(blankRow2,5).setValue(formXX.getRange("E11").getValue());
downTime.getRange(blankRow2,9).setValue(formXX.getRange("E19").getValue());
downTime.getRange(blankRow2,8).setValue(formXX.getRange("E21").getValue());
downTime.getRange(blankRow2,7).setValue(formXX.getRange("E17").getValue());

}

}

CodePudding user response：

This works for me:

function submitData2() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Sheet1");
  const sh2 = ss.getSheetByName("Sheet2");
  const vs = [sh1.getRange("E1").getValue(),
  sh1.getRange("E2").getValue(),
  sh1.getRange("E3").getValue(),
  sh1.getRange("E4").getValue(),
  sh1.getRange("E5").getValue(),
  sh1.getRange("E6").getValue()];
  const blankRow2 = sh2.getLastRow()   1;
  if (sh2.getRange("E1").isChecked()) {
  //if (sh2.getRange("E1").isChecked() == true) {//this works also
    sh2.getRange(blankRow2, 1).setValue(vs[0]);
    sh2.getRange(blankRow2, 2).setValue(vs[1]);
    sh2.getRange(blankRow2, 5).setValue(vs[2]);
    sh2.getRange(blankRow2, 9).setValue(vs[3]);
    sh2.getRange(blankRow2, 8).setValue(vs[4]);
    sh2.getRange(blankRow2, 7).setValue(vs[5]);
  }
}

Sheet 1:

Sheet2:

The true value of my checkboxes is "TRUE" the false value is "FALSE"

CodePudding user response：

The answer was in the "if" statement:

The if statement was: if(downtime.getRange("E15").isChecked()==true)

Answer is: if(formSS.getRange("E15").isChecked()==true)

I was calling from the incorrect form which always yielded false.