Collect string objects from Form response

Question

I'm quite new in Google Apps Script and Java, migrating from MS Office VBA. I've already done some research, but still need help in this.

I have set up two Forms in order to control the equipment movement in my company. One of them must be filled when you are taking the equipment out of storage, and the other must be filled on return. So, I set up an onFormSubmit trigger to switch each equipment status on a specific Spreadsheet, in order to have an overview of what was taken and returned.

However, I could not find a way to do this using the 'e' argument on the trigger function. I took some time trying to work this around and I did came up with a not so good solution: looking into the Spreadsheet with the Form Responses, reading the last line and breaking the information apart to do what I need. The big problem is that I found some instability by working this way, since sometimes the Spreadsheet with the responses take some time to update. Anyway, here's the code I have now for the "Equipment removal Form":

function onFormSubmit(e) {

  Utilities.sleep(30000); // I put this to try to minimize the Spreadsheet update problem

  var ds = SpreadsheetApp.openById("storage-control-sheet-id").getSheetByName("Control");
  var os = SpreadsheetApp.openById("form-responses-sheet-id").getSheetByName("Form Responses 1");
  var val = os.getRange(os.getLastRow(), 10, 1, 14).getValues().toString(); // holds data from region with the equipments names
  var responsavel = os.getRange(os.getLastRow(), 4).getValue().toString(); // holds the name of the person in charge of equipment
  var motivo = os.getRange(os.getLastRow(), 3).getValue().toString(); // holds the reason for using the equipment
  var prevret = os.getRange(os.getLastRow(), 6).getValue(); // holds the date for returning the equipment
  var table = ds.getRange("B3:B149").getValues(); // holds a list with equipment names
  var output = ds.getRange("C3:F149").getValues(); // holds current info at the Storage Control Spreadsheet
  // now working with the data collected...
  var arr = [];
  var indexes = []; // collect the lines that need to change on the ds object
  val = val.split(",");
  for (var i = 0; i<val.length; i++){
    if (val[i].toString()!=="") {
      arr.push(val[i].trim());
    }
  }

  for (var i = 0; i<table.length;i++){
    if (arr.indexOf(table[i][0]) >= 0) {
      indexes.push(i); //checking which lines on 'output' should change 
    }
  }
  for (var i = 0; i<indexes.length; i++) {
    output[indexes[i]][0] = "In use";
    output[indexes[i]][1] = responsavel;
    output[indexes[i]][2] = motivo;
    output[indexes[i]][3] = prevret;
  }

  ds.getRange("C3:F149").setValues(output);
  SpreadsheetApp.flush();
}

From what I see here, if I could get a single string object with the answers from certain questions, from the event argument 'e', and storing it in the 'val' variable, that would do It. How do I access the answers directly from 'e', instead of going to the Responses Spreadsheet? Any help will be great!

Cameron Roberts Cameron Roberts · Accepted Answer · 2017-10-24T19:30:04

You can get the values directly from the Event by referencing either it's "values" or "namedValues" properties, which are documented here: https://developers.google.com/apps-script/guides/triggers/events#form-submit

   e.values
   e.namedValues

namedValues is an object which maps the form field names to the response values.

values is an array which containing the responses in the same order they appear in the spreadsheet.

Collect string objects from Form response

1 Answers