Designing an Excel template for a report that does not have data in every row

One of my fellow workers wanted that certain columns in a report would not be displayed. I told him that the HTML template does not display these columns, which is when I realised that he is probably exporting the data to Excel. To the best of my knowledge, Excel does not honour designed templates and shows all the columns. The answer was to create an Excel template that hid the required columns. 

I have never (knowingly) done this before so I wasn't sure how to start. I right clicked on the appropriate menu option and chose 'design Excel template'; I indicated that I was creating a new template. I then filled in the parameters for the report and after a while Excel displayed the data. I deleted the appropriate columns in the spreadsheet then saved it. When I tried to create the report again using this template, the unwanted columns still appeared.

After receiving some help, I was told to add a new worksheet then copy the appropriate data from the 'datasheet' worksheet. I thought that I would be clever, so I added this formula to cell A1:

=datasheet!A:L

Whilst this effectively copied the appropriate data (I added the formula =datasheet!P:V to cell M1), it also copied over a million empty lines! In other words, I had to find a formula for copying the contents of a column until an empty cell is reached. I reached for my trusty AI and received this formula

=FILTER(DataSheet!A:A, (DataSheet!A:A <> ""))

I then copied this formula to the appropriate column headers ensuring that I was accessing the appropriate column in the datasheet (i.e. skipping over three columns). This seemed to be correct, but unfortunately was not, as some columns have empty cells in rows where other columns do have values, and so the data was 'non-aligned'. I knew that columns A-C would have data in every row, so I asked the AI program how I could use these columns as a reference. Below is the final formula, for column D, that shows how each column can use column A (the dollar sign means "don't change the column when the formula is copied to another column). Again, the formula for column M (the column that shows data taken from column P) simply subtitutes P for D in the formula.

=FILTER(DataSheet!D:D, (DataSheet!$A:$A <> ""))

In my humble opinion, this is sufficiently non-intuitive to warrant documenting it here.

String length bug found

In a procedure that sends email, I had the following expression

:EMAIL = (:$.DBG = 1 ? 'noamn@testing.com' : :EMAIL);

The value of :EMAIL prior to this expression was sigall@somecompany.co.il - the length of this string is 24 characters, whereas the length of noamn@testing.com is 17 characters. Regardless of the value of :$.DBG, the resulting string would have a length of 17 characters, and so if :$.DBG = 0, there would be an attempt to send an email to the non-existing address sigall@somecompan; of course, this would fail.

As always, it took me quite some time to find out why the email address in the letter was being truncated but took only a minute to fix. If my address is assigned to a variable prior to evaluation, then the resulting value will have the correct length.

:EMAIL = 'sigall@somecompany.co.il';
:NOAM = 'noamn@testing.com';
:EMAIL = (:$.DBG = 1 ?  :NOAM : :EMAIL);

If :$.DBG = 0 then :EMAIL = 'sigall@somecompany.co.il'. If :$.DBG = 1 then :EMAIL = 'noamn@testing.com'.