Question 277464 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Convert content to text parsing JSON URL response

I’ve got a url where the result is shown as below

    "playerInfo": {
        "059gh": {
            "eligiblePos": "Flx3,G,Flx,Flx2,PG",
            "status": "FA"
        },
        "04mr6": {
            "eligiblePos": "Flx3,G,Flx,Flx2,PG",
            "status": "FA"
        },
        "059fz": {
            "eligiblePos": "Flx3,G,Flx,Flx2,PG",
            "status": "FA"
        },
        "059fw": {
            "eligiblePos": "Flx3,G,Flx,Flx2,PG",
            "status": "FA"
        },
        "033b7": {
            "eligiblePos": "Flx3,Flx,Flx2,C",
            "status": "FA"
        },
        ...

I’ve got a function that tries to retrieve the player id and the eligiblePos
For ex : 059gh // Flx3,G,Flx,Flx2,PG

function getPlayerData() {
  var DATA_SPREADSHEET_ID = "xxxx"
  var ss = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
  var PlayerDataSheet = ss.getSheetByName("PlayerData");
  PlayerDataSheet.clearContents();
  var url = 'https://www.fantrax.com/fxea/general/getLeagueInfo?leagueId=' + leagueID;
  var response = UrlFetchApp.fetch(url);
  var content = response.getContentText();
  var data = JSON.parse(content);
  var playerKeys = Object.keys(data.playerInfo);
  PlayerDataSheet.appendRow(playerKeys)
  ...

The problem I’ve got is the results for playerKeys in my spreadsheet, tab "PlayerData", are shown as numbers and I want to convert to text.

Indeed 033b7 gets converted to 33b7
or 03e75 to 3E+75

I tried to cast to text but with no success

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks, What I have done to solve the issue, is to create a list (in cell A1) with "|" as separator

      var listplayer = playerKeys.join("|");
  PlayerDataSheet.getRange("A1").setValue(listplayer);

    Then thanks to that page : split google sheet keeping the character, I split the result directly in another worksheet keeping the original text

    =TRANSPOSE(SPLIT(REGEXREPLACE(A1,"^||","|'"),"|"))

  2. 0

    Try:

    function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0")
  let data = {playerInfo: {"059gh": { "eligiblePos": "Flx3,G,Flx,Flx2,PG", "status": "FA" },"04mr6": { "eligiblePos": "Flx3,G,Flx,Flx2,PG", "status": "FA" },"059fz": { "eligiblePos": "Flx3,G,Flx,Flx2,PG", "status": "FA" },"059fw": { "eligiblePos": "Flx3,G,Flx,Flx2,PG",  "status": "FA"},"033b7": { "eligiblePos": "Flx3,Flx,Flx2,C","status": "FA"}}};
  var playerKeys = Object.keys(data.playerInfo);
  sh.getRange(sh.getLastRow() + 1, 1, 1, playerKeys.length).setValues([playerKeys]).setNumberFormat("0.###############")
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search