Is it possible to have a script in Google sheets to read all the data of Loyverse POS with the API?

[Doppellecker]

Hello.

 

Is it possible to have a script in Google sheets to read all the data (sale-date, price, items, etc.)  of Loyverse Point of Sale with the API?

Thanks

[Yasuaki]

Hello there,

You can try Integromat to export data into Google sheets.

https://loyverse.com/marketplace/integromat

[ilih]

I'm using integromat to sheets but somehow i cant collect email addresses from loyverse with integromat. would i need to use the api call module for that?

[Nile]

By default, Integromat doesn't have an integration with the customer end point of the API. As a result, you have to use the API call or HTTP requests to move customer information. You can check the API documentation for more.
https://developer.loyverse.com/docs/

[ilih]

hi Nile thank you for responding
I'm slowly starting to understand it, but i have some missing pieces:

if i would want to call the "items" endpoint to get the stock level of a variant, what do you think i do wrong in the attached image?
I'm almost getting there. my integromat chain is updating a sheets cell, but i cant seem to get the stock quantity

[Nile]

@ilih Could you walk me through the flow you've got and the end goal?
I might be able to set something up and share a screenshot here.

[ilih]

that would be fantastic, here is 4 images showing the 4 modules:

1: connect to loyverse account

2: if a stock quantity gets update by a sale or manual action it triggers this module, make the api call and look for the stock quantity of a variable product (that's the part that is not working right in my chain)

3:make the sheet connection and look for a sku anywhere in the sheet 

4. enter new stock quantity in Colum K  (i can paste other strings like date or emloyee id etc in the right place so i know the chain is working but i just get the stock levels)

 

additional things that matter: i dont need stock level per store just one total because for online sales it doesnt matter where the stock is located, that's why in the first module i'm not connecting to a specific store but its just blanc so it takes any action from any store)

 

 

[ilih]

oh and the end goal:

sync variant stock quantity from loyverse to shopify in a sheet.  So shopify can then update the quantities based on that google sheet. 
 

[Nile]

Hi @ilih, I was able to solve the first challenge; Sending items along with inventory levels to Google sheet and having the inventory levels update anytime a sale is made. This was just a one way sync, Loyverse to Google sheet. I will share the JSON file of the mapping which you can easily import into your Integromat dashboard and link your accounts. I will just explain how it works.

In the Loyverse' API documentation, I found "https://api.loyverse.com/v1.0/inventory" which is an inventory end point for reading and writing inventory levels. I used this to update the inventory levels by searching for items by variant ids.

For my Google sheet, I used the Item template from Loyverse item list which you can get by simply importing your item list from your back office and I added extra fields like Item ID and Variant ID.

Flow process for Inventory Update:
- Created a Data store on integromat to save Variant IDs and Inventory levels (Variant ID, In stock, Store id)
- Use Inventory end point using HTTP requests on Integromat to periodically call up inventory levels update which will be sent to the data store. (I used Integromat's data store so as to prevent sending duplicate data)
- At this point your inventory levels will be updated every time a sale is made.
Next
- Get records from data store using the variant id that was sent to the data store
- Search google sheets for item with the same variant id
- Update inventory levels in google sheet.
 

 

Flow process for Item sync with Google sheet:
- Watch for items created which will be sent to Google sheets
- Search rows for all items added and match the variant ID against the Data store
- Update inventory levels of create item from In stock value stored in the Data store

 

 

You might have to through in an ignore end point just in case there are some errors.
I tested it an it works. 
Here is a link to the blueprint. You can import into your scenario and all endpoint will be mapped.
https://drive.google.com/drive/folders/12K1XzZKFYtfKWUgnf5z_lXaHUryPoM2i

 

 

[ilih]

that's just absolutely fantastic and i can not thank you enough that you created this! besides that it already works this gives my finally some examples how things work and to learn from. I added a filter in the first connection so that the sheet is not populated with old loyverse products that are no longer relevant(see pic below)

So the first part (populating a sheet with products works, but i can not understand how to connect to the HTTP OAuth module, the first module in the chain to update the stock

[ilih]

does this makes any sense to connect the http Oauth2 module? i get a error

 

[Nile]

Hi @ilih, I am glad it worked.
I'll take you through the entire process just in case you missed something. I also noticed you have a "?" at the end of the Authorize URL, perhaps that is the reason for the error. 

Connecting the HTTP OAuth2.0 is pretty easy. I forgot to mention one important part of setting it up.
You will need to go to https://developer.loyverse.com/ and add an App. When adding the app you will have to enter the redirect URL for Integromat - https://next.integromat.com/oauth/cb/oauth2 once you save this, you will get the App ID and App Secret. This is what you will use on Integromat.



On Integromat's end, once you open the Http OAuth2.0 connection, you will need to add the Authorization URL, Token URL, Client ID and Client Secret only.
Use these
Authorize URL: https://api.loyverse.com/oauth/authorize
Access Token Request: https://api.loyverse.com/oauth/token

 

Once you've done this, you can simply use the get request for inventory levels.
https://api.loyverse.com/v1.0/inventory

 

This should work.
 

[ilih]

that's great, i needed to add INVENTORY_READ in the scope and after that it worked with the HTTP connection

I run the second blueprint and i get an error in the last module (see pic below)

Do i use the same data store as the first script/blueprint (the one that creates the sheet with the products)and if yes do i make a KEY where there second script is looking for? or is it two different data stores that are just blanc.

 

 

 

last module gives this error:

[ilih]

oh and i manually added "IN STOCK"in column U header, i guess that's needed also

[Nile]

@ilih The Row number is gotten from the searched row endpoint



and the In stock is gotten from the data store:

 

Would you like to have a copy of my Google sheet template? 
Since I exported the Blueprint, some of the mapped points might be in line with my GSheet. 

[ilih]

yes that would be good, but i suspect you have  something setup in your data store or data structure that i'm missing cause its not passing in the in stock quantities:

[ilih]

[ilih]

this is how my data store looks:

 

[Nile]

@ilih ah yes, here is the structure of my Data store
You need just 4 fields - Variant ID, In Stock, Store ID, Date Updated (the only important ones are Variant ID and In Stock.)



You can find the Google sheet here: https://bit.ly/3dOaZZ7
 

[ilih]

yes that's what i thought, and sorry to drag this on, do i make a key in the database?

maybe if you feel like helping me further i can make you a user? i'm sure its just one setting somewhere cause it's not populating the database. if you send me a mail at hello  a   almost-summer.com 

 

[Nile]

Sure, I will be glad to take a look at it

[Efren]

Hello and thanks for this nice information. 

I´m trying to add all the rows of each receipt to gsheets, but not able to in the way I would like.

Right now I have a "watch receipts" module connected to a couple of "Add a row in Gsheet". I can choose which row will be added, but I cannot choose all the rows of each receipt. I could duplicate modules and choose rows until 20-30, for example, but I would have 2 issues:

- Receipts usually don´t have more than 20 rows, but sometimes you can do sales with more than 20 rows, and those will not be added to Gsheet.

- Blank rows will be added to Gsheet in receipts with less than 20 rows.

I would appreciate your guidance 

Thanks in advance.

[Nile]

Hi @Efren For end points with braces like this "[]" you need to use an Iterator because they store lists of items.