Iterating different arrays in the same branch to add to goodle sheet rows all at once

[Romain_cote]

Hello, I’m sure there is a better way to do what I’m trying to do than the way I’ve found so far.

purpose of my scenario: from an invoice in pdf format from my car sharing service, listing different trips for a given month, I am using Gemini to parse different informations through a JSON in the form of an array for each(starting date, ending date, price, type of car, etc.) to then add it in a google sheet.

I want each trip to be added as a new row in the sheet, and each information to go in the right cell of the right column for this row.

At the moment I’m using a Google sheets add rows with the first information array, via an integrator. But I can’t chain integrators for each info, otherwise thearrays just multiply and the informations get added int he same cell. Then I’m doing another integrator and an edit row for each other info. It seems super inneficient but also, it relies on me specifying which raw to edit while I’d rather everything gets created row per row “dynamically”.

I’m guessing maybe my data should not be organised in a single bundle with multiple arrays but rather multiple bundles, maybe?

Could anybody send me in the right direction, please?

Please find the blueprint attached, as well as a couple of screengrabs of the scenario.
Thanks for your help!

Factures Communauto.blueprint.json (97.1 KB)

the invoice pdf looks like this btw:

And here is the Gemini prompt I use to generate the JSON:

“Please extract the following information from the invoice pdf attached and return it as a JSON object.The JSON object must conform to the following schema:
{ “type”: “object”, “properties”:
{
“invoice_sender”: {“type”: “string”},
“invoice_number”: {“type”: “string”},
“invoice_date”: {“type”: “string”},
“dateDebut”: {"type: “array”},
“dateFin”: {"type: “array”},
“prixTemps”: {"type: “array”},
“Nb km”: {"type: “array”},
“Prix km”: {"type: “array”},
“Frais/
Crédit”: {"type: “array”},
“tarifApplique”: {"type: “array”},
“total_amount”: {“type”: “number”},
“currency”: {“type”: “string”}
}}

Extract:

• invoice_sender: The company name that issued the invoice
• invoice_number: The invoice reference number
• invoice_date: The invoice date in DD-MM-YYYY
• dateDebut: all the dates under the column “Date Début” in DD-MM-YYYY HH:MM
  format
• dateFin: all the dates under the column “Date Fin” in DD-MM-YYYY HH:MM
  format
• prixTemps: all the amounts under the column “Prix temps”
• Nb km: all the amounts of kilometers under the column “Nb km”
• Prix km: all the amounts under the column “Prix km”
• Frais/
  Crédit: all the amounts under the column “Frais/
  Crédit”
• tarfifApplique: rate applied under the column “Tarif
  appliqué”
• total_amount: The total amount due as a number
• currency: The currency code (USD, EUR, etc.)

Return only the JSON object, no additional text or explanations.”

[Romain_cote]

I’ve figured it out actually! It’s amazing how looking at it a couple of days later with fresh eyes I got it.

So, I asked chatGPT to rewrite the JSON structure to iterate each row from the bill. And here it is:

Here is the new Gemini prompt:

Please extract the following information from the invoice pdf attached and return it as a JSON object.The JSON object must conform to the following schema:

{
“type”: “object”,
“properties”: {
“invoice_sender”: { “type”: “string” },
“invoice_number”: { “type”: “string” },
“invoice_date”: { “type”: “string” },
“trips”: {
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“start_date”: { “type”: “string” },
“end_date”: { “type”: “string” },
“days”: { “type”: “number” },
“hours”: { “type”: “number” },
“cost_time”: { “type”: “number” },
“distance_km”: { “type”: “number” },
“cost_distance”: { “type”: “number” },
“booking_fee”: { “type”: “number” },
“extra_fee”: { “type”: “number” },
“total_before_tax”: { “type”: “number” },
“total_after_tax”: { “type”: “number” },
“tariff_applied”: { “type”: “string” }
},
“required”: [
“start_date”,
“end_date”,
“days”,
“hours”,
“cost_time”,
“distance_km”,
“cost_distance”,
“total_before_tax”,
“total_after_tax”,
“tariff_applied”
]
}
},
“total_amount”: { “type”: “number” },
“currency”: { “type”: “string” }
},
“required”: [
“invoice_sender”,
“invoice_number”,
“invoice_date”,
“trips”,
“total_amount”,
“currency”
]
}

Extract:

• invoice_sender: The company name that issued the invoice
• invoice_number: The invoice reference number
• invoice_date: The invoice date in DD-MM-YYYY
• dateDebut: all the dates under the column “Date Début” in DD-MM-YYYY HH:MM
  format
• dateFin: all the dates under the column “Date Fin” in DD-MM-YYYY HH:MM
  format
• prixTemps: all the amounts under the column “Prix temps”
• Nb km: all the amounts of kilometers under the column “Nb km”
• Prix km: all the amounts under the column “Prix km”
• Frais/
  Crédit: all the amounts under the column “Frais/
  Crédit”
• tarfifApplique: rate applied under the column “Tarif
  appliqué”
• total_amount: The total amount due as a number
• currency: The currency code (USD, EUR, etc.)

Return only the JSON object, no additional text or explanations.

[damato]

Hi @Romain_cote

Congratulations on figuring it out.

How many bundles were returned from Google Gemini AI? I’d suspect only one in your test run.

You need to parse the response as a JSON array and feed it to Google Sheets Bulk Add.

Not sure without seeing the details, but I’d suspect your JSON output only contains the first element.

@damato

[Romain_cote]

Hi @damato!

it returned two bundles actually and I managed to feed them directly to the Google sheets add rows and it filled up the sheet as expected. I can’t show you cause I exceeded the limit of my '(currently) free plan. I guess all these iterators in the first version didn’t help