Gabe
1
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?
arikfr
2
There are several reasons data might not update:
/api/queries/<query id>/results.csv vs. an API call that mentions the query result ID.
elimash
3
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 
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
