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?

3

1 Answer 1

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.

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.