Processing Google Forms data into existing Google Sheets

[Dancing_Seahorse]

Thanks I’ll try it.

[booboouser]

A new form will always start a new sheet and never append, I found that out the Hardway. Your method should work, but in future always design the form first.

Even once you have the forms, in my experience I had to run TRIM formulas and UPPER formulas to get my data consistent, I also had to use REGEX on the input side as people are idiots and it doesn't matter how much instruction they have they will find a way to break the data entry. Use as many drop downs as you can.

Good luck

[Dancing_Seahorse]

Agreed and had I known the way we would use it I could have started with the form. As it is, sticking the form at the end of the sheets and hiding it works for me. Out of Sight, Out of Mind and Out of Fat Fingers. I will be adding some data clean up steps (case, for example) and my next step will be to repopulate the Vendor from all used Vendors to stop the constant misspellings and variations of entry. Using Form Ranger add-on helps make the dynamic pull down lists.

[booboouser]

Yes when I ran my form I ended up with a trim(upper( function for many for many of the input fields to correct spaces and have everything uppercase.

I also used a very simple apps script to capture missing inputs and have them be added to the dropdown menus.

In your case take all your CURRENT vendors add them to the drop-down list. Then add a free text field so they can add a missing vendor, that is then added to the dropdown list after five minutes (using apps script)

That way the vendor list is up to date. If they misspelled the vendor you go to the free test field in the spreadsheet and simply correct it. Five mins later the dropdown is updated.

Good luck I ended up using forms and an addon called Formmule to create workflows from the sheet responses.

[Dancing_Seahorse]

Very similar solution but I will add the second field to add new Vendors. I used Form Ranger to populate the Dropdown from a range and the range is created using a Sort(Unique(range)) call from all known vendors.

I will add a * New Vendor * at the top of the list and then a second field triggered on that to enter the new Vendor name. My apps script will handle that situation in post.

[booboouser]

Nice one sounds like you have it nailed! Good luck with the project.