Skip to main content

Here’s how the data looks:


{

   "gatewayMessage":{

       "gatewayID":"956995" ,

       "gatewayName":"Ethernet Gateway 4 - 956995" ,

       "accountID":"40029",

       "networkID":"66417" ,

       "messageType":"0" ,

       "power":"0",

       "batteryLevel": "101" ,

       "date": "2022-09-16 00:00:30",

       "count":"3",

       "signalStrength": "0",

       "pendingChange": "False" 



   },

   "sensorMessages":"

      {

          "sensorID":"701519" ,

          "sensorName":"Cooler 1",

          "applicationID":"2",

          "networkID":"66417",

          "dataMessageGUID":"88e199c6-62a3-4d2c-9a57-7c0a375282d0",

          "state": "16",

          "messageDate": "2022-09-16 00:00:03",

          "rawData":"7.9",

          "dataType": "TemperatureData",

          "dataValue": "7.9",

          "plotValues": "46.22",

          "plotLabels": "Fahrenheit",

          "batteryLevel": "13",

          "signalStrength": "76",

          "pendingChange": "True",

          "voltage": "2.24"



      },

      {

          "sensorID":"644289" ,

          "sensorName":"Wash Water Temp",

          "applicationID":"86",

          "networkID":"66417",

          "dataMessageGUID":"53691a40-f355-4286-bc71-e34dc45e9002",

          "state": "0",

          "messageDate": "2022-09-16 00:00:04",

          "rawData":"23.7",

          "dataType": "TemperatureData",

          "dataValue": "23.7",

          "plotValues": "74.66",

          "plotLabels": "Fahrenheit",

          "batteryLevel": "100",

          "signalStrength": "93",

          "pendingChange": "True",

          "voltage": "3.33"



      },

      {

          "sensorID":"701520" ,

          "sensorName":"Cooler 2",

          "applicationID":"2",

          "networkID":"66417",

          "dataMessageGUID":"c3b90bc1-4f99-4b59-8bf5-6f3e07cbdd60",

          "state": "16",

          "messageDate": "2022-09-16 00:00:04",

          "rawData":"22.8",

          "dataType": "TemperatureData",

          "dataValue": "22.8",

          "plotValues": "73.04",

          "plotLabels": "Fahrenheit",

          "batteryLevel": "100",

          "signalStrength": "77",

          "pendingChange": "True",

          "voltage": "2.68"



      }



   ]



}

It shows up in airtable this way:


For each of these sensors, I would like to create a new record with the name of the sensor and the temperature. Any help would be appreciated.

There are only 2 ways to loop through JSON data and create a new record in Airtable for each record in your JSON data:




  1. Write a custom JavaScript to process the data. I don’t know JavaScript, so somebody else would need to guide you there.




  2. Use an external automation tool that offers webhooks, like Make.com. Send your data to a webhook there, and Make will automatically loop through all the JSON records on your behalf so you can create individual records in Airtable. (Make might also natively support whatever app is providing you with that JSON data.)



Can you proxy the JSON data through a different service to flatten the data? Otherwise you are going to have trouble with the Airtable receiving webhook accessing all of the body.

There are only 2 ways to loop through JSON data and create a new record in Airtable for each record in your JSON data:




  1. Write a custom JavaScript to process the data. I don’t know JavaScript, so somebody else would need to guide you there.




  2. Use an external automation tool that offers webhooks, like Make.com. Send your data to a webhook there, and Make will automatically loop through all the JSON records on your behalf so you can create individual records in Airtable. (Make might also natively support whatever app is providing you with that JSON data.)




I don’t know JavaScript either. I did try make.con but it only works with the first record, it doesn’t keep going with the others. Perhaps there’s a setting in missing?

Make definitely works with all the records. You may need to use the Parse JSON tool or the Iterator tool to help you get all the records.

I don’t know JavaScript either. I did try make.con but it only works with the first record, it doesn’t keep going with the others. Perhaps there’s a setting in missing?


it’s a tricky task for first time, but last weeks I had todo a lot of table ‘rotations’, so it’s not so much code needed


put each value in separate variable, try to not add spaces and extra chars, because then array will be converted to string.

I did for 4 properties, for example, you may add all or choose any you want and even no need to change code.



You can use output variables to create records at next step. I mean, 3 next create record steps ))


Source:


const {id,...rest}=input.config();

const msg={id,...rest}

const keys=Object.keys(msg)

const sensors=id.map((o,ix)=>(Object.assign({},...keys.map(k=>({sk]:msg=k]tix]})))))

sensors.forEach(s=>(output.set('sensor_'+s.id,s)))

or, you can match variable names with field names, and if fields are simple text, substitute last line (sensors.foreach...) by write command, and put it to table:





await base.getTable('NAME').createRecordsAsync(sensors.map(s=>({fields:s})))

Make definitely works with all the records. You may need to use the Parse JSON tool or the Iterator tool to help you get all the records.


I was able to get it done with the parse json tool and the iterator.

it’s a tricky task for first time, but last weeks I had todo a lot of table ‘rotations’, so it’s not so much code needed


put each value in separate variable, try to not add spaces and extra chars, because then array will be converted to string.

I did for 4 properties, for example, you may add all or choose any you want and even no need to change code.



You can use output variables to create records at next step. I mean, 3 next create record steps ))


Source:


const {id,...rest}=input.config();

const msg={id,...rest}

const keys=Object.keys(msg)

const sensors=id.map((o,ix)=>(Object.assign({},...keys.map(k=>({kk]:msg{k]]ix]})))))

sensors.forEach(s=>(output.set('sensor_'+s.id,s)))

or, you can match variable names with field names, and if fields are simple text, substitute last line (sensors.foreach...) by write command, and put it to table:





await base.getTable('NAME').createRecordsAsync(sensors.map(s=>({fields:s})))


Thank you for the help. I was able to get it done with make, but I’m going to try scripting as well since the native solution would be the most robust.

I was able to get it done with the parse json tool and the iterator.



That’s great to hear! Make is the best! :grinning_face_with_big_eyes:

Reply


Terms & Conditions