0

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.. enter image description here

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

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.

  • As I understand it, you record more information about a rental than appears on the form; you already have a "data base" for tracking rentals but this isn't linked to the form. Leaving aside existing database records for a moment, quick and dirty what-if... you have 1 file, 2 sheets: "FormResponse" and "Database"; as each new form response is received, a script automatically appends a copy of that response to the bottom of the "Database" sheet. The common link (if needed) is the timestamp. You can add extra columns, track equipment, filter the data, etc without having to worry about data loss. – Tedinoz Oct 3 at 6:02
  • I can already pull the form response data into a spreadsheet with the Query command, but this results in duplicate entries. We currently track the information in real time on a Formatted excel spreadsheet, listing all of our customers, warehouses and storage yards and as the equipment moves, we copy the # and data attached to it from one site to another. This is all done manually. the purpose of the database is to link the location field to the location on the display sheet and allow changes in the database to automatically update there. Duplicate entries from formresponse would break that – charlie4 Oct 3 at 16:06
  • The "problem" (so to speak) with query, if you manually add any information on the Query sheet, is to retain the linkage between the new information and the "queried" information as and when queried data is added. Perhaps it is just me, but it seems that the information in the question does not really explain the nature and extent of the data that you are managing nor the context/relationship of form responses.You asked "is it possible to..." Would you please share a copy of your spreadsheet (exclude private or confidential information) but include an example of a successful outcome. – Tedinoz Oct 4 at 0:48
0

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

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 Script Screenshot

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.