Formatting data strings in google sheet

Question

I am fetching data from an api to google sheet. all its getting is data string on a single cell. How can I format it into a table with headers. Part of the long string I am getting and the code are below.

String - {instantBuy=5679.95, pricechange=0, instantSell=5623.39, currency=AUD, 24hoursHigh=5848.86, market=5848.86, virtualCurrency=ETH, 24hoursLow=5848.86, sell=5848.86, volume=0.341947, buy=5848.86, pair=ETH-AUD} ...

Code:

function myQuotes() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var mainSheet = ss.getSheetByName('Sheet1');

var url = "https://www.zebapi.com/pro/v1/market/";
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);

mainSheet.getRange(4,2).setValue(data);

Thanks!

score 1 · Answer 1 · answered Dec 10 '21 at 22:11

I'm not very familiar with JavaScript, but you could try the search() method, which I believe returns the index at which the word is found. So for example, if you were trying to put data from "instantBuy", and put it in it's own row or column, you could do search("instantBuy"), get the string between that and the next index that search will return, search("pricechange")

Sorry if that was confusing!

score 1 · Accepted Answer · answered Dec 10 '21 at 22:29

Use Object.entries(), like this:

function myQuotes() {
  const url = "https://www.zebapi.com/pro/v1/market/";
  const response = UrlFetchApp.fetch(url);
  const json = response.getContentText();
  const data = transpose_(Object.entries(JSON.parse(json)));
  const targetRange = SpreadsheetApp.getActive().getRange('Sheet1!B4');
  targetRange.offset(0, 0, data.length, data[0].length).setValues(data);
}

function transpose_(a) {
  // @see https://stackoverflow.com/a/13241545/1536038
  return Object.keys(a[0]).map(c => a.map(r => r[c]));
}

You may also want to take a look at ImportJSON.

Formatting data strings in google sheet

2 Answers2