Solved: Re: About tags inserted into templates in google d...

gougou · 10-09-2023 11:20 PM

I want to output data from a data source to a template created with Google Docs in appsheet using in.

The template is predefined, not dynamically increasing rows,
I want to plug in the template row by row.

I am very troubled.

TeeSee1

This implementation is slightly cleaner..

You create a separate table with the number of columns you want to show in the output table and append that to the target data.

<html>
<head>
    <style>
		table, td, th { border: 2px #808080 solid; }
        tr:nth-child(even) {
            background-color: Lightgreen;
        }
    </style>
</head>

<body>
    <h1>New step Task - 3</h1>
    <h2>orders</h2>
    <p>id: &lt;&lt;[id]&gt;&gt;</p>
    <p>cust: &lt;&lt;[cust]&gt;&gt;</p>
    <p>date: &lt;&lt;[date]&gt;&gt;</p>
    <br /><br />
    <h2>order_details</h2>

    <table>
        <tbody>
            <tr>
                <th>id</th>
                <th>order_id</th>
                <th>product</th>
            </tr>
            <p>&lt;&lt;Start: SELECT(order_details[id],[order_id]=[_thisrow-1].[id])&gt;&gt;</p>
            <tr>
                <td>&lt;&lt;[id]&gt;&gt;</td>
                <td>&lt;&lt;[order_id]&gt;&gt;</td>
                <td>&lt;&lt;[product].[name]&gt;&gt;</td>
            </tr>
            <p>&lt;&lt;End&gt;&gt;</p>
			
			<p>&lt;&lt;Start: TOP(SELECT(blanks[id],TRUE),9 - COUNT(SELECT(order_details[id],[order_id]=[_thisrow-1].[id])))&gt;&gt;</p>
            <tr>
                <td>&lt;&lt;[c1]&gt;&gt;</td>
                <td>&lt;&lt;[c2]&gt;&gt;</td>
                <td>&lt;&lt;[c3]&gt;&gt;</td>
            </tr>
            <p>&lt;&lt;End&gt;&gt;</p>
        </tbody>
    </table>
    <br />
	
</body>
</html>

I also learned that you can @import css frameworks to 'easily' make the format pretty though I am not sure how much would be supported.

An example using https://bulma.io/documentation/elements/table/

So lots of room for experimentation if you are up to it.

Good luck!

View solution in original post

TeeSee1

I can think of one way that I believe would work but it is not very 'clean' in terms of data integrity. Anyway here it goes..

you create additional rows in the details table with blank values except for the key and a column that can identify these rows. The column is also used to sort rows so make sure the value places these rows at the end when sorted by this. The number should be the same as the lines of the table you want to display minus 1.

Instead of START: [related details], you use something like the following to retrieve a combined list of your real target rows and a variable number of dummy rows to fill in the remaining 'blank' rows to create the desired table

TOP(
 ORDERBY(
  SELECT(
   details[key],
   OR(
    [foreign key] = [_THISROW-1].[parent key],
    [some column to identify the dummy rows] = "a value to identify the dummy rows"
   )
  ),
  [some column to ensure dummy rows come at the bottom]
 ),
 9 //the number of rows you want in the table displayed
)

Again I am not sure if this is a good way...

gougou

Thank you for your advice.

I apologize for any misunderstanding in reading comprehension due to my poor English.

Is this the method you are using to output the rows sequentially to increase the number of rows?

The way I want to do it is to insert the tags into the already displayed fixed template.

TeeSee1

@gougou wrote:

Is this the method you are using to output the rows sequentially to increase the number of rows?

No, my reply was to display a table with a fixed number of rows. Say the fixed number is 9 and you have only 5 rows of real data. Then the first 5 rows are populated with data and the rest are blanks.

It seems like this is not what you are looking for.

@gougou wrote:

The way I want to do it is to insert the tags into the already displayed fixed template.

I still am not sure what you want...

gougou

Thank you very much, I will try to find out how to do that.

However, I do not know how to create the table into a fixed 9-row template (a tag that writes each row into the template) instead of creating the table one row at a time.

Instead of dynamically outputting the table section one line at a time, the values are inserted into a 9-line table.

I would appreciate it if you could help me.

TeeSee1

I see what you want. Unfortunately I do not know how to populate data into a pre-existing table in a Google Document. (Except I can probably do it by calling Apps Script. In which case I would use a spreadsheet instead.)

The solution I was explaining still uses a dynamic table but always prints out 9 rows (or however many you want.) by printing dummy rows as can be seen in the second table below (I have printed out id columns but you can choose not to do it). I do not know how to implement alternate row colors.

Sample Print Out

Doc Template

Maybe you have better luck with other community members with more experience.

gougou

Thank you again and again for your advice.

Is it still impossible to insert data into an existing template....
I would like to make the layout look like a commercial paper.

Is it possible to use a spreadsheet as a template?

If anyone knows, I would like to know.

In any case, thank you for your advice!!😊

TeeSee1

Not with Doc templates, I do not think.

Either use AppScript or HTML templates.

@SkrOYC has posted very comprehensive tips

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Part-1-Almost-pixel-perfect-pdf-reports-from-aut...

You probably do not need all of the techniques so just use what is needed. (Part 2 is the meat of his techniques.)

TeeSee1

Just a sample HTML template

<html>
<head>
    <style>
		table, td, th { border: 2px #808080 solid; }
        tr:nth-child(even) {
            background-color: Lightgreen;
        }
    </style>
</head>

<body>
    <h1>New step Task - 3</h1>
    <h2>orders</h2>
    <p>id: &lt;&lt;[id]&gt;&gt;</p>
    <p>cust: &lt;&lt;[cust]&gt;&gt;</p>
    <p>date: &lt;&lt;[date]&gt;&gt;</p>
    <br /><br />
    <h2>order_details</h2>

    <table>
        <tbody>
            <tr>
                <th>id</th>
                <th>order_id</th>
                <th>product</th>
            </tr>
            <p>&lt;&lt;Start: TOP(ORDERBY(SELECT(order_details[id],OR([order_id]=[_thisrow-1].[id],[A]=”Z”)),[A],FALSE,[_ROWNUMBER],FALSE),9)&gt;&gt;</p>
            <tr>
                <td>&lt;&lt;[id]&gt;&gt;</td>
                <td>&lt;&lt;[order_id]&gt;&gt;</td>
                <td>&lt;&lt;[product].[name]&gt;&gt;</td>
            </tr>
            <p>&lt;&lt;End&gt;&gt;</p>
        </tbody>
    </table>
    <br />
</body>
</html>

And a resulting pdf. (Note that HTML templates cannot be used for an Email body. You use PDF attachments instead.)

gougou

Thanks to you, I can see the goal.
Can this HTML file be saved as a PDF file on output?

Sorry to ask this question now, but how do I create an additional row in the detail table that contains blank values other than the columns I identified as keys?

TeeSee1

The screen shot I pasted is that of a PDF file.

You manually enter rows to the table. Nothing special.

TeeSee1

I also want to ask others..

Is there a way to create empty rows directly in an HTML template? Like how do you output multiple <tr>...</tr> tags using expressions/functions? This would be much much cleaner as a solution.

gougou

Well, I would like to convert an HTML file to a PDF file automatically in an action and output the path to any field in the table. (so that the file can be displayed from the link)

TeeSee1

PDF files are automatically created (html files are not created).

See this for your second request.

TeeSee1

This implementation is slightly cleaner..

You create a separate table with the number of columns you want to show in the output table and append that to the target data.

<html>
<head>
    <style>
		table, td, th { border: 2px #808080 solid; }
        tr:nth-child(even) {
            background-color: Lightgreen;
        }
    </style>
</head>

<body>
    <h1>New step Task - 3</h1>
    <h2>orders</h2>
    <p>id: &lt;&lt;[id]&gt;&gt;</p>
    <p>cust: &lt;&lt;[cust]&gt;&gt;</p>
    <p>date: &lt;&lt;[date]&gt;&gt;</p>
    <br /><br />
    <h2>order_details</h2>

    <table>
        <tbody>
            <tr>
                <th>id</th>
                <th>order_id</th>
                <th>product</th>
            </tr>
            <p>&lt;&lt;Start: SELECT(order_details[id],[order_id]=[_thisrow-1].[id])&gt;&gt;</p>
            <tr>
                <td>&lt;&lt;[id]&gt;&gt;</td>
                <td>&lt;&lt;[order_id]&gt;&gt;</td>
                <td>&lt;&lt;[product].[name]&gt;&gt;</td>
            </tr>
            <p>&lt;&lt;End&gt;&gt;</p>
			
			<p>&lt;&lt;Start: TOP(SELECT(blanks[id],TRUE),9 - COUNT(SELECT(order_details[id],[order_id]=[_thisrow-1].[id])))&gt;&gt;</p>
            <tr>
                <td>&lt;&lt;[c1]&gt;&gt;</td>
                <td>&lt;&lt;[c2]&gt;&gt;</td>
                <td>&lt;&lt;[c3]&gt;&gt;</td>
            </tr>
            <p>&lt;&lt;End&gt;&gt;</p>
        </tbody>
    </table>
    <br />
	
</body>
</html>

I also learned that you can @import css frameworks to 'easily' make the format pretty though I am not sure how much would be supported.

An example using https://bulma.io/documentation/elements/table/

So lots of room for experimentation if you are up to it.

Good luck!

gougou

I really can't thank you enough!

It looks like I can almost make it happen!
My CSS is poor, I'll have to go over it again...

Also, the PDF file is garbled.
(Japanese)
The character encoding of the template file is UTF-8.
Do you know the cause?

TeeSee1

This works for me.

<!DOCTYPE html>
<html lang="jp">
<head>
	<meta charset="utf-8">
...

gougou

Thank you very much for your help.
I apologize for my inexperience.
I will try to implement the desired specifications this way!😇

I have a lot of respect for you!

About tags inserted into templates in google docs