Link to home
Create AccountLog in
Avatar of Shaun Wingrin
Shaun WingrinFlag for South Africa

asked on

Mailmerge document automation using Autocrat

We want to create donation certificates for a Non profit organization. 


Currently we are using google sheets, google docs and autocrat google add on (https://workspace.google.com/marketplace/app/autocrat/539341275670) to "mail merge" selected fields from our data table to generate individual certificates for each donor.


Currently we only generate a single line with the total that was donated for the year.


What we want to achieve is a solution that can create a table by automatically adding and removing rows depending on the number of donations made by 1 individual for each certificate. 


Example:


**Data**


Donor      Date      Amount      

Joe Soap   01/03/2023   500.00

Joe Soap    05/05/2023   500.00

Sally Silver   10/10/2023   100.00

Joe Soap    10/11/2023   500.00


**


Certificate for Joe Soap


Donations:

01/03/2023   500.00

05/05/2023   500.00

10/11/2023   500.00




Certificate for Sally Silver

10/10/2023   100.00


The output is pdf.

This solution yields promising solutions but I don't have the experience to adjust for our needs. 

https://groups.google.com/g/nv-autocrat-add-on/c/uvaG0LH0bG4/m/bUPpVJKJAQAJ


Perhaps a better search will help too






Avatar of Roy Weil
Roy Weil
Flag of United States of America image

One way to do this is by PHP code using ImageMagick to place the text onto an existing jpg file.
See https://yockatomac.org/certificate/ (currently not working 8/26)


I can send you the code if you want to go that route. It was written some time ago (around 23 years ago).
code worked about 1 year ago, but is currently having a font problem. (Grrrr updates)


Roy

Avatar of Shaun Wingrin

ASKER

Thank you Roy for the offer. Please see updated question. Output is pdf (jpg, is not n option)

many ways to convert jpg to pdf.


in a Linux environment 

sudo apt install img2pdf

img2pdf -o document.pdf picture.jpg


Top 9 Tools to Convert JPG to PDF File – Free Download
  • TalkHelper PDF Converter. Windows 7/8/8.1/10. ...
  • Apowersoft Image to PDF Converter. Windows 7/8/10. ...
  • iSkysoft PDF Editor 6. Windows 7/8/8.1/10. ...
  • Adobe Acrobat. Windows 7/8/8.1/10. ...
  • Icecream PDF Converter. ...
  • Image2PDF. ...
  • Convert-jpg-to-pdf.net. ...
  • SmallPDF JPG to PDF Converter.

The code now works. see https://yockatomac.org/certificate/  


Also, the code uses the GdImage rather than ImageMagick but the concept is the same.

Thankyou  Please see updated question with link at the bottom.  This uses autocrat without needing to convert to jpg. The environment is windows.



This solution not requiring anything but autocrat google add on yields promising solutions but I don't have the experience to adjust for our needs. 


https://groups.google.com/g/nv-autocrat-add-on/c/uvaG0LH0bG4/m/bUPpVJKJAQAJ


Perhaps a better search will help too

If you are going to stick with Google Docs/Sheets for mailmerge, to do what you are after will require scripting  and not using autocrat https://developers.google.com/apps-script/samples/automations/mail-merge


I tried looking at the documentation for autocrat https://docs.google.com/document/d/1WTGSSlH6gwUHpipusPKLIsgfFUamDbFbJ7JaoG6-ZBo/edit#heading=h.y1q61e2vhshm and it is very basic and there does not seem to be an easy way to achieve what you are after as far as multiple rows of data. From the video linked in the documentation https://www.controlaltachieve.com/2016/06/autocrat3.html there is the option to put everything on one document. The hint there is to have multiple sheets, one for each person and that may be a way to loop through data. 


It seems like you are trying to solve two problems. 1) Looping through data and 2) Output to pdf. 


All of this can be done in MS Word. Looping through data is easy by going to the Rules section and using next record and if then else. The second part for oupting to a pdf can be done in a couple of ways. I have Adobe Acrobat that I paid for so it is part of my menu options. However, for free, you can use the printer, Microsoft Print To PDF.


User generated image

Another alternative if you don't want to pay for the Microsoft products is to use open office in the same manner https://wiki.openoffice.org/wiki/Documentation/How_Tos/Creating_Mail_Merge_Documents_From_Text/CSV_or_Spreadsheets


It just does not look like you can do what you are after in your current Google add on. 


Tx 4 this detail! 

There is an additional requirement, to email the document on.


Autocrat creates pdf natively and emails out with ease.


In addition our solution is completely cloud based. 


Are you able to please share more on the scripting option? I have no experience with this.

ASKER CERTIFIED SOLUTION
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account