Merging lines with the same items from Quickbook online

How To
1

Hi!
I’m working on a project that involves integrating QuickBooks and Google Sheets. So far, I’ve successfully set up a system where each new estimate in QuickBooks automatically creates a new row in Google Sheets. This row includes details like Estimate_ID, product description, and quantity.

However, I’m encountering a challenge when the same product appears multiple times in an order. In these instances, I want my Google Sheet to consolidate these multiple entries into a single row, showing the aggregate quantity of that product.

I’ve tried using an aggregator to sum up the quantities, but I haven’t been successful in getting it to work correctly.

I’m looking for advice or suggestions on how to solve this. Any help would be greatly appreciated. Thanks in advance!

3

Hello @GUIGUI13,

Is there any chance you could share the JSON output from QuickBooks showing an example of this issue? Once you get the JSON you can mask any private information.

If you could also show screenshots of your modules’ setups as well as how the Google Sheet
looks vs how it should look, that would all help us out a lot in helping you!

2 Likes
4

Hi Donald,

Here is the output of the “get estimate from quickbook” module. It’s from a sample order with a few lines that are for similar items. I want to group these lines and keep only:

“Id”: “47228”,
“Description”: “a620778316624”, (that’s the SKU)
“name”: “#Spiraea japonica ‘Golden Princess’ 2 gal” (that’s the name)
“Qty”: 4 (that’s the field that should be a sum of every lines with the same SKU and description)

Let’s assume that my google sheet is only 4 columns.

Many thanks for your help.

Here is the complete output:

[
{
“domain”: “QBO”,
“sparse”: false,
“Id”: “47228”,
“SyncToken”: “8”,
“MetaData”: {
“CreateTime”: “2023-11-11T03:13:08.000Z”,
“LastUpdatedTime”: “2023-11-11T04:04:52.000Z”
},
“CustomField”: [
{
“DefinitionId”: “1”,
“Name”: “Temps resté sur place”,
“Type”: “StringType”
}
],
“DocNumber”: “9158”,
“TxnDate”: “2023-11-10T05:00:00.000Z”,
“CurrencyRef”: {
“value”: “CAD”,
“name”: “Dollar canadien”
},
“ExchangeRate”: 1,
“TxnStatus”: “Pending”,
“Line”: [
{
“Id”: “2”,
“LineNum”: 1,
“Description”: “a620778316624”,
“Amount”: 7.6,
“DetailType”: “SalesItemLineDetail”,
“SalesItemLineDetail”: {
“ItemRef”: {
“value”: “2775”,
“name”: “#Spiraea japonica ‘Golden Princess’ 2 gal”
},
“UnitPrice”: 7.6,
“Qty”: 1,
“ItemAccountRef”: {
“value”: “367”,
“name”: “4060 Ventes de produits”
},
“TaxCodeRef”: {
“value”: “8”
}
}
},
{
“Id”: “1”,
“LineNum”: 2,
“Description”: “a620778327668”,
“Amount”: 207.2,
“DetailType”: “SalesItemLineDetail”,
“SalesItemLineDetail”: {
“ItemRef”: {
“value”: “1772”,
“name”: “#Hydrangea Paniculata Quick fire PW tige”
},
“UnitPrice”: 51.8,
“Qty”: 4,
“ItemAccountRef”: {
“value”: “367”,
“name”: “4060 Ventes de produits”
},
“TaxCodeRef”: {
“value”: “8”
}
}
},
{
“Id”: “3”,
“LineNum”: 3,
“Description”: “a620778327668”,
“Amount”: 51.8,
“DetailType”: “SalesItemLineDetail”,
“SalesItemLineDetail”: {
“ItemRef”: {
“value”: “1772”,
“name”: “#Hydrangea Paniculata Quick fire PW tige”
},
“UnitPrice”: 51.8,
“Qty”: 1,
“ItemAccountRef”: {
“value”: “367”,
“name”: “4060 Ventes de produits”
},
“TaxCodeRef”: {
“value”: “8”
}
}
},
{
“Id”: “4”,
“LineNum”: 4,
“Description”: “a620778316624”,
“Amount”: 7.6,
“DetailType”: “SalesItemLineDetail”,
“SalesItemLineDetail”: {
“ItemRef”: {
“value”: “2775”,
“name”: “#Spiraea japonica ‘Golden Princess’ 2 gal”
},
“UnitPrice”: 7.6,
“Qty”: 1,
“ItemAccountRef”: {
“value”: “367”,
“name”: “4060 Ventes de produits”
},
“TaxCodeRef”: {
“value”: “8”
}
}
},
{
“Id”: “7”,
“LineNum”: 5,
“Description”: “b620778333744”,
“Amount”: 59,
“DetailType”: “SalesItemLineDetail”,
“SalesItemLineDetail”: {
“ItemRef”: {
“value”: “4020”,
“name”: “Acer Ginella 200 cm”
},
“UnitPrice”: 59,
“Qty”: 1,
“ItemAccountRef”: {
“value”: “367”,
“name”: “4060 Ventes de produits”
},
“TaxCodeRef”: {
“value”: “8”
}
}
},
{
“Amount”: 333.2,
“DetailType”: “SubTotalLineDetail”,
“SubTotalLineDetail”: {}
}
],
“TxnTaxDetail”: {
“TotalTax”: 49.91,
“TaxLine”: [
{
“Amount”: 16.66,
“DetailType”: “TaxLineDetail”,
“TaxLineDetail”: {
“TaxRateRef”: {
“value”: “8”
},
“PercentBased”: true,
“TaxPercent”: 5,
“NetAmountTaxable”: 333.2
}
},
{
“Amount”: 33.25,
“DetailType”: “TaxLineDetail”,
“TaxLineDetail”: {
“TaxRateRef”: {
“value”: “23”
},
“PercentBased”: true,
“TaxPercent”: 9.975,
“NetAmountTaxable”: 333.2
}
}
]
},
“CustomerRef”: {
“value”: “1582”,
“name”: “Julien Pepin (perso)”
},
“BillAddr”: {
“Id”: “13842”
},
“ShipAddr”: {
“Id”: “16098”,
“Line1”: “Julien Pepin (perso)”
},
“FreeFormAddress”: true,
“GlobalTaxCalculation”: “TaxExcluded”,
“TotalAmt”: 383.11,
“HomeTotalAmt”: 383.11,
“PrintStatus”: “NotSet”,
“EmailStatus”: “NotSet”
}
]

5

So I was able to get this done, but it was weird and I’m sure there’s a more efficient way.

Due to the way Google Sheets accepts input, the output has to be like this so that :

image
image450×934 37 KB

It uses up about 5 Ops + number of unique items. In this case it’s 5 + 3 = 8 ops.

Does this look right? If so, I’ll post the blueprint so you can take a look an adapt to your scenario.

2 Likes