0

The header and end of the template need to operate like mail merge, one set of data per document, while the central body of the template needs to operate like address-label-mail-merge, one row per section.

The header content would be for example: Course name, Date, Class #, Location

The body would consist of several sections, for example: welcome; warm-up; barre work; centre work; across-the-floor; routine; cool-down; farewell, each having multiple specifications about time, music, learning goals, etc etc.

The end content would be: Choreography [just a text box is okay]

Here's the Doc template: https://docs.google.com/document/d/1EscScwtLpnSdW9SugveO-44lzxjlPY4gbQAkwbcJGfw/edit?usp=sharing

Here's the Spreadsheet: https://docs.google.com/spreadsheets/d/1GCGaCZlzfiV2SmD_t-yqL23e1bNR9-LUBmLfGFhtib0/edit?usp=sharing

I could enter the header and end content manually after generating the body output. If I do that, I still encounter a technical problem: I tried two address-label mail merge add-ons to Google, but they both are only usable with actual addresses - they can't be adapted so that, for example, a complex section can take 12 lines of description. Is there a more flexible way to generate multiple address-label-like text boxes?

Since I have 40+ versions I'd rather not enter the header and end data manually. I want to get the data from the spreadsheet where the other data is. Can that be done using functions built into Google Sheets and Google Docs? If not, can it be done with other software's built-in functions? Or is there a free-to-use add-on that can integrate into Google Sheets&Docs? Or, is there a programmable solution?

Improve this question
3
  • Do you use Google sheets? Then remove the Excel tag, because Excel is different and does not apply in this situation. Commented Oct 12, 2021 at 20:20
  • Have you read How do I mail merge from Google Spreadsheet to a Google Document? or Trying to mail merge information from several different tabs of a Google sheet? Commented Oct 14, 2021 at 5:17
  • 1. "Can that be done using functions built into Google Sheets and Google Docs?" A: No. 2. "If not, can it be done with other software's built-in functions?" A: Excel has mail merge built-in, Google Sheet does not. 3 "Is there a free-to-use add-on that can integrate into Google Sheets&Docs?" A: I don't know. 4. "Is there a programmable solution?" A: Yes, refer the links in the preceding comment. Commented Oct 15, 2021 at 6:23

1 Answer 1

Reset to default
0

There are many mail merge solutions for Google Sheets, but all the ones I know expect that there is exactly one row of data per record.

I think what you are asking is doable with AutoCrat. It has a single-document mode that lets you concatenate the output from multiple records in the same file without page breaks in between. The header information in the Docs template would have to be entered manually for this to work.

Another way to do this with AutoCrat, avoiding the need to enter headers manually, would be to expand the data so that all data pertaining to a single record is in the same row. You can try doing that with something like this:

=arrayformula( query( trim(LineDance2!A1:O), "select Col1, Col2, max(Col3), max(Col4), max(Col5), max(Col6), max(Col7), max(Col8), max(Col9), max(Col10), max(Col11), Col12, Col13, Col14 where Col15 is not null group by Col1, Col2, Col12, Col13, Col14 pivot Col15 order by Col2", 1 ) ) 
=arrayformula( { "row ID"; iferror(B2:B / isnumber(B2:B) ) } )

See the new MailMerge and LineDance2 sheets in your sample spreadsheet.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.