Google Apps Script HTML Form - Put Data into Spreadsheet using Variables for Form Fields

Question

I have a small rota application in Google Sheets.

There is a tab that lists the types of jobs/roles. This list can change, e.g. last week a new role was added to the list.

I have an HTML form for adding new employees to the rota application.

The form uses the list of roles in the spreadsheet to dynamically create the input fields on the form on my addPerson.html:

<? var ss = SpreadsheetApp.getActive(); //get the currently open & active workbook
     var roles =ss.getSheetByName("Roles");  // get the Roles tab
     var roleValues = roles.getRange(1,1,roles.getLastRow(),1).getValues();?>
  <table>      
  <? for (i = 0; i < roleValues.length; i++) { ?>

  <tr><td><?= roleValues[i]?>:</td><td> £</td><td> <input type = "number" name=<?= String(roleValues[i]).replace(/\s/g,'') ?> id=<?= String(roleValues[i]).replace(/\s/g,'') ?>/></td></tr>
  <? } ?>
  </table>

That's all working fine.

Where I am having trouble is in accessing the data from the form for the fields that are created in the code above. I can access the other data without a problem.

Here is how I am trying to get the fields in question in my code.gs:

for(i=0; i<lastRole; i++){
  nextRow++;
  var role = String(roleValues[i]).replace(/\s/g,'');
  Logger.log(role);
  if(form.role !== null){
    Logger.log(form.role);
    rates.getRange(nextRow, 1).setValue(name);
    rates.getRange(nextRow, 2).setValue(form.role);
    rates.getRange(nextRow, 3).setValue(rate);
    rates.getRange(nextRow, 4).setValue(team);

  }
}

The initial definition of role works fine, but using that as form.role doesn't work, as you can see in the logs.

[18-03-16 04:04:43:634 PDT] Bar
[18-03-16 04:04:43:635 PDT] undefined
[18-03-16 04:04:43:643 PDT] BarB
[18-03-16 04:04:43:643 PDT] undefined
[18-03-16 04:04:43:651 PDT] BarSup
[18-03-16 04:04:43:652 PDT] undefined
[18-03-16 04:04:43:660 PDT] DM
[18-03-16 04:04:43:661 PDT] undefined
[18-03-16 04:04:43:669 PDT] DT
[18-03-16 04:04:43:670 PDT] undefined
[18-03-16 04:04:43:678 PDT] FoHC
[18-03-16 04:04:43:679 PDT] undefined
[18-03-16 04:04:43:688 PDT] Maint
[18-03-16 04:04:43:688 PDT] undefined
[18-03-16 04:04:43:697 PDT] TM
[18-03-16 04:04:43:698 PDT] undefined
[18-03-16 04:04:43:707 PDT] VT
[18-03-16 04:04:43:707 PDT] undefined

How do I work through the list of HTML form input fields to get the data into a spreadsheet?

There's nothing in your html code above that suggests your form has an input control with the value of "name" attribute set to "role". All "name" attributes are set to be equal to roleValues[i]. So my guess is, form.role is undefined simply because it doesn't exist. — Anton Dementiev
– Anton Dementiev, Commented Mar 16, 2018 at 13:58
@tehhowch - here is the output of Logger.log(form): [18-03-16 17:07:49:266 GMT] {BarB=8, BarSup=, DT=, Maint=, Bar=, baseRate=8, FoHC=, name=Test, DM=, TM=, team=Box, VT=} — kxm
– kxm, Commented Mar 16, 2018 at 17:09
@AntonDementiev - The names are set programatically. The <?= tag is used to populate the HTML form with the output of the code within the tag. You can see from the log information above that each of the elements has the required name. What I am seeking to achieve is that the content in the field BarB is added to a spreadsheet. However, I don't want to call BarB explicitly - I want to create that call using variables, i.e. pick up BarB from the list of roles on the spreadsheet and then use that to find the corresponding value from the form and put it into another sheet. — kxm
– kxm, Commented Mar 16, 2018 at 17:16

tehhowch · Accepted Answer · 2018-03-16 17:32:10Z

1

The issue you are having here is that the dot notation for an object is not interpreted as a variable - it is interpreted as a literal. So for form.role, the engine is looking at the object form, and inspecting the property named "role", even if in your environment you have a variable named role present.

To obtain the value of the property named by the value of the variable named role, you must use bracket notation: form[role].

Consider reviewing the MDN resource on Property Accessors

answered Mar 16, 2018 at 17:32

tehhowch

9,9124 gold badges27 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kxm Over a year ago

Perfect. That solved things. Thanks for the advice - as a self taught amateur, knowing what to search for is difficult sometimes. I guess after reading the documentation, I'm not entirely certain when to use bracket notation over dot notation, but at least I know about bracket notation now and can try that in the future.

Collectives™ on Stack Overflow

Google Apps Script HTML Form - Put Data into Spreadsheet using Variables for Form Fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related