How to auto populate form options based on a column in the attached spreadsheet

Question

I created a new google sheet for collecting user information. In my sheet script editor, I created this form: https://script.google.com/macros/s/AKfycbz6uGmXfSiXg4lYseeX0IN7Qv_9eM4eN9knUtBm5Co/exec

The above form has a multiple choice question that I need to auto populate using a specific column in the attached spreadsheet

I used he below code, but for some reason the update function is not reading the form options or something

When I run the update form function, it gives "TypeError: Cannot find function getItemById in object"

Is there any solution to update the form options by the sheet column

    function doGet() {
    return HtmlService.createTemplateFromFile('form.html')
        .evaluate() // evaluate MUST come before setting the Sandbox mode
        .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
    }


    function updateForm(){
    // call your form and connect to the drop-down item
    var form = ("https://script.google.com/macros/s/AKfycbz6uGmXfSiXg4lYseeX0IN7Qv_9eM4eN9knUtBm5Co/exec");

    var namesList = form.getItemById("890244015").asListItem();


    // identify the sheet where the data resides needed to populate the drop-down
    var ss = SpreadsheetApp.getActive();
    var names = ss.getSheetByName("CHOICES");

    // grab the values in the first column of the sheet - use 2 to skip header row 
    var namesValues = names.getRange(2, 16, names.getMaxRows() - 1).getValues();

    var studentNames = [];

    // convert the array ignoring empty cells
    for(var i = 0; i < namesValues.length; i++)    
    if(namesValues[i][0] != "")
      studentNames[i] = namesValues[i][0];

     // populate the drop-down with the array data
     namesList.setChoiceValues(studentNames);

     }

This is the form.html code:

<!DOCTYPE html>
<html>
<body>

<style>

body {
padding: 0px 0px 0px 0px;
margin: 0px 0px 0px 0px;
background-color: white;
}

</style>


<link href='/static/forms/client/css/3145455273- 
mobile_formview_st_ltr.css' type='text/css' rel='stylesheet' media='screen 
and 
(max-device-width: 721px)'>



<div class="ss-form-container">

<div class="ss-header-image-container"><div class="ss-header-image-image"> 
<div class="ss-header-image-sizer"></div></div></div>

<div class="ss-top-of-page"><div class="ss-form-heading"><h1 class="ss- 
form-title" dir="ltr">Test</h1>
<div class="ss-form-desc ss-no-ignore-whitespace" dir="ltr">WELD DATE 
00</div>

<div class="ss-required-asterisk" aria-hidden="true" id="Required">* 
Required</div></div></div>

<br><br>

<div class="ss-form">


<script type="text/javascript">var submitted=false;</script>
<iframe name="hidden_iframe" id="hidden_iframe" style="display:none;"     
onload="if(submitted) 
{window.location='https://sites.google.com/site/formredirection/';}"> 
</iframe>
  <form action="https://docs.google.com/forms/d/e/formid/formResponse" 
  method="post" target="hidden_iframe" 
 onsubmit="submitted=true;">


<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-select"><div class="ss- 
 form-entry">
<label class="ss-q-item-label" for="entry_890244015"><div class="ss-q- 
title">JOINT
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)"> 
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>



<select name="entry.890244015" id="entry_890244015" aria-label="JOINT  " 
aria-required="true" required=""><option value=""></option>
<option value="OPTION 01">OPTION 01</option> <option value="OPTION 
02">OPTION 02</option> <option value="OPTION 03">OPTION 03</option> 
<option value="OPTION 04">OPTION 04</option></select>
</div></div></div>


<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-radio"><div class="ss- 
form-entry">
<label class="ss-q-item-label" for="entry_117174731"><div class="ss-q- 
title">REP NO.
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)"> 
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>


<ul class="ss-choices" role="radiogroup" aria-label="REP NO.  "><li 
class="ss-choice-item">
<label><span class="ss-choice-item-control goog-inline-block"><input 
type="radio" name="entry.735659431" value="" id="group_735659431_1" 
role="radio" class="ss-q-radio" aria-label="" required="" aria- 
required="true"></span>
<span class="ss-choice-label"></span>
</label></li></ul>
<div class="error-message" id="117174731_errorMessage"></div></div></div> 
</div>



<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-date"><div class="ss- 
form-entry">
<label class="ss-q-item-label" for="entry_1715668372"><div class="ss-q- 
title">WELD DATE
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)"> 
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>

<input type="date" name="entry.1715668372" value="" class="ss-q-date" 
dir="auto" id="entry_1715668372" aria-label="WELD DATE  " aria- 
required="true" required="">
</div></div></div>



<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-select"><div class="ss- 
form-entry">
<label class="ss-q-item-label" for="entry_1048274308"><div class="ss-q- 
title">WELDER
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)"> 
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>

<select name="entry.1048274308" id="entry_1048274308" aria-label="WELDER  
" aria-required="true" required=""><option value=""></option>
<option value="WR 01">WR 01</option> <option value="WR 02">WR 02</option> 
<option value="WR 03">WR 03</option> <option value="WR 04">WR 04</option> 
</select>
</div></div></div>


<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-select"><div class="ss- 
form-entry">
<label class="ss-q-item-label" for="entry_1712008875"><div class="ss-q- 
title">WPS
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)"> 
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>

<select name="entry.1712008875" id="entry_1712008875" aria-label="WPS  " 
aria-required="true" required=""><option value=""></option>
<option value="WPS 01">WPS 01</option> <option value="WPS 02">WPS 
02</option> <option value="WPS 03">WPS 03</option> <option value="WPS 
04">WPS 04</option></select>
</div></div></div>


<input type="hidden" name="draftResponse" value=" 
[null,null,&quot;-8333688893315580231&quot;]
">
<input type="hidden" name="pageHistory" value="0">



<input type="hidden" name="fbzx" value="-8333688893315580231">
<div class="ss-send-email-receipt" style="margin-bottom: 4px;" dir="ltr"> 
<label for="emailReceipt" style="display:inline;"></label></div>


<input type="submit" name="submit" value="Submit" id="ss-submit" 
class="jfk-button jfk-button-action "></form></div></div>

</body>
</html>

Thanks for your interest Mike. What is the proper way to get to the option list in my form?? — Mahmoud Bayoumi, Aug 10 '19 at 11:45
@MikePoole The question use Google Apps Script server side services, so Stack Snippet should not be used as those services aren't executable here. — Rubén, Aug 10 '19 at 13:52
The link ask for permission. Anyway, instead include a [mcve] of the client-side code. — Rubén, Aug 10 '19 at 14:04
Provide [mcve]. Your code is too long with many stuff unnecessary to the question. See [this question](https://stackoverflow.com/questions/57444860). Start from scratch and build only what's necessary to reproduce the problem. — TheMaster, Aug 11 '19 at 07:15

Rubén · Answer 1 · 2019-08-10T17:46:49.623

1

On a previous thread you already got an answer about how to create an HTML form.

On the code of this question you are trying to use getItemById over a string value but this method is from Class Form. In other words, this method should be used only on Google Forms, not on web forms. Something similar happens with other methods used like setChoiceValues.

To set the option list from a web form by using client side code, you should use DOM methods, like querySelectorAll, getElementsByTagName, etc. If you need to get the options from a spreadsheet you could use server side code and could call that code from the client side by using google.script.run.

For an overview of how Google Apps Script web applications works, please read https://developers.google.com/apps-script/guides/web

answered Aug 10 '19 at 14:02

Rubén

34,714
9
70
166

Thank you Ruben for your interest I am trying to change the form sharing options, but it keeps giving me an error I use google forms already but I added the source code to the app script so I can add some modification on it Below is the question code in my form.html file: – Mahmoud Bayoumi Aug 10 '19 at 14:53
As you can see, the form still submits the results to the attached google sheet. So can you give me an example on how to use google.script.run. to update the above list item automatically using a specific column in the spreadsheet. Thanks in advance – Mahmoud Bayoumi Aug 10 '19 at 14:57
@MahmoudBayoumi Regarding the example you are asking, please checkout this question : https://stackoverflow.com/q/33701881/1595451 – Rubén Aug 10 '19 at 15:32
Thank you Ruben very much for your effort. I checked the thread you sent, still have some issues when running the functions (can not GetRange of null, can not get method GetLastRaw of null) Would you please advise with the script needed to update the option list in my app script as i am not much familiar with java script. Thank you – Mahmoud Bayoumi Aug 11 '19 at 05:15
Okay now I succeeded to run the function (functionToRunOnFormSubmit) but still did not get luck with (genDiscRep); it gives an error (The number of rows in the range must be at least 1) – Mahmoud Bayoumi Aug 11 '19 at 05:25
Okay finally I was able to run both functions. Now the form sends the results to the defined range in the sheet but the sheet does not update the form options in return I need to update the option list in the form using a predefined range in the sheet – Mahmoud Bayoumi Aug 11 '19 at 05:49
@MahmoudBayoumi Follow-up questions should be posted as new questions. Please bear in mind that questions looking for help on non-working code should include a [mcve]. By the way I notice that you already posted three questions and got answers but you didn't accept any of them yet. If the answers aren't good there is no problem but if they help you, the way to say thank you is by accepting them or by answering some of the questions of those tried to help you. Eventually you will earn some rep and you will be able to ask for help about how to use this site on [meta]. – Rubén Aug 11 '19 at 15:38

ziganotschka · Accepted Answer · 2019-08-13T11:12:30.120

Consider using `<?= ?>` and `<? ?>` scriptlets

This allows you to access Apps SCript functionalities within the HTML file.

.gs file

function doGet() {  
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

function createInnerHTML(){
    var ss = SpreadsheetApp.getActive();
    var names = ss.getSheetByName("CHOICES");
    var namesValues = names.getRange(2, 16, names.getMaxRows() - 1).getValues(); 
   return namesValues;
}

HTML file

<body>
...
<? var namesValues= createInnerHTML(); ?>  
<div>
<select name="entry.890244015" id="entry_890244015" aria-label="JOINT  " aria-required="true" required="">
<option value=""></option>
<option value="OPTION 01"><?= namesValues[0][0]?></option>
<option value="OPTION 02"><?= namesValues[1][0]?></option>
<option value="OPTION 03"><?= namesValues[2][0]?></option>
<option value="OPTION 04"><?= namesValues[3][0]?></option>
</select>
</div>
...
</body>

This is an easy solution if your HTML frame is predetermined (you know in advance that your dropdown menu will have 4 options).

If you want to adjust the amount of options dynamically, you a more elegant solution would be:

.gs file

function doGet() {  
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

function createInnerHTML()
{
    var ss = SpreadsheetApp.getActive();
    var names = ss.getSheetByName("CHOICES");
    var namesValues = names.getRange(2, 16, names.getMaxRows() - 1).getValues(); 
    var InnerHTML = [];
    for (var i=0;i<namesValues.length;i++){
      InnerHTML.push('<option value="OPTION '+(i+1)+'>' + namesValues[i][0]+ '</option>'; 
    }); 
   InnerHTML.join('');
   return InnerHTML;
  }

HTML file

<body>
...
<? var innerHTML= createInnerHTML(); ?>  
<div>
<select name="entry.890244015" id="entry_890244015" aria-label="JOINT  " aria-required="true" required="">
<option value=""></option>
//HERE the inner HTML will be inserted
<?= innerHTML?>
</select>
</div>
...
</body>

Wow Ziganotschka, the first code works just fine, thank you man very much. The dynamic code does not give any errors when running the function, but, still does not work for some reason and the list becomes empty in the form. If you could help with that, I will be very much grateful. Thanks again — Mahmoud Bayoumi, Aug 13 '19 at 10:56
By the way, I just added the doGet function to it and fixed some syntax errors so at the end it becomes like that: function createInnerHTML() { var ss = SpreadsheetApp.getActive(); var names = ss.getSheetByName("CHOICES"); var namesValues = names.getRange(2, 2, names.getMaxRows() - 1).getValues(); var InnerHTML = []; for (var i=0;i — Mahmoud Bayoumi, Aug 13 '19 at 11:02
True, I did not insert the doGet() function in the sample. Now, that you added it - does the code work for you? — ziganotschka, Aug 13 '19 at 11:08
And another issue appeared with the first code, the sheet stops receiving any new submits from the form in the script editor but, at the same time still receives new submits from google form. Any help with that ?? — Mahmoud Bayoumi, Aug 13 '19 at 11:41
Google Forms automatically saves all form responses in the destination spreadsheet, but if you create a manual form you have to take care of it yourself. I suggest you to in corporate into an `onchange` Event which triggers a manual JS saving function to run. Here an idea how to implement it: https://stackoverflow.com/a/57337242/11599789. The samples I gave you above are only snippets, to be intergrated within your project. — ziganotschka, Aug 13 '19 at 13:28

How to auto populate form options based on a column in the attached spreadsheet

2 Answers2

Consider using `<?= ?>` and `<? ?>` scriptlets

If you want to adjust the amount of options dynamically, you a more elegant solution would be:

Linked

How to auto populate form options based on a column in the attached spreadsheet

2 Answers2

Consider using <?= ?> and <? ?> scriptlets

If you want to adjust the amount of options dynamically, you a more elegant solution would be:

Linked

Consider using `<?= ?>` and `<? ?>` scriptlets