Reconnecting form responses to current sheet after file move to Team Drive

Question

I have searched for an answer for the "current" Google Forms problem I am having. It seems the answers here for this issue are outdated as Google keeps changing their technology about as fast as I can change underwear.

I had a drive set up for my organization's files under my personal account and shared the files with them. We have a form there that we use and it has about 500 responses.

My organization switched to G-Suite and I have been migrating our files over to the new team drive. I recently copied the form and the sheet over to the new Team Drive and the connection between the form and sheet was lost. I tried reconnecting them, but the form wants to create a new sheet within the existing spreadsheet and then shows no responses.

I thought "fine, I'll just copy the responses over to the newly created sheet from the older sheet and let it pick up from where it left off." Nope, this didn't work either. It does not see the previous responses.

I tried following the linking instructions on the G-Suite education site for form responses, but that also didn't work.

Is there any way I can fix this issue with the current system? We want the previous responses to show up in the graphs, etc. that you can view for form responses.

I will humbly take the downvotes if this really has been answered recently for the current version of their web apps, and I apologize if it is already answered.

Answer 1

• let's say that responses from Form come in Sheet1

• create Sheet2 sheet in your spreadsheet

• copy over those previous 500 responses to Sheet2

• on row 501 of Sheet2 paste this QUERY formula:

  =QUERY({Sheet1!A2:Z}, "select * where Col1 is not null", 0)

• or create Sheet3 and paste this QUERY into A1 cell:

  =QUERY({Sheet1!A1:Z1;Sheet2!A1:Z;Sheet1!A2:Z}, "select * where Col1 is not null", 1)

• now when the old and the new dataset are merged under one sheet edit your graphs/charts to reflect this change and feed them from Sheet2 (or Sheet3)

• extra sheets can be hidden by right-clicking them and selecting Hide sheet option