Question

Google Apps Script custom query

  • 7 June 2022
  • 1 reply
  • 133 views

Hello!

I’m new to the community, happy to be here!

 

I’m trying to develop a small program in Google Appscripts which would connect to looker’s API, get some info and run some actions. However, I am not an expert in Javascript and I have some doubts.

My goal is to get a look (which already has some filters in it), change a filter, submit the query and get the results back. I have already done this in Python by getting the query properties, modify the filters, set to None the client_id and id parameters and resubmit them to “create query” and then “run the query”.

However, I am not capable of mimicking the same behaviour in Javascript.

Right now, I am calling the “query” object from the GET /looks/look_id and modifying the filter, client_id and id of it. Then, I’m trying to POST this new query to the /queries endpoint, but it says the JSON is incorrect.

This is the code:

var response = UrlFetchApp.fetch(BASE_URL  + "/looks/" + model_name, options);

var qq = JSON.parse(response).query;
qq.filters['real_time_int.warehouse'] = 'MY WH';

qq.id = null;

qq.client_id = null;

// Until here it works

createQuery(token, qq);


function createQuery(token, query){

  var options = {

            "method": "post",

            "headers": {

                "Authorization": "token " + token

            },

            query           

        };

  var response = UrlFetchApp.fetch(BASE_URL  + "/queries", options); //either json or csv

  Logger.log(response);

}

Hope if anybody has had experience with appscript. Many thanks in beforehand!

Regards,

 

Aitor


This topic has been closed for comments

1 reply

Hi!

 

Seems I solved it, I was setting the payload wrong and I was missing the payload keyword and then JSON.stringify.

 

function createQuery(token, query){
var options = {
"method": "post",
"headers": {
"Authorization": "token " + token
},
"payload": JSON.stringify(query)
};
var authentication = {
"method": "get",
"headers": {
"Authorization": "token " + token
}
};
var response = UrlFetchApp.fetch(BASE_URL + "/queries", options); //either json or csv
Logger.log(JSON.parse(response).id);
var response = UrlFetchApp.fetch(BASE_URL + "/queries/" + JSON.parse(response).id + '/run/json', authentication); //either json or csv
//var resp = (JSON.parse(response.getContentText("UTF-8")));
Logger.log(Object.keys(JSON.parse(response)));
}

Thanks for everything!

Regards,

 

Aitor