CODEWITHSUNDEEP
google-sheetsgoogle-forms
Jump_Ace
Jump_Ace

Reputation: 303

Dump Google Form Data To Different Google Sheets (Not Tabs) Based On Question Response

I'm looking to have my Google Form put data into different Google Sheets (not different tabs) based on a question response.

For example, if someone selects 'Honda' for the car manufacturer, I want all the form data to dump into Sheet A. If someone picks 'Toyota' I want all the form data to dump into Sheet B, and so on. While these are separate Sheets, the tab names of the sheets match the manufacturer.

I'm familiar with Google App Scripts, but not an expert or developer by trade. Any help would be greatly appreciated, thanks!

Upvotes: 1

Views: 258

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

I do this all the time for clients (if I'm correctly understanding what you're trying to do; your use of the words "sheet" and "tab" are still a bit unclear). Here is my recommendation.

1.) Send all form responses to a single spreadsheet. For the sake of this discussion, we'll say that this spreadsheet's name is Master (ex ID 1234567890abcdef...z) and the name of the sheet within "Master" that is receiving the form responses is All. We'll further suppose that the car make is in All!A:A and that the full data runs All!A:J.

2.) Set up your individual sheets to pull from the Master spreadsheet's All sheet. For the sake of this discussion, we'll say that the first individual spreadsheet is named Honda, and the receiving data sheet within the Honda spreadsheet is named List. In List!A1, place a formula like this:

=ArrayFormula(QUERY(IMPORTRANGE("1234567890abcdef...z","All!A:J"), "Select * WHERE Col1 = 'Honda'",1))

It will say "Loading..." and then a #REF error will appear. Hover your cursor over the error. A flyout will appear. Click the blue button granting remote access to the Master spreadsheet.

Your relevant data should fill in.

3.) Set up other individual sheets the same way, simply copying and pasting the above formula into List!A1 of the new sheet and changing Honda within the formula to Toyota, etc.

Note:

I have read different places about instances of people saying that IMPORTRANGE calls have been dropping in and out sporadically. I myself have not experienced this problem with any of the systems I've built for people. But as a safeguard, I've seen other contributors across different forums recommending structuring the formula with an error-catching safeguard like this:

=ArrayFormula(IFERROR(QUERY(IMPORTRANGE("1234567890abcdef...z","All!A:J"), "Select * WHERE Col1 = 'Honda'",1),QUERY(IMPORTRANGE("1234567890abcdef...z","All!A:j"), "Select * WHERE Col1 = 'Honda'",1)))

(Notice the lowercase j in the IFERROR's second parameter.)

Upvotes: 1

Related Questions