Using Data from API on Google Sheets

​

Overview

• Connect to Sectors Financial API using Extensions (API Connector).
• Connect to Sectors Financial API on Google Sheets’ Apps Script.
• Schedule API runs.

​

Prerequisite

• Valid API keys from Sectors Financial API, which you can acquire from your Sectors App account
• Google Sheets

​

API Connector

• Now, to start, click API Connector > Open.
• A sidebar will open on the right side of your worksheets, and you can click on Create request to establish our very first connection to the API. Similar with how we created a connection in Excel, all we need to do is change the Request URL and add the headers with Authorization and value of your <API Key>. For the first trial, we are going to use https://api.sectors.app/v1/daily/bbca and track the daily trading data of BBCA.JK. Feel free to change the ticker with your personal watchlist!
• Next, if you scroll down a bit, there is this section OUTPUT SETTINGS where you can setup a few things. First, you can set which sheet and cell you would like to load the table to. For this recipe, I will just use the Set current option and it will load the data to the current active sheet and cell A1. There are a few handy options that you can explore yourself which you can refer to their official documentation. For example, on Output mode selection, since this request is going to be the very first, we can just use overwrite. If you’re looking to populate more data of another tickers, then you can create a separate request and uses the option append to automatically add on the first empty rows on the destined place. Another tip is when you use append, make sure that you check the Remove header row option.
• Finally, after you have configured everything, you are ready to establish your connection. Optionally you can also save this request and add a name to it, so that you can revisit this request in case there are changes in the configuration that you would like to make. Click Run! Bam! Your data is successfully loaded.
• Using Excel, handling nested JSON is quite complex since it requires you to expand the values one by one as needed. In contrast, API Connector is able to automatically expand your nested JSON objects and explode them to new columns per values. Try creating a new request to https://api.sectors.app/v1/company/report/bbca/!
• FInal tips: If you want to refresh all of your requests, you can go to the Extensions tab > API Connector > Refresh All Now. Additionally, you can also schedule the requests either hourly or daily, but this feature is currently only available for paid subscription, and is beyond the scope of this recipe.

​

Apps Script

• To start creating your custom script, click on Extensions > Apps Script. This will redirect you to the Apps Script code editor, and by default they provide you with Code.gs file.
• Replace the code in the script editor with this importJSON open source code. As specified in their code, importJSON includes these main fuctions:

  ImportJSON: For use by end users to import a JSON feed from a URL

  ImportJSONFromSheet: For use by end users to import JSON from one of the Sheets

  ImportJSONViaPost: For use by end users to import a JSON feed from a URL using POST parameters

  ImportJSONAdvanced: For use by script developers to easily extend the functionality of this library

  ImportJSONBasicAuth: For use by end users to import a JSON feed from a URL with HTTP Basic Auth (added by Karsten Lettow)

• On the top of the editor page, you can now create your own functions that extend the provided functions. I am gonna provide you with some examples:
  Copy

  function ImportDailyData(ticker, query, parseOptions) {
      var header = {headers: {Authorization: <API key>}}
      var url = "https://api.sectors.app/v1/daily/" + ticker;
      var jsondata = UrlFetchApp.fetch(url, header);
      var object   = JSON.parse(jsondata.getContentText());
  
      return parseJSONObject_(object, query, parseOptions, includeXPath_, defaultTransform_);
  }
  
  function ImportCompanyReport(ticker, section, query, parseOptions) {
      var header = {headers: {Authorization: <API key>}}
      var url = "https://api.sectors.app/v1/company/report/" + ticker;
      var jsondata = UrlFetchApp.fetch(url, header);
      var object   = JSON.parse(jsondata.getContentText());
  
      object = object[section]
  
      return parseJSONObject_(object, query, parseOptions, includeXPath_, defaultTransform_);
  };
  

  Save the script by clicking on the disk icon or by pressing Ctrl + S.
• You can now use the custom functions in your sheet. In a cell where you want to display the data, enter the following formula:
  Copy

  =ImportDailyData('bbca')
  

  
  Copy

  =ImportCompanyReport('bbca', 'peers')
  

  You can change the parameters to any tickers and/or section that you would like. Additionally, you can create more functions which handle fetch requests to all Sectors Financial API available endpoints.

​

Schedule API runs using Triggers

• Create the wrapper functions:
  Copy

  function RunDailyDataBBCA() {
      ImportDailyData('bbca')
  }
  
  function RunCompanyReportBBCAPeers() {
      ImportCompanyReport('bbca', 'peers')
  }
  

• In the Apps Script editor, you then click on the clock icon (Triggers) located in the left sidebar. Click on + Add Trigger. Choose the wrapper function (e.g., RunDailyDataBBCA) from the function dropdown, and choose Time-driven from the event source dropdown. Select the type of time-based trigger you want (in this case: daily):

  Specific date and time

  Minutes timer: Runs every X minutes.

  Hour timer: Runs every X hours.

  Day timer: Runs once a day at a specific time.

  Week timer: Runs once a week on a specific day and time.

  Month timer: Runs once a month on a specific day and time.

  Then, specify the time of the day you would like the trigger to run at.
• You can then monitor the executions of this scheduled functions too.