HtmlService: Passing array from google sheet to html service and passing form array back error

Question

I have a spreadsheet where I am taking unique values of 2 columns and passing that array to the HtmlService. The unique values are then displayed as check boxes. I can check the boxes, but when I submit I get an error message: Unsafe JavaScript attempt to initiate navigation for frame with URL... The frame attempting navigation of the top-level window is sandboxed, but the flag of 'allow-top-navigation' or 'allow-top-navigation-by-user-activation' is not set.

I've seen some other examples where the answer was to set HtmlService.Sandbo‌xMode.NATIVE. I've done this, but I am still getting that error.

What is working: Getting the unique array passed to the HtmlService and creating checkboxes. On form submit, I can see my selected values all captured on form submit (in a url of a blank page).

I will eventually need to have the checked box values sent back to my .gs code as an array where further processing will occur, but I am currently stuck on this problem.

Code updated to working code for reference: (use this template for passing column arrays (unique values only) to the HtmlService in order to create a checkbox form and to send the checked boxes back to the .gs code as an array)

code.gs

function bugPieChart() {
   getVersionArray();
   openDialog();
   getCheckedValues();
   // rest of spreadsheet code here
}  
function getVersionArray() {
  // I'm making a unique array of 2 columns here. Do whatever array building you like in this part of the code. This gets sent as checkboxes eventually.
  var ss = SpreadsheetApp.getActive();
  var valuesR = ss.getSheetByName("report").getRange('R1:R').getValues();
  var valuesS = ss.getSheetByName("report").getRange('S1:S').getValues();

  var versionRSArray = [];

  for (var i = 0; i < valuesR.length; i++) {
    versionRSArray.push(valuesR[i][0]);
  }

  for (var i = 0; i < valuesS.length; i++) {
    versionRSArray.push(valuesS[i][0]);
  }

  versionRSArray.sort();

  var uniqueArray = [];

  uniqueArray.push(versionRSArray[0]);

  for (var i in versionRSArray ) {  
    if((uniqueArray[uniqueArray.length-1]!=versionRSArray[i]) && (versionRSArray[i] !== "")) {
      uniqueArray.push(versionRSArray[i]);
    }
  }
  return uniqueArray;
}

function openDialog() {    
  var html = HtmlService.createHtmlOutputFromFile('index').setSandboxMod‌e(HtmlService.Sandbo‌xMode.NATIVE);
  SpreadsheetApp.getUi().showModalDialog(html, 'Dialog title'); 
}

function getCheckedValues(checkedValues) {
  Logger.log(checkedValues); // working WIN!!!
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>
    <script>
      $(function() {
        google.script.run.withSuccessHandler(buildOptionsList)
            .getVersionArray();    
      });
      function buildOptionsList(uniqueArray) {
         var div = document.getElementById('optionList');
         for (var i = 0; i < uniqueArray.length; i++) {
            var checkbox = document.createElement('input');
            checkbox.type = "checkbox";
            checkbox.name =  "checkbox";
            checkbox.value = uniqueArray[i];
            checkbox.id = uniqueArray[i]; 
            var label = document.createElement('label');
            label.appendChild(document.createTextNode(uniqueArray[i]));

            div.appendChild(checkbox);
            div.appendChild(label);
            var linebreak = document.createElement("br");
            div.appendChild(linebreak);            
         }
         setCheckedValues();
      }
      function setCheckedValues() {
        $("#checkBoxForm").submit(function (e) {
          e.preventDefault();
          var checkedValues = [];
          $('input[type="checkbox"]:checked').each(function () {   
             checkedValues.push($(this).val());
           });
           console.log(checkedValues);
           google.script.run.withSuccessHandler(sendCheckedValues)
           .getCheckedValues(checkedValues);
        });
      }

     function sendCheckedValues(checkedValues) {
       console.log('called sendCheckedValues');
       return checkedValues;
     }
    </script>
  </head>
  <body>
    <form id="checkBoxForm">
      <div id="optionList"></div>
      <input type="submit" value="Submit" />
    </form> 
    <input type="button" value="Abort" onclick="google.script.host.close()" />
  </body>
</html>

Cameron Roberts Cameron Roberts · Accepted Answer · 2017-09-21T17:17:53

The error you are seeing is due to the HTML form submission action.

The best way to handle this is to prevent the default form submit action on the form and then submit your data to Apps Script using google.script.run.

You can stop the default action by placing e.preventDefault(); in your submit() function, then use google.script.run to run your server-side Apps Script function to handle the form submission.

There is a full example in the documentation: https://developers.google.com/apps-script/guides/html/communication#forms

  function handleFormSubmit(formObject) {
    google.script.run.withSuccessHandler(updateUrl).processForm(formObject);
  }

Where 'updateUrl' would be a client-side javascript function, and 'processForm' is a serve-side Apps Script function.

HtmlService: Passing array from google sheet to html service and passing form array back error

1 Answers