Invoice app into chart data

Hi guys so I’ve made an invoice app that works pretty well. the sales person fills in the fields item 1 artichokes item 2 broad beans etc. PIC A

once everything is captured the client gets an email and all is well.
now every month I would like to use a graph in googlesheets off the source data to show for example all products being sold.
PIC B

now because all the products are in different columns eg item 1 and item 2 it’s difficult to create the chart. google sheets would prefer it if the data was in the format below.

PIC C

just want to know if anyone else ran into a problem like this?

Ultimately I would like to report on all the produce sold, but the structure of the data currently does not allow me.

I could not find a good sample app that would give me the app in the format I wanted because most invoice apps only catered for one line item.

thanks for the read guys and any thought would be appreciated.

First, you can create Google Bar charts in sheets from data in columns. I have done some fairly complicated charts in that manner. I would recommend you look into using Google QUERY statements. These allow you to create SQL-like statements that can grab and aggregate your data from the AppSheet datasource sheet.


Have you looked into creating Parent/Child tables. (See article link below.) The idea is that you have two tables. The first being the Order table as the parent and then, in your case, an Items or maybe Ordered Items table that is set as the child table.

With this structure you can enter as many child Items as needed for the Order and they can be displayed in a mini-table within the Order record. See image below.

An Order record with Photos and Products as children tables

Parent/Child relationships - scroll down to "Expressing Ownership Between Tables"

1 Like