How to handle url.fetch response that comes in UTF-8 without BOM?

Question

Google Apps Script

Hello.

Through App Script I send a request to the API to a specific service.

The service returns a stream of data in text format (UTF-8 without BOM)

Here is a piece of my code in a google app script that accepts a response from UrlFetchApp.fetch

function second(exp_id, secret){
 var respons = UrlFetchApp.fetch('https://vsemart.getcourse.ru/pl/api/account/exports/'+exp_id+'?key='+secret)
 Logger.log('Second: ' + JSON.stringify(respons.getContentText()))
}

Here is the answer:


Answer in text format:

Logging output too large. Truncating output. Second: "{\"success\":true,\"info\":{\"fields\":[\"id\",\"Email\",\"Тип регистрации\",\"Создан\",\"Последняя активность\",\"Имя\",\"Фамилия\",\"Телефон\",\"Дата рождения\",\"Возраст\",\"Страна\",\"Город\",\"От партнера\",\"В какой соцсети о нас узнали?\",\"Leelloo_Chat\",\"счетчик дожима\",\"test server\",\"Leeloo_id\",\"source_link\",\"reg_out_compain\",\"Код для доступа к урокам\",\"vk_uid\",\"sourceUtmCompain\",\"4 секретные цифры\",\"Секретные цифры для Нового Года\",\"Строка\",\"Выбор\",\"LinkToPact\",\"MessageToWA\",\"sendMessageToPact\",\"Я согласен с правилами\",\"Код для бонусных уроков\",\"Код после автовеба NEW\",\"bothelp_id\",\"Откуда пришел\",\"utm_source\",\"utm_medium\",\"utm_campaign\",\"utm_term\",\"utm_content\",\"utm_group\",\"ID партнера\",\"Email партнера\",\"ФИО партнера\",\"ФИО менеджера\",\"VK-ID\"],\"items\":[[\"26911176\",\"[email protected]\",\"Зарегистрировался самостоятельно\",\"2018-10-12 07:46:25\",\"2021-04-26 23:00:05\",\"Елена\",\"\",\"7927\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"utm_source=%D0%A2%D0%95%D0%A1%D0%A21&utm_medium=%D0%A0%D0%A4+2+%E2%80%94+%D0%9A%D0%BE%D0%BF%D0%B8%D1%8F&utm_campaign=1&fbclid=PAAaYhXyWb--p-E\",\"\",\"\",\"\",\"1\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\"],[\"34357234\",\"[email protected]\",\"Зарегистрировался самостоятельно\",\"2019-02-11 18:37:32\",\"2021-04-27 18:15:11\",\"Яна\",\"Кирьянова\",\"[phone]\",\"1981-05-24\",\"40\",\"Россия\",\"Санкт-Петербург\",\"\",\"\",\"https://app.leeloo.ai/chats/All/5cfccdd311000000000/go\",\"\",\"Записали ссылку на ГК Аккаунта в Лилу\",\"5cfccdd31179000000000\",\"cookie_id=a478aab3832f461000000000066&sender_id=60881d7a15c00000000001&leeloo_account_id=5cfccdd31179c000000000&utm_medium=messenger&utm_campaign=besplatniikursrassilka2\",\"\",\"\",\"\",\"besplatniikursrassilka2\",\"6936\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"https://away.vk.com/\",\"away.vk.com\",\"referral\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\"],[\"34361296\",\"[email protected]\",\"Зарегистрировался самостоятельно\",\"2019-02-11 19:11:56\",\"2021-04-27 19:16:37\",\"Марина\",\"Иванова\",\"[phone]\",\"1976-11-22\",\"45\",\"Россия\",\"Ступино\",\"\",\"\",\"http

The question is, how can I process this data, extract, for example, the value of a certain parameter? Or put this data in the form of a table on a google sheet?

Answer 1

Indeed, Apps Script services don't usually support marker files directly.
In this case, you get text, and the API service ensures that the body of the response is in JSON format (also text!). To represent the formatted data as a programming language resource (for JS, these are root ancestor objects), it is enough to parse this text.

if(respons.getResponseCode()){
  const data = JSON.parse(respons.getContentText());
  console.log(data.info);
}

As an addition . It's worth noting that parsing external text is an exclusive operation, so if a parsing error occurs, the environment (what JS does) will throw an exception to ensure the order in which the code is executed.

> JSON.parse('rewt{')
< VM240:1 Uncaught SyntaxError: Unexpected token r in JSON at position 0
    at JSON.parse (<anonymous>)
    at <anonymous>:1:6

• Help ongetResponseCode
  
• Cut UTF-8 BOM into a convenient format
  

Answer 2

var json = JSON.parse(respons.getContentText());

Logger.log(json.info.fields[0][2]); // Тип регистрации
Logger.log(json.info.items[0][2]);

Logger.log(json.info.fields[0][5]); // имя
Logger.log(json.info.items[0][5]); // елена

Answer 3

This is JSON. Remove extra text at the beginning and then parse the JSON with JSON.parse()