1. SurveyToGo Support Center
  2. How Tos
  3. API & Integration

How To Connect the External Dashboard API to Google Sheets

Avatar
Customer Support Manager
Follow

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

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 !

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

4 comments
Sort by Date Votes
  • Avatar
    Encuestas Forteinsights

    hi. I did all this, but when i refresh my Google Sheet, i get a code 401 error. Why this happens?

    0
    Comment actions Permalink
  • Avatar
    acorthorn

    It also happened to me

    0
    Comment actions Permalink
  • Avatar
    Diego Leal

    I don't know if it's the same error that happened to me, but you can do a test remove the <> from the link, and save again.

    Att.

    Diego

    0
    Comment actions Permalink
  • Avatar
    Monjur Rashed

    Does this process work for Excel Online (Microsoft Office 365)? I could not generate any table, and the Excel options shown here (selecting script editor from Tools, Adding trigger from Script editor etc.) do not match either. Any updated literature on this?

    0
    Comment actions Permalink

Please sign in to leave a comment.

Articles in this section