Using the SurveyToGo REST API to Retrieve Data to a Google Sheet

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.