I made a selectBox which had its range of values from a Google Sheet Column. I also want to take an Integer input value from the user and then write this value in a specific cell according to option taken from selectBox. The html link does not show the integer response box. Is it possible to do the above plan in a while loop? Would appreciate any ideas and correction of code
function doGet() { var ap = SpreadsheetApp.openByUrl("Gsheet URL here"); var ui = SpreadsheetApp.getUi(); var user = ui.prompt("Put down a number"); var result = result.getSelectedButton(); var sheet = ap.getSheetByName("lv"); var values = sheet.getRange("A2:A10").getValues(); var options = values.map(function(row) { #To show show the selected option?? var item = options.getSelecteditem(); if (item === A3) { var cell = SpreadsheetApp.getActiveSheet().getActiveCell(); var a1 = cell.getA3Notation(); var val = cell.getValue(); SpreadsheetApp.getUi().alert("Ur value is "+a1+" value is "+val); } { return '<option value="' + row[0] + '">' + row[0] + '</option>'; }); var html = '<form onSubmit="handleSubmit(this)"> Type of Cuisine' + options.join('') + '</select></form>'; return HtmlService.createHtmlOutput(html); }
Advertisement
Answer
Using an Html Dialog to Control User Inputs
Not sure what you wanted so here’s a complete example I whipped up for you.
Code.gs:
function processInput(obj) { Logger.log(JSON.stringify(obj)); const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName('Sheet0'); const [min,max,locs] = sh.getRange('B1:B3').getValues().flat(); Logger.log('min: %s max: %s locs: %s',min,max,locs) const lA = locs.split(','); if(obj.int > max) { obj.msg = "Too High Try Again"; return obj; } else if (obj.int < min) { obj.msg = "To Low Try Again"; return obj; } else if (!~lA.indexOf(obj.loc)) { obj.msg = "Invalid Location"; return obj; } else { sh.getRange(obj.loc).setValue(obj.int); obj.msg = "Complete"; return obj; } }
Following function Launches the dialog:
function launchInputDialog() { SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah1'),"Enter Input"); }
html:
<!DOCTYPE html> <html> <head> </head> <style>input {margin: 2px 5px 2px 0;}</style> <body> <form> <input type="text" id="in1" placeholder="Enter an integer" /> <br /><input type="text" id="in2" placeholder="Enter a location" /> <br /><input type="button" value="Process" onClick="processinput();" /> </form> <div id="msg"></div> <script> function processinput() { document.getElementById("msg").innerHTML = ''; let v1 = parseInt(document.getElementById('in1').value); let v2 = document.getElementById('in2').value; let obj = {int:v1,loc:v2,msg:''}; google.script.run .withSuccessHandler(robj => { console.log(JSON.stringify(robj)) if(robj.msg == "Complete") { document.getElementById("msg").innerHTML = `Value: ${robj.int} Location: ${robj.loc} Try Again`; document.getElementById("in1").value = ''; document.getElementById("in2").value = ''; } else { document.getElementById("msg").innerHTML = robj.msg; } }) .processInput(obj); } </script> </body> </html>
Short Demo:
This version uses a <select>
tag to allow the user to determine where the data will be loaded
GS:
function doPost(e) { Logger.log(e.postData.contents); Logger.log(e.postData.type); const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName("Sheet1"); let data = JSON.parse(e.postData.contents); sh.getRange(data.loc).setValue(data.id) } function sendData(obj) { const url = ScriptApp.getService().getUrl(); const params = { "contentType": "application/json", "payload": JSON.stringify(obj), "muteHttpExceptions": true, "method": "post", "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } }; UrlFetchApp.fetch(url, params); } function displayError(msg) { SpreadsheetApp.getUi().alert(msg); } function launchMyDialog() { SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah1'), 'My Dialog'); } function getSelectOptions() { var ss = SpreadsheetApp.getActive(); var sh = ss.getSheetByName('Options'); var rg = sh.getDataRange(); var vA = rg.getValues(); var options = []; for (var i = 0; i < vA.length; i++) { options.push(vA[i][0]); } return vA; }
HTML:
<!DOCTYPE html> <html> <head> <base target="_top"> </head> <body> <form> <input type="text" id="txt1" name="id" placeholder="Enter Numbers only"/> <select id="sel1" name="loc"></select> <input type="button" value="submit" onClick="processForm(this.parentNode);" /> </form> <script> function processForm(obj) { console.log(obj.id.value); if(obj.id.value.match(/[A-Za-z]/)) { google.script.run.displayError("Invalid Characters Found in id field"); } else { google.script.run.sendData(obj); } } window.onload = function() { google.script.run .withSuccessHandler(updateSelect) .getSelectOptions(); } function updateSelect(vA) { var select = document.getElementById("sel1"); select.options.length = 0; for(var i=0;i<vA.length;i++) { select.options[i] = new Option(vA[i],vA[i]); } } </script> </body> </html>
Demo: