Square Brackets not valid URLs?

LiquidLuke

Ok so Im trying to load_Relations which according to the API examples requires square brackets, 
I noticed there is a tip referencing encoding of reserved characters, I have try the following urls and get an 'Invalid Argument' error for this url: 

https://api.lightspeedapp.com/API/Account/accountID/Sale.json?load_relations=%5B"SaleLines","SaleLines.item"%5D&shopID=3

and the same for this url:

https://api.lightspeedapp.com/API/Account/accountID/Sale.json?load_relations=["SaleLines","SaleLines.item"]&shopID=3

At the end of the day I need to grab sales data that includes:

I would like to have this data updated every 15 minutes and merging multiple data calls seems like a nightmare as opposed to making a single call with the correct fields? 

Employee(s)

Hours worked by D/W/M

Average Sales D/W/M

Units Per Sale D/W/M

Total Sales D/W/M

Sales

Store Total By D/W/MTD

Average Sales D/W/MTD

Units Per Sale D/W/MTD

Sales By Hour MTD/YTD

Sales By Day MTD/YTD

Store Margin D/M/YTD

Product

Top 10 Products MTD/YTD

 Advice and suggestions much appreciated  

gregarican

The second URL you posted should work I think. Here is a GET request I just issued and it came back fine.

https://api.merchantos.com/API/Account/{AccountID}/Sale.json?archived=false&shopID=3&load_relations=["SaleLines","SaleLines.Discount","SaleLines.Note","SaleLines.Item","SalePayments","SalePayments.PaymentType","SalePayments.SaleAccounts","Customer","Customer.Contact","Discount"]&offset=0&limit=100

If you only needed daily reporting what I would suggest is what I instituted here. After-hours I pull all the product, customer, vendor, sales, etc. data via the LS Retail API. Push it all into local SQL Server DB tables. Then I can create any slice-and-dice type of report to render whatever my endusers request. It's a lot easier then, so the response time is super-quick, any new angles in terms of table joins for a different view can be taken into account, and the stingy LS Retail API throttling is transparent to the endusers.

Since you are looking for a more real-time view of what's going on, you will likely have to make a couple of interrelated API queries. Pull the sales, store the associated employee ID's in a variable, pull their full names,  pull their hours, etc. Then the other branch would be off the initial sales pull --- storing the associated product ID's in a variable, in order to pull the product identifiers, etc.

The end game will be a very effective dashboard for sure. If it were me I'd still store all the MTD and YTD data in some local middleman DB. Then you would just have to pull the current daily sales in real-time from the API...

LiquidLuke

I've never worked with a proper Database.... that is my next personal learning venture so to speak, from which I do plan to use for this. I've been a bit gun shy about not having the visible data that a spreadsheet offers, but, at the end if the day when I think about all the code I have to write just to set and retrieve code from the spreadsheets.... it's a little ridiculous I'm afraid to say but I appreciate the reply and I will definitely make use of a similar call as you have provided. 
I'm actually having trouble with the query portion as it's just returning all of the data and not the data > last entry, is there something about the syntax that iI have wrong maybe?

https://api.lightspeedapp.com/API/Account/AcctID/Sale.json?load_relations=[%22SaleLines.Item%22]&shopID=3&saleID%3D%3E%2C15392"

Adrian Samuel

@LiquidLuke, make sure you encode all the "=" after the initial one from the load_relations in the query, but other than that, i'm not having any issues with this query

LiquidLuke

I cant get it to load anything but All its like its not even seeing the query request for the > saleID
is this one ok?

https://api.lightspeedapp.com/API/Account/166476/Sale.json?shopID=3&load_relations=[%22SaleLines.Item%22]&saleID=%3E%2C513829

LiquidLuke

Does the order of the various parameters in the URL matter at all? that is to say should my shopID=3&saleID=>,23445  be entered before the l
load_relations[] or visa versa?

LiquidLuke

Ok I got it sorted, turns out I had mistaken the logging of my url for the call of the url and it was being overwritten after my debug log DOH! always the little dumb ones that are the worst! anyway I now have a script which I run from Google Sheets that creates an object with pre determined urls and then creates a separate spreadsheet for each shop and the logs the daily sales to the appropriate sheet. that data is then then used by DataStudio.google.com to display KPIs to the shop owner. 
Ive done my best to be as DRY as possible for now and have most common function call populated in a custom menu in google sheets and I would like to make this a plugin available in the google marketplace at some point. if I ever do I ill let you know

Adrian Samuel

Just incase anyone is reading, the order of query parameters do not matter

@LiquidLuke haha it always happens!!
Great work! I actually had an identical idea myself haha, probably going to schedule sometime for myself to hack it out

What back-end language/framework are you using?

LiquidLuke

I'm no pro I'm just using Apps Script in Googles Gsuite. Which is a fancy Namespace implementation of JavaScript.  Which is why I'm second guessing much of this as the closest examples are the shell examples given on the API page. But Gsuite comes baked in with organizational security and allows me to easily access and send email, as well as create reminders on a users Calendar and save files to Google drive. 
I'm currently segmenting my code as much as possible to allow for additional endpoints without rewriting all the code with the different endpoints explicitly defined, but the relations make it tough. I'm basically trying to automate invoicing and order managing as many of our suppliers require a csv with the details be uploaded to their site for processing. Also, I have the sales and various stats being displayed on a user-specific dashboard. and all of this code centralized from a single Spreadsheet script lol.
Admittedly it's getting a little frazzled hence my now focus on cleaning and modulizing my code, Id love any feedback if you wanna have a look. you can check out my code on Github.com/marshallsbest /lightspeed-api 
And I can't seem to push the lates down to Github at the moment But I will as sooin as Github is back up.