How to split JSON data from app script on a spreadsheet getting info from Wialon platform

Question

I've collected some code to get this work, this GPS platform (Wialon) is for tracking vehicles and it has some functions to get notifications, one of them is to send them via server GET / POST method, so I have the following result in one cell:

{"|2020/08/13 18:57|CR-03 FR|0 km|🦂|JESUS SALVADOR GARCIA SCOTT|":""} //example

I separated some values by "||||" characters just to split them easily by SPLIT() formula in Google Sheets, but I want a cleaner result from the script, this is what I got from this code:

Please if you can help me to get this FINAL result, it didn't have to be necessarily formatted (date), this already splitted and separated by "|":

In this code are other functions that send the same data to a Telegram Group, ignore it, just put it here in case helps to anyone.

var token = "FILL IN YOUR OWN TOKEN";     // 1. FILL IN YOUR OWN TOKEN
var telegramUrl = "https://api.telegram.org/bot" + token;
var webAppUrl = "FILL IN YOUR GOOGLE WEB APP ADDRESS"; // 2. FILL IN YOUR GOOGLE WEB APP ADDRESS
var ssId = "FILL IN THE ID OF YOUR SPREADSHEET";      // 3. FILL IN THE ID OF YOUR SPREADSHEET
var adminID = "-XXXXXXXXX";   // 4. Fill in your own Telegram ID for debugging

function getMe() {
  var url = telegramUrl + "/getMe";
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

function setWebhook() {
  var url = telegramUrl + "/setWebhook?url=" + webAppUrl;
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

function sendText(id,text) {
  var url = telegramUrl + "/sendMessage?chat_id=" + id + "&text=" + encodeURIComponent(text);
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

function doGet(e) {
  return HtmlService.createHtmlOutput("Hi there");
}

function doPost(e) {
  try {
    // this is where telegram works
    var data = JSON.parse(e.postData.contents);
    var text = data.message.text;
    var id = data.message.chat.id;
    var name = data.message.chat.first_name + " " + data.message.chat.last_name;
    var answer = "Hi " + name;
    sendText(id,answer);
    SpreadsheetApp.openById(ssId).getSheets()[0].appendRow([new Date(),id,name,text,answer]);
    
    if(/^@/.test(text)) {
      var sheetName = text.slice(1).split(" ")[0];
      var sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName) ? SpreadsheetApp.openById(ssId).getSheetByName(sheetName) : SpreadsheetApp.openById(ssId).insertSheet(sheetName);
      var newText = text.split(" ").slice(1).join(" ");
      sheet.appendRow([new Date(),id,name,newText,answer]);
      sendText(id,"your text '" + newText + "' is now added to the sheet '" + sheetName + "'");
    }
  } catch(e) {
    sendText(adminID, JSON.stringify(e,null,4));
  }
}

This is the notification panel in the GPS platform and how it should be configured with the App Script:

Answer 1

I believe your goal as follows.

• You want to split the following value in a cell of Google Spreadsheet.
  • {"|2020/08/13 18:57|CR-03 FR|0 km|🦂|JESUS SALVADOR GARCIA SCOTT|":""}

Sample formula:

=QUERY(ARRAYFORMULA(SPLIT(REGEXEXTRACT(SUBSTITUTE(A1:A5," km","|km"),"\|(\w.+)\|"),"|",TRUE,FALSE)),"select Col2,Col1,Col6,Col3,Col4")

• The flow of this formula is as follows.
  1. Put | to 0 km using SUBSTITUTE.
  2. Retrieve |2020/08/13 18:57|CR-03 FR|0 km|🦂|JESUS SALVADOR GARCIA SCOTT| from {"|2020/08/13 18:57|CR-03 FR|0 km|🦂|JESUS SALVADOR GARCIA SCOTT|":""} using REGEXEXTRACT.
  3. Split it with | using SPLIT.
  4. Rearrange the columns using QUERY.

Result:

When your sample value is used with above formula, it becomes as follows.

Note:

• Above proposed answer uses the built-in functions of Google Spreadsheet. If you want to convert above using Google Apps Script, please tell me. At that time, can you provide the sample values including {"|2020/08/13 18:57|CR-03 FR|0 km|🦂|JESUS SALVADOR GARCIA SCOTT|":""} from the response of the API? By this, I would like to think of the solution.

References:

• SUBSTITUTE
• REGEXEXTRACT
• SPLIT
• QUERY