Google Data Studio - No errors and no data

Question

I'm creating a very basic Data Connector to open up changing JSON within Google Data Studio. I've been debugging this code for some time, and I cannot work out why it's not showing inside Data Studio.

Here's the current broken down idea:

• getSchema() - return columns of unknown JSON structure
• getFlattened() - return flattened JSON structure, i.e "parent.child"
• getData() - return fresh data based on previous schema structure detected

Other considerations include detecting value types when building the schema, and date formatting.

Please note that this isn't the smartest code, and I'm not looking for code optimisations.

Code:

function getAuthType() {
    var response = { type: 'NONE' };
    return response;
}

function getConfig(request) {
    var cc = DataStudioApp.createCommunityConnector();
    var config = cc.getConfig();

    config.newInfo()
    .setId('instructions')
    .setText('Enter JSON Url');

    config.newTextInput()
    .setId('json_url')
    .setName('Enter JSON Url')
    .setPlaceholder('https://example.com/2347ygsdegf.json');

    return config.build();
}

function getFields(request) {
    var cc = DataStudioApp.createCommunityConnector();
    var fields = cc.getFields();
    var types = cc.FieldType;
    var aggregations = cc.AggregationType;

    var response = UrlFetchApp.fetch(request.configParams.json_url);
    var parsedResponse = JSON.parse(response);

    var flattened = getFlattened(parsedResponse);

    var fieldsFound = [];

    for (var i =0; i < flattened.length; i++){
        for (entry in flattened[i]){

            if (!fieldsFound.indexOf(entry) == -1){
                continue;
            }
            else {
                fieldsFound.push(entry);
            }

            var value = flattened[i][entry];

            if (isDate(value)){
                fields.newDimension()
                .setId(entry)
                .setName(entry)
                .setType(types.YEAR_MONTH_DAY);
            }
            else if (typeof value == "number"){
                fields.newMetric()
                .setId(entry)
                .setName(entry)
                .setType(types.NUMBER);
            }
            else if (typeof value == "boolean") {
                fields.newMetric()
                .setId(entry)
                .setName(entry)
                .setType(types.BOOLEAN);
            }
            else {


                fields.newDimension()
                .setId(entry)
                .setName(entry)
                .setType(types.TEXT);  
            }
        }
    }

    return fields;
}

function isDate(value) {
    var dateFormat;
    if (toString.call(value) === '[object Date]') {
        return true;
    }
    if (value == false || value == null || value === true || (typeof value == "string" && !value.length) || typeof value != "string"){
        return false;
    }
    value.replace(/^\s+|\s+$/gm, '');
    dateFormat = /(^\d{1,4}[\.|\\/|-]\d{1,2}[\.|\\/|-]\d{1,4})(\s*(?:0?[1-9]:[0-5]|1(?=[012])\d:[0-5])\d\s*[ap]m)?$/;
    return dateFormat.test(value);
}

function getFlattened(parsedResponse){
    //flatten result, get fields

    var flattened = [];

    //https://stackoverflow.com/a/19101235/5865284 -- amended
    var result = {};
    function recurse (cur, prop) {
        if (Object(cur) !== cur) {
            result[prop] = cur;
        } else if (Array.isArray(cur)) {
            for(var i=0, l=cur.length; i<l; i++)
                recurse(cur[i], prop);
            if (l == 0)
                result[prop] = [];
        } else {
            var isEmpty = true;
            for (var p in cur) {
                isEmpty = false;
                recurse(cur[p], prop ? prop+"."+p : p);
            }
            if (isEmpty && prop)
                result[prop] = {};
        }
    }

    for (i in parsedResponse){
        result = {};
        recurse(parsedResponse[i], "");
        flattened.push(result);
    }

  return flattened;
}

function isAdminUser(){ return true }

function getSchema(request) {
    return { schema: getFields(request).build() }
}

function getData(request){


    var response = UrlFetchApp.fetch(request.configParams.json_url);
    var parsedResponse = JSON.parse(response);

    var flattened = getFlattened(parsedResponse);

    var schema = getFields(request).build();

    var rows = [];
    var fieldsFound = [];

    for (var i =0; i < flattened.length; i++){

        var row = {
            values: []
        };

        var rowEntries = [];

        for (key in schema){
            var value = flattened[i][schema[key]["name"]];

            switch (typeof value){
                case "number":
                row.values.push(value.toString());
                break;
                case "boolean":
                row.values.push(value ? true:false);
                break;
                default:

                if (isDate(value)){
                    row.values.push(value.replace(/-/g, ''));
                }
                else if (value == null){
                    row.values.push(null);
                }
                else {
                    row.values.push(""+value);
                }
                break;
            }
        }

        rows.push(row);
    }

    var result = {
        schema: schema,
        rows: rows,
        cachedData: false,
    };

    console.log(JSON.stringify(result));
    // reports as expected - matching https://developers.google.com/datastudio/connector/reference#getdata

    return result;
}

Result:

Debugging: (withdrawn) - an image of the console.log() command in getData() - all rows and schemas match up correctly to https://developers.google.com/datastudio/connector/reference#getdata

Any ideas? Thank-you.

Answer 1

When use UrlFetchApp.fetch with JSON response needs to call getContentText before parse:

var json = resp.getContentText();
var data = JSON.parse(json);

Or use JSON parse with reviver:

JSON.parse(resp,function(k,v) { if (""===k) return v;});

https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app