Autofill a Google Sheet with data from form - new sheet for each submission

[Niccole George]

Hello!

I'm trying to create a workflow whereby form data creates a new row in a google sheet on submission (got this part ok!) and then a copy of a Google Sheet template is created, and auto-filled with the form data. Sample report can be seen here for context: https://docs.google.com/spreadsheets/d/17dc7y0TQZ3II7UNSGJYcvklM_8-JM1_CwJTjCzoCxpc/edit?usp=sharing

I have found a few tutorials on doing this with Google Docs (and in a pinch I could revert to this-- but my report includes calculations and is better formatted in Sheets). 

Is what I'm attempting possible? If so, I'd be most appreciative if anyone could point me in the right direction as to where to start.

The other thing I've tried is getting the form data to add a row to a sheet in a worksheet, and having a copy of a template sheet made every time a row is added -- I thought I might be able to create a formula whereby each field to be filled was associated with a cell in the reference sheet, but I couldn't work out how to change the reference row for each new sheet without manually changing all of the fields..

Many thanks!

Nic

[Laurie J. Nason]

Hi Niccole,

Given the following assumptions:

1. One row of data on the “Form Data” tab, contains most of the information needed to generate the Report
2. The calculator tab contains the rest of the information needed to complete the form
3. The report will not change apart from the values contained in the other 2 tabs (i.e. every time you do a report, you’ll include all rows and text (although probably with some clever conditional formatting, you could possibly hide rows with no data.

I would do the following:

1. On the form data -  have a calculated column (using array formula in the first row column headers) to make some kind of unique identifier to distinguish between rows (name and date usually is a good one)
2. On your “calculator” tab, add a few rows at the top and have a drop down listing the values from #1 above to select them easily in Cell B1
3. In one of the extra rows just added, use a “Query” to select all the columns from “Form Data” that match the unique selected value (B1) 
4. Use these values to populate your “Calculator” to provide the rest of the information needed for the report
5. Base your Report tab on the values on the “Calculator” tab
6. This means that when you change the unique value to a different one on the calculator tab, your report data populates automagically.
7. Export the Report tab as a pdf and store somewhere as a permanent record (in case someone changes your form data by accident!)

Let me know if you want any more Information or help.

Laurie

------ Original Message ------

From "Niccole George" <nicmc...@gmail.com>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 27/06/2022 08:26:46

Subject [Apps-Script] Autofill a Google Sheet with data from form - new sheet for each submission

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/78835502-8c01-4fe6-ac7b-939afab1436bn%40googlegroups.com.

[Niccole George]

Hi Laurie,

Legend! Thank you so much for that - I'm very green so It's taken me a few hours to wrap my head around all of this, but I've been able to get it to work which is beyond exciting. Really appreciate you taking the time to outline it for me. 

All the best!

Nic

[Laurie J. Nason]

No problem - you’re welcome - glad it made enough sense for you to get it to work!

L

------ Original Message ------

From "Niccole George" <nicmc...@gmail.com>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 27/06/2022 13:11:05

Subject Re: [Apps-Script] Autofill a Google Sheet with data from form - new sheet for each submission

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2e77f7b5-70b9-4a0d-89b0-c2ff39b94e9fn%40googlegroups.com.