Please, I am requesting your help to finish a sidebar with a form in GSheet.
Actual statut :
Update Value with submited checkbox by sidebar form (50 checkbox) = 100% working
Checkbox "checked" at sidebar open if value = 1 : All working fine for 4 checkbox, but not working for 5 and more = 50%
Please Help requested :
Check code if it can be cleaned with : while loop or something like that
And the most important, make the checkbox "checked" value working for around 50 checkbox
Please find the Script code in HTML file :
<script>
google.script.run.withSuccessHandler(function(returnCheck1) {
document.getElementById("check1").innerHTML = returnCheck1;
if (document.getElementById("check1").innerHTML=="checked"){document.getElementById("cbA1").checked = true;}
}).getOneCellData();
google.script.run.withSuccessHandler(function(returnCheck2) {
document.getElementById("check2").innerHTML = returnCheck2;
if (document.getElementById("check2").innerHTML=="checked"){document.getElementById("cbA2").checked = true;}
}).getOneCellData();
google.script.run.withSuccessHandler(function(returnCheck3) {
document.getElementById("check3").innerHTML = returnCheck3;
if (document.getElementById("check3").innerHTML=="checked"){document.getElementById("cbA3").checked = true;}
}).getOneCellData();
google.script.run.withSuccessHandler(function(returnCheck4) {
document.getElementById("check4").innerHTML = returnCheck4;
if (document.getElementById("check4").innerHTML=="checked"){document.getElementById("cbA4").checked = true;}
}).getOneCellData();
google.script.run.withSuccessHandler(function(returnCheck5) {
document.getElementById("check5").innerHTML = returnCheck5;
if (document.getElementById("check5").innerHTML=="checked"){document.getElementById("cbA5").checked = true;}
}).getOneCellData();
google.script.run.withSuccessHandler(function(returnCheck6) {
document.getElementById("check6").innerHTML = returnCheck6;
if (document.getElementById("check6").innerHTML=="checked"){document.getElementById("cbA5a").checked = true;}
}).getOneCellData();
<!-- Up to 50 checkbox -->
function submitForm() {
google.script.run.appendRowFromPART1Submit(document.getElementById("formulaire"));
document.getElementById("form").style.display = "none";
document.getElementById("thanks").style.display = "block";
}
</script>
Please find the HTML code :
<body>
<div id="form">
<form id="formulaire">
<input type="button" id="form" name="form" value="Save" onclick="submitForm();">
<p><b>Title</b></p><br>
<input type="checkbox" id="cbA1" name="cbA1" value="1">
<label for="A1">A1 - </label><textarea id="check1" style="display: none;"></textarea><hr>
<input type="checkbox" id="cbA2" name="cbA2" value="1">
<label for="A2">A2 - </label><textarea id="check2" style="display: none;"></textarea><hr>
<input type="checkbox" id="cbA3" name="cbA3" value="1">
<label for="A3">A3 - </label><textarea id="check3" style="display: none;"></textarea><hr>
<input type="checkbox" id="cbA4" name="cbA4" value="1">
<label for="A4">A4 - </label><textarea id="check4" style="display: none;"></textarea><hr>
<input type="checkbox" id="cbA5" name="cbA5" value="1">
<label for="A5">A5 - </label><textarea id="check5" style="display: none;"></textarea><hr>
<input type="checkbox" id="cbA5a" name="cbA5a" value="1">
<label for="A5a">A5a - </label><textarea id="check6" style="display: none;"></textarea><hr>
<!-- Up to 50 checkbox -->
</form>
</div>
<div id="thanks" style="display: none;"><br>Thanks.</div>
</body>
Please find the Apps Script code :
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('=> Menu <=')
.addItem('Suivi', 'suivi')
.addToUi();
}
function suivi() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('Valeurs');
//var sheet = SpreadsheetApp.getActiveSheet();
var html = HtmlService.createHtmlOutputFromFile('suivi').setTitle('Suivi');
SpreadsheetApp.getUi().showSidebar(html);
}
function getOneCellData() {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var sheetss = ss.getSheetByName("Valeurs");
var data1 = sheetss.getRange("H1").getValue();
if (data1 == "1") { check1 = "checked"; } else { check1 =""; }
var data2 = sheetss.getRange("H2").getValue();
if (data2 == "1") { check2 = "checked"; } else { check2 =""; }
var data3 = sheetss.getRange("H3").getValue();
if (data3 == "1") { check3 = "checked"; } else { check3 =""; }
var data4 = sheetss.getRange("H4").getValue();
if (data4 == "1") { check4 = "checked"; } else { check4 =""; }
var data5 = sheetss.getRange("H5").getValue();
if (data5 == "1") { check5 = "checked"; } else { check5 =""; }
var data6 = sheetss.getRange("H6").getValue();
if (data6 == "1") { check6 = "checked"; } else { check6 =""; }
// Up to 50 again
return check1,check2,check3,check4,check5,check6;
// Up to 50 again
}
function appendRowFromPART1Submit(form) {
var spreadsheet = SpreadsheetApp.getActive();
var sheet2 = spreadsheet.getSheetByName('Valeurs');
// DATA FORM
var email = Session.getActiveUser().getEmail();
if (form.cbA1 != "1"){var rowcbA1 = "0";}else{var rowcbA1 = form.cbA1;}
var data1 = sheet2.getRange("H1").getValue();
if (data1 != rowcbA1) {sheet2.getRange("I1").setValue(email);}
sheet2.getRange("H1").setValue(rowcbA1);
if (form.cbA2 != "1"){var rowcbA2 = "0";}else{var rowcbA2 = form.cbA2;}
var data2 = sheet2.getRange("H2").getValue();
if (data2 != rowcbA2) {sheet2.getRange("I2").setValue(email);}
sheet2.getRange("H2").setValue(rowcbA2);
if (form.cbA3 != "1"){var rowcbA3 = "0";}else{var rowcbA3 = form.cbA3;}
var data3 = sheet2.getRange("H3").getValue();
if (data3 != rowcbA3) {sheet2.getRange("I3").setValue(email);}
sheet2.getRange("H3").setValue(rowcbA3);
if (form.cbA4 != "1"){var rowcbA4 = "0";}else{var rowcbA4 = form.cbA4;}
var data4 = sheet2.getRange("H4").getValue();
if (data4 != rowcbA4) {sheet2.getRange("I4").setValue(email);}
sheet2.getRange("H4").setValue(rowcbA4);
if (form.cbA5 != "1"){var rowcbA5 = "0";}else{var rowcbA5 = form.cbA5;}
var data5 = sheet2.getRange("H5").getValue();
if (data5 != rowcbA5) {sheet2.getRange("I5").setValue(email);}
sheet2.getRange("H5").setValue(rowcbA5);
if (form.cbA5a != "1"){var rowcbA5a = "0";}else{var rowcbA5a = form.cbA5a;}
var data6 = sheet2.getRange("H6").getValue();
if (data6 != rowcbA5a) {sheet2.getRange("I6").setValue(email);}
// Up to 50 again
All this come from my searchs and been adapted for this project.
I not get so much knowledge on script, some in HTML and PHP to help get the logic of coding.
Thanks a lot for your time and help !!
Aucun commentaire:
Enregistrer un commentaire