Looping series from Google sheet to a google doc

[terryble66]

Hello

From previous workflow i have populated a line of data in my google sheet to a google doc.
Replacing worlds in google doc by items from google sheet, that’s done

Describe the issue/error/question

The challenge i have is that in my sheet i have few lines that i need to included with the same format

The Google Sheet datas

Titreprojet	etat	a_ouverte	a-retard
#MATRIX	Stagne	0	45
#PESOS	A la baisse ⇩	1	12
#GED2023	Stagne	3	7
#CQI2022	Stagne	7	72

The Google doc template for the replication

{{titreprojet}}

Le compte rendu est aussi disponible ici : {{titreprojet}}

Statut du projet

{{etat1}}

Compte d’actions Retardés

Actions ouvertes en retard sur le planning prévisionnel	En retard sur le planning initial
{{a_ouverte}}	{{a-retard}}

What is the error message (if any)?

The error is me
I don’t know how to do it to be honest
1/ N8N as node and jsscript that can do the trick ?
2/ Jsscript and html import ?
I have created an html file because I used to use a google script to populate with a loop similar entries to a gmail draft.

<! The title !>
<h2 style="line-height: 1.56; background-color: #ffffff; margin-top: 18pt; margin-bottom: 0pt; padding-bottom: 4pt;"><span lang="fr" style="font-size: 12pt; font-family: Comfortaa, cursive; color: #4a86e8; font-weight: 400;">{{titreprojet}}</span></h2>
<p style="line-height: 1.56; background-color: #ffffff; margin-top: 0pt; margin-bottom: 0pt;"><span lang="fr" style="font-size: 11pt; font-family: Comfortaa, cursive; color: #353744;">Le compte rendu est aussi disponible ici : {{titreprojet}}</span></p>
<p style="line-height: 1.56; background-color: #ffffff; margin-top: 0pt; margin-bottom: 0pt;">&nbsp;</p>
<div align="left">
<! The Status Table for the project !>
<table style="border: none;"><colgroup><col style="width: 220px;" /></colgroup>
<tbody>
<tr>
<td style="border-width: 1pt; border-style: solid; vertical-align: top; background-color: #9bbb59;">
<p style="text-align: center; margin-top: 0pt; margin-bottom: 0pt;"><span lang="fr"><strong><span style="font-size: 14pt; font-family: Comfortaa, cursive;">Statut du projet</span></strong></span></p>
</td>
</tr>
<tr>
<td style="border-width: 1pt; border-style: solid; vertical-align: top;">
<p style="line-height: 1.2; text-align: center; margin-top: 0pt; margin-bottom: 0pt;"><span lang="fr" style="font-size: 11pt; font-family: Comfortaa, cursive; color: #353744;">{{etat1}}</span></p>
</td>
</tr>
</tbody>
</table>
</div>
<! The table with details informations for the project !>
<table style="border: none; height: 113px; width: 82.3691%;"><colgroup><col style="width: 28.0936%;" /><col style="width: 17.3913%;" /><col style="width: 11.8729%;" /><col style="width: 8.86288%;" /><col style="width: 17.2241%;" /><col style="width: 15.5518%;" /></colgroup>
<tbody>
<tr style="height: 34pt;">
<td style="vertical-align: bottom; border: 1pt solid #353744;">
<p style="text-align: center; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size: 11pt; font-family: Comfortaa, cursive;">Actions ouvertes en retard sur le planning pr&eacute;visionnel</span></p>
</td>
<td style="vertical-align: bottom; border: 1pt solid #353744;">
<p style="text-align: center; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size: 11pt; font-family: Comfortaa, cursive;">En retard sur le planning initial</span></p>
</td>
</tr>
<tr style="height: 22.75pt;">
<td style="border-width: 1pt 0.75pt 0.75pt; border-style: solid; border-color: #353744; vertical-align: bottom;">
<p style="text-align: center; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size: 10pt; font-family: Arial;">{{a_ouverte}}</span></p>
</td>
<td style="border-width: 1pt 0.75pt 0.75pt; border-style: solid; border-color: #353744; vertical-align: bottom;">
<p style="text-align: center; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size: 10pt; font-family: Arial;">{{a-retard}}</span></p>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<p style="line-height: 1.56; background-color: #ffffff; margin-top: 0pt; margin-bottom: 0pt;"><span lang="fr" style="font-size: 11pt; font-family: Comfortaa, cursive;">&nbsp;</span></p>
<h2 style="line-height: 1.56; background-color: #ffffff; margin-top: 0pt; margin-bottom: 4pt; padding-top: 18pt;"><span lang="fr" style="font-size: 10pt; font-family: Comfortaa, cursive; color: #404040; font-weight: 400;">{{commentaire projet}}</span></h2>

Please share the workflow

Share the output returned by the last node

The expected ouput is the google doc with the replication of the template for each line of the googlesheet what ever how many lines it has.

I will be more than happy to get this because doing it manually every week cost me times.
Thank you

Information on your n8n setup

• n8n version:0.182
• Database you’re using (default: SQLite): Google sheet
• Running n8n with the execution process [own(default), main]: own
• **Running n8n via [Docker, npm, n8n.cloud, desktop app]: **

[MutedJam]

Hi @terryble66, inserting data into a table inside a Google Doc is rather tricky I am afraid, and I’d suggest using a real spreadsheet instead.

That said, if you’d like to use a table inside a Google doc I’d first make sure to process only one row at a time (perhaps through a loop triggered by SplitInBatches) as this will reduce complexity a lot (it’s complicated enough as it is).

The basic logic would then be something like below:

1. Identify the index of the table inside your document. You can do so by using the Document resource and the Get operation with the Simplify option disabled. You can then see the response coming through with a body field. check the start index of your table:

2. With this index, you can then add a new table row:

3. You’d then need to Get the start index of each table cell, this works similar to getting the start index of the entire table above.

4. Once done, insert the row data, this time using the start index of the cells in questions.

In a workflow, this could look like so:

What’s important here is that in the last step (where the data is actually inserted) you insert the rightmost column first, then make your way towards the left. This is because subsequent indices will change after each insert operation.

This is how it looks when running:

[terryble66]

Hello @MutedJam
Interresting but this is not quitly what is expected to be honest

There is as many block (tiltles, and tables) as there are lines in the spreadsheet
it shown 4 lines meaning 4 blocks
“project tile” 4 times, “état” (status) 4 times and table of action 4 times.

that’s way i tought about have function that create this 4 blocks as items then update the document with this 4 blocks could be easier.

What do you think ?

Thank you

[MutedJam]

Hi @terryble66, the basic logic should still work the same, I think. Only instead of the table elements you’d insert the elements you’d like to use in each block. For re-usability you could consider putting the logic in a separate workflow and then call this workflow from your main workflow through the Execute Workflow node.

[terryble66]

Hi @MutedJam Thank you
As i’m not dev. it’s a challenge for me. So I can be in silence mode for the time of try : D but i’ll keep you posted.

[MutedJam]

Hi @terryble66, tbh, I am also having a very hard time with this. It took me rather long to understand how Google Docs works

I’ll be out next week, but if you get stuck or have any questions just leave a message on the thread and I’ll check this when I come back.