Jump to content

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


Recommended Posts

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


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)





loy 2.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.
- 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.



Edited by Nile
grammar correction
  • 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


Link to comment
Share on other sites

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://www.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.



This should work.

  • Like 1
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.





last module gives this error:


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:



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'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 



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.


Link to comment
Share on other sites

  • 1 month later...

  • Similar Content

    • By Isobe_Yasuaki
      Loyverse Appマーケットプレイスには会計freeeと直接連動するためのアプリはありませんが、「Integromat」と「Google Sheets」を利用することでLoyverse上のレシートを会計freeeに直接送信することができます。

      手順 1:freeeとGoogle Sheetsの連携
      取引データ連携用シートを取得し、自分のGoogle Driveにコピーを作成します。    

      手順 2:Integromatのアカウントを作成し、Loyverseと接続する。
      その後、ログインした状態で「Create a new Senario」をクリックします。

      「Loyverse」をシナリオに追加したら、「Watch Receipts」を選択します。

      次にIntegromatとLoyverseを接続するために、「Connection」→「Add」→「Loyverse OAuth2」を選択してください。

      LoyverseとIntegromatの接続が完了したら、Event Type「Create」を選択し、店舗名を選択してください。

      次にIntegromatにGoogle Sheets [Add a row]を追加して、下図のように設定してください。



      手順 3:Google Apps Script で連携用シート→freeeの書き込みを自動化する。


      次に「スクリプト」へ移動し、function freeeMenuDealsPost()の内容を変更します。

      function freeeMenuDealsPost(){ dealsTranscription(); postDeals(); } 変更後
      function freeeMenuDealsPost(){ dealsTranscription(); postDeals(); var bk = SpreadsheetApp.getActiveSpreadsheet(); var ss = bk.getSheetByName("Data"); ss.getRange('A2:Q1000').clear(); }  
      実際にLoyverse POSで取引を行い、freeeに記録されることを確認してください。
      今回の方法では、各レシートごとの取引がfreeeに記録されていきますが、一日の売上合計のみの記録が良い場合は、Google Sheetsの取引連携用シートを任意に編集してください。
      また、Google Sheetsからfreeeへデータ連携をする際、
      このコンテキストから SpreadsheetApp.getUi() を呼び出せません(行 181、ファイル「POSTリクエスト」) というエラーが発生する場合がございます。

      /*SpreadsheetApp.getUi().alert( countPostedDeals + "件の取引を送信しました" );*/  
      筆者:磯部 宇彰
      新卒ビジネス職の人間が、IFTTTを使ってGoogle Homeとfreeeを連携した話
      【freee API】GASを用いてGoogleスプレッドシートと連携する

Loyverse Point of Sale





  • Create New...