Part 3: Pixel perfect reports with Skia (AppSheet's PDF generator)

[SkrOYC]

Well, it's about time, isn't it? 😅
Some months ago I told you I'd make a final part 3 talking abour tips to get you started on HTML+CSS templating for your AppSheet reports.
Between part 2 and today (the writting today, which is months before the posting today 😅) I had a lot to do that prevented that and I'm into it now, finally.

• 1. Objetive
• 2. Use cases
• 3. Planning before the execution
• 3.1. Notification per row entry
• 3.2. Invoices with multi company support
• 3.3. Weekly list of expenses per employee
• 3.4. Reports from a custom dashboard
• 4. The making of the templates
• 4.1. Notification per row entry
• 4.2. Invoices with multi company support
• 4.3. Weekly list of expenses per employee
• 4.4. Reports from a custom dashboard
• 5. The templates...
• 5.1. Notification per row entry
• 5.2. Invoices with multi company support
• 5.3. Weekly list of expenses per company and employee
• 5.4. Reports from a custom dashboard

---

1. Objetive

To get started, I'll point you to the most common reports I think you would need to do and how I'd approach them with my limited-but-practical knowledge of HTML+CSS for AppSheet.
Then I will show you the actual code and how I did it and I'll make my best effort to explain things as clear as possible in the process.
Finally I'll add some tips to follow no matter the kind of template/report you need. Let's go!

---

2. Use cases

This is a list I think covers enough templates for us to practice.

1. Email per entry on table as notification
2. Invoices with multi company support, so multi-logos and stuff, sended at will to a list of personalized emails
3. Weekly lists of expenses per employee
4. Reports from custom dashboard

As you can see, this will not be just about the template but also the config needed for the report generation via automation. So it's like 4-8 posts into one 😁

---

3. Planning before the execution

I'm making available a Sample App with each use case in order to make this post with sample data as well as provide you with the base you need to understand all of this. Don't underestimate the "Look under the hood" button.
Also I'm thinking of using this sample app to practice more use cases so that I can polish them before I apply them to production apps. This may sound silly (too much work) but I think it will be safer to do this way rather than moving things around on production and maybe sometime in the future AppSheet will allow the modularity we need so that we could use just the parts we need on other apps. Also you will take benefit from the new use cases I'll be adding that are not part of this post.

3.1. Notification per row entry

Eventhough we could trigger this on any data change, even deletion, I made this on Adds only because it's the most common and reliable I personally use.
This is the easier one of the 4 use cases so let's break it's parts

1. Tables: Just one table, called "feedback"
2. Automation: Just one bot, one trigger (add), one process with one task, send an email to the user that added a row to "Feedback"
3. Template: Simple template with the contents of the row that was added.

3.2. Invoices with multi company support

This is not too hard, it'll be trigger on adds also to the "send_invoice" table which selects an invoice from the "Invoices" table. We will be able to select the emails we want this to go thanks to other tables. ("company", "employee", "client" and "client_contact").
Although there are more tables involved, this will help you if you have a very simple setup and you expect that setup to work on a complex way. You may notice that you are expecting too much. Also, don't be afraid of adding more tables as needed for your apps.
Breaking it into parts:

1. Tables: More tables than the first one but easy to manage anyway (7 tables).
2. Automation: One bot, one trigger (Add), one process with one task, send an email to a list of emails from the "company", "client", "client_contact" and "receiver" tables.
3. Template: Kinda-simple template but with a little twist. Since we want to show different logos depending on the company, we will use a format rule to just get the url of the logo so that we can use that logo wherever we want on the template and customice it's size and more. Also we will show all of the children of the invoice (the "invoice_item") using Start:.

3.3. Weekly list of expenses per employee

This is a little bit more interesting since it's a scheduled report.

I like them, I'm using a lot of them currently to update real columns with expensive expressions to limit the usage of virtual columns with the corresponding App Formula. I guess that's for another post.

The important thing here will be to understand that the trigger can be attached to a certain table but it's not needed, as we may be used to.
TLDR:

• Am I taking a look at all of the rows from outside? Then ForEachRowInTable is not needed
• I want this to be executed for multiple rows? Then ForEachRowInTable is needed.

This can be confusing for some but let's understand this by using our Sample App. We have companies and each company has employees. We can do two things:

1. Send a PDF via email that has all of the companies and their corresponding list of employees inside of it.
2. Send a PDF per company via email that has their corresponding list of employees on each one.

The first one doesn't need "ForEachRowInTable" while the second one will need "ForEachRowInTable" quering the "Company" table. See? Not that hard.

This report will be using another table called "expense".

Breaking it down:

1. Tables: 3 already mentioned above, company, employee and expense.
2. Automation: Single bot, one trigger (weekly), one process with one task, send an email with a PDF that has all of the companies and employees with the expenses they made this week.
3. Template: This is a little bit more complex and we will use nested Start: expressions. Since this is the first multi-page report, we will make use of some page breaks as well as some layout stuff. Also don't forget that this is not attached to any table, so we need to use expressions that are context-agnostic.

3.4. Reports from a custom dashboard

To be honest, this is basically the same as the scheduled one but with a different trigger. In other words, if you manage to do the previous one this will be easy. What's different is that we will make use of a Slice that listen to changes made to a row on the "filter" table.

1. Tables: Same as the scheduled one but using a Slice + a "report" table that will serve as a helper for our automation.
2. Automation: Single bot, one trigger (add to "report"), one process with one task, send an email with a PDF that has the companies, employees and expenses previously filtered.
3. Template: Similar to the scheduled one but we will give it a twist since we will be using a slice this time, but, again, it's almost the same.

---

4. The making of the templates

This is also part of the planning but focused on the templates specifically.

4.1. Notification per row entry

The easiest one.
Just some basic data from the added row.
This is a peak at the result:

4.2. Invoices with multi company support

Here we will make extensive use of tables. It's not the only way but it's the simpler.
The more interesting side of this template is the usage of custom color schemes that's different for each company and how the logo is also changed.
Also you will see that we can define styles with our CSS so that no matter how much tables we use each one will have the same styling based on our color scheme. Sometimes we need to make a cell wider so that it takes the space of 2 or more columns or taller so that it takes the space of 2 or more rows, we will also do that here.

4.3. Weekly list of expenses per employee

The one I'm more eager to do, a big paginated document with all of the companies, employees and their expenses. Here I'll use some "display: flex" stuff (more on that later) and the expenses are going to be shown using tables. Then each company will have a total of employees, average of expenses per employee as well as total expenses (company wise).
Finally, each company is shown on the right page, and each employee's expenses have a page break before. You will understand this better later.

4.4. Reports from a custom dashboard

As I told you before, this is similar to the previous one. I'll use the same template actually. The only difference will be a timestamp and the email of the user that generated the report, you...

5. The templates...

Here is the actual code. I suggest you to read my previous post in order to understand most of what we are going to see on this one.
The basic stuff will be the following under the head tag:

Show More

<link
    rel="stylesheet"
    type="text/css"
    href="https://fonts.googleapis.com/css?family=Open+Sans"
/>
<style>
    @Page {
        size: 8.5in 11in portrait;
        margin: .3in;
    }
    @Page:right {
        margin-left: .5in;
    }
    @Page:left {
        margin-right: .5in;
    }
    body {
        font-family: "Open Sans";
    }
</style>

This is giving our PDFs a letter size and portrait orientation as well as Open Sans font-family for everything.
I won't mention this again, it will be on all of our templates.
Now let's check the specific code for each of the templates.

5.1. Notification per row entry

As said before, the most basic one:

HTML

Show More

<body>
    <h1>
    Thanks for your feedback!
    </h1>
    <p>We are always trying to improve so your feedback is very valuable in order to understand the real needs of our users.</p>
    <p>This is the data you provided:</p>
    <blockquote>&lt;&lt;[comments]&gt;&gt;</blockquote>
    <p>If you want to add more comments or you made a typo, please send us new feedback. We read all of your messages!</p>
    <div class="flex-container">
    <p>&lt;&lt;CONCATENATE(UPPER([user]), " - AT ", TEXT([timestamp], "MM/DD/YYYY"))&gt;&gt;</p>
    </div>
</body>

CSS

Show More

div.flex-container {
    display: flex;
    justify-content: center;
}

I added a little detail, the "display: flex". This was present on the previous post but I didn't explain what it does. We will make use of it on the other templates and basically it's a way to layout the inner items inside of the tag that has it. In this case we just have a <p> tag and the justify-content: center; help us center it horizontally. If we had more than just one tag all of them are shown by default on the same row and with equal space on left and right, each one aside the other.

5.2. Invoices with multi company support

This is where it starts to get interesting.
As always, we need to understand which data is available before we make a template, so check the Sample App to understand the fields.
We are going to add a very cool concept called variables. This may be obvious or completely new for you so I'll try to explain it using plain english.
A variable is a data holder that can be changed and used at any time. In this case, we are going to use variables that will hold our primary and secondary colors from the company the invoice belongs to. This way our invoices/templates will have a different look depending on the company. Cool, right?
This is how we define our variables:
CSS

Show More

:root {
    --company-primary-color: <<[invoice_key].[company_key].[primary_color_hex]>>;
    --company-secondary-color: <<[invoice_key].[company_key].[secondary_color_hex]>>
}

We will make use of them later.
Since these templates are going to be more difficult from now on, I'll add all of the code and then explain from top to bottom.

HTML

Show More

<body>
    <h1 class="title">Invoice #&lt;&lt;[invoice_key].[number]&gt;&gt;</h1>
        <div class="container company-data">
            <div class="company-text">
                <p>
                    <b>&lt;&lt;[invoice_key].[company_key].[name]&gt;&gt;</b><br>
                    &lt;&lt;[invoice_key].[company_key].[address]&gt;&gt;<br>
                    &lt;&lt;[invoice_key].[company_key].[email]&gt;&gt;
                </p>
            </div>
            <div class="company-logo">
                <img src="&lt;&lt;[invoice_key].[company_key].[logo]&gt;&gt;" alt="Company Logo" width="180">
            </div>
        </div>
    <table class="header">
        <thead>
            <tr>
                <th>Buyer</th>
                <th>Receiver</th>
                <th colspan="2">Dates</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td rowspan="3">
                    <b>&lt;&lt;[invoice_key].[client_key].[name]&gt;&gt;</b><br>
                    &lt;&lt;[invoice_key].[client_key].[address]&gt;&gt;<br>
                    &lt;&lt;[invoice_key].[client_key].[email]&gt;&gt;
                </td>
                <td rowspan="3">
                    <b>&lt;&lt;[invoice_key].[receiver_key].[name]&gt;&gt;</b><br>
                    &lt;&lt;[invoice_key].[receiver_key].[address]&gt;&gt;<br>
                    &lt;&lt;[invoice_key].[receiver_key].[email]&gt;&gt;
                </td>
                <td><b>Invoice Date:</b></td>
                <td>&lt;&lt;TEXT([invoice_key].[emission_date], "MM/DD/YYYY")&gt;&gt;</td>
            </tr>
            <tr>
                <td><b>Payment Period:</b></td>
                <td>&lt;&lt;[invoice_key].[payment_period]&gt;&gt; days</td>
            </tr>
            <tr>
                <td><b>Due Date:</b></td>
                <td>&lt;&lt;TEXT([invoice_key].[due_date], "MM/DD/YYYY")&gt;&gt;</td>
            </tr>
        </tbody>
    </table>
    <table class="invoice-items">
        <thead>
            <tr>
                <th>Item description</th>
                <th>Quantity</th>
                <th>Unit Price</th>
                <th>Total</th>
            </tr>
        </thead>
        <tbody>
            <p class="startifend">&lt;&lt;Start:[invoice_key].[Related invoice_items]&gt;&gt;</p>
            <tr>
                <td>&lt;&lt;[description]&gt;&gt;</td>
                <td>&lt;&lt;[quantity]&gt;&gt;</td>
                <td>&lt;&lt;[unit_price]&gt;&gt;</td>
                <td>&lt;&lt;[total_price]&gt;&gt;</td>
            </tr>
            <p class="startifend">&lt;&lt;End&gt;&gt;</p>
        </tbody>
    </table>
    <table class="items-totals">
        <tr>
            <th>Subtotal</th>
            <td>
            &lt;&lt;SUM(
                SELECT(
                    invoice_item[total_price],
                    [invoice_key]=[_THISROW].[invoice_key]
                )
            )&gt;&gt;
            </td>
        </tr>
        <tr>
            <th>VAT 19.0%</th>
            <td>
            &lt;&lt;SUM(
                SELECT(
                    invoice_item[total_price],
                    [invoice_key]=[_THISROW].[invoice_key]
                )
            )*0.19&gt;&gt;
            </td>
        </tr>
        <tr>
            <th>Total with taxes</th>
            <td>
            &lt;&lt;SUM(
                SELECT(
                    invoice_item[total_price],
                    [invoice_key]=[_THISROW].[invoice_key]
                )
            )*1.19&gt;&gt;
            </td>
        </tr>
    </table>
    <div class="container thanks">
        <b>Thanks for your purchase!</b><br/>
    </div>
    <div class="container thanks">
        <span>This invoice was sent to you by &lt;&lt;USEREMAIL()&gt;&gt; at &lt;&lt;_TIMENOW&gt;&gt;</span>
    </div>
</body>

CSS

Show More

p.startifend {
    display: none;
}
div.container {
    display: flex;
    align-items: center;
}
div.company-data {
    justify-content: space-between;
}
table {
    width: 100%;
    border-collapse: collapse;
}
table th {
    background-color: var(--company-secondary-color);
    border: solid 2px;
    border-color: var(--company-secondary-color);
    padding: 8px;
}
b, h1, {
    color: var(--company-primary-color);
}
table.header tr:nth-child(1) td:nth-child(1),
table.header tr:nth-child(1) td:nth-child(2) {
    text-align: center;
}
table.header tr td:last-child {
    text-align: right;
}
table.invoice-items {
    margin-top: 20px;
}
table.invoice-items td {
    text-align: right;
    border-left: solid 2px;
    border-left-color: var(--company-secondary-color);
    padding-left: 8px;
    padding-right: 8px;
}
table.invoice-items tr td:first-child {
    text-align: left;
    border-left: none;
}
table.items-totals {
    width: 33%;
    margin-top: 20px;
    margin-left: auto;
    margin-bottom: auto;
}
table.items-totals th {
    text-align: left;
}
table.items-totals td {
    text-align: right;
}
div.thanks {
    justify-content: center;
}

That's a lot of info, and it's not the most difficult template yet...
Well, let's digest this step by step.

---

HTML:

• h1 is just a header, think about it as a title.
• div is a tag that helps us as a container, we can add anything inside of it. It's like a 1x1 table.
  • There are two other div, one holding the text with the company data and the other one holding an img tag, which is the logo.
    • I added a Format Rule on the Sample App so that we can refer to the [logo] column and receive the URL. If we don't have a Format Rule for that, when you add an image column or similar inside a template, the backend adds it as an image with an anchor/link.
• table is obvious, but it's a little bit complex because of the colspan and rowspan attributes
  • thead is the table header
    • tr is a table row
      • th is a table cell meant for headers
      • colspan makes it take the space of 2 columns, this way we can have a two column layout for the cells bellow
  • tbody is the table body
    • td is a table cell, don't ask me why it's not called tc.
      • rowspan makes it take the space of 3 rows, this way we can have more rows at the right side, which is how the Dates table column have the data using 3 tr
    • notice how the last two tr have only two td inside, that's because we are adding the two cells for the columns inside the Dates column.
  • colspan and rowspan can confuse a lot, but practice will help for sure.
• There is a more "normal" table with no colspan or rowspan which holds the invoice items
  • thead has a tr with 4 th, this is familiar, right?
  • tbody has a p before and after it's only tr. This p is necesary for our Start: expression. Check my previous post for an explanation of it
    • tr is going to be replicated for each row taken from the Start: expression
      • td is the cell with the content from our dataset's column/field, same amout of td as th where in the header.
• Last table has no thead or tbody
  • 3 tr
    • each one with an expression to get the total values from the items displayed above
• Finally, two div to display a message at the bottom

---

CSS:

• p.startifend applies this style to all p tags with a class of "startifend", remember?
• container is a class that I only gave to div and I'm using it to implement "display: flex" and "align-items: center". Flex is a way to layout things, this way the div that has company text and company logo at the top of our template centers them on the same Y axis dynamically (what I mean by this is that it doesn't matter the height of any of the children, they are all displayed aligned at their middle).
• company-data is a class to add more specific configs to the container that has company text and company logo. Check the HTML, the div that holds these two has 2 classes, container and company-data. Here we use "justify-content: space-between" so that each item is touching the template left and right border and the space between them is equal. If we had 3 or more, there would be one at the left border, one at the center and the last one at the right border. Hope you get the idea.
• We are making all of our tables the width of the whole page as well as a the "border-collapse: collapse" which is the expected behaviour from your experience with MSWord or GDocs tables. Change that setting or remove it to understand more.
• th that are inside of a table will have a background color equal to our "--company-secondary-color" mentioned before. We could have placed here an expression that gets the data from AppSheet directly but this way it's more organized. We add a border (it will be almost unnoticeable, I add it because I can see a tiny white border without it) with the same color as background and some padding to make sure the text is not so close to our borders.
• b (bold) and h1 will have a text color equal to our "--company-primary-color"
• Here things get a little bit complicated, but will help as an introduction to advace CSS selectors.
  • The first and second td after the first tr of the table of class header are going to have it's text aligned to the center. These are the Buyer and Receiver data.
  • The last td of all of the tr inside the table of class header are going to have it's text aligned to the right
• table (the table as a whole) of class invoice-items will have a margin at the top of 20px
• td inside of table of class invoice-items will have it's text aligned to the right (because there are mostly numbers), a border with the same color as the table headers and some padding at the left and right
• the first td inside tr and table of class invoice-items is going to ignore the aforementioned text alignment and border so that the text is at the left and with no border at the left side
• table of class items-totals is going to have a width of 33% of the available space (page width basically) and again a margin at the top. The "margin-left: auto" is pushing the table to the right of the page, it's giving as much margin as it can basically.
• the th inside the table of class invoice-items are going to be left aligned
• the td inside the table of class invoice-items are going to be right aligned
• Finally the message at the bottom is also using our container class (check the html) and our thanks class is to add the setting that centers the content on the X axis.

---

Voilà! Not that hard, isn't it? I suggest you to read the explanation of the code with the code side-by-side.

5.3. Weekly list of expenses per company and employee

This is where things get a little bit more complex. Yeah, like if the previous one was easy.
Another thing to consider is that I'm going a little bit further than needed so that you can see how much you can make by using an HTML template. This means that some things will not be explained with much detail but I'm thinking on making a separate post for this and the previous template where I could give a more detailed explanation and some variants for each template.

Similar to the previous one, I'll add the code and then the explanation step by step (I'll do my best).

First, the part I told you was far from needed but was a good playground for me and makes possible a lot of things in the future:

CSS

Show More

:root {
  --qr-code: url("https://quickchart.io/qr?text="); /* I could have left this blank because of the next code */
}

Javascript (Yes, I know)

Show More

var r = document.querySelector(':root');
function getVarValue () {
  var root_all = getComputedStyle(root);
  alert("The value of QR Code is " + root_all.getPropertyValue('--qr-code'));
}
document.addEventListener(
  "DOMContentLoaded",
    function setVarValue() {
      var AppSheetAttachmentURL = "<<_ATTACHMENTFILE_URL>>";
      var QuickChartParameters = "&margin=0&format=svg"
      var newUrl = 'url("https://quickchart.io/qr?text='+ encodeURIComponent(AppSheetAttachmentURL.replace(/amp;/g, "")) + QuickChartParameters.replace(/amp;/g, "") +'")';
      r.style.setProperty('--qr-code', newUrl);
    }
);

This two and some CSS made possible to add a QR code that let's you download a digital copy of the report if you print it. Basically the <<_ATTACHMENTFILE_URL>> inside a QR code.

HTML

Show More

<h1 class="report-title">Weekly expenses<br>per company and employee</h1>
<!-- First Start, the companies one -->
<p class="startifend">
  &lt;&lt;Start:
    SELECT(
      employee[company_key],
      IN(
        [key_employee],
        SELECT(
          [Related expenses][employee_key],
          CONCATENATE(
            YEAR([date]),
            ISOWEEKNUM([date])
          )=
          CONCATENATE(
            YEAR(TODAY()),
            ISOWEEKNUM(TODAY())
          )
        )
      ),
      0=0
    )
  &gt;&gt;
</p>
<div class="flex-container company-header title-logo" style="color: &lt;&lt;[primary_color_hex]&gt;&gt;">
  <h2 class="company-title">&lt;&lt;name&gt;&gt;</h2>
  <img src="&lt;&lt;logo&gt;&gt;" alt="company-logo" width="180">
</div>
<table class="company-header-values">
  <thead style="color: &lt;&lt;[primary_color_hex]&gt;&gt;">
    <tr>
      <th>Employees in the list</th>
      <th>Average spent per employee</th>
      <th>Total spent this week</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>
        &lt;&lt;
        COUNT(
          SELECT(
            [Related employees][key_employee],
            IN(
              [key_employee],
              SELECT(
                [Related expenses][employee_key],
                CONCATENATE(
                  YEAR([date]),
                  ISOWEEKNUM([date])
                )=
                CONCATENATE(
                  YEAR(TODAY()),
                  ISOWEEKNUM(TODAY())
                )
              )
            )
          )
        )
        &gt;&gt;
      </td>
      <td>
        &lt;&lt;
        SUM(
          SELECT(
            expense[amount],
            AND(
              [employee_key].[company_key]=[_THISROW-1],
              CONCATENATE(
                YEAR([date]),
                ISOWEEKNUM([date])
              )=
              CONCATENATE(
                YEAR(TODAY()),
                ISOWEEKNUM(TODAY())
              )
            )
          )
        )/
        COUNT(
          SELECT(
            [Related employees][key_employee],
            IN(
              [key_employee],
              SELECT(
                [Related expenses][employee_key],
                CONCATENATE(
                  YEAR([date]),
                  ISOWEEKNUM([date])
                )=
                CONCATENATE(
                  YEAR(TODAY()),
                  ISOWEEKNUM(TODAY())
                )
              )
            )
          )
        )
        &gt;&gt;
      </td>
      <td>
        &lt;&lt;
        SUM(
          SELECT(
            expense[amount],
            AND(
              [employee_key].[company_key]=[_THISROW-1],
              CONCATENATE(
                YEAR([date]),
                ISOWEEKNUM([date])
              )=
              CONCATENATE(
                YEAR(TODAY()),
                ISOWEEKNUM(TODAY())
              )
            )
          )
        )
        &gt;&gt;
      </td>
    </tr>
  </tbody>
</table>
<!-- Second Start, the employees one -->
<p class="startifend">
  &lt;&lt;Start:
  SELECT(
    [Related employees][key_employee],
    IN(
      [key_employee],
      SELECT(
        [Related expenses][employee_key],
        CONCATENATE(
          YEAR([date]),
          ISOWEEKNUM([date])
        )=
        CONCATENATE(
          YEAR(TODAY()),
          ISOWEEKNUM(TODAY())
        )
      )
    )
  )
  &gt;&gt;
</p>
<h3 class="employee-title" style="color: &lt;&lt;[_THISROW-1].[primary_color_hex]&gt;&gt;">&lt;&lt;name&gt;&gt;</h3>
<table class="expenses">
  <!-- <caption style="color: &lt;&lt;[_THISROW-1].[primary_color_hex]&gt;&gt;">&lt;&lt;name&gt;&gt;</caption> -->
  <thead>
    <tr>
      <th style="background-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;; border-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;">Receipt</th>
      <th style="background-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;; border-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;">Date</th>
      <th style="background-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;; border-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;">Amount</th>
    </tr>
  </thead>
  <tbody>
    <!-- Third Start, the expenses one -->
    <p class="startifend">
      &lt;&lt;Start:
        SELECT(
          [Related expenses][key_expense],
          CONCATENATE(
            YEAR([date]),
            ISOWEEKNUM([date])
          )=
          CONCATENATE(
            YEAR(TODAY()),
            ISOWEEKNUM(TODAY())
          )
        )
      &gt;&gt;
    </p>
    <tr>
      <td><a href="&lt;&lt;receipt&gt;&gt;"><img src="&lt;&lt;receipt&gt;&gt;" alt="receipt" width="80"></a></td>
      <td style="border-left-color: &lt;&lt;[_THISROW-2].[secondary_color_hex]&gt;&gt;">&lt;&lt;date&gt;&gt;</td>
      <td style="border-left-color: &lt;&lt;[_THISROW-2].[secondary_color_hex]&gt;&gt;">&lt;&lt;amount&gt;&gt;</td>
    </tr>
  </tbody>
</table>
<!-- Third End, the expenses one -->
<p class="startifend">&lt;&lt;End&gt;&gt;</p>
<!-- Second End, the employees one -->
<p class="startifend">&lt;&lt;End&gt;&gt;</p>
<!-- First End, the companies one -->
<p class="startifend">&lt;&lt;End&gt;&gt;</p>
<p class="startifend"></p>

CSS

Show More

@Page {
  size: 8.5in 11in portrait;
  margin: .3in;
}
@page:right {
  margin-left: .5in;
}
@page:left {
  margin-right: .5in;
}
body {
  font-family: "Open Sans";
}
p.startifend {
  display: none;
}
div.flex-container {
  display: flex;
}
div.company-header {
  break-before: right;
}
h1.report-title::after {
  content: '';
  background-image: var(--qr-code);
  background-size: 100px;
  background-repeat: no-repeat;
  width: 100px;
  height: 100px;
  position: absolute;
  top: 10px;
  right: 10px;
}
h1 + div.company-header {
  break-before: avoid;
}
div.flex-container.company-header.title-logo {
  justify-content: space-between;
  align-items: center;
}
table.company-header-values {
  margin-top: 20px;
  margin-left: auto;
  text-align: center;
}
table.company-header-values th {
  width: 1.2in;
}
h3.employee-title {
  break-before: page;
}
table.company-header-values + h3.employee-title {
  break-before: avoid;
}
table.expenses {
  width: 100%;
  text-align: center;
  border-collapse: collapse;
}
table.expenses th {
  border: solid 2px;
  padding: 8px;
}
table.expenses td {
  padding-left: 8px;
  padding-right: 8px;
  border-left: solid 2px;
}
table.expenses td:first-child {
  border-left: none;
}
table.expenses tbody tr td:last-child {
  text-align: right;
}

If you look closely, it seems like a lot but it's not that much different from the invoice one.
If you check the HTML code, a lot of it is just my obsession with indentation so my AppSheet expressions look good and I can understand them better after just a simple look.

---

HTML:

From top to bottom:

• The first tag is an h1 of class report-title, so you know what it is, right? I haven't explained this before so here it is: a br is a line break.
• p of class startifend was explained before
  • Maybe the most difficult part to understand (aside from that javascript thing I told you before) are the AppSheet expressions. We are going to use a lot of SELECT() because we are not in the context of any row so all should be queried by "brute force". Let's break them here
    • The first SELECT()
      • Querying the employee table and taking the company key of each employee that will match the criteria.
      • IN() to check if the current employee is part of another list
        • Second SELECT()
          • Querying the related expenses table and taking the list employee key
          • Filtering expenses that were made this week
          • Omiting duplicates because I'm taking the company keys from other table rather than the company one
    • Needless to say, if you have a better, more efficient or shorter expression, post it below!
• div is company-header but also have a style property. Here is where things get different.
  • Before we discussed that the CSS code will always be inside the style tag which is inside another tag called head on our HTML file, but I want to apply different CSS to basically the same tags, so Inline CSS comes to play. We can add CSS code inside our tags to style just that specific tag. In this case we are styling our text color to be the company's primary color in our header.
  • There is an img inside the previous div that has the company logo, this is added dynamically for each company.
• A table tag with global values for each company will also have the company's primary color
  • Here we have some big expressions that are not helpful for you to learn about templating or how to write context-agnostic expressions, I added those just to give more content to the final report.
  • Again, if you have a better, more efficient or shorter expression, post it below!
• Here things get a little more interesting, a second Start: expression.
  • This SELECT() is taking the employees from the [Related employees] dataset if it's mentioned on the expenses of this week.
• h3 is the employee name and it's also styled with a text color matching it's company primary color.
  • The way we select the primary color is interesting because we are on the context of an employee not a company, and since we are inside another Start: expression we know that our company row is one context above, so we have two ways of doing this.
    • Asking a field one context above with [_THISROW-1]
    • Doing a dereference with the company foreign key [company_key].[primary_color_hex]
    • I did it the fancier way 😄
• A table is going to hold the expenses and it's going to have a style almost identical to the one we used on our invoice template.
  • Making use of [_THISROW-1] again
• Our third Start:
  • Adding all the expenses from the [Related expenses] dataset that were made this week.
• Each row has the receipt image, date and amount from the expense table:
  • The receipt column/field has a format rule so that we get the url as text.
  • The date column/field has a border at the left that matches the secondary color, same as the header background, BUT now we are two contexts below the company table so we use [_THISROW-2] (or the company table is two contexts above, whatever you understand this better. Also we could have used a chained dereference expression like [employee_key].[company_key].[secondary_color_hex] but I like the [_THISROW-n] consistency across the template)
  • The amount column/field has the same border as the previous one
• Finally the three End, one per Start: expression.

---

CSS

From top to bottom:

• :root
  • AFAIK I actually don't need this since we are adding it with Javascript, but I made the template before the JS code so I kept it
  • If you remember, this is where we usually define variables, mentioned on Part 2 if I recall.
• p.startifend
  • I talked about this one on Part 2
• div.flex-container
  • Just a handy way to give "display: flex"
• div.company-header
  • break-before: there is a page break before the company-header and this page break makes sure that the page will be at the right or recto
• h1.report-title::after
  • Each element has a before and after pseudo-element, and this is an advance topic. Just understand that this is what places the QR code at the right of the main report title
• h1 + div.company-header
  • Since we said that our company-headers will add a page break before itself, our main title would be left at the first page alone. We don't want that, so this selector says that if the company-header is inmediatly after an h1, then apply a break-before: avoid
• div.flex-container.company-header
  • When there is a div tag that is both, flex container and company-header:
    • justify-content: place the children with the space available between them, so all the horizontal space available is placed at the middle, moving company name to the left and logo to the right.
    • align-items: align items on the Y axis to their middle/center
• table.company-header-values
  • margin-top: a little bit of separation from the previous data
  • margin-left: auto adds as much as available, so the content is pushed to the right
  • text-align: text is center-aligned
• table.company-header-values th
  • When there is a th (table header cell) inside the table whose class is company-header-values
    • width: of 1.2 inches
• h3.employee-title
  • break-before: adding a simple page break before the title/employee name
• table.company-header-values + h3.employee-title
  • Similar to how we implemented an excemtion
• table.expenses
  • width: It takes all the horizontal space available
  • text-align: text is center-aligned
  • border-collapse: this is particular to html tables, needed for the common default border behavior of GDocs or MSWord
• table.expenses th
  • When there is a th inside the table whose class is expenses
    • border: solid line and 2px thickness
    • padding: to add internal space between the data and it's borders
• table.expenses td
  • When there is a td (normal table cell) inside the table whose class is expenses
    • padding-left & padding-right: 8px, so top and bottom is by default
    • border-left: all left borders are solid and 2px thickness
• table.expenses td:first-child
  • The first td inside the table whose class is expenses
    • border-left: no border, because we leave the left side of the table borderless
• table.expenses tbody tr td:last-child
  • The last td inside each tr that's also inside of a tbody that's inside a table of class expenses
    • text-align: right because it's a number

---

That was a big one! The final fourth template will be exactly the same but with added data at the top and different AppSheet expressions since it's now event-driven and not scheduled.

5.4. Reports from a custom dashboard

Already mentioned, this looks almost identical to the previous one, but the data is different, the trigger is different, so our AppSheet expressions need to be different as well.
I'll add all of the content but just mention/explain the differences.

HTML

Show More

<h1 class="report-title">Weekly expenses<br>per company and employee</h1>
<p class="generated-by">Generated by &lt;&lt;UPPER([user])&gt;&gt; at &lt;&lt;TEXT([timestamp])&gt;&gt;</p>
<!-- First Start, the companies one -->
<p class="startifend">
  &lt;&lt;Start:
  SELECT(
    employee[company_key],
    IN(
      [key_employee],
      expense_sl[employee_key]
    ),
    0=0
  )
  &gt;&gt;
</p>
<div class="flex-container company-header" style="color: &lt;&lt;[primary_color_hex]&gt;&gt;">
  <h2 class="company-title">&lt;&lt;name&gt;&gt;</h2>
  <img src="&lt;&lt;logo&gt;&gt;" alt="company-logo" width="180">
</div>
<table class="company-header-values">
  <thead style="color: &lt;&lt;[primary_color_hex]&gt;&gt;">
    <tr>
      <th>Employees in the list</th>
      <th>Average spent per employee</th>
      <th>Total spent</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>
        &lt;&lt;
        COUNT(
          SELECT(
            [Related employees][key_employee],
            IN(
              [key_employee],
              SELECT(
                [Related expenses][employee_key],
                IN(
                  [key_expense],
                  expense_sl[key_expense]
                )
              )
            )
          )
        )
        &gt;&gt;
      </td>
      <td>
        &lt;&lt;
        SUM(
          SELECT(
            expense_sl[amount],
            AND(
              [employee_key].[company_key]=[_THISROW-1],
              IN(
                [key_expense],
                expense_sl[key_expense]
              )
            )
          )
        )/
        COUNT(
          SELECT(
            [Related employees][key_employee],
            IN(
              [key_employee],
              SELECT(
                [Related expenses][employee_key],
                IN(
                  [key_expense],
                  expense_sl[key_expense]
                )
              )
            )
          )
        )
        &gt;&gt;
      </td>
      <td>
        &lt;&lt;
        SUM(
          SELECT(
            expense_sl[amount],
            AND(
              [employee_key].[company_key]=[_THISROW-1],
              IN(
                [key_expense],
                expense_sl[key_expense]
              )
            )
          )
        )
        &gt;&gt;
      </td>
    </tr>
  </tbody>
</table>
<!-- Second Start, the employees one -->
<p class="startifend">
  &lt;&lt;Start:
  SELECT(
    [Related employees][key_employee],
    IN(
      [key_employee],
      expense_sl[employee_key]
    )
  )
  &gt;&gt;
</p>
<h3 class="employee-title" style="color: &lt;&lt;[_THISROW-1].[primary_color_hex]&gt;&gt;">&lt;&lt;name&gt;&gt;</h3>
<table class="expenses">
  <thead>
    <tr>
      <th style="background-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;; border-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;">Receipt</th>
      <th style="background-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;; border-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;">Date</th>
      <th style="background-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;; border-color: &lt;&lt;[_THISROW-1].[secondary_color_hex]&gt;&gt;">Amount</th>
    </tr>
  </thead>
  <tbody>
    <!-- Third Start, the expenses one -->
    <p class="startifend">
      &lt;&lt;Start:
        SELECT(
          [Related expenses][key_expense],
          IN(
            [key_expense],
            expense_sl[key_expense]
          )
        )
      &gt;&gt;
    </p>
    <tr>
      <td><a href="&lt;&lt;receipt&gt;&gt;"><img src="&lt;&lt;receipt&gt;&gt;" alt="receipt" width="80"></a></td>
      <td style="border-left-color: &lt;&lt;[_THISROW-2].[secondary_color_hex]&gt;&gt;">&lt;&lt;date&gt;&gt;</td>
      <td style="border-left-color: &lt;&lt;[_THISROW-2].[secondary_color_hex]&gt;&gt;">&lt;&lt;amount&gt;&gt;</td>
    </tr>
  </tbody>
</table>
<!-- Third End, the expenses one -->
<p class="startifend">&lt;&lt;End&gt;&gt;</p>
<!-- Second End, the employees one -->
<p class="startifend">&lt;&lt;End&gt;&gt;</p>
<!-- First End, the companies one -->
<p class="startifend">&lt;&lt;End&gt;&gt;</p>
<p class="startifend"></p>

CSS

Show More

:root {
  --qr-code: url("https://quickchart.io/qr?text=");
}
@page {
  size: 8.5in 11in portrait;
  margin: .3in;
}
@page:right {
  margin-left: .5in;
}
@page:left {
  margin-right: .5in;
}
body {
  font-family: "Open Sans";
}
p.startifend {
  display: none;
}
div.flex-container {
  display: flex;
}
div.company-header {
  break-before: right;
}
h1.report-title::after {
  content: '';
  background-image: var(--qr-code);
  background-size: 100px;
  background-repeat: no-repeat;
  width: 100px;
  height: 100px;
  position: absolute;
  top: 10px;
  right: 10px;
}
p.generated-by {
  font-size: 6pt;
}
p.generated-by + div.company-header {
  break-before: avoid;
}
div.flex-container.company-header {
  justify-content: space-between;
  align-items: center;
}
table.company-header-values {
  margin-top: 20px;
  margin-left: auto;
  text-align: center;
}
table.company-header-values th {
  width: 1.2in;
}
table.company-header-values + h3.employee-title {
  break-before: avoid;
}
h3.employee-title {
  break-before: page;
}
table.expenses {
  width: 100%;
  text-align: center;
  border-collapse: collapse;
}
table.expenses th {
  border: solid 2px;
  padding: 8px;
}
table.expenses td {
  padding-left: 8px;
  padding-right: 8px;
  border-left: solid 2px;
}
table.expenses td:first-child {
  border-left: none;
}
table.expenses tbody tr td:last-child {
  text-align: right;
}

The differences...

---

HTML:

• Just after the h1 there is a p tag now that shows the user that generated the report and the timestamp.
• First Start: expression:
  • I could have made this different, like making the report table with more fields so that we could reference those since we are in the contex of the added row, that's how we added [user] and [timestamp] on the previous item (the p tag). But I decided to make it simpler on the AppSheet side and deal with it on the template itself. So all expression are context-agnostic again but refer to the slice instead.
  • SELECT() companies from the employee table if:
    • The employee is one of the employees whose expenses where filtered.
    • Remove duplicates since we are getting the company from the employee table.
• "Employees in the list", "Average spent per employee" and "Total spent" had a change on the last part of their expressions. Instead of filtering the "[Related expenses][employee_key]" based on the date (last week), now I check if the expense is inside the slice. The simplest method I found to change this template.
• Second Start: expression:
  • Similar to the first Start:
  • SELECT() the related employee if:
    • The employee is one of the employees whose expenses where filtered.
• Third Start: expression:
  • Also similar to previous ones
  • SELECT() the related expenses if:
    • The expense is one of the expenses filtered by the slice

---

CSS:

• p of class generated-by has a font size of 6pt
• If there is a p of class generated-by right before the div of class company-header, there shouldn't be a page break (previously this worked by checking if the company-header was right after the h1 tag, but now we added the p tag on it's place)

---

That's it!
Yes, we finally did it, and probably you don't understand a thing after reading this once or twice. But that's the idea, you can read it as much as you need now that this post is finally out.
I'm sorry for posting this a little too late, but this took months to prepare, plus I have a stable job that takes most of my time.

My main goal with this little series of posts around HTML+CSS is to show you how much is posible if you push hard enough. It's not easy and you might be right that this is just too much work for some cases, but you can't denny that this method can produce some of the most awesome reports you will ever see even if I couldn't show you some of them, like restaurant menus, filled forms, charts and many more...

See you on Part 4

[SkrOYC]

This reply is reserved.

[SkrOYC]

---

@SkrOYC wrote:

4.3. Weekly list of expenses per employee

---

Try to scan the QR Code 😉

[dbaum]

You're very generous. Thanks for sharing your expertise!

[SkrOYC]

Btw, I forgot to add comments of the JS part:

var r = document.querySelector(':root');
function getVarValue () {
  var root_all = getComputedStyle(root);
  alert("The value of QR Code is " + root_all.getPropertyValue('--qr-code'));
}
document.addEventListener(
  "DOMContentLoaded",
    function setVarValue() {
      var AppSheetAttachmentURL = "<<_ATTACHMENTFILE_URL>>";
      var QuickChartParameters = "&margin=0&format=svg"
      var newUrl = 'url("https://quickchart.io/qr?text='+ encodeURIComponent(AppSheetAttachmentURL.replace(/amp;/g, "")) + QuickChartParameters.replace(/amp;/g, "") +'")';
      r.style.setProperty('--qr-code', newUrl);
    }
);

Since this is a little advance, I don't think it's important to explain every part of it, but you can use it as is on any HTML  template of your choice.

The important stuff is at the end, inside function setVarValue():

AppSheet changes some of the content like & to & (HTML Entities) when adding content to our templates, but this will completely break the QR code since quickchart needs to interpret the values without encoding, it just need the URL to be encoded  (the text= part).
So, unless AppSheet provides a way to deactivate this default behaviour (which I guess won't happend since it would break other stuff), this is the only method to add a dynamic QR Code with the URL to the current file (the _ATTACHMENTFILE_URL).

Finally, you can change any or all of the parameters inside the QuickChartParameters variable, just don't forget to add them using HTML Entities, which in this case is the & instead of just &, or AppSheet will complain that there is an error in your template

[SkrOYC]

---

@SkrOYC wrote:

Try to scan the QR Code

---

In case anyone wonders about this, the QR Code is still showing the document corrently.
I have found that the file signing is saved for a loong time, and this could serve as a test.
As long as the file was archived and you don't move/delete it from your storage provider, this is an awesome solution that I love to have found

[Lurent]

o my god, this is fantastic, I hope one day I can implement it in my applications. I have a question, how can I add an image to the body of the email? I have tried every way and I can't find the solution... if I enter a <html> <body> when using <imag src=... I get errors everywhere, the only way I don't get an error is to enter code without the <html> <body> tags... I have directly entered a single line of code from <p><img src="https://www.google.com/......miimagen.png" alt="mi descripción" width="234"> etc, etc <\p> and then I don't get an error but the images do not appear

[SkrOYC]

Well...

Email body has been behaving very weirdly, I suggest to use a .html file as email body... just in case.
Now, img tags should work fine if it's a public URL.
If you are using an AppSheet field for the image, please use the proper format rule for it (text)

[Lurent]

Thank you very much for your advice, I finally achieved it with an html template for the body of the email. Now I encounter the following problem, before in the body of the email when I placed a <<stard: ...>> <<end>> of a daughter table it created line breaks for each row but now it places everything in a single line. Could you tell me how to solve this? My knowledge of HTML is limited and I am learning little by little. Thank you very much for your time

[SkrOYC]

Happy to help.

Please share the result as a screenshot

[Lurent]

I already found my mistake, you have to separate the start and the end in a separate <p>.

thanks a lot

[Tiger1]

Have you built some sort of Appsheet app to help build these reports?

[SkrOYC]

The HTML template process cannot be done by using an AppSheet app sadly, it's something you need to learn

[BMacheroOrtiz]

hola, 

tengo este progblema, aun no me permite visualizar imagen en mi html

 <div class="flex border h-64 justify-center p-2">

               <img src="&lt;&lt;[Link_Ev_2]&gt;&gt;" alt="Ev_1" width="180">

[Lurent]

Has creado un format rules de la columna de donde traes la imagen ?

[T_I_Phong]

Hi @SkrOYC 

I've learned about creating PDF files using HTML through your excellent shares. However, due to my basic knowledge of HTML, I'm having difficulty designing a template file similar to the image I've attached below.

Specifically, I have a dataset, and when generating a PDF report, I want to separate it into each Step and calculate the total value for each Step. I have two designs for the file as shown in the image.

I would greatly appreciate your assistance.

[SkrOYC]

Both are doable using HTML without issues.

The main reason that this has ben difficult for you is that you are grouping the data, which is not something easy.

Please check this post in which I explained how to handle that behavior

Grouping in Templates - Google Cloud Community