Import Form data into a sheet

Question

I am trying to accomplish something that may not be possible. My work rents equipment to multiple companies and my boss wants me to setup a "database" of rental equipment and where its located and any notes tied to that #.

With the help of you all I was able to create a Vlookup to pull data from the database when a equipment # was typed into another sheet. That worked great, but then the project parameters changed.

Firstly they want to eliminate "user error" when inputting data, So I made a google form that allows our sales team to input the data into a form, and lock/limit their interaction with the actual data sheet. I can't figure out from there how to have the sheet, check the form data, and pull up to date info into the correct cells on the data sheet.

Here are the results of the form..

And here is my data entry page that I use to track all my rentals.

Is it possible to pull the data from the forms, check the trailer # in the form data, find it on the data sheet, then update the cells with the information or am I barking up the wrong tree. Also, I need to not erase data on the data sheet, if the cell on the data form is left blank, just to make matters more complicated.

I am open to setting up a actual real data base, preferably free and cloud based(is that a thing?) and going that route, but spending money isn't an option currently.

Answer 1

The penny has dropped, and the answer to your question is "Yes".

Though a form may have many records for a given trailer, that trailer can only be on one job at a time though its status may change (which I suppose is what the database records). Presumably it is the latest form response for the trailer that is important; and if not, then an alternative criteria would apply. In any event, the following offers a formula and a script - two methods each of which may have their own advantages and disadvantages.

Formula

`=arrayformula(iferror(vlookup(A13,arrayformula(vlookup(unique($C$2:$C$9),SORT({$C$2:$E$9,$B$2:$B$9,$A$2:$A$9},5,0),{1,2,3,5},0)),{2,3,4},0),"Not Found"))`

Refer the attached screen shot.
Enter this in cell B13 and copy down as many rows as necessary.

The logic is:

• it takes the form data and:
• gets the unique trailer numbers
• re-organises the data by Trailer, Location, Status, User and Timestamp, and
  • sort by Timestamp
• vlookup the unique Trailer numbers in the re-sorted list and return the values assuming no sorting (i.e. it takes the first matching value, which is the most recent value).

props to @TheMaster for the formula logic.

---

Query screenshot

---

Script

This is a fairly conventional script.

• it is triggered by an Installable trigger (Form Submit)
• it gets the database data
• creates a list of trailers using the Javascript map method. Again props to TheMaster for the elegant syntax
• gets the form responses
• finds the matching Trailer number on the Trailer list using the Javascript indexOf method.
• builds an array of the form responses
• creates a range on the database

• setValues on the database using the array of form responses

  Refer the attached screenshot

---

function onFormSubmit(e) {

  //wa_135180()
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formsheetname = "Form Responses 1";
  var form = ss.getSheetByName(formsheetname);
  var dbsheetname = "database";
  var db = ss.getSheetByName(dbsheetname);


  // get db data
  var dbLR = db.getLastRow();
  var dbRange = db.getRange(2,1,dbLR-1,4);
  var dbValues = dbRange.getValues();
  // Logger.log("DEBUG: The db range = "+dbRange.getA1Notation());
  // Logger.log(dbValues);//DEBUG


  // get Trailer list
  var trailers = dbValues.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]
  //Logger.log(trailers);//DEBUG

  // get Form Response
  //Logger.log(JSON.stringify(e));
  var row = e.range.getRow();
  var range = form.getRange(row,1,1,5);
  //Logger.log("DEBUG: The range is "+range.getA1Notation());  
  var values = range.getValues();  
  var formTrailer = values[0][2]
  var formLocation = values[0][3];
  var formStatus = values[0][4];
  var formTimeStamp = values[0][0];
  // Logger.log("DEBUG: Trailer="+formTrailer+", Location="+formLocation+", Status="+formStatus+", Timestamp="+formTimeStamp);

  //find formTrailer in the database
  var index = trailers.indexOf(formTrailer);
  // Logger.log("DEBUG: The form trailer ("+formTrailer+") index in the dbase is "+index);

  //update the database
  // header in row 1
  // index is zero-based
  // so add 2 to index
  var dbupdate = [];
  var dbfinal=[];
  // push the values to a blank array
  dbupdate.push(formLocation);
  dbupdate.push(formStatus);
  dbupdate.push(formTimeStamp);
  //push the array to a second array to create 2D
  dbfinal.push(dbupdate);

  // define the range on database
  var dbupdaterange = db.getRange(+index+2,2,1,3);

  // update the form values to the database
  dbupdaterange.setValues(dbfinal);

}

---

Script screenshot