cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeffzimcase
New Member

Using Power Automate to generate a printable calendar in HTML

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:

jeffzimcase_0-1634315316800.png

 

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

jeffzimcase_1-1634315906426.png

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?

1 ACCEPTED SOLUTION

Accepted Solutions
jzcase
Resolver III
Resolver III

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.

 

jzcase_0-1634739520891.png

 

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:

jzcase_1-1634739829230.png

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.

jzcase_2-1634740430656.png

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

jzcase_3-1634740662839.png

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'),'&nbsp;')

 

 

 

 

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

jzcase_4-1634741091603.png

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!

jzcase_5-1634741324211.png

 

View solution in original post

5 REPLIES 5
jzcase
Resolver III
Resolver III

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.

 

jzcase_0-1634739520891.png

 

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:

jzcase_1-1634739829230.png

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.

jzcase_2-1634740430656.png

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

jzcase_3-1634740662839.png

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'),'&nbsp;')

 

 

 

 

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

jzcase_4-1634741091603.png

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!

jzcase_5-1634741324211.png

 

View solution in original post

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

jzcase
Resolver III
Resolver III

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
Resolver III
Resolver III

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.

jzcase_0-1634760845614.png

 

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
Resolver III
Resolver III

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:
    jzcase_0-1634935259318.png

     

  • 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.
    jzcase_1-1634935423418.png

     

    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.
    jzcase_2-1634935725078.png

     

  • Otherwise, append to CalendarHTML all of the HTML and variables for one week.
    jzcase_3-1634936011864.png

    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
		), 
		'&nbsp;', 
		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 '
				), 
				''
			)
		)
	),
	'&nbsp;'
)

 

 

  • 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.
    jzcase_4-1634936601999.png

     

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,602)