Populating radio buttons from google sheets into HTML

Question

I have a google sheets field that will have a yes or no value. When I render an HTML form that I use I want to populate the "checked" value on the HTML input tag based on what is in the google sheets but cannot seem to get the HTML and script to work so any help is greatly appreciated.

Here is what I have :

In the google app script I have a function to show the HTML form

var html = HtmlService.createHtmlOutputFromFile("addUserLeadScore").setWidth(500).setHeight(550);

In the HTML file I load the form and call the getData function

window.onload = function LoadFormWithData()
        {
          google.script.run.withSuccessHandler(showData).getData();
        }

Here is the getData

function getData(){
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getSheetByName("Template");
    var data = sheet.getRange(iRow, iCol, iNumRows, iNumCols).getValues(); 
    return data;
}

And here is the HTML to build the radio buttons

<div id="firstAndLast"></div>
<td id="firstAndLastTd">
    <center>Prospect provided a first <b>AND</b> last name </center>           
    <input type="radio" name="firstAndLast" value="Yes"> Yes <br>
    <input type="radio" name="firstAndLast" value="No"> No <br>
</td>

But how do I do it so that if data[0][1] = "yes" :

<input type="radio" name="firstAndLast" value="Yes" checked>

instead of

<input type="radio" name="firstAndLast" value="Yes" checked>

"Cannot seem to get something to work" isn't a question. Please be specific and include as many details as possible about the results you are seeing, including error messages. — Anton Dementiev, Jan 01 '20 at 13:45
You question is how to do `html `instead of `html `? This is exactly the same. Can you explain better what you are trying to do and when? You want to change the preselected choice of the radio button depending on your spreadsheet data? — ziganotschka, Jan 01 '20 at 16:11
@AntonDementiev - As in I dont know how to write the code that will pass the state from XLS to the HTML so if the Google Sheet has a value of "Yes" then the radio button will be checked when the HTML is rendered and if the Value is "No" the radio button will not be checked. — DallasDavidow, Jan 01 '20 at 20:53
@ziganotschka your right - sorry. What I meant was :But how do I do it so that if data[0][1] = "yes" : instead of — DallasDavidow, Jan 01 '20 at 20:53

Cooper · Answer 1 · 2020-01-01T20:23:08.727

Here's a fun checkbox dialog example:

This function produces checkboxes based upon settings in a spreadsheet. Here's an image of the spreadsheet:

Here's the GS code:

function runMe() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet5');
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  var vA=rg.getValues();
  var none=true;
  var html='<html><head>';
  html+='<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"><script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>';
  html+='<style>input(margin:5px 5px 5px 0;}</style>';
  html+='</head>';
  html+='<body>';
  html+='<div id="mycheck">';
  html+='<form>';
  vA.forEach(function(r,i){
    if(r[0]) {
      html+=Utilities.formatString('<br /><input type="checkbox" id="%s" name="mychecks" value="%s" /><label for="%s">%s</label>',r[2],r[4],r[2],r[3]);
      none=false
    }
  });
  html+='<br /><input type="button" value="Submit" onClick="submitForm(this.parentNode);" /></form>';
  html+='</div>';
  html+='<script>';
  html+='function submitForm(frmData){google.script.run.withSuccessHandler(function(msg){$("#mycheck").append(msg);google.script.run.withSuccessHandler(function(){google.script.host.close();}).delay();}).processForm(frmData);}';
  html+='</script>';
  html+='</body></html>';
  if(none) {
    SpreadsheetApp.getUi().alert("No checkboxes enabled");
  }else{
    var userInterface=HtmlService.createHtmlOutput(html);
    SpreadsheetApp.getUi().showModelessDialog(userInterface, "My Checkboxes");
  }
}

function processForm(obj) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet5');
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()-1);
  var crg=sh.getRange(2,6,sh.getLastRow()-1,1);
  var vA=rg.getValues();
  var cvA=crg.getValues();
  var ts=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"yyyyMMdd HH:mm:ss" );
  Logger.log(JSON.stringify(obj));
  if(obj.mychecks) {  
    vA.forEach(function(r,i){
      if(r[0]) {
        if(obj.mychecks) {
          var idx=obj.mychecks.indexOf(r[4]);
          cvA[i][0]=(idx>-1)?"CHECKED":"UNCHECKED";
        }
      }else{
        cvA[i][0]="UNUSED";
      }
    });
    crg.setValues(cvA);
    return Utilities.formatString('<br />%s - Got it!',ts)
  }else{
    vA.forEach(function(r,i){
      cvA[i][0]=(r[0])?"UNCHECKED":"UNUSED";
    });
    crg.setValues(cvA);
    return Utilities.formatString('<br />%s - Got it! No checkboxes checked.',ts)
  }

}

function delay() {
  Utilities.sleep(3000);
}

This is the dialog in action:

Thats a great piece of code and I will save it for next time but I dont want to have to build the HTML each time in code as its rather a big HTML. In your example what I am looking to do is if the HTML was a predefined file and Label 10 was checked in Google Sheets, I would want the HTML to load with Label 10 checked and the same thing if it wasn't checked I would like the radio button to load not checked - is that possible ? — DallasDavidow, Jan 01 '20 at 20:58
Right - but how do you make it if the value in google Sheets is "Checked" when you load the HTML and if the value is blank in Google Sheets when you load the HTML ? — DallasDavidow, Jan 01 '20 at 21:04
A couple of ways: 1 - using [templated html](https://developers.google.com/apps-script/guides/html/templates) or 2 - using JavaScript `window.onload=function() {document.getElementById("checkbox").checked = true; }` or 3 - using JQuery `$(function(){$("#checkbox").prop("checked", true);});` [examples](https://stackoverflow.com/a/8206573/7215091) — Cooper, Jan 01 '20 at 21:10

Populating radio buttons from google sheets into HTML

1 Answers1