I have a google sheet connect to a google form. The form is compiled by team leaders with his members and some informations that are reported in a row by google. I need to make another sheet with all data from members in a single column. I won't copy and paste 'cause there are more than 50 leaders and thousand of members and the real problem is that some rows are full and some, considering that not all teams are made up of the same number of members, are half empty. What is the fastest way to complite the sheet? I need something like:

Rows from google sheet
[Team1; Bob, data; Rob, data]
[Team2; Rose, data; Mark, data; Jenny, data]

Result that I want:
[
[Bob, data],
[Rob, data],
[Rose, data],
[Mark, data],
[Jenny, data],
]

If there's no way to do it internally to Google Sheet can I use python and think to google sheet like a matrix?

CodePudding user response：

You CAN do it internally using Google Apps Script. This allows users to make custom functions and code in JavaScript which can make changes to sheets in Google Sheets or get info based on what code you write.

Here is the link for the Apps Script documentation which is quite well written.

You will basically need to create 2 sheets, write a function to extract the cells you want from each row, and then input that data into the second sheet. You do not need to be very well versed in JavaScript to do this, I myself am not adept at Javascript however I am able to make functions as per my need.

Also some advice, please test it with a sample sheet first so that you do not delete data or make errors.