I had to do something quite similar to this recently. In my case, I had to add anywhere between 1 and 52 rows of data from a single form submission, and the number of rows was not known in advance.
The solution I came up with was moderately complex, and involved a 3rd party service (Integromat), but it works.
I’ll describe it here at a very high level, and if you think it may be applicable to your scenario let me know and I can provide more details.
- I started by creating a working (Glide) table with 52 rows. Each row was numbered from 1 to 51. (This was necessary as each row neeed to contain a timestamp, which incremented by one week for each row).
- I then defined a JSON structure and built it out on each row, using templates and based on the submitted data
- The required number of rows are combined into a collection using a joined list
- The JSON structure containing the collection is sent to Integromat via a webhook
- Integromat parses the JSON and inserts the rows into the Google Spreadsheet