Get the SCBAM fund nav to Google Sheet.

scbam-nav.gs

function getSCBAMNAV() {
  var url = 'https://';
  var options = {
      'method': 'get',
      'contentType': 'application/json'
  };
  var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm');

  var response = UrlFetchApp.fetch(url, options);
  var tableNAV = JSON.parse(response.getContentText());
  var sortedFundCodes = Object.keys(tableNAV).sort();
  var data = sortedFundCodes.map(function(fundcode) {
      return [fundcode, tableNAV[fundcode].navunit, tableNAV[fundcode].navdate];
  });
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SCBAMNAV');
  if (!sheet) {
      sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('SCBAMNAV');
      sheet.appendRow(['Fund Code', 'NAV', 'NAV Date']);
  }

  sheet.getRange('E1').setValue(today);
  var range = sheet.getRange('A2:D')
  range.clearContent();
  sheet.getRange(2, 1, data.length, 3).setValues(data);

  var cell = sheet.getRange('B2:B');
  cell.setNumberFormat('0.0000');

}

/**
* create custom menu
*/
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('SCBAM NAV')
      .addItem('Get NAV', 'getSCBAMNAV')
      .addToUi();
}