1

Let me start by indicating I know nothing about programming. I have a Google Sheet populated by a Google Form to track time spent on activities and the translation of that time to decimal. The data populates a row which can be 10 entries long.

Common info (columns A:E) with each activity repeating every four columns (Area/Activity/Duration/Decimal). The row goes for 30+ columns but I would like the row split by each set of ten columns. I don't see a place to upload my sample spreadsheet for a visual.

Here is what it looks like and below it what I'd like it to look like. Is there help for my problem?

Activity Tracker Sample

1
1

Try this formula in Cell A1 of a blank sheet:

=query({"Timestamp","Email","Short Name","Date","Area/Activity","DC Activity 1","DC Duration 1","DC A1 Decimal","Rio Activity 1","Rio Duration 1","Rio A1 Decimal","TPS Activity 1","TPS Duration 1","TPS A1 Decimal";data!A3:D4,data!E3:N4;data!A3:D4,data!O3:X4;data!A3:D4,data!Y3:AH4;data!A3:D4,data!AI3:AR4},"select * where Col5 is not null order by Col1,Col3")

The logic is:

  • the query is acting on 5 array groups set inside curly brackets({}). Each group is separated by a semi-colon (;) which creates multiple rows in the same columns.
  • Col5 is not null: this tests the "Area/Activity" value avoids blank rows
  • order by Col1,Col3: the data is sorted by timestamp (Col1) and Short Name (Col3). This keeps each response row together.

The array groups are:

  • a header row
  • data!A3:D4,data!E3:N4: the first group of responses in the row
  • data!A3:D4,data!O3:X4: the second group of responses in the row
  • data!A3:D4,data!Y3:AH4: the third group of responses in the row
  • data!A3:D4,data!AI3:AR4: the fourth group of responses in the row

Note that in each array group, there are two arrays separated by a comma (,) - this will display both arrays in the same row.

  • The first group is always the "fixed" data (Timestamp, Email, Short Name, and Date)
  • the second group is the ten column range: "Area/Activity" to "TPS A1 Decimal"

Query output

Query output

Sample data

Sample data


Addendum

Assuming that the form responses are on a sheet named "Form Responses 1", the following would be the appropriate query:

=query({"Timestamp","Email","Short Name","Date","Area/Activity","DC Activity 1","DC Duration 1","DC A1 Decimal","Rio Activity 1","Rio Duration 1","Rio A1 Decimal","TPS Activity 1","TPS Duration 1","TPS A1 Decimal";'Form Responses 1'!A3:D4,'Form Responses 1'!E3:N4;'Form Responses 1'!A3:D4,'Form Responses 1'!O3:X4;'Form Responses 1'!A3:D4,'Form Responses 1'!Y3:AH4;'Form Responses 1'!A3:D4,'Form Responses 1'!AI3:AR4},"select * where Col5 is not null order by Col1,Col3")

7
  • Hello and thank you Ruben! Can you help me understand where to insert the query? I have a tab for form responses. I am not sure how to setup a 2nd tab to collect this information or where to put the query. My apologies, this is very new to me and I'm exploring with little experience. – Regina Jan 4 at 14:53
  • 1
    On a new sheet in your spreadsheet, copy the query from the answer and paste into Cell A1 (of the new sheet). It will be an error cause the query looks for a sheet called "data" but yours is probably "Form Responses 1". To edit the query, click in the Formula Bar and replace "data" with "'Form Responses 1'" (or your sheet name). Note the single quotes around "'Form Responses 1'" - they are deliberate because the name includes spaces. FYI#1-"data" appears eight (8) times; FYI#2-do NOT delete any exclamation marks; FYI#3-if you get fed up, I added a query for the probable sheet name. – Tedinoz Jan 4 at 21:25
  • Tedinoz...this is magnificent! Now only one issue I have left. It updated the rows that I had but didn't update when I entered new responses. I have refreshed the sheet but don't know what else to do. – Regina Jan 5 at 23:27
  • I got it! I changed the 4 to 100 (A3:D4 to A3:D100 and so on). Thank you so very much! You have no idea how much this helped. Have a blessed 2021. – Regina Jan 5 at 23:41
  • 1
    Great. If you expect to get more responses, then you can make the query dynamic by removing the row number on the second part of each range. For example, you changed "A3:D4 to A3:D100". To make it dynamic, change "A3:D100 to A3:D" and so on. This will automatically pick up any/all new responses. BTW, if this answer was helpful then you might consider "accepting" it. – Tedinoz Jan 6 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.