2

When I receive submissions from a form I am trying to find a way to include my formulas and set values in the same row as the information submitted by form. My form is only collecting 14 values but my data sheet for the form responses is 45 columns wide. I am getting recorded weight measurements from the form. These come over and I need to subtract the weight of the container that the recorded weight from the form came from, then I need to show that weight.

I have tried adding a script to the spreadsheet.

function onFormSubmit(event) {
  Logger.log("Event: " + event.changeType);
  Logger.log("New row added");
  var sheet = event.range.getSheet();
  var addedRowIdx = event.range.getRow();
  Logger.log("Index of new row: " + addedRowIdx);
  var range = sheet.getRange(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 
    14, 15, 16, 17, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 
    sheet.getMaxRows(), 1); // 4 == fourth column == c
  var queryColumnFormulas = range.getFormulasR1C1();
  for (var i = 1; i < queryColumnFormulas.length; i++) {
    if (queryColumnFormulas[i][0] == "") {
      Logger.log("Inserting formula in row " + i);
      // Copy formula from previous row
      queryColumnFormulas[i][0] = queryColumnFormulas[i - 1][0]; 
    }
  range.setFormulasR1C1(queryColumnFormulas);
  }
}

I found this in a previous answer and added my column numbers where values or formulas are stored but I am getting an error.

TypeError: Cannot read property "changeType" from undefined. (line2, file"Code")

Here is a link to the spreadsheet and here the form.

  • Jeff, I'm not able to access the spreadsheet. – Jacob Jan Tuinstra Feb 5 '15 at 20:35
1

This is a FAQ on Stack Overflow ---> How can I test a trigger function in GAS?

Bye the other side, the changeType property belongs to the change event, not to the form submit event.

According to Serge Insas, "to know what value is returned in the event info" instead of event.changeType use Utilities.jsonStringify(event).

References

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.