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
- Open Google Sheets and create a new Sheet (There are many online guides to learn how to use)
- Navigate to the Tools Menu and choose 'Script Editor'
- A new tab will be opened in the browser with an empty
- 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.
- Replace the following parameters in the code with your organization details:
- 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' - In the following window click on the link 'Click here to add one now'
- In the Run - choose the function we have just created and in the Events choose 'From Spreadsheet' along with 'On Open' and save.
- Save your Trigger and the script and re-open the google sheet.
That's It !
Comments
hi. I did all this, but when i refresh my Google Sheet, i get a code 401 error. Why this happens?
It also happened to me
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
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?
Please sign in to leave a comment.