1
OK, I hope to be clear while explaining what I would like to do... I've setup a personal ADSB station (helping me with other tasks, too) putting antenna for 1090, Raspberry Pi 3B, filter and amplifier on my roof. I'm running dump1090-mutability task 24/7, feeding 5-6 online flight data services. With the help of 'jq' package (parsing aircrafts.json from dump1090 data), and after setting 'mailutils' and 'ssmtp' package to notify me by mail when custom ICAO, flight name or emergency transponder SQUAWK code is detected, with the help of CRON and some scripts of mine, I'm receiving notification mail of this kind:
{
"hex": "400af7",
"squawk": "1000",
"flight": "EZY42KU ",
"lat": 42.692322,
"lon": 10.078458,
"nucp": 7,
"seen_pos": 3.7,
"altitude": 37000,
"vert_rate": 960,
"track": 318,
"speed": 409,
"category": "A0",
"mlat": [],
"tisb": [],
"messages": 402,
"seen": 0,
"rssi": -27.5
}
This is the message body with detected ADSB data array parsed by 'jq'. With the help of IFTTT service, I've setup that for every mail of this kind a new record on a Google Spreadsheet is appended, to archive and log the contacts and the emergency SQUAWKs...
But the best result I've got until now is: column A for date and time, and column B for the ENTIRE body of the mail - I would appreciate if there's anyone who can tell me how to appoint for EVERY column a section of the array, like for example B for hex, C for squawk, C for flight name, etc. in the Google Spreadsheet.
Thanks!
Indrid Cold
Posted 2017-10-02T12:26:28.883
Reputation: 11
This looks like a huge effort to build this kind of system! However, I wonder, if it wasn't easier to update Google spreadsheet directly from the script that sends the email? With some Python knowledge and this documentation: https://developers.google.com/sheets/api/quickstart/python it shouldn't be hard.
– wvxvw – 2017-10-02T13:20:18.803Really thanks for your comment, first of all - I liked the mail notification idea to alert me when not in front of PC... if there aren't ways to do this, or if it is too complex, I can try directly from BASH script to GDrive/Google Spreadsheet... any idea for the correct parsing? – Indrid Cold – 2017-10-02T13:38:04.700
I'm not knowledgeable enough about this specific Python library, but I had to deal with Google spreadsheets about 6-7 years ago. I might try to write something simple tomorrow. Python also has an IMAP library that would allow to connect to your mailbox and read emails. – wvxvw – 2017-10-02T14:26:22.380
@wvxvw I'm sharing with you the script I'm using - this one catch and parse the data arrays from aircraft.json, file updated by dump1090-mutability task checking flight traffic:
#!/bin/bash
list=(7500 7600 7700)
for value in "${list[@]}" do curl http://127.0.0.1/dump1090/data/aircraft.json 2>/dev/null | jq '.aircraft[] | select(.squawk == "'$value'") | .' # echo ${value}
done
@wvxvw in the script before, I'm checking only for flights with emergency squawk codes. Now, how to parse every result string of the array to every columns of Google Spreadsheet (if I'll choose to go directly from bash to spreadsheet, leaving the GMail passage)? - to get things more 'salty', I've seen that if more contacts are seen if the same time, the arrays logged become multiple. – Indrid Cold – 2017-10-03T15:30:32.823
Hey, sorry for slow response. I've been trying to do something with https://developers.google.com/sheets/api/quickstart/python but it's really a pile of garbage... :( It's so bad, that it's totally unworkable. There is an alternative Python library that works with GooleSheets, but it doesn't support new API, and the whole of Google API is one huge mess... I've spent a day trying to edit a spreadsheet, but so far I couldn't even connect...
– wvxvw – 2017-10-04T14:48:59.153This is half the way through: it can only update the spreadsheet, but it cannot connect and read from GMail yet: https://github.com/wvxvw/mail2spreadsheet I put some instructions in the readme file, which should be displayed on the front page.
– wvxvw – 2017-10-04T16:25:13.920