0

Is it possible to have Google Form data inputs being added not one after the other but corresponding with the Date that is pre-filled on the response form?

Here you can see how the data is currently added from the Form responses, one after the other.

This is how I want it to look like.

I tried to pre-add the date in column B and would now like the responses from the Form to be added according to the corresponding date on the Sheet instead of consecutively. I've tried this with a test but the responses are still added consecutively as you can see here. Is this possible to achieve?

3
  • Hi and welcome to WebApps. May I just clarify, and please correct me if I am wrong... There are no Production Reports for Monday 10 Jan 2022 or Mon 17 Jan 2022 (and, possibly, for every Monday of every week). For unexplained reasons, you want a Production Report for every single day of the week; so if a date is skipped, you want to create a blank/"dummy" row for that date AND you want that blank/dummy row to appear in the correct date sequence Is that right?
    – Tedinoz
    Jan 24, 2022 at 11:48
  • Hey Tedinoz. Thank you for your response. Correct, there are no reports on Mondays since we have no production. The reason why I want to have the row added but blank is that I want to use that row for a weekly summary, but as you answered my question, it seems that it would be easier to do this on a different sheet altogether. Your idea works great actually, so thanks for that. I will try both solutions and see what works best.
    – George
    Jan 25, 2022 at 5:29
  • Hey, George. If the answer met your needs, you might consider accepting it
    – Tedinoz
    Mar 1, 2022 at 2:11

1 Answer 1

0

You want to create "dummy" form responses that will appear in the correct date order.

To do this you create a query where you combine two ranges (1=the original responses, 2=the dummy responses) by stacking them and ordering the result by date

The query looks something like this: =query({'Daily Production Report'!A1:B;Manual_Prod_Reports!A1:B},"select * where Col1 is not null order by Col1",1)

A few things to note:

  • the data ranges are stacked by wrapping them in curly brackets, and separating them by a semi-colon (or a comma if your locale uses decimal comma instead of decimal point)
  • the dummy responses are placed in the correct date order by using the order by clause
  • Leave the original form responses as-is.
  • Do your reporting from the query (which you might put on a new sheet)
  • In this example, the responses are just two columns for the sake of brevity BUT in your 'live' spreadsheet the dummy responses must include all the columns from the "form responses" sheet - even though the cell with any content is the Timestamp.

Sample Production Report

responses

Sample dummy responses

dummy

Sample query output

query

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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