Collecting crowd-sourced data in tabular or spreadsheet format

Question

Full disclosure: I originally posted this to the SE/Web application site but garnered zero comments amidst 15 views. Hoping for a better outcome here.

I'm involved in a citizen-science project polling recreational anglers about their preferred ocean fishing locations (lat-lon), a few characteristics about the location (depth, what species they catch, etc.), and some voluntary contact information. In spreadsheet form with each row being a unique location, there would be about 10 columns (each column being the response to a question).

I did a trial run with a small number of respondents using a Google Form that compiles all the responses to a Google Sheet, but due to limitations in Google Forms, respondents must submit a new form response for each fishing location they wish to provide. Every respondent said it was tedious and would prefer entering the data directly into a spreadsheet versus scrolling through 10 questions and submitting multiple forms to provide multiple locations.

Is there a process where I can distribute a link to potentially hundreds of people (who can in turn share that with whomever they wish) where the respondent is presented with an empty spreadsheet they populate with their responses? It would require that the field headers can't be edited and no one can see anyone else's responses. The spreadsheet would just look empty to each respondent. On the back end, the responses would be compiled into a single spreadsheet, much like how a Google Forms/Sheets works now. Google Forms is close - if they would just allow users to embed a Google Sheet in the form itself, I'd be set, but that's not possible at this time.

Edit - this is what the spreadsheet would include. Sorry I don't know how to properly embed or format this in tabular form. What each respondent would see is these column headers in a completely clean spreadsheet. They'd enter their data and submit, and on the back end, I'd have a master version of this that would append add each new location row-wise as they are submitted.

RowID | Latitude | Longitude | Target species 1 |Target species 2|Target species 3 | Habitat type| Home port    | Name |Email address

Answer 1

• click on this: https://docs.google.com/spreadsheets/create
• copy-paste this fx in A1

---

=SPLIT("RowID|Latitude|Longitude|Target species 1|Target species 2|Target species 3|Habitat type|Home port|Name|Email address", "|")

• copy the url

• change edit#gid=0 to copy
• take that URL and send it to your buddies and ask them to send you their URL of the sheet and enable sharing

• or you can create those sheets for them and give to each of your people one spreadsheet

• then create a new spreadsheet (master sheet) and use this in A2:

---

=QUERY({
 IMPORTRANGE("url1"; "A2:J"); 
 IMPORTRANGE("url2"; "A2:J"); 
 IMPORTRANGE("url3"; "A2:J")}; "where Col1 is not null"; )