Google Apps Script custom query

Looker Q&A Looker
2

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"));
}
1 Like