Grouping checkboxes in Google Apps Script tied to Google Sheets

Question

I created a form that has a set of checkboxes that populates from a column in the google sheet. So if the entries in the column changes the form checkboxes will too.

My question relates to obtaining the selections and the manner in which the data is retrieved.

When a submit button on the form is clicked it calls a function: google.script.run.test(document.forms[0])

My test function appends the info to a table in sheets as well as Logs using Logger.log().

document.forms[0] is of the form: {=[Level 1, Level 2, Level 3, Level 4], name=test 4}

My first question is:
How do I name the group of checkboxes so the array in the object document.forms[0] - so that I can call it with document.forms[0]["SOME-NAME"]?
Right now I have to call it with document.forms[0][""] which makes me very uncomfortable.

My second question is a little weirder to explain. I discovered that if the descriptions that populate the checkboxes from the google sheet are two word descriptions - then the form returns an array with selections. eg. from Logger {=[Level 1, Level 2, Level 3, Level 4], name=test 4}

But if the descriptions are single word the form returns each selection as its own entry eg. from Logger {name=test 6, Three=Three, One=One, Two=Two}

How do I make sure that the form returns an array instead of individual selections?

The sheet that I created to test this and demonstrate is here:

https://docs.google.com/spreadsheets/d/1vzg4VMgDbqz0ImCH_V5-1vF5qY0UHVGHK6JJfCVrADM/edit?usp=sharing

Any help would be appreciated!!

Html for the form :
<p><b>Testing check boxes and retrieving info</b> </p>
<hr>
<div>
  <form>
    <table>
      <tr>
        <td>Name:  </td><td><input id="name" name="name" type ="text" /></td>
      </tr>      
      <tr>      
          <td>Choices: </td>
          <td><fieldset  class="group" id="choices" name="choices" >
             --CHANGE--           
          </fieldset></td>          
      </tr>      
      <tr>
        <td>Submit Information: </td><td> <input onclick="formSubmit()" type="button" value="Submit" /></td>
      </tr>      
    </table>
    <hr>
    <table>
      <tr>
        <td>Cancel Transaction: </td><td>
          <input onclick="google.script.host.close()" type="button" value="Cancel" /></td>
       </tr>
     </table>    
  </form>
</div>

<script>
  function formSubmit(){
    google.script.run.test(document.forms[0]);
    google.script.host.close();
  }  
</script>


Function in Code.gs that opens the form:
// Set up the checkbox list and update html code from html file and open form
function openForm(){
  // get Classes to populate the check box list and create html string
  var values = getClasses();
  var html = ""
  for(var i=1; i < values.length; i++){
         html = html + '<input type="checkbox" label="classList" name="' + values[i][0] + '" value="' + values[i][0] + '"> '+values[i][0] + '<br>';
  }

  // replace place holder in html file with checkbox code
  var f1 = HtmlService.createHtmlOutputFromFile('form1').getContent();
  f1 = f1.replace("--CHANGE--",html);

  // open spread sheet and then open form in sheets
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var f2 = HtmlService.createHtmlOutput(f1);
  ss.show(f2); 
}

Function that is called after submit button pressed:
// Get the info from the form and do something with it
function test(info){
  Logger.log(info);
  var classList = info[''];

  if (Array.isArray(classList)){ 
    classList = classList.toString();
  } 

  var data = [info['name'], classList];

  var ss = SpreadsheetApp.getActive().getSheetByName("Test");
  ss.appendRow(data);

}

Thank you for creating the sheet. Can you provide what the form looks like? And the code you are using? — Alan Wells
All the code is accessible through the "Tools"-->"Script editor" menu within the Google Sheet. And the form can be opened through the "Test Check Box" -->"Open Form" menu also on the Google Sheet. I was not sure that it was appropriate to add all the code to the post itself. @SandyGood should I do that? — Hitesh Lala
The 'Script Editor' option is "greyed out". Can't get there. You may have shared it View Only? It's always preferable to post at least some code. — Alan Wells
@SandyGood HI, Just changed it to can edit: docs.google.com/spreadsheets/d/… — Hitesh Lala

Alan Wells Alan Wells · Accepted Answer · 2015-07-26T04:00:21

To get the check box selections passed as an array, you will need to process the data with custom code before passing it to the server. I would get all elements by tag name. In other words, get all the input elements.

<script>

  function formSubmit(){
    console.log('document.forms[0] ' + document.forms[0]);

    var choicesElmt = document.getElementById("choices");
    var allInputElements = choicesElmt.getElementsByTagName("input");
    var isChecked=false,
        arrayOfChecked=[],
        thisElmt;

    for (var key in allInputElements) {
      console.log('key is: ' + key)

      thisElmt = allInputElements[key];

      console.log('thisElmt is: ' + thisElmt)

      if (thisElmt!==undefined) {
        isChecked = thisElmt.checked;

      };

      console.log('isChecked: ' + isChecked)
      if (isChecked===true) {
        arrayOfChecked.push(key);

      };
    };

    console.log('arrayOfChecked: ' + arrayOfChecked);

    google.script.run.test(arrayOfChecked);

    //google.script.host.close();
  }

</script>

Right now your code is dynamically assigning a name to each check box that is the value in ColumnA of the spreadsheet. Apps Script changes the form object, and the only way you can get the values out is with the name. If there is no check, or value in an input, no value is passed in the form object. On the server side, you don't need to use the zero index on the object. Just use "dot" and the name.

Logger.log('info.One: ' + info.One);

If check box two was never checked, then info.Two will be undefined.

You can loop through the object in the .gs server code to see everything in the object:

for (var key in info) {
  Logger.log('key is: ' + key)
  Logger.log('value is: ' + info[key])

};

Grouping checkboxes in Google Apps Script tied to Google Sheets

2 Answers