Skip to content
Advertisement

How to add conditionals to user input in App Scripts with while loops?

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:

enter image description here

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:

enter image description here

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement