Creating Google Form from Sheet in App Script using GO_TO_PAGE

Question

I have a sheet with a column of group names, and things they need to report that they did or didn't do. I'm having trouble creating a script that would get the column of names to make a multiple choice question AND direct to the correct next page based on the answer.

Names       Option1       Option2       Option 3       Option4
Frank       Something1    Something2    Something3
Hank        AnotherThing  AnotherThing1 AnotherThing3  Anotherthing4
Bob         Foo           Bar

First question is Multiple Choice with column 1 with the answers. Depending on the answer I need it to direct to a new section with the other columns with content on the same row as the chosen answer. Choose Frank and submit to be sent to a Checkbox question with 3 options.

Anyone have the faintest on how to do this? I can make the questions manually but when it comes to directing the GO_TO_PAGE part I can't get that to work. Thanks in advance!

Cooper Cooper · Accepted Answer · 2017-09-29T22:01:32

Here's a possible approach to your problem. I'm using a sidebar which could just as easily be a dialog if you want to make it wider and could ultimately be a webapp.

Take a look at the dialog or sidebar and you'll see that you get a drop down list of name from your spreadsheet and when you select a name and click on GetToDoList then you get a list of to do items from your spreadsheet along with buttons that have links to different sheets that I've included in your spreadsheet.

Code.gs:

function getNames()
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('ToDoList');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var names=['Select Name'];
  for(var i=1;i<vA.length;i++)
  {
    names.push(vA[i][0]);
    Logger.log('%s',vA[i][0]);
  }
   return names;
}

function loadToDoSideBar()
{
  var userInterface=HtmlService.createHtmlOutputFromFile('todolist').setTitle('ToDoLists');
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

function getToDoList(name)
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('ToDoList');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var hl='';
  for(var i=0;i<vA.length;i++)
  {
    if(vA[i][0]==name)
    {
      for(var j=1;j<vA[i].length;j+=2)
      {
        if(vA[i][j] && vA[i][j+1])
        {
          hl+=Utilities.formatString('<br /><label>%s<input class="btn1" type="button" value="%s" onClick="goToSheet(\'%s\')" /></label>',vA[i][j],'Go to' + vA[i][j+1],vA[i][j+1]);
        }
      }
    }
  }
  return hl;
}

function goToSheet(name)
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName(name).activate();
}

todolist.html

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
  <script>
    $(function() {
        $('#txt1').val('');
        google.script.run
          .withSuccessHandler(updateSelect)
          .getNames();
      });
    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]);
      }
    }
    function getToDoList()
    {
      var name=$('#sel1').val();
      google.script.run
        .withSuccessHandler(updateToDoList)
        .getToDoList(name)
    }
    function updateToDoList(hl)
    {
      document.getElementById('viewlist').innerHTML=hl;
    }
    function goToSheet(name)
    {
      google.script.run.goToSheet(name);
    }
    console.log('My Code');  
  </script>
  <style>
  div.scroll{background-color: #ffffff;width: 250px;height:100%;display:block;margin:0 0 0 5px;padding:0 0 0 10px;}
  #sel1 option{width:250px;}
  .type{color:#ff0000;font-weight:500;}
  .date{color:#080b58;font-weight:500;}
  .note{font-weight:500;}
  .btn1{margin:10px 10px 10px 10px;vertical-align:25%;}
  body{margin:5px 5px 5px 10px;width:275px;}
  </style>
  </head>
  <body>
    <select id="sel1" style="width:125px;height:35px;margin:10px 0 10px 0;">
      <option value="" selected></option>
   </select>
   <input type="button" value="GetToDoList" onClick="getToDoList()" />
   <br /><div id="viewlist" class="scroll"></div>
  </body>
</html>

Here's what I did with your data. I just added another column between each value for the Locations or Links (not sure what you want). But the code shows you how you can incorporate links to different sheets into the solution.

And this is what the dialog looks like:

Creating Google Form from Sheet in App Script using GO_TO_PAGE

1 Answers