You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Custom script for grab json data from web

Hi.


I need check currency exchange rate from particular bank service. I already successfully done it in google sheets but for some reasons I wish the Numbers, and have problem with this part. I put here part of javascript what done for me in google sheets, and ask you is it possible to make something here, or suggest something close to it what can easy initiate every time.


It's getting currency type and date and returning me rate for one working day before that date:


function rateNBP(currency,cellDate,boolDataStamp) {
  var price
  let date
  for(let i = 1; i < 6; i++){
    date = createDate(cellDate,i)
    try{
      let resp = UrlFetchApp.fetch(createLinkNBP(currency,date))
      let content = resp.getContentText();
      let jsonn = JSON.parse(content);
      price = jsonn["rates"][0]['mid'];
      break
    }
    catch(e){
      continue
    }
  }
  if(boolDataStamp){
    price = price + " (" + date + ")"
  }
  return price
}
function createDate(date,howMuchTakeAway){
  let result = new Date(date.getTime()-howMuchTakeAway*(24*3600*1000))
  result = Utilities.formatDate(result,SpreadsheetApp.getActive().getSpreadsheetTimeZone(),"yyyy-MM-dd")
  return result
}

function createLinkNBP(currency,date){
  let baseOfString = 'http://api.nbp.pl/api/exchangerates/rates/'
  let separator = '/'
  let tableType = 'a'
  let format = '?format=json'

  let returnString = baseOfString + tableType + separator + currency + separator + date + separator + format

  return returnString
}
}



MacBook Air 13″

Posted on Sep 27, 2024 4:57 AM

Reply
1 reply

Sep 27, 2024 9:17 AM in response to AndreiPrystupchyk

It isn't clear what is/isn't working for you.


Are you saying the script runs? but you don't know how to set the cell in Numbers to the result?

Or does the script not run? what error message does it throw?


How are you running the script?


Any reason you're using this JavaScript approach rather than the built-in CURRENCYH() command which seems to do what you're asking.

Custom script for grab json data from web

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.