A User Macro to Create a Table From an Attached CSV File Part Two

[#1]

This is part two of an article about creating a user macro that renders out an attached CSV file as a table. Part one of the article has the code for this user macro. I cannot put the code here as well because I am limited to 20,000 characters. So, if you want to follow along I would recommend copying the user macro template from part one into a text editor to refer to it.

If you are familiar with user macros most of this is pretty straight forward. At the beginning of the user macro (lines 1-5) we set some parameters. Specifically the file name of the attachment, whether the top row should be a header or not, and the page that the CSV file is attached to.

The next bit of code (lines 7-97) essentially gets the download url of the attachment based on the file name and page name you entered into the user macro properties. It also gets all the CSV attachments on the page you selected and puts them into an alphabetized array ... more on that later.

We're going to skip the next section and go to the end of the user macro. We'll come back to it in a second as it is somewhat out of the ordinary for a user macro.

This last section (lines 181-232) is where we render out the CSV file to the page using JavaScript. Basically, we take the download url from the top section and do an AJAX call to get the data from the csv file. The data is parsed into a two dimensional array of rows and cells. Once we have the data in the array it is simply a matter of looping through the rows and cells to create the table.

Ok, now back to the section that was skipped (lines 99-179). What in the world is going on there? Well, if you have created user macros before you know that your choice of field types is somewhat limited. For instance there is no field type for page attachment. Which means that you have to know the name of the csv file on the page prior adding the user macro to your page and then type that file name into a free text box ... and don't typo it or you won't see anything.

I didn't like that idea so much. Ideally, it would be nice if we had a field type of confluence-attachment that you could specify a file mask to get a dropdown of attachments. And then be able to tie that to a confluence-content field so that the attachments would be updated if you specify a different page. But alas, that field type does not exist. So, I found a workaround.

I found that I could detect if the user macro was being rendered on the page or in the user macro preview window. If the user macro is being rendered in the preview window I break out of that preview window, hide the file name text box in the user maco properties, and add in my own dropdown field with the csv files attached to the page that was entered using the velocity array that I created before. If the user macro is being rendered on the page this section of code is not executed at all.

When you select one of the drop down options it programatically updates the hidden free text field. Which in combination with the page name field is what is actually used to create the table.

Also if you change the page name field it will cause the preview pane to refresh and thus re-poll the attachments from the standpoint of the new page that you just selected and then populate the dropdown with the new attachment list.

Such a hack I know. But hey, now I can have a nice attachment dropdown field in a user macro. That means my users and I don't need to know the file name ahead of time and we won't typo it.

[Fabienne Gerhard]

Hey @Davin Studer thanks for this nice article - that's something I was looking for - definitely gonna try it.  Love the workaround for the attachment field! 

Will give more feedback after trying!