how to specific JSON data from cell in google sheets-CodePudding

Im using TTN to send data as JSON to a cell in sheets, but all the data is ofc in one cell. how would I extract only the data I need into a cell for each data point. the JSON file looks like this. The data I need is all the decoded.payload

{"end_device_ids":{"device_id":"carls","application_ids":{"application_id":"prove"},"dev_eui":"A861A3032496B11","join_eui":"0000000000000000","dev_addr":"260BDF9"},"correlation_ids":["as:up:01FXJ2E9J1PDY7QPR8DPM69NMK","gs:conn:01FXHWEX62T1MBPBP0MX9JJJ95","gs:up:host:01FXHWEX6GK8F3ZKYTZNC7GEJ","gs:uplink:01FXJ29BDR6AK5R9D5P5R1T3","ns:uplink:01FXJ2E9BPAQMTQBBRTQCKFRC","rpc:/ttn.lorawan.v3.GsNs/HandleUplink:01FXJ2E9B2M1Y63PR0DV5BZW7","rpc:/ttn.lorawan.v3.NsAs/HandleUplink:01FXJ2E9J0DXKCP7KV5F00DK9"],"received_at":"2022-03-07T11:32:41.665432609Z","uplink_message":{"session_key_id":"AX9t/rec6yxiPpfOgbbw==","f_port":2,"f_cnt":2,"frm_payload":"AWBYQEtA7s=","decoded_payload":{**

"I1I_Overflade":3.59,"I2I_Dybde":3.53,"I3I_Klarhed":3.01,"I4I_Lys":9.55

**},"rx_metadata":[{"gateway_ids":{"gateway_id":"carls-gateway","eui":"58A0CFFFE80127"},"time":"2022-03-07T11:32:41.363879919Z","timestamp":1974812332,"rssi":-111,"channel_rssi":-111,"snr":-7,"location":{"latitude":55.284274413402,"longitude":14.7813155831094,"source":"SOURCE_REGISTRY"},"uplink_token":"ChsKGQoNYFybHMt2F0ZXdheRIIWKDL//6AGycrP3UrQcaDjZ2peRBhD5jufSASDgn/PfvLYBKgwI2dXkQYQ7vBrQE="}],"settings":{"data_rate":{"lora":{"bandwidth":125000,"spreading_factor":8}},"coding_rate":"4/5","frequency":"86500000","timestamp":1974812332,"time":"2022-03-07T11:32:41.363879919Z"},"received_at":"2022-03-07T11:32:41.443074303Z","consumed_airtime":"0.113152s","locations":{"user":{"latitude":52.282073015039,"longitude":12.7818470012207,"source":"SOURCE_REGISTRY"}},"version_ids":{"brand_id":"arduino","model_id":"mkr-wan-1310","hardware_version":"1.0","firmware_version":"1.2.0","band_id":"EU_863_870"},"network_ids":{"net_id":"000013","tenant_id":"ttn","cluster_id":"ttn-eu1"}}}

CodePudding user response：

Try this

=extractPayload(A1)

with this custom function

function extractPayload(json) {
  var data = JSON.parse(json)
  var result = []
  result.push(data.uplink_message.decoded_payload.I1I_Overflade)
  result.push(data.uplink_message.decoded_payload.I2I_Dybde)
  result.push(data.uplink_message.decoded_payload.I3I_Klarhed)
  result.push(data.uplink_message.decoded_payload.I4I_Lys)
  return [result]
}