Using Power Automate to generate a printable calendar in HTML

[Anonymous]

I have a canvas app in which some school physical therapists are inputting therapy notes for students. One of the reports they need to export is a "calendar of attendance" that shows one student's activity at a glance across the school year. It should look somewhat like the following:

I used Microsoft Word to spit out an HTML template that gives me a nice looking report.

Each "week" in these calendars is two HTML <TR>'s, one to contain the date and one to contain the attendance data.

Now I'm just trying to work out in my head how Power Automate will populate these rows with the correct dates and their corresponding student attendance data.

Now, if I need to reinvent the wheel with this, I absolutely can (and I'll document it here), but before I go any further: Is anyone here aware of similar existing solutions that I might adapt to this purpose?

[jzcase · Accepted solution]

EDIT: STOP! There's a better version of this calendar now! See here

Here is how to make Power Automate draw a printable calendar in HTML (so that you can save and convert to PDFs). There is also the problem of how to populate this calendar with data, but I will tackle that separate.

1. Initialize an array variable MonthCells as [0,1,2,3,4... 41]. This is the maximum number of possible cells a month will use (42 cells, i.e., 6 rows of 7).

2. Initialize a boolean variable MonthComplete as false. This is a control we will use to break out of the calendar-drawing loop so that it doesn't create too many rows.

3. Initialize an array variable Months with the months you want to appear in the order you want them to appear. In my case, I use [8,9,10,11,12,1,2,3,4,5,6] because I want to capture the months in a school year.

4. Initialize a string variable ReportHTML containing all of the HTML in your document just before it starts drawing the first month. I recommend designing your template in Word and saving it as HTML, and then copy-pasting the code here. If you have data that needs to populate part of the HTML, you can insert that data here.

NOTE: In your Word document, I recommend placing all of your content (regardless of left-right-center justification) into a single-cell borderless centered table. This will keep your margins equal on both sides of the HTML document when it is printed.

5. Create a ForEach loop using the Months array

A. Compose Month1st that uses expressions to get the first date of that month. This will look a little different for everybody. In my case, I have a student file with a Year field formatted like: "2021-2022," so I use some logic and substrings to make sure I get the correct year depending on which month I'm pulling:

if(
	greater(
		int(
			item()
		),
		7
	),
	formatDateTime(concat(item(),'/1/',substring(outputs('Get_File')?['body/_case_yearid_value@OData.Community.Display.V1.FormattedValue'],0,4))),
	formatDateTime(concat(item(),'/1/',substring(outputs('Get_File')?['body/_case_yearid_value@OData.Community.Display.V1.FormattedValue'],5,4)))
)

B. Compose 1stCell that captures the date represented by the upper-left-most cell of each calendar month.

addDays(outPuts('Month1st'),mul(-1,dayOfWeek(outputs('Month1st'))))

C. Append to the ReportHTML variable the HTML for the top part of each calendar, comprising the label and the first row with the weekday labels, basically this stuff.

For the month name, just use: formatDateTime(outputs('Month1st'),'MMMM')

D. Create a ForEach using the MonthCells array, and put everything into a Condition, If MonthComplete is false, so that we can break out of the loop by setting MonthComplete to true:

i. Ignore the "Get This Days Data" and "CellContent" compositions, I will get to those later.

ii. First we want to check three things that will determine when our loops should stop drawing more rows:

a. If the value of the current MonthCells item() is evenly divisible by 7 (this means it's a Sunday): 

mod(item(),7) is equal to 0

b. If the current item() is NOT the upper-left-most cell: item() is not equal to 0

c. If the month of the current cell is NOT the same as the current month.

formatDateTime(addDays(outputs('1stCell'), item()), '%M') is not equal to formatDateTime(outputs('Month1st'), '%M')

If all three of those things are true, set MonthComplete to true, and the loop will break and go on to the next month.

iii. When those conditions fail, Append to the ReportHTML variable the HTML for the new cell.

We need to put in several conditionals so that the cell formats correctly. Here they are in the order they appear in the picture above:

If the current MonthCell is divisible by 7, insert <tr> tags to conclude the previous row and start a new one:

if(equals(mod(item(),7),0),'</tr><tr style="mso-yfti-irow:2;mso-yfti-lastrow:yes">','')

If the month of this cell's date is not equal to the current month, make the cell background a light gray color:

if(not(equals(formatDateTime(addDays(outputs('1stCell'), item()), '%M'),formatDateTime(outputs('Month1st'), '%M'))),'background:lightgray;','')

Print the date of this cell:

formatDateTime(addDays(outputs('1stCell'),item()),'%d')

If the month of this cell's date equals the current month, print the data for this cell, otherwise print an &nbsp;

if(equals(formatDateTime(addDays(outputs('1stCell'), item()), '%M'),formatDateTime(outputs('Month1st'), '%M')),outputs('CellContent'),' ')

E. Outside of the MonthCells loop, append the concluding HTML to your ReportHTML variable, and then set MonthComplete to false.

From there, you can do anything you want with the HTML! Save it to OneDrive and convert to PDF (though this stinks at the moment, see below), or publish to a webserver.

Here is the result of my workflow!

[AnthonyAmador]

Awesome, thanks for sharing this, it will be useful for other users in the future. 

[jzcase]

BONUS: Don't want calendar months getting cut off between pages?

1. Initialize an integer variable MonthsIndex = 0 with all your other variables
2. Replace step 'E' in the above instructions with the following:

if(
	or(
		equals(variables('MonthIndex'),1),
		equals(variables('MonthIndex'),4),
		equals(variables('MonthIndex'),7)
	),
	'</tr></table><div style="page-break-after: always;">&nbsp;</div>',
	'</tr></table>'
)

• At the end of the "For each month" loop, increment MonthIndex by 1.

This will put a page break after the 2nd, 5th, and 8th month, essentially showing 2 months on the first page, and 3 months on each subsequent page, with no cutoffs.

[jzcase]

I do have a problem, which is that the OneDrive Convert to PDF action is terrible for this application. Look what it did to my boy.

My users are better off downloading the HTML file, opening it in a browser, and printing it to PDF. But I wish there was a more elegant way.

[jzcase]

OK here is Power Automate printable calendar version 2!

This version has much better performance, completes in less than half the time as the old one, because we draw the calendars one whole week at a time (thanks to ekarim2020's help).

1. Initialize an array Weeks as [0,1,2,3,4,5] (no calendar month will require more than 6 rows). This replaces the "MonthCells" in the previous method.
2. Initialize boolean MonthComplete as false
3. Initialize an array Months with the months you want to appear in the order you want them to appear. In my case, I use [8,9,10,11,12,1,2,3,4,5,6] because I want to capture the months in a school year.
4. Initialize string MinutesJSON where we will store the JSON object containing the data that needs to go into our calendar cells.
5. Initialize boolean CalendarComplete as false (optional if you wish to stop drawing the calendar once it reaches the present date, to save time).
6. Initialze string CalendarHTML containing all of the HTML in your document just before it starts drawing the first month. I recommend designing your template in Word and saving it as HTML, and then copy-pasting the code here. If you have data that needs to populate part of the HTML, you can insert that data here.

   NOTE: In your Word document, I recommend placing all of your content (regardless of left-right-center justification) into a single-cell borderless centered table with 0.01pt padding on the left. This will keep your margins equal on both sides of the HTML document when it is printed. The padding helps in case you have bordered tables within your document--I have found that 0 padding causes border lines to disappear.
   
   

7. Shape your calendar data into a JSON object (an array will NOT work). Use the date of each item as that item's label. For more information and one sample method, see this solution.
8. Create a Composition action labelled MinutesJSON and use a json() expression to make your data referenceable. 
9. Create a ForEach loop using the Months array
   1. Place all the steps into a Condition: CalendarComplete is equal to false so that we can break out of the loop when you don't want to draw any more months.
   2. Compose Month1st that uses expressions to get the first date of that month. This will look a little different for everybody. In my case, I have a student file with a Year field formatted like: "2021-2022," so I use some logic and substrings to make sure I get the correct year depending on which month I'm pulling:
      

if(
	greater(
		int(
			item()
		),
		7
	),
	formatDateTime(concat(item(),'/1/',substring(outputs('Get_File')?['body/_case_yearid_value@OData.Community.Display.V1.FormattedValue'],0,4))),
	formatDateTime(concat(item(),'/1/',substring(outputs('Get_File')?['body/_case_yearid_value@OData.Community.Display.V1.FormattedValue'],5,4)))
)​

• Compose 1stCell that captures the date represented by the upper-left-most cell of each calendar month.
  

addDays(outPuts('Month1st'),mul(-1,dayOfWeek(outputs('Month1st'))))​

• Append to the ReportHTML variable the HTML for the top part of each calendar, comprising the label and the first row with the weekday labels, basically this stuff:
  
• Create a ForEach using the Weeks array, and put everything into a condition: If MonthComplete is false, so that we can break out of the loop by setting MonthComplete to true.
  
  1. We need to check for two things to decide whether to draw the next week of our calendar:
     1. This week is not week 0

item() is not equal to 0​

• Sunday's date has a different month than this month

formatDateTime(addDays(outputs('1stCell'), mul(item(),7)), '%M')
is not equal to
formatDateTime(outputs('Month1st'), '%M')​

• If those are both true, set MonthComplete to true, and do nothing else. This breaks the weeks loop and goes on to the next month.
  
• Otherwise, append to CalendarHTML all of the HTML and variables for one week.
  
  

  You're going to repeat those three expressions 7 times, one for each day, each one with a slight difference to make sure you're referencing the correct day of the week. Here they are in the order they appear above:
  If this day is within the current month, set this cell's color to light gray (for a Monday)

if(
not(equals(formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), '%M'), formatDateTime(outputs('Month1st'), '%M'))), 'background:lightgray;', '')

Print this cell's date as just the day (for a Monday)

formatDateTime(addDays(outputs('1stCell'), mul(item(),7)), '%d')

If this day is within the current month, print the appropriate data from our MinutesJSON object (for a Monday)
(fair warning, this one's a little ridiculous, but string expressions often are)

if(
	equals(
		formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), '%M'), 
		formatDateTime(outputs('Month1st'), '%M')
	),
	if(
		equals(
			outputs('MinutesJSON')?[formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), 'd')],
			null
		), 
		' ', 
		concat(
			if(
				not(
					equals(
						outputs('MinutesJSON')?[formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), 'd')]?['AttCode'], 
						''
					)
				), 
				concat(
					substring(
						outputs('MinutesJSON')?[formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), 'd')]?['AttCode'], 
						0, 
						indexof(
							outputs('MinutesJSON')?[formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), 'd')]?['AttCode'],
							' '
						)
					), 
					' '
				), 
				''
			), 
			if(
				greater(
					float(
						coalesce(
							outputs('MinutesJSON')?[formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), 'd')]?['Direct'], 
							0
						)
					), 
					0
				), 
				concat(
					outputs('MinutesJSON')?[formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), 'd')]?['Direct'], 
					'D '
				), 
				''
			), 
			if(
				greater(
					float(
						coalesce(
							outputs('MinutesJSON')?[formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), 'd')]?['Consult'], 
							0
						)
					), 
					0
				), 
				concat(
					outputs('MinutesJSON')?[formatDateTime(addDays(outputs('1stCell'), add(mul(item(),7),1)), 'd')]?['Consult'], 
					'C '
				), 
				''
			)
		)
	),
	' '
)

• After the loop, append some HTML to the CalendarHTML to close the table, and if you wish, insert a page break after certain months. In the example below, I page break after September, December, and March.

if(or(equals(item(), 9), equals(item(), 12), equals(item(), 3)), '</tr></table><div style="page-break-after: always;">&nbsp;</div>', '</tr></table>')​

• Set MonthComplete to false
• Set Calendar complete to true if utcNow()'s month is the same as Month1st's month (this will prevent the workflow from drawing months in the future, if that's your jam).

if(equals(utcNow('%M'), formatDateTime(outputs('Month1st'), '%M')), true, false)​

• Outside the Months loop, append the last bit of HTML to your CalendarHTML variable.
  

That's it! I do NOT recommend converting your HTML to PDF, at least not using the built-in OneDrive action. It's bad. Just tell your users to open the HTML in a browser and print to PDF.

[GlennBullion]

I'm trying to follow your steps above to create something similar.  I'm trying to generate an HTML calendar from a Sharepoint list, with vacation dates.  We'd only need to print one month at a time, so no need for a Months array, etc.

Could you clarify this step below?

You're going to repeat those three expressions 7 times, one for each day, each one with a slight difference to make sure you're referencing the correct day of the week. Here they are in the order they appear above:

What is this "slight difference" between days in your HTML? I'm so close to getting a nice looking calendar created (Step one. The actual data will be step two 🙂 ).

In my test calendar, I can't get Monday - Saturday (Sunday is fine) to represent the rights days. They are actually repeats of Sunday. I'm not sure what to change per "day" to make it grab the right day.

[jzcase]

Ah ok.

So you're concatenating each week, and each concatenation includes 7 day's worth of HTML.

The thing you're missing is that the code for the date needs to increment the date each time.

So Sunday's date looks like:

formatDateTime(addDays(outputs('1stCell'),item()),'%d')

Monday's date will be:

formatDateTime(addDays(outputs('1stCell'),add(item(),1)),'%d')

 Tuesday:

formatDateTime(addDays(outputs('1stCell'),add(item(),2)),'%d')

 Wednesday:

formatDateTime(addDays(outputs('1stCell'),add(item(),3)),'%d')

 Thursday:

formatDateTime(addDays(outputs('1stCell'),add(item(),4)),'%d')

 Friday:

formatDateTime(addDays(outputs('1stCell'),add(item(),5)),'%d')

 Saturday:

formatDateTime(addDays(outputs('1stCell'),add(item(),6)),'%d')

[GlennBullion]

That did it. Thanks much. 🙂 The generated calendar looks excellent.

Now, it's data time. Going to follow your steps to get a Sharepoint list into a JSON file for reference.

[GlennBullion]

Hopefully last question. 🙂  Sorry for the noob JSON questions.

With your method above, is your calendar system designed to only have one item in a day? I'm trying to build a vacation calendar, which would have multiple user names in a day. But since you're using the data as a label, can you store multiple items under a single data, and still be referenced?

[jzcase]

Sorry for the late reply. I don't seem to get notifications from the community.

Your idea is doable but tricky. You're right that the above calendar only has 1 data output per day. The key is to make that 1 data output into a mini HTML table containing all of the vacations for that day.

So you've got a JSON array of vacations. For each day, you'll need to filter the vacations array by the date, and then loop the result with an Append to String variable to create a mini-HTML table.

Since you're generating the calendar one week at a time, you'll need 5 string variables, containing the HTML table for each workday.

monVacations

tuesVacations

wedVacations

thursVacations

friVacations

Initialize each variable with the beginning HTML for the table.

When your workflow is at the point generating the HTML for each week, before it appends to the HTML string, I would do the following:

1. Filter the JSON array for vacations occurring monday
2. For each of the results, append to string variable mondayVacations the HTML for a row containing vacation data
3. Conclude the HTML table for mondayVacations
4. Repeat steps 1-3 for each work day

Then, when you append the HTML for the week of the month, stick those [weekday]Vacations variables in there, and you have your multiple data-points per day.

Sorry to tackle this so abstractly, I haven't been deep in the code here for a while.

[GlennBullion]

Thanks for all this. I did end up coming up with a solution that works for us, using your previous methods.

I changed from generating a week at a time, as used in your second method, back to your first method of generating each day at a time. For us, I needed to manipulate that data a bit more (we add 4 or 8 hours, sick or vacation, etc.). It was just easier to do this on a "day to day" generation. We also color code by department, so had to add in a background color as well.

The result came out very well. Takes about 1 minute for the flow to run. A Power App pushes the choices to the Flow (what Month and Year to retrieve, who to email, etc).

I'm still trying to fine tune things, hopefully take out another Apply to Each loop, but all the work above made this so much simpler.

[jzcase]

Fantastic. Yes I thought about possibly switching back to 1 day at a time. Definitely easier from a developer's standpoint!

[rhulse]

Can you post an expanded view of the flow, I may be misunderstanding one of the loops and placing a step in the wrong location.  Much appreciated and thank you for taking the time to post this, I am nearly done following your steps to create a PTO/Birthday/Anniversary calendar.  This will be a huge help replacing our current method.