Hi there,
I have been reading a lot about how to automatically add and fill in a row in a Google doc with Make. I have been able to automatically add a row with Google Docs’ API but dynamically adding text content in these rows gets very complex with Google’s index system.
I read in this discussion and and in this one that there was a workaround variabilizing the HTML of the table with the google doc but I’m not able to reproduce the solution.
Here is my google doc
And here is the related HTML
htmlviewer.html.txt (18.9 KB)
What should I use the HTML to have dynamic text populating the new rows created through the API? Or alternately, is there a way to dynamically add rows and fill in the rows dynamically by variabilizing the whole HTML table part in the HTML code and if so, how?
Thanks!
That first thread you linked to describes perfectly what to do. Your Google Doc template has some sort of placeholder text in it representing where the table will go.
You use an iterator and text aggregator to build the HTML code for your table.
Download your Google doc as HTML, replace your placeholder with the table HTML, then create a new doc using that new HTML as the content.
Could you follow as best as possible then post details about how far you get and where it breaks down and doesn’t work as expected?
Thanks a lot for your reply.
I think I have made progress now but what I’m struggling with is
You use an iterator and text aggregator to build the HTML code for your table
Here’s the table on which I want to iterate (this is an invoicing table wit description quantity Unitary price and Total)
I guess in the HTML of my table there’s three parts :
- Part 1: The general HTML headers and the CSS styling + the start of the table code with the header row - these should not be variabilized
- Part 2 with the XXs: the actual code of the line on which I want to iterate and the XXs should become variables built from my source dataset. So let’s say if two orders are placed, the iteration should create twice the code for a row in the table with the right strings in the right cells
- Part 3: The rest of the table and the end of the code
Here’s the scenario I built where module 4 is part 2 of the HTML:
The last variable called “Whole table code” concatenates modules 4,9,10 outputs to get the entire HTML. Here’s the output table I get
However, I’m not able to calculate the total amounts (both ZZ) and VAT amount (TT) as it’s part of the static part of the HTML that is not iterated. I think I built the HTML not in a good way, could you help with this? thanks!
Can you post the original text that you are trying to parse? Or provide a sample?
The line items are the repeatable parts for which you build the table HTML - all the tr and td tags with appropriate formatting, etc…
You then surround that HTML with the non-repeatable parts like the opening table tags before the HTML and closing after.
At the end of the HTML, before the closing table tag, you would use array and numeric functions (like map(), get(), and sum() on the array you used to build the line items) to calculate values for the final few lines.
As @LinkYourTech asked, once we get a sample of your data, we can build an example.
Please see this blueprint:
dynamically-adding-and-filling-a-row-to-a-table-in-a-google-doc-using-html-50463.json (10.1 KB)
Hopefully it will help you understand the concepts and apply to your specific situation.
I started with base HTML whose table looks like this:
Code
<table style="width: 100%; border-collapse: collapse;">
<thead>
<tr style="border-bottom: 1px solid #ccc;">
<th style="padding: 10px; text-align: left; background-color: black; color: white;">Description</th>
<th style="padding: 10px; text-align: left; background-color: black; color: white;">Qté</th>
<th style="padding: 10px; text-align: left; background-color: black; color: white;">Prix unitaire</th>
<th style="padding: 10px; text-align: left; background-color: black; color: white;">Total HT</th>
</tr>
</thead>
<tbody>
<tr style="border-bottom: 1px solid #ccc;">
<td style="padding: 10px; text-align: left;">XX</td>
<td style="padding: 10px; text-align: left;">XX</td>
<td style="padding: 10px; text-align: left;">XX</td>
<td style="padding: 10px; text-align: left;">XX</td>
</tr>
<tr style="border-bottom: none; font-weight: bold;">
<td style="padding: 10px; text-align: right;" colspan="3">Total HT</td>
<td style="padding: 10px; text-align: left;">ZZ</td>
</tr>
<tr style="border-bottom: none; font-weight: bold;">
<td style="padding: 10px; text-align: right;" colspan="3">TVA (20%)</td>
<td style="padding: 10px; text-align: left;">TT</td>
</tr>
<tr style="border-bottom: none; font-weight: bold;">
<td style="padding: 10px; text-align: right;" colspan="3">Total TTC</td>
<td style="padding: 10px; text-align: left;">ZZ</td>
</tr>
</tbody>
</table>
</body>
</html>
After running the scenario it came up with this table:
Code
<table style="width: 100%; border-collapse: collapse;">
<thead>
<tr style="border-bottom: 1px solid #ccc;">
<th style="padding: 10px; text-align: left; background-color: black; color: white;">Description</th>
<th style="padding: 10px; text-align: left; background-color: black; color: white;">Qté</th>
<th style="padding: 10px; text-align: left; background-color: black; color: white;">Prix unitaire</th>
<th style="padding: 10px; text-align: left; background-color: black; color: white;">Total HT</th>
</tr>
</thead>
<tbody>
<tr style="border-bottom: 1px solid #ccc;">
<td style="padding: 10px; text-align: left;">Item 1</td>
<td style="padding: 10px; text-align: left;">3</td>
<td style="padding: 10px; text-align: left;">11</td>
<td style="padding: 10px; text-align: left;">33</td>
</tr>
<tr style="border-bottom: 1px solid #ccc;">
<td style="padding: 10px; text-align: left;">Item 2</td>
<td style="padding: 10px; text-align: left;">1</td>
<td style="padding: 10px; text-align: left;">99</td>
<td style="padding: 10px; text-align: left;">99</td>
</tr>
<tr style="border-bottom: none; font-weight: bold;">
<td style="padding: 10px; text-align: right;" colspan="3">Total HT</td>
<td style="padding: 10px; text-align: left;">132,00</td>
</tr>
<tr style="border-bottom: none; font-weight: bold;">
<td style="padding: 10px; text-align: right;" colspan="3">TVA (20%)</td>
<td style="padding: 10px; text-align: left;">26,40</td>
</tr>
<tr style="border-bottom: none; font-weight: bold;">
<td style="padding: 10px; text-align: right;" colspan="3">Total TTC</td>
<td style="padding: 10px; text-align: left;">158,40</td>
</tr>
</tbody>
</table>
In the original HTML, the repeatable part is highlighted here:
That highlighted area is the part you build with the Iterator and Text Aggregator.
You Iterate on your line item data object, then output that highlighted area once for each line item.
That should make more sense once you look at the scenario from the blueprint.
The final table HTML will consist of all the text BEFORE the highlighted section + the result of the Text Aggregator + the all the text AFTER the highlighted section.
For the text after the highlighted section you’ll need to replace the ZZ and TT with the actual results of the calculations.
These are also shows in the example scenario.
The final step here is downloading your Google Doc as HTML, find the table placeholder text, replace it with the “final table HTML”, then creating a new Google Doc with the final document HTML.
Hope that helps!
1 Like
Makes total sense and I was able to get the correct table from the HTML with the variables in the right place etc. Thanks!
If I may ask one more question, the only thing last issue I’m facing is that I’m not able to replace the placeholder with the Table HTML when creating the final document.
Here is the doc with the placeholder
And below the settings of the Google docs “Create a document” module
Also below the blueprint and screenshot of the scenario if needed
blueprint (2).json (65.7 KB)
A million thanks! 
I believe you need to use Google Drive app to download the document as HTML. Then you use Google Docs to Create the Document using HTML as the source content.
Ok I will try to figure it out and keep this thread updated for the rest of the community. Thanks a lot for your help!
1 Like
