Jump to content

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


Doppellecker

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

image.thumb.png.8f4938b89173ef7cd8e270baf450f531.png

Link to comment
Share on other sites

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)

 

 

loy3.JPG

loy4.JPG

loy 2.JPG

loy1.JPG

Link to comment
Share on other sites

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. 
 

loy5.JPG

Link to comment
Share on other sites

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.
 
image.thumb.png.68b5c6648d0dab1fb9e9cf19f1210527.png

 

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
image.thumb.png.29b73d004a7547bad5a378e44f50e551.png
 

 

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

 

 

  • Like 1
Link to comment
Share on other sites

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

image.png.03712ebd2c72e6befa662c344bdfa36f.png

Link to comment
Share on other sites

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.

 

 

 

image.thumb.png.8f8c3913384480f16537c2ba957f95a9.png

last module gives this error:

image.png.e0d53a7daf92fae5e4066deaaf9c14a2.png

Link to comment
Share on other sites

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

Loyverse Point of Sale

and the In stock is gotten from the data store:

image.thumb.png.0efcbdaba550310d78cb32fd0be1fc8b.png

 

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. 

Link to comment
Share on other sites

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:

image.png.bfa03049b718416607c2d178f3d817c9.png

Link to comment
Share on other sites

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 

 

image.thumb.png.114f7f5ac0ac8e4fca70c0d10a85ff46.png

Link to comment
Share on other sites

  • 5 weeks later...

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.

1288272093_Add-rows-to-a-Google-Sheet-from-Loyverse-Receipts-Integromat(1)2.thumb.png.cdfee8eec8b81819c48c55cf3497084c.png

Link to comment
Share on other sites

  • 1 month later...

Loyverse Point of Sale

 

 

 

 

×
×
  • Create New...