Search code examples
jsonobjectgoogle-apps-scriptgoogle-sheetsfetch

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


Solution

  • 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,"^|\|","|'"),"|"))