How do I pass HTML form data to apps script variable?

1
votes

I am having a hard time assigning variables to data that I collect in an HTML form for use in Apps Script. My current code collects values from HTML and appends the data to a row in a Google Sheet. What I would like to also do is assign each form value to a variable so I can send automated emails, etc...

What I know so far...

The HTML and script works, it collects values from the form and sends them to code.gs

function addData starts as expected as the values are transferred

The form data is appended to the Google Sheet

Both Code.gs loggers "Confirm received data" and "Received data" log the transferred values

I get an execution error when attempting to assign var columnA that reads: Execution failed: TypeError: Cannot read property "0" from undefined.

Below are logs from the HTML and gs files as well as the Code.gs script I am trying to get to work...

Thank you very much in advance...

console.log:

["20141223", "CraneJe960303", "Jeff", "Crane", "1231231234", "[email protected]", "123 Any St Here, AR 11111", "11111", "19960303", "11-Completed Junior Year", "16-Earned Bachelors Degree", "L-Leadership", 37.****125, -85.****217, 22]

Code.js Logger.log:

Logger.log([Received data: 20141223,CraneJe960303,Jeff,Crane,1231231234,[email protected],123 Any St Here, AR 11111,11111,19960303,11-Completed Junior Year,16-Earned Bachelors Degree,L-Leadership,37.****125,-85.****217,22, []]) [0 seconds]

function addData(data) {
  var ss = SpreadsheetApp.openById('1g5N1******************doOnE').getSheetByName('Sheet1');
  ss.appendRow(data);
  Logger.log('Confirm received data: ' + data);

  Logger.log('Received data: ' + data);  
var columnA = data.values[0];
  Logger.log('Start data value: ' + columnA);
var columnB = data.values[1];
var etc....
2
arraysgoogle-apps-scriptparameter-passing

2 Answers

1
votes

The variable data looks like it is an array. By putting a dot operator, and the word "values" on the end of data, you are trying to invoke a method on the array named "data". But there is no JavaScript method for an array name "values". There is a .valueOf method.

Methods:

  • concat() Joins two or more arrays, and returns a copy of the joined arrays
  • indexOf() Search the array for an element and returns its position
  • join() Joins all elements of an array into a string
  • lastIndexOf() Search the array for an element, starting at the end, and returns its position
  • pop() Removes the last element of an array, and returns that element
  • push() Adds new elements to the end of an array, and returns the new length
  • reverse() Reverses the order of the elements in an array
  • shift() Removes the first element of an array, and returns that element
  • slice() Selects a part of an array, and returns the new array
  • sort() Sorts the elements of an array
  • splice() Adds/Removes elements from an array
  • toString() Converts an array to a string, and returns the result
  • unshift() Adds new elements to the beginning of an array, and returns the new length
  • valueOf() Returns the primitive value of an array

Properties

  • constructor Returns the function that created the Array object's prototype
  • length Sets or returns the number of elements in an array
  • prototype Allows you to add properties and methods to an Array object

If you want the first value indexed in the array named data, use:

data[0]

In other words:

var columnA = data[0];

If it's not an array, but just a string, you may need to convert it to an array.

1
votes

You can't read values like data.values[0]. Whenever you pass form values to GAS, function parameter doesn't have property like 'values'. Those 'data' parameter will act like a json data. You can access it by name of parameter which you have given in your form, i.e <input type='text' name='user' value=''>. So if you pass this value to GAS, then this can be accessed like data['user'].

Or you can access it in following way too,

var array = [];
for(var i in data) {  // data is a function parameter you have used in your GAS function, addData(data)
  arr.push(data[i]);
}

After having done this, you can use this array, array[], to assign your columns, i.e

var columnA = arr[0];
Logger.log('Start data value: ' + columnA);
var columnB = arr[1];
...
...