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
 Share

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

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

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://bit.ly/3dOaZZ7

 

 

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

image.png.03712ebd2c72e6befa662c344bdfa36f.png

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.

image.thumb.png.1d3471d87733f5cec93f7136b0e244d9.png

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

image.thumb.png.92c9478569e9d873d79b64b14fc5fe2b.png

 

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

image.png.e949285e46ac742dada909f7a2bbab13.png

 

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.

 

 

 

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

  • Similar Content

    • By Isobe_Yasuaki
      こんにちは!
      このページではLoyverseの売上データを自動で会計freeeに送信する方法をご紹介します。
      ※本内容は動作保証対象外です。ご利用は自己責任でお願い致します。
      Loyverse Appマーケットプレイスには会計freeeと直接連動するためのアプリはありませんが、「Integromat」と「Google Sheets」を利用することでLoyverse上のレシートを会計freeeに直接送信することができます。

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

      「使い方」シートの指示にしたがって、連携用シートをfreeeアカウントに接続してください。
       
      手順 2:Integromatのアカウントを作成し、Loyverseと接続する。
      https://www.integromat.com/にアクセスし、アカウントを作成します。
      その後、ログインした状態で「Create a new Senario」をクリックします。

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

      次にIntegromatとLoyverseを接続するために、「Connection」→「Add」→「Loyverse OAuth2」を選択してください。
      これによって、IntegromatがLoyverseのデータにアクセスする許可を求める画面が表示されますので、許可をしてください。

      LoyverseとIntegromatの接続が完了したら、Event Type「Create」を選択し、店舗名を選択してください。
      販売量が多い店舗では、Limitに大きな数を入力してください。

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

      Values欄に、必須項目の収支区分、発生日、売上高、金額を図のように設定します。

      自動更新のインターバルを入力して、保存ボタンを押すとIntegromatの設定は完了です。


       
       
       
      手順 3:Google Apps Script で連携用シート→freeeの書き込みを自動化する。
      取引連携用シートから「スクリプトエディタ」を開きます。

       
      「トリガー」をクリックし、「freeeMenuDealsPost」をスプレッドシート「変更時」にトリガーが発動するようセットしてください。(時間ごとのトリガーでもかまいません。)

      次に「スクリプト」へ移動し、function freeeMenuDealsPost()の内容を変更します。
      この変更は、スプレッドシート上のデータをfreeeに書込んだあと、取引データを削除することを意味しています。
      (取引データの重複を避けるため。)

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

      ※コメントアウトの例
      /*SpreadsheetApp.getUi().alert( countPostedDeals + "件の取引を送信しました" );*/  
      なお、税率が2種類ある場合は、Integromatの編集が必要になります。
      これについては、余裕があれば更新します。
      以上です。 
       
      筆者:磯部 宇彰
      参考サイト:
      新卒ビジネス職の人間が、IFTTTを使ってGoogle Homeとfreeeを連携した話
      【freee API】GASを用いてGoogleスプレッドシートと連携する

Loyverse Point of Sale

 

 

 

 

×
×
  • Create New...