How To Connect the External Dashboard API to Google Sheets

Overview

This guide describes how to connect an external Dashboard data URL to your Google Sheets which can be used as a Data source for other Dashboard Software such as Tableau, Google Data Studio and others. 

Pre-Conditions

• External Data URL - click here to learn how to setup. 
• API user/password - contact our support (support@dooblo.com)

Online Video

Direct Youtube link (for full screen): https://youtu.be/ji7iX62H0-I

Steps

1. Open Google Sheets and create a new Sheet (There are many online guides to learn how to use)
2. Navigate to the Tools Menu and choose 'Script Editor' 
   
3. A new tab will be opened in the browser with an empty 
   
4. Replace the code there with the following script and save
   

   function callDoobloAPI() {
    var headers = {
    "Authorization" : "Basic " + Utilities.base64Encode("<APIKey>/<USERNAME>" + ':' + "<PASSWORD>"),
    "Accept" : "text/csv" //this line is to convert JSON input to CSV
   };
     var params = {
       "method":"GET",
       "headers":headers
     };
   var response = UrlFetchApp.fetch("<API-DATA-URL>",params);
     Logger.log(response.getContentText());
   var csvData = Utilities.parseCsv(response);
   var sheet = SpreadsheetApp.getActiveSheet();
   sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
   }

   • Replace the following parameters in the code with your organization details:
     • APIKey -  Enter your API key
     • UserName - Your STG studio username
     • Password - Your STG Studio password
     • API-Data-URL - the External Tabulation Data URL that you have created. 
5. Creating Trigger - Create a trigger that will update/refresh the data when file is opened. 
   In the Script Editor - navigate to 'Edit' menu - and choose 'Current project's triggers' 
   
6. In the following window click on the link 'Click here to add one now' 
   
7. In the Run - choose the function we have just created and in the Events choose 'From Spreadsheet' along with 'On Open' and save.
   
8. Save your Trigger and the script and re-open the google sheet.

That's It !