DataStudio returns random error id when using custom connector

Question

I am using script.google.com to create a custom connector that can read CSV data from drive.google.com and send the data to Googles data studio.

When running the connector and inserting a simple table inside the data studio, I receive a simple that the request could not be processed because of an server error. The error id is changing every time I "re-publish" the script.

This is

function getData(request) {

  var dataSchema = [];

  request.fields.forEach(function(field) {
    for (var i = 0; i < csvDataSchema.length; i++) {
      if (csvDataSchema[i].name === field.name) {
        dataSchema.push(csvDataSchema[i]);
        break;
      }
    }
  });

  csvFile = UrlFetchApp.fetch("https://drive.google.com/uc?export=download&id=" + request.configParams.documentId);
  var csvData = Utilities.parseCsv(csvFile);

  var data = [];

  csvData.forEach(function(row) {
    data.push({
      values: row
    });
  });


  console.log(  {
    schema: dataSchema,
    rows: data
  } );

  return {
    schema: dataSchema,
    rows: data
  };
};

This is the csvDataSchema:

var csvDataSchema = [
  {
    name: 'date',
    label: 'Date',
    dataType: 'STRING',
    semantics: {
      conceptType: 'DIMENSION'
    }
  },
  {
    name: 'nanoseconds',
    label: 'nanoseconds',
    dataType: 'NUMBER',
    semantics: {
      "isReaggregatable": true,
      conceptType: 'METRIC'
    }
  },{
    name: 'size',
    label: 'Size of Testfile in MByte',
    dataType: 'STRING',
    semantics: {
      "isReaggregatable": false,
      conceptType: 'DIMENSION'
  }

  }
];

And this is the result of the getData function, stringified:

{"schema":[{"name":"date","label":"Date","dataType":"STRING","semantics":{"conceptType":"DIMENSION"}},{"name":"size","label":"Size of Testfile in MByte","dataType":"STRING","semantics":{"isReaggregatable":false,"conceptType":"DIMENSION"}}],"rows":[{"values":["2017-05-23",123,"1"]},{"values":["2017-05-23",123,"1"]}]}

It perfectly fits to the reference. I am providing more information, but following the tutorial it should work, anyways.

Those are the fields provided in request:

And this is what getDate returns:

So, what I am wondering first is: Why is there a random error id? And what could be wrong with my script?

Answer 1

@Minhaz Kazi gave the missing hint:

As I did not "dynamically" filled the response object in getData, I always returned all three columns.

With my code above the only thing I had to do is adding the third column as a dimension or a metric.

So I changed my code to dynamically return the columns so it will fit to the response. For this I had to implement an function that will transform the CSV-data into an object.

This is the getData() function now:

  function getData(request) {

  var url = "https://drive.google.com/uc?export=download&id="
  + request.configParams.documentId;

  var csvFile = UrlFetchApp.fetch(url);

  var csvData = Utilities.parseCsv(csvFile);

  var sourceData = csvToObject(csvData);

  var data = [];

  sourceData.forEach(function(row) {
    var values = [];
    dataSchema.forEach(function(field) {
      switch(field.name) {
        case 'date':
          values.push(row.date);
          break;
        case 'nanoseconds':
          values.push(row.nanoseconds);
          break;
        case 'size':
          values.push(row.size);
          break;
        default:
          values.push('');
      }
    });
    data.push({
      values: values
    });
  });

  return {
    schema: dataSchema,
    rows: data
  };

};}

And this is the function to convert the CSV data to an object:

 function csvToObject(array) {
    var headers = array[0];

  var jsonData = [];
    for ( var i = 1, length = array.length; i < length; i++ )
    {
      var row = array[i];
      var data = {};
      for ( var x = 0; x < row.length; x++ )
      {
        data[headers[x]] = row[x];
      }
      jsonData.push(data);

    }

    return jsonData;
  }

(it's based on a so-solution from here, I modified it to fit my source CSV data)

Answer 2

You should only return fields/columns included in request. Currently, data contains all fields that are in csvFile. Depending on your chart element in your dashboard, request will most likely contain only a subset of your full schema. See example implementation at the Data Studio Open Source repo.

If this does not solve the problem, you should setup error handing and check if the error is occurring at any specific line.