Solved: Google Apps Script custom query - Page 2

aitorgastaminza · 06-07-2022 03:37 AM

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

aitorgastaminza

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

PS: better approach:

////////////////// GET LOGIN TOKEN
function loginLooker() {
    var post = {
        'method': 'post'
    };
    var query = encodeURI("client_id=" + CLIENT_ID + "&client_secret=" + CLIENT_SECRET);
    //Logger.log(BASE_URL + "/login?" + query);
    var response = UrlFetchApp.fetch(BASE_URL + "/login?" + query, post);
    //Logger.log(response.getAllHeaders()   );
    //Logger.log(JSON.parse(response.getContentText("UTF-8")).access_token   );
    return JSON.parse(response.getContentText()).access_token;
}

////////////////// LOG OUT OF LOOKER FOR THE CURRENT TOKEN
function logOut(tk) {
  var options = {
            "method": "delete",
            "headers": {
                "Authorization": "token " + tk
            }
        };
        // just a list of explore names on the model here
        var response = UrlFetchApp.fetch(BASE_URL  + "/logout" , options);
        Logger.log('Logut success!');
}
//////////////// GET THE QUERY OF A EXPLORE
function getExploreQuery(token,model_name){
  var options = {
            "method": "get",
            "headers": {
                "Authorization": "token " + token
            }
        };
        // just a list of explore names on the model here
  var response = UrlFetchApp.fetch(BASE_URL  + "/looks/" + model_name, options); //either json or csv
  var qq = JSON.parse(response).query;
  return qq;
}
/////////////////// CREATE A FILTERED QUERY
function createFilteredQuery(token, query, filters){
  for (const [key, value] of Object.entries(filters)) {
    query.filters[key] = value;
    Logger.log('query.filters[' + key + '] = ' + value);
  }
  query.id = null;
  query.client_id = null;
  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
  return response;
}

/////////////// GET THE JSON OF A EXPLORE
function getExplore(token, model_name){
  var options = {
            "method": "get",
            "headers": {
                "Authorization": "token " + token
            }
        };
  var response = UrlFetchApp.fetch(BASE_URL  + "/looks/" + model_name + '/run/json', options); //either json or csv
  return JSON.parse(response.getContentText("UTF-8"));
}

View solution in original post