Hey Guys,

I love the feature that allows redash query results to be displayed in Google Sheets via the IMPORTDATA function, however I can’t seem to get the data to update when the query re-runs. Any ideas or advice on the refreshing of data when using the API to import into Google Sheets?

There are several reasons data might not update:

  1. Google Sheets has its own caching mechanism and it updates the data based on its own schedule. I’m not sure if the interval is documented, you can check their documentation.
  2. You should make sure to use the results API call: /api/queries/<query id>/results.csv vs. an API call that mentions the query result ID.
  3. If this is a query with parameters, it might be another reason why this doesn’t get updates. Queries with parameters are not supported (at the moment) with this feature.

It happens quite often, I simply do: Ctrl+X and then Ctrl+V (cut. and then paste back). It goes through “loading…” and refresh.

I am using google apps script to get the data from redash and write it to sheets. It also has scheduler to set your refresh interval. I can share the script if you are interested :wink:

3 Likes

Hey,
Can you share the script ?
I am facing the same issue.

Hi Kartavya,

Sure! Feel free to use my script. It’s a bit messy, but it works! Hope it helps.
Note: I turned off the refresh part in this query, I use schedule refresh on Redash instead. This script will get the latest query result.

You need to use scheduler on google apps script to schedule run this script as well.

function getRedashData() {

   // data
   var start = new Date();
   var redash_query_id = "QUERY_ID" // redash query id
   var redash_format = "csv" // redash format
   var redash_api_key = "USER_API_KEY_OR_QUERY_API_KEY" // your user api key
   var sheet = "SHEET_NAME" // your sheet name

  // refresh data (turn on for get newest result if your query less than 30 minutes)
//   var status = RedashRefreshData(redash_query_id, redash_api_key);

   // get the target sheet to write the data
   var url_get_data = "https://app.redash.io/dekoruma/api/queries/" + redash_query_id + "/results." + redash_format + "?api_key=" + redash_api_key
   var jsonString = RedashGetData(url_get_data);
   var dataObjectAll = Utilities.parseCsv(jsonString)
   var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet);
   var results = [];
   var dataRows = dataObjectAll.map(function(row) {
     return Object.keys(dataObjectAll[0]).map(function(col) {
       return String(row[col]);
     });
   });
   results = results.concat(dataRows);
   var range = targetSheet.getRange(1, 1, dataObjectAll.length + 0, Object.keys(dataObjectAll[0]).length);
   targetSheet.clearContents();
   range.setValues(results);

   // redash get data
   function RedashGetData(url_get_data) {
       var options = {
             "method" : "get"
             }
       return UrlFetchApp.fetch(url_get_data, options).getContentText()
    }

   // redash refresh data
   function RedashRefreshData(redash_query_id, redash_api_key) {
       var headers = {
         "Authorization": "Key " + redash_api_key
       }
       var post_options = {
             "method" : "post",
             "headers": headers
       }
       var get_options = {
             "method" : "get",
             "headers": headers
       }
       var url_refresh = "https://app.redash.io/dekoruma/api/queries/" + redash_query_id + "/refresh"
       var status = UrlFetchApp.fetch(url_refresh, post_options).getContentText()
       var status_parsed = JSON.parse(status)
       var job = status_parsed.job
       var url_job_status = "https://app.redash.io/dekoruma/api/jobs/" + job['id']
       var job_status = job['status']
       var runningTime = 0;
       while (job_status !== 3 && runningTime < 300000) {
         var response = UrlFetchApp.fetch(url_job_status, get_options).getContentText()
         var now = new Date();
         var runningTime = now.getTime() - start.getTime();
         var status_parsed = JSON.parse(response)
         var job = status_parsed.job
         var job_status = job['status']
         Utilities.sleep(500)
       }
       return job
    }
}

Hi Rafialvin,

Thanks for taking out sometime & sharing the script here.
I tried using it and customised it as per my use case, but somehow it doesn’t work.
Can you please place more comments in the above script to make us understand better. Thanks

Hi, I get the following error while importing the data. Can anyone pls help look into it?

1 Like

It looks like an authentication error. Are you sure the permissions are set properly?

can you please send me the script

This user posted the script in the thread above.

Hi,
Did you get the solution? I’ve the same problem
@hiteshahuja292

Best,
FAMT

Hi Rafialvin
How can I turn on the refresh part in this script? Thank you!