1

Google Sheet updated by Wialon Local report - Apps Script

Topic: Google Sheet updated by Wialon Local report - Apps Script

Good morning,

As this is my first post, I hope I am posting in the right place.

We have a Wialon Local server and are planning to get data from a Wialon Report onto a Google Sheet.

This is because we are already subscribing to Google's services and were trying to explore what we can achieve with Apps Script's Installable Triggers.

Initially we would like to be getting Location Reports every hour or 30 minutes as per our defined geofence names. Please note below screenshot of the Wialon Report.

https://drive.google.com/file/d/1v7vxu9 … sp=sharing

Unfortunately I am also learning Google Apps Script (GAS) so will appreciate any direction on how to achieve this. Currently, I am unable to connect to our Wialon Local from GAS although working on it ... It does support Javascript so have been trying to connect using the sample codes in the Javascript API documentation.

Therefore, I will greatly appreciate any assistance to achieve this.

Warm regards.

2

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Good day,

Still need some direction as to how to go about this. I got a token for a user on our Wialon Local manually using these instructions:

How can I use this token to execute a report?

From my little understanding, am I not supposed to add this token to an API URL requesting to execute and run the report? And in the case of GAS, can I then run

UrlFetchApp.fetch(apiURL).getContentText()

?

Please any assistance will be appreciated.

3

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hello, shalin.lazar !

It's possible to execute report with required table data, but please note this request may be quite heavy (depend on report interval, amount of tables, units) and you can reach limits in system (depend on request frequency).
System Limitations - https://sdk.wialon.com/wiki/en/local/re … its/limits

If you need to get unit last data and position (location), there is other better way :
to execute request search items (units) with required flags.

Search items by property - https://sdk.wialon.com/wiki/en/local/re … arch_items
Data flags  - https://sdk.wialon.com/wiki/en/local/re … ormat/unit

Example,  searching units by name mask "*"
“itemsType”:avl_unit
flags = 1025 : 1  (base flag ) + 1024 (Last message and position) , first 200 elements (from":0,"to":200)

hst-api.wialon.com/wialon/ajax.html?svc=core/search_items&params={"spec":{"itemsType":"avl_unit","propName":"sys_name","propValueMask":"*","sortType":"sys_name"},"force":1,"flags":1025,"from":0,"to":200}}],"flags":0}&sid=0207c58abf39ae543e70b32387

Response (JSON)  returns all units which user has access, for example  "totalItemsCount":777,"indexFrom":0,"indexTo":200

where parameter "lmsg" show the last message with coordinates of last position (then you can check position by Google internal service to get exact Location).

for example , "lmsg":{"t":1441400392,"f":1073741891,"tp":"ud","pos":{"y":50.3382,"x":26.4060666667,"c":0,"z":0,"s":0,"sc":255}

Please note at all API request you need to use DNS platform site, for example (instead 'hst-api.wialon.com') my.local_site.com/wialon/ajax.html....
Also you need to execute POST requests from your script - https://sdk.wialon.com/wiki/en/local/re … /reqformat


Regarding Google Apps Script we cannot suggest you something as we don't know their functions(method), but I can explain a little bit more about authorization into Wialon.

First you need to login via token - https://sdk.wialon.com/wiki/en/local/re … oken/login
https://sdk.wialon.com/wiki/en/local/re … ogin/login

The token you need to receive on special authorization form  where you enter username and password
URL for authorization form  = DNS (platform site) + login.html + additional parameters (optional).

Then you can use this token (72-digital number) in your script

Please note without additional parameters which you can pass in URL , the token will have the default values (see them in table)
https://sdk.wialon.com/wiki/en/local/re … ogin/login

But in you case it's better to pass parameters duration=0 (means infinite token), access_type=-1 (full access token = the same access rights which user has on platform)

If you would like to use JS API, you can find example here  https://sdk.wialon.com/wiki/en/local/js … odesamples
For example, how to work with authorization form / token - https://sdk.wialon.com/playground/demo/app_auth_token
Or Login - https://sdk.wialon.com/playground/demo/login

More about token authorization is here https://weblog.gurtam.com/storage/old_e … hod_EN.pdf

Diana Cheley
Wialon Hosting Expert
Gurtam
4

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hi Diana,

Many thanks for your detailed feedback. Still have to digest everything said, but I am getting the following error right at the start when trying to login

{
          "error": 8,
          "reason": "OPERATE_AS_USER_NO_ACCESS: shalin"
}

I have even tried to use the wialon user to the same effect. What am I doing wrong here?

http://our_url/wialon/ajax.html?svc=token/login&params={%22token%22:%2272-char-token%22,%22operateAs%22:%22shalin%22}

Please also note that when I try a secured connection (https) the url is unreachable ...

Finally, I know I'm quite far away from this but ... we have been building geofences on our Wialon Local and we would like to use these same geofence location names in our final Google Spreadsheet. Can we be able to get these geofence names by executing request search items instead of dealing with coordinates?

We are planning to have this Location Live Feed updated once every 30 minutes, or even an hour. We have around 150 active units.

Please bare with me as I come to grips with how to work with your API. Will greatly appreciate your patience.

Thanks again!

5

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hi shalin.lazar !

Please find below the required information.

1. Please skip parameter "operateAs" - it needs only when you login under other sub-user.
We will add notes about it in documentation.

2. If there is not SSL certificates for monitoring site, the HTTPS will be not available.
Please request to your provider (or manager) Wialon Local with this issue

3. If the frequency of receiving data is not more than 30 minutes, you can execute request to get report data.
Before you need to create report for unit group,  add table Unit Latest Data (select column), set up option "Addresses from Geofences" in report's settings (in this case if unit is in geofence, the address location will be changed to geofence's name).

After you can execute report, for example -for previous 30 minutes.
( Here is example )

Interval beginning    2021-01-26 08:00:00
Interval end    2021-01-26 08:29:59
Report execution time    2021-01-26 08:30:29

https://hst-api.wialon.com/wialon/ajax.html?svc=report/exec_report&sid=02a11914b29a58aa81c602eed3df0396

{"reportResourceId":930849,"reportTemplateId":6,"reportTemplate":null,"reportObjectId":269,"reportObjectSecId":0,"interval":{"flags":16777344,"from":0,"to":30},"reportObjectIdList":[]}

where "reportResourceId" - resource Id where report template is created
"reportTemplateId" - report template id

"reportObjectId" - unit group id

"interval":{"flags":16777344,"from":0,"to":30} - the values of flags means interval in minutes, "from"/"to" - how much

The response will reserve the report content (tables array, how much rows, grouping level, column names and etc).

Then you need to execute request to get table data - for example use request report/select_result_rows.  It returns JSON that contains rows of all levels.


https://hst-api.wialon.com/wialon/ajax.html?svc=report/select_result_rows&sid=02a11914b29a602eed3df0396&params={"tableIndex":0,"config":{"type":"range","data":{"from":0,"to":5,"level":0,"unitInfo":1}}}

{"tableIndex":0,"config":{"type":"range","data":{"from":0,"to":5,"level":0,"unitInfo":1}}}

"tableIndex":0 - means 1st table in array

"from":0,"to":5 - as in previous request parameters rows: 6 , and level:1

Response

+ show spoiler

[
{
"n":0,
"i1":0,
"i2":0,
"t1":1581184794,
"t2":1581184794,
"d":0,
"uid":2075,
"c":[
"AKVF Samu3",
{
"t":"Batisha geofence",
"y":23.1959667206,
"x":91.3166732788
}
]
},
{
"n":1,
"i1":0,
"i2":0,
"t1":1611579809,
"t2":1611579809,
"d":0,
"uid":21414,
"c":[
"awesomebr",
{
"t":"Williams zones",
"y":42.1454546667,
"x":-123.325493167
}
]
},
{
"n":2,
"i1":0,
"i2":0,
"t1":1606132148,
"t2":1606132148,
"d":0,
"uid":22242,
"c":[
"diana zapravka",
{
"t":"Zone1",
"y":54.064365,
"x":38.2653316667
}
]
},
{
"n":3,
"i1":0,
"i2":0,
"t1":1606130259,
"t2":1606130259,
"d":0,
"uid":22256,
"c":[
"FREIG di",
{
"t":"France, Aube, Neuville-sur-Seine 10250, D 26",
"y":57.079603,
"x":65.233575
}
]
},
{
"n":4,
"i1":0,
"i2":0,
"t1":1582359234,
"t2":1582359234,
"d":0,
"uid":2070,
"c":[
"Renaul di",
{
"t":"Zone3",
"y":54.0035171509,
"x":38.3098869324
}
]
},
{
"n":5,
"i1":0,
"i2":0,
"t1":1603273334,
"t2":1603273334,
"d":0,
"uid":2284,
"c":[
"Unit 4",
{
"t":"market",
"y":54.9297294617,
"x":47.9538650513
}
]
}
]

So you will get in parameter "c" : unit name, zone name ("t"), coordinates (x & y)

"c":[
"Renaul di",
{
"t":"Zone3",
"y":50.0035171509,
"x":12.30988324
}
]

Please note if there is not geozones for location, the 't' will contain just address

{
"t":"France, Aube, Neuville-sur-Seine 10250, D 26",
"y":57.079603,
"x":65.233575
}
Diana Cheley
Wialon Hosting Expert
Gurtam
6

Google Sheet updated by Wialon Local report - Apps Script

(edited by shalin.lazar 27/01/2021 16:58:18)

Re: Google Sheet updated by Wialon Local report - Apps Script

Hi Diana,

Many thanks for your patience. Please note my responses below:

Diana wrote:

1. Please skip parameter "operateAs" - it needs only when you login under other sub-user.
We will add notes about it in documentation.

Well noted and I have got desired response from our Wialon Local server on a web browser. However, I am getting the following error when trying to retrieve the EID.

EDIT: Below Issue was resolved by encoding the URL as follows:

  var urlEncoded = encodeURI(apiURL);
  var resText = UrlFetchApp.fetch(urlEncoded).getContentText()

Kindly disregard below question which I am leaving in case anyone else faces the same issue. I am now slowly going to attempt to execute and retrieve data from the report.

Please note below call in Apps Script:

  let apiURL = `http://mywialon.com/wialon/ajax.html?svc=token/login&params-{"token":"d368bcc25d173fVVVVVVVVVVVVVVVVVVVVC947EBC1FDAB4EE7B73AF1ABF370370B89E2A9"}`  
  console.log(apiURL)
  var resText = UrlFetchApp.fetch(apiURL).getContentText()
  console.log(resText)

I am getting the following error on Apps Script, please let me know if you have any idea why this is happening. Just to confirm, the responses to all these requests are in JSON, right?

11:48:27 AM    Error    
Exception: Invalid argument: http://mywialon.com/wialon/ajax.html?svc=token/login&params={"token":"d368bcc25d173fVVVVVVVVVVVVVVVVVVVVC947EBC1FDAB4EE7B73AF1ABF370370B89E2A9"}
wialonLogin    @ wialon.gs:20

2. If there is not SSL certificates for monitoring site, the HTTPS will be not available.
Please request to your provider (or manager) Wialon Local with this issue

We have our own Wialon Local server and I am the administrator. I never really got full training, so will have to look through documentation.

I did however use the https://hst-api.wialon.com example and got the same results: I.e. successful on browser but the same Apps Script error when plugging the below into the code, so I'm assuming this isn't the reason why I am getting the error.

let apiURL = `https://hst-api.wialon.com/wialon/ajax.html?svc=token/login&params={"token":"5dce19710a5e26ab8b7b8986cb3c49e58C291791B7F0A7AEB8AFBFCEED7DC03BC48FF5F8"}`

Regarding the reports, I haven't gotten there yet as I am still trying to come to grips with how to get Apps Script to work with the API.

However, I have already created a report which we are running using the Advanced Reporting module which we purchased. Only thing we need to do is add all the required Item Groups as objects and the report works as we require. I will attach a copy of the report for your reference.

Diana wrote:

After you can execute report, for example -for previous 30 minutes.
( Here is example )

Interval beginning    2021-01-26 08:00:00
Interval end    2021-01-26 08:29:59
Report execution time    2021-01-26 08:30:29

https://hst-api.wialon.com/wialon/ajax.html?svc=report/exec_report&sid=02a11914b29a58aa81c602eed3df0396
[/quote]
Noted. Is the sid parameter the same as the eid parameter I'm seeing on login?

{"reportResourceId":930849,"reportTemplateId":6,"reportTemplate":null,"reportObjectId":269,"reportObjectSecId":0,"interval":{"flags":16777344,"from":0,"to":30},"reportObjectIdList":[]}

where "reportResourceId" - resource Id where report template is created
"reportTemplateId" - report template id

"reportObjectId" - unit group id

"interval":{"flags":16777344,"from":0,"to":30} - the values of flags means interval in minutes, "from"/"to" - how much

How can I get the ID values for reportResource, reportTemplate, etc.? As I will be executing the report on multiple Objects, how would I input the list of all objects instead of just one?

Thanks again for your assistance.

Post's attachments

Attachment icon Freight_Backups_[Freight_Backups]_Last_Geofence_Location_2021-01-27_12-07-54.xlsx 17.34 kb, 26 downloads since 2021-01-27 

7

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hello shalin.lazar !

1. If the error related to Apps Script, unfortunately, we cannot help with this.
When you execute POST request to login into Walon (  for example, https://hst-api.wialon.com/wialon/ajax.html?svc=token/login&params={"token":"2fe8024e0ab91aa6c8ed82717b71bddcECDC362358DF7D90986F5173D405CD0D42DE7B38"}   ), the server will return response in JSON format.

2. To get items id - you can use request searh_items, and search element by name.
Please look example how to find users, the same you can use to find unit, resource, just specify the proper 'itemsType' - avl_unit , avl_resource
https://sdk.wialon.com/wiki/en/local/re … arch_items

Also (some lifehack ) you can open Develop Console in browser, and look which requests  going from Wialon , when do one of the action.
For example report's execution.

Diana Cheley
Wialon Hosting Expert
Gurtam
8

Google Sheet updated by Wialon Local report - Apps Script

(edited by shalin.lazar 08/02/2021 09:07:04)

Re: Google Sheet updated by Wialon Local report - Apps Script

Hi Diana,

Thanks for your reply. As mentioned in earlier edit, I managed to get a response on GAS from our Wialon Local server by removing the password and encoding the URL. Many thanks for that.

Please note response from our server after login request:

{
"host": "XXX.XXX.XXX.XXX",
"eid": "213139c8hcjdkdbfb55f21vff8aa5104",
"gis_sid": "4bb3a880",
"au": "shalin",
"tm": 1612737634,
"wsdk_version": "1.286",
"api": "local",
"local_version": "local_2004",
"env": {
"max_http_buff": 209715200
},
"base_url": "",
"hw_gw_ip": "",
"hw_gw_dns": "",
"avd": 1,
"user": {
"nm": "shalin",

In this response is my session id 4bb3a880?

I am getting error 1 when trying to view all users, basically failing to run the example you gave me sad

Here is my request:

https://mywialon.com/wialon/ajax.html?svc=core/search_items&
    params={
        "spec":{
             "itemsType":"user",
            "propName":"sys_name",
                "propValueMask":"*",
            "sortType":"sys_name"
        },
                "force":1,
              "flags":1,
              "from":0,
              "to":0
    }&sid=4bb3a880

I tried the sid with and without double quotes, both times getting:

{
    "error": 1
}

Will greatly appreciate your assistance again.

9

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hello shalin.lazar !

In this response is my session id 4bb3a880?....
I am getting error 1 when trying to view all users.....

No, parameter "eid" is session id, so  your session is 213139c8hcjdkdbfb55f21vff8aa5104
( https://sdk.wialon.com/wiki/en/local/re … n#response )

That is why you get error 1 which means invalid session

Diana Cheley
Wialon Hosting Expert
Gurtam
10

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hi Diana,

Many thanks once again for your patience. I am now able to get user data.

However, I am not getting any data when setting the "itemsType" parameter to resource, unit, unit group or reporttemplates.

http://mywialon.com/wialon/ajax.html?svc=core/search_items&
    params={
        "spec":{
             "itemsType":"resource",
            "propName":"sys_name",
                "propValueMask":"*",
            "sortType":"sys_name"
        },
                "force":1,
              "flags":1,
              "from":0,
              "to":0
    }&sid=...

I am getting this same response with all the different itemsType mentioned earlier:

{
"searchSpec": {
"itemsType": "resource",
"propName": "sys_name",
"propValueMask": "*",
"sortType": "sys_name",
"propType": "",
"or_logic": "0"
},
"dataFlags": 1,
"totalItemsCount": 0,
"indexFrom": 0,
"indexTo": 0,
"items": []
}

What am I doing wrong here? Will greatly appreciate any assistance.

11

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hello!

Not correct value -  "itemsType":"resource"
Correct is  "itemsType":"avl_resource"
Please look here https://sdk.wialon.com/wiki/en/sidebar/ … parameters

Diana Cheley
Wialon Hosting Expert
Gurtam
12

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hi Diana,

Thanks to your patience, I have been able to execute and see data from the report. Greatly appreciate your assistance!

One detail that I have no idea on how to begin is with the time zone. The data I have receiving in JSON has time as at GMT, I wanted it as at GMT+2. How would I go about that?

I can also add 2 hours to the data but was wondering whether I could execute the report with a time zone parameter.

Many thanks once again.

13

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hello Shalin,

By default the data response always at GMT+0
To get response data with required timezone , it needs after login execute request svc=render/set_locale to set up timezone
For example,  GMT+2

https://hst-api.wialon.com/wialon/ajax. … 516e0d6647
params: {"tzOffset":134224928,"language":"en","flags":256,"formatDate":"%m-%E-%Y %H:%M:%S"}

Please note it also need to URL-encoded special characters as % (use %25 instead  %)

Diana Cheley
Wialon Hosting Expert
Gurtam
14

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

Hi Diana,

Thanks again, I have got the time in GMT+2 but the format is YYYY-MM-DD. I wanted it as DD-MM-YYYY.

Please see my request:

http://mywialon.com/wialon/ajax.html?svc=render/set_locale&sid=e8718964942f5g2f5g79fc3e105c551b&params={"tzOffset":134224928,"language":"en","flags":256,"formatDate":"%25E-%25m-%25Y %25H:%25M:%25S"}

I can always change the format on Google Sheets, but what am I doing wrong here?

15

Google Sheet updated by Wialon Local report - Apps Script

Re: Google Sheet updated by Wialon Local report - Apps Script

shalin.lazar wrote:

Hi Diana,

Thanks again, I have got the time in GMT+2 but the format is YYYY-MM-DD. I wanted it as DD-MM-YYYY.

Please see my request:

http://mywialon.com/wialon/ajax.html?svc=render/set_locale&sid=e8718964942f5g2f5g79fc3e105c551b&params={"tzOffset":134224928,"language":"en","flags":256,"formatDate":"%25E-%25m-%25Y %25H:%25M:%25S"}

I can always change the format on Google Sheets, but what am I doing wrong here?

Hello Shalin,

yes, there is correct format, %25E-%25m-%25Y shows the date like 16-02-2021

https://hst-api.wialon.com/wialon/ajax.html?svc=render/set_locale&sid=02baa4b164ecffc126cac978bc622d31&params={"tzOffset":134228528,"language":"en","flags":256,"formatDate":"%25E-%25m-%25Y %25H:%25M:%25S"}

Diana Cheley
Wialon Hosting Expert
Gurtam