Taking Range of Data and Generating Multiple PDFs thru Document Builder

Options
bkg73123
bkg73123
edited 06/07/24 in Add Ons and Integrations

Hello,

As I may have stated before I am very new at Smartsheet but have good understanding of Excel. I have attached a Smartsheet to this post, but in Excel format.

I want to take the data from this Sheet 1 and turn it into multiple PDF invoices automatically upon a trigger.

Lets say that trigger is checking the box in Column [INVOICE PAY PERIOD 1] Row 1.

Upon that trigger I want all the data in columns [PROJECT] thru [PAYROLL HANGER PAID] or (AW thru BO) to auto populate into fillable PDF forms and either be emailed or preferably automatically downloaded to my computer.

However, I need a PDF generated for each named person in the PAYROLL HANGER column that only includes the data related to them.

You can see an example of how each PDF would look for each individual on Sheet 2. The idea is for a single trigger to generate 3 separate PDF forms in this case, one for each person listed, but could be more depending on how many people are listed in the future. Each will be the same PDF form just with different data for each person listed. Please explain how I can do this.

You will see that the data is spread out in the columns with blank rows in between. There will need to be some sort of collection function to gather all the pertinent data and report it to the PDF.

I provided a mapping example invoice as well to show my thoughts on what columns in Sheet 1 will populate what fields on the PDF form. Additionally there would need to be a function that allow for summing one of the columns and reporting the result for Total Pay field on the PDF.

Also hoping there could be an auto-filling of the date the report was generated in one of the fields on the PDF.

The idea is to make this completely autonomous without the user needing to do anything other than trigger it.

Please help with how this might be accomplished and help determine how close I can get to the ideal state. There may need to be some considerations for field formats like $ or numbers with commas, I am hoping the PDF will handle this.

Thank you!

Answers