Help needed: mapping fields from GPT Vision to Google Sheets

[Pedro_Salgado_Lucena]

Hi Everyone, I’m new user with Make and I can’t seem to get around an issue I’m having with properly mapping fields to export to google sheets from a GPT vision completion.

Im pulling invoice data from a pdf using GPT vision which results in a Long String.
I’m then using json outputting to pull all the high level headers, but then i’m stuck when it comes to Line items. i can’t get pass the first line item, as the mapping keeps duplicating the value of the first group of variables…
Line items seem to be mapped as an array (see bundle below)

[
{
“result”: “{\n "Invoice": {\n "Invoice_Number": "RE30232",\n "Invoice_Date": "13.06.2024",\n "Delivery_Date": "n/a",\n "Supplier_Email": "info@andraschkokaffee.com",\n "Contact_person": "Steph",\n "Contact_person_email": "n/a",\n "Supplier_Name": "Andraschko Kaffeemanufaktur GmbH & Co. KG",\n "Supplier_VAT_ID": "DE248395736",\n "Total_Net_Amount_subject_to_7_percent": "685,74 €",\n "Total_Net_Amount_subject_to_19_percent": "n/a",\n "Section_Net_Amount_subject_to_7_percent": "n/a",\n "Section_Net_Amount_subject_to_19_percent": "n/a",\n "Total_NET_AMOUNT_to_pay": "685,74 €",\n "Total_7_percent_AMOUNT_to_pay": "48,00 €",\n "Total_19_percent_AMOUNT_to_pay": "n/a",\n "Total_combined_VAT_AMOUNT_to_pay": "48,00 €",\n "Total_GROSS_AMOUNT_to_pay": "733,74 €",\n "Payment_Due_Date": "14 days from invoice date",\n "leergut_pfand_amount": "n/a",\n "total_net_weight": "n/a",\n "total_gross_weight": "n/a",\n "Line_Items": [\n {\n "Colli": "n/a",\n "Line_item_description": "Karton Wiener Kaffeehausmischung Espresso Blend 1000g x 12",\n "Inhalt": "n/a",\n "Menge": "3,00 Stk.",\n "Einzelpreis": "225,36 €",\n "Einzelpreis_with_Einheit": "225,36 €/Stk.",\n "Gesamtpreis": "676,08 €",\n "VAT": "7%",\n "Context": "Karton Wiener Kaffeehausmischung Espresso Blend 1000g x 12 at 225,36 €/Stk., ordered 3 Stk. Total was 225,36 €/Stk. x 3 = 676,08 €"\n },\n {\n "Colli": "n/a",\n "Line_item_description": "Night Express Espresso Blend entkoffeiniert 250g",\n "Inhalt": "n/a",\n "Menge": "2,00 Stk.",\n "Einzelpreis": "4,83 €",\n "Einzelpreis_with_Einheit": "4,83 €/Stk.",\n "Gesamtpreis": "9,66 €",\n "VAT": "7%",\n "Context": "Night Express Espresso Blend entkoffeiniert 250g at 4,83 €/Stk., ordered 2 Stk. Total was 4,83 €/Stk. x 2 = 9,66 €"\n }\n ]\n }\n}”,
“id”: “chatcmpl-9lcGtaxeTdToV7PQ7dkweT6iKrvuJ”,
“object”: “chat.completion”,
“created”: “2024-07-16T13:12:39.000Z”,
“model”: “gpt-4-0613”,
“choices”: [
{
“index”: 0,
“message”: {
“role”: “assistant”,
“content”: “{\n "Invoice": {\n "Invoice_Number": "RE30232",\n "Invoice_Date": "13.06.2024",\n "Delivery_Date": "n/a",\n "Supplier_Email": "info@andraschkokaffee.com",\n "Contact_person": "Stephanie Aman-Mendrzky",\n "Contact_person_email": "n/a",\n "Supplier_Name": "Andraschko Kaffeemanufaktur GmbH & Co. KG",\n "Supplier_VAT_ID": "DE248395736",\n "Total_Net_Amount_subject_to_7_percent": "685,74 €",\n "Total_Net_Amount_subject_to_19_percent": "n/a",\n "Section_Net_Amount_subject_to_7_percent": "n/a",\n "Section_Net_Amount_subject_to_19_percent": "n/a",\n "Total_NET_AMOUNT_to_pay": "685,74 €",\n "Total_7_percent_AMOUNT_to_pay": "48,00 €",\n "Total_19_percent_AMOUNT_to_pay": "n/a",\n "Total_combined_VAT_AMOUNT_to_pay": "48,00 €",\n "Total_GROSS_AMOUNT_to_pay": "733,74 €",\n "Payment_Due_Date": "14 days from invoice date",\n "leergut_pfand_amount": "n/a",\n "total_net_weight": "n/a",\n "total_gross_weight": "n/a",\n "Line_Items": [\n {\n "Colli": "n/a",\n "Line_item_description": "Karton Wiener Kaffeehausmischung Espresso Blend 1000g x 12",\n "Inhalt": "n/a",\n "Menge": "3,00 Stk.",\n "Einzelpreis": "225,36 €",\n "Einzelpreis_with_Einheit": "225,36 €/Stk.",\n "Gesamtpreis": "676,08 €",\n "VAT": "7%",\n "Context": "Karton Wiener Kaffeehausmischung Espresso Blend 1000g x 12 at 225,36 €/Stk., ordered 3 Stk. Total was 225,36 €/Stk. x 3 = 676,08 €"\n },\n {\n "Colli": "n/a",\n "Line_item_description": "Night Express Espresso Blend entkoffeiniert 250g",\n "Inhalt": "n/a",\n "Menge": "2,00 Stk.",\n "Einzelpreis": "4,83 €",\n "Einzelpreis_with_Einheit": "4,83 €/Stk.",\n "Gesamtpreis": "9,66 €",\n "VAT": "7%",\n "Context": "Night Express Espresso Blend entkoffeiniert 250g at 4,83 €/Stk., ordered 2 Stk. Total was 4,83 €/Stk. x 2 = 9,66 €"\n }\n ]\n }\n}”
},
“logprobs”: null,
“finish_reason”: “stop”
}
],
“usage”: {
“prompt_tokens”: 1279,
“completion_tokens”: 669,
“total_tokens”: 1948
},
“system_fingerprint”: null
}
]

The thing with Line item is, because i cant predict w/ exact certainty how many will be in the next invoice, they have to be dynamic and I’m really stuck…

This the current scenario…

Please help!

[samliew]

Welcome to the Make community!

Perhaps you shouldn’t be converting PDFs to Images, but extract the text content instead.

Then you can use the “Transform text to Structured Data” module to map the invoice data into a predefined JSON structure, like how I’m doing it here.

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

[Pedro_Salgado_Lucena]

Thank you @samliew - in general i agree with you - i have also used PDF.co, CloudConvert and others, just simply have had better results with Vision for german food supplier invoices.

My main obstacle is in the google sheet importing step. Because I can’t get the line items to iterate sequentially, and only getting the first one…

[samliew]

Your line items are likely to be an array.

When you see ARRAY, think Iterator.

Then you will be able to map each line item from the Iterator module.

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

[Pedro_Salgado_Lucena]

Also didn’t work… I tried earlier…

[samliew]

Are you trying to combine the line items before you add a single invoice to Google Sheets as a single row?

Then you are missing an aggregator module.

Module Export

You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.

1. Copy the JSON code below by clicking the copy button when you mouseover the top-right of the code block
   

2. Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the canvas.

3. Click on each imported module and save it for validation. You may be prompted to remap some variables and connections.

Click to Expand Module Export Code

JSON - Copy and Paste this directly in the scenario editor

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 142,
                    "module": "json:ParseJSON",
                    "version": 1,
                    "parameters": {
                        "type": ""
                    },
                    "mapper": {
                        "json": "{{141.result}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 1990,
                            "y": -380
                        },
                        "restore": {
                            "parameters": {
                                "type": {
                                    "label": "Choose a data structure"
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "type",
                                "type": "udt",
                                "label": "Data structure"
                            }
                        ],
                        "expect": [
                            {
                                "name": "json",
                                "type": "text",
                                "label": "JSON string",
                                "required": true
                            }
                        ]
                    }
                },
                {
                    "id": 143,
                    "module": "builtin:BasicFeeder",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "array": "{{142.Invoice.Line_Items}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 2237,
                            "y": -381,
                            "name": "Iterate Line Items"
                        },
                        "restore": {
                            "expect": {
                                "array": {
                                    "mode": "edit"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "array",
                                "type": "array",
                                "label": "Array",
                                "mode": "edit",
                                "spec": []
                            }
                        ]
                    }
                },
                {
                    "id": 144,
                    "module": "util:TextAggregator",
                    "version": 1,
                    "parameters": {
                        "rowSeparator": "\n",
                        "feeder": 143
                    },
                    "mapper": {
                        "value": "{{143.Line_item_description}} - {{143.Gesamtpreis}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 2481,
                            "y": -381,
                            "name": "Combine Line Items"
                        },
                        "restore": {
                            "parameters": {
                                "rowSeparator": {
                                    "label": "New row"
                                }
                            },
                            "extra": {
                                "feeder": {
                                    "label": "Iterate Line Items [143]"
                                }
                            },
                            "flags": {
                                "groupBy": {
                                    "collapsed": true
                                },
                                "stopIfEmpty": {
                                    "collapsed": true
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "rowSeparator",
                                "type": "select",
                                "label": "Row separator",
                                "validate": {
                                    "enum": [
                                        "\n",
                                        "\t",
                                        "other"
                                    ]
                                }
                            }
                        ],
                        "expect": [
                            {
                                "name": "value",
                                "type": "text",
                                "label": "Text"
                            }
                        ],
                        "advanced": true
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}

[Pedro_Salgado_Lucena]

Thank you for this @samliew! appreciate it!

I’m trying to list the line items groups separately and have them listed in the spreadsheet but the issue remains… i still can’t iterate through each line item and keep getting only the first group.

In the GSheets module this is how it appears when I try to map them from the other modules:

I was wondering if I need to use a get or map function for this? and if that’s scalable for future (unknown) line items?
Thanks!

[samliew]

If you want each line item in the same invoice ROW, but different COLUMN (thanks for the clarification), then yes you’ll need to use the map and get functions.

[Pedro_Salgado_Lucena]

Thanks! Any specific resources you’d recommend?

[samliew]

I would definitely suggest completing the Make Academy before jumping into building a complete scenario.

Here are some useful links and guides to help you get started and learn more on how to use the Make platform, apps, and modules —

General

• Help Center | Tutorials
• Make Academy – learn Make and get your certificate ← CHECK THIS OUT

Help Center Basics

• Mapping – What is mapping? What can I map? ← CHECK THIS OUT
• Mapping with arrays – How to map items in an array ← CHECK THIS OUT
• Tokens for parseDate | Tokens for formatDate
• HTTP modules – Make a request, Get (download) a file
• Webhooks – Error Handling, Responding to webhooks

Articles & Videos

• Router Magic Formula
• How to use Regex in Make
• Getting started with OpenAI

samliew – request private consultation

Join the Make unofficial Discord server!

[Pedro_Salgado_Lucena]

Probably a wise idea

Thanks for your support @samliew !

[MichaelStranks]

You can also use an API Call in Google Sheets and input data in array format, instead of being forced to use an entry for each specific field. It would be a lot more dynamic then, but takes some getting-used-to to get everything going in properly (in my experience). You’d have to check the API documentation for Google Sheets

I would say for the bigger picture overall, if you’re not constrained, maybe re-think the structure of your sheet and treating each line-item as a separate row, and then re-combining somewhere else if you need to see them in a specific layout for a given invoice ID.

[Pedro_Salgado_Lucena]

Thank you @MichaelStranks!
Your suggestion is actually more suitable to my use cases.
Will look into it.

Thanks again!