Role based embedded reports in data studio

Published On: 23 April 2020.By prince yadav.

General

Role based embedded reports in data studio

Multiple times we need to build reports to present our data. The Google Data Studio provides a powerful tool to build your own reports with less time and without any coding knowledge.

Google Studio depends on Data Source. The data source is a connection or method, which can be used to connect to our database.

There are multiple types of connectors available on the google data source and we can also build our own connector.

Inbuilt data source provider https://datastudio.google.com/u/0/datasources/create. Here we can use data sources, according to our requirements.

First, we are using Mysql connector:

Step 1: First, add details to our database. Add Click on Authentication. If all details are correct, it will be connected to our database.

Step 2: There are 2 sections. a) You can select a table that will report the data needed. b) You can write own query. Then click on the connect button.

Step 3: According to the query, the column will show there. You can create an explorer report.

https://drive.google.com/file/d/17RvzBabIrOdT5CX8yZwdlFAWYbsxxify/view?usp=sharing

These connectors have a problem. Suppose we have a database of the HR Department. So the Administrator can view all departments’ data, but the Head of a department allows the only view of his department data.

In that case, we need to create a custom data source provider.

Step 1: We need to write a custom data source to https://script.google.com/. First, edit the Application.json. Here we have to update the manifest JSON and Code.gs script.

Application.json

{
  "timeZone": "GMT",
  "dependencies": {
  },
  "runtimeVersion": "V8",
  "dataStudio": {
    "name": "Custom JSON Connect",
    "logoUrl": "https://www.aurigait.com/resources/themes/ait-child/assets/ait/images/auriga_logo_white.svg",
    "company": "Auriga It",
    "companyUrl": "https://aurigait.com/",
    "addonUrl": "https://aurigait.com/",
    "supportUrl": "https://aurigait.com/",
    "description": "Free JSON connector. Fetch data from any JSON data source by URL. Use caching to speed up rendering.",
    "sources": ["CUSTOM_JSON"],
    "shortDescription": "Connect to JSON data by URL",
    "authType": ["NONE"],
    "feeType": ["FREE"]
  }
}

{

"timeZone": "GMT",

"dependencies": {

"runtimeVersion": "V8",

"dataStudio": {

"name": "Custom JSON Connect",

"logoUrl": "https://www.aurigait.com/resources/themes/ait-child/assets/ait/images/auriga_logo_white.svg",

"company": "Auriga It",

"companyUrl": "https://aurigait.com/",

"addonUrl": "https://aurigait.com/",

"supportUrl": "https://aurigait.com/",

"description": "Free JSON connector. Fetch data from any JSON data source by URL. Use caching to speed up rendering.",

"sources": ["CUSTOM_JSON"],

"shortDescription": "Connect to JSON data by URL",

"authType": ["NONE"],

"feeType": ["FREE"]

}

Code.gs

/**
 * Throws and logs script exceptions.
 *
 * @param {String} message The exception message
 */
function sendUserError(message) {
  var cc = DataStudioApp.createCommunityConnector();
  cc.newUserError()
    .setText(message)
    .throwException();
}

/**
 * function  `getAuthType()`
 *
 * @returns {Object} `AuthType` used by the connector.
 */
function getAuthType() {
  return {type: 'NONE'};
}

/**
 * function  `isAdminUser()`
 *
 * @returns {Boolean} Currently just returns false. Should return true if the current authenticated user at the time
 *                    of function execution is an admin user of the connector.
 */
function isAdminUser() {
  return false;
}

/**
 * Returns the user configurable options for the connector.
 *
 * Required function for Community Connector.
 *
 * @param   {Object} request  Config request parameters.
 * @returns {Object}          Connector configuration to be displayed to the user.
 */
function getConfig(request) {
  var cc = DataStudioApp.createCommunityConnector();
  var config = cc.getConfig();

  var option1 = config
    .newOptionBuilder()
    .setLabel('Text')
    .setValue('text');

  var option2 = config
    .newOptionBuilder()
    .setLabel('Inline')
    .setValue('inline');

  config
    .newInfo()
    .setId('instructions')
    .setText('Fill out the form to connect to a JSON data source.');

  config
    .newTextInput()
    .setId('url')
    .setName('Enter the URL of a JSON data source')
    .setHelpText('e.g. https://my-url.org/json')
    .setPlaceholder('https://my-url.org/json');
 
  config
      .newTextInput()
      .setId("reportid")
      .setName("reportid")
      .setAllowOverride(true);
  
  config
      .newTextInput()
      .setId("department")
      .setName("department")
      .setAllowOverride(true);
  
  config.setDateRangeRequired(false);

  return config.build();
}

/**
 * Gets UrlFetch response and parses JSON
 *
 * @param   {string} url  The URL to get the data from
 * @returns {Object}      The response object
 */
function fetchJSON(url,request) {
  try {
    if(request.configParams.department){
      url = url + '?reportid=' + request.configParams.reportid + '&department=' + request.configParams.department;
    }
    else{
      url = url + '?reportid=' + request.configParams.reportid;  
    }
    var response = UrlFetchApp.fetch(url);
  } catch (e) {
    sendUserError('"' + url + '" returned an error:' + e);
  }

  try {
    var content = JSON.parse(response);
  } catch (e) {
    sendUserError('Invalid JSON format. ' + e);
  }

  return content;
}

/**
 * Fetches data. Either by calling getCachedData or fetchJSON, depending on the cache configuration parameter.
 *
 * @param   {String}  url   The URL to get the data from
 * @param   {Boolean} cache Parameter to determine whether the request should be cached
 * @returns {Object}        The response object
 */
function fetchData(url,request) {
  if (!url || !url.match(/^https?:\/\/.+$/g)) {
    sendUserError('"' + url + '" is not a valid url.');
  }
  try {
    var content = fetchJSON(url,request);
  } catch (e) {
    sendUserError(
      'Your request could not be cached. The rows of your dataset probably exceed the 100KB cache limit.'
    );
  }
  if (!content) sendUserError('"' + url + '" returned no content.');

  return content;
}

/**
 * Matches the field value to a semantic
 *
 * @param   {Mixed}   value   The field value
 * @param   {Object}  types   The list of types
 * @return  {string}          The semantic type
 */
function getSemanticType(value, types) {
  if (!isNaN(parseFloat(value)) && isFinite(value)) {
    return types.NUMBER;
  } else if (value === true || value === false) {
    return types.BOOLEAN;
  } else if (typeof value != 'object' && value != null) {
    if (
      value.match(
        new RegExp(
          /[-a-zA-Z0-9@:%_\+.~#?&//=]{2,256}\.[a-z]{2,4}\b(\/[-a-zA-Z0-9@:%_\+.~#?&//=]*)?/gi
        )
      )
    ) {
      return types.URL;
    } else if (!isNaN(Date.parse(value))) {
      return types.YEAR_MONTH_DAY_HOUR;
    }
  }
  return types.TEXT;
}

/**
 *  Creates the fields
 *
 * @param   {Object}  fields  The list of fields
 * @param   {Object}  types   The list of types
 * @param   {String}  key     The key value of the current element
 * @param   {Mixed}   value   The value of the current element
 */
function createField(fields, types, key, value) {
  var semanticType = getSemanticType(value, types);
  var field =
    semanticType == types.NUMBER ? fields.newMetric() : fields.newDimension();

  field.setType(semanticType);
  field.setId(key.replace(/\s/g, '_').toLowerCase());
  field.setName(key);
}

/**
 * Handles keys for recursive fields
 *
 * @param   {String}  currentKey  The key value of the current element
 * @param   {Mixed}   key         The key value of the parent element
 * @returns {String}  if true
 */
function getElementKey(key, currentKey) {
  if (currentKey == '' || currentKey == null) {
    return;
  }
  if (key != null) {
    return key + '.' + currentKey.replace('.', '_');
  }
  return currentKey.replace('.', '_');
}

/**
 * Extracts the objects recursive fields and adds it to fields
 *
 * @param   {Object}  fields  The list of fields
 * @param   {Object}  types   The list of types
 * @param   {String}  key     The key value of the current element
 * @param   {Mixed}   value   The value of the current element
 * @param   {boolean} isInline if true
 */
function createFields(fields, types, key, value, isInline) {
  if (typeof value === 'object' && !Array.isArray(value) && value !== null) {
    Object.keys(value).forEach(function(currentKey) {
      var elementKey = getElementKey(key, currentKey);

      if (isInline && value[currentKey] != null) {
        createFields(fields, types, elementKey, value[currentKey], isInline);
      } else {
        createField(fields, types, currentKey, value);
      }
    });
  } else if (key !== null) {
    createField(fields, types, key, value);
  }
}

/**
 * Parses first line of content to determine the data schema
 *
 * @param   {Object}  request getSchema/getData request parameter.
 * @param   {Object}  content The content object
 * @return  {Object}           An object with the connector configuration
 */
function getFields(request, content) {
  var cc = DataStudioApp.createCommunityConnector();
  var fields = cc.getFields();
  var types = cc.FieldType;
  var aggregations = cc.AggregationType;
  var isInline = request.configParams.nestedData === 'inline';

  if (!Array.isArray(content)) content = [content];

  if (typeof content[0] !== 'object' || content[0] === null) {
    sendUserError('Invalid JSON format');
  }
  try {
    createFields(fields, types, null, content[0], isInline);
  } catch (e) {
    sendUserError('Unable to identify the data format of one of your fields.');
  }
  return fields;
}

/**
 * Returns the schema for the given request.
 *
 * @param   {Object} request Schema request parameters.
 * @returns {Object} Schema for the given request.
 */
function getSchema(request) {
  var content = fetchData(request.configParams.url, request);
  var fields = getFields(request, content).build();
  return {schema: fields};
}

/**
 * Performs a deep merge of objects and returns new object. Does not modify
 * objects (immutable) and merges arrays via concatenation.
 * Thanks to jhildenbiddle https://stackoverflow.com/users/4903063/jhildenbiddle
 * https://stackoverflow.com/questions/27936772/how-to-deep-merge-instead-of-shallow-merge
 *
 * @param            Objects to merge
 * @returns {object} New object with merged key/values
 */
function mergeDeep() {
  var objects = Array.prototype.slice.call(arguments);

  return objects.reduce(function(prev, obj) {
    Object.keys(obj).forEach(function(key) {
      var pVal = prev[key];
      var oVal = obj[key];

      if (Array.isArray(pVal) && Array.isArray(oVal)) {
        prev[key] = pVal.concat.apply(pVal, toConsumableArray(oVal));
      } else if (pVal === Object(pVal) && oVal === Object(oVal)) {
        prev[key] = mergeDeep(pVal, oVal);
      } else {
        prev[key] = oVal;
      }
    });
    return prev;
  }, {});
}

/**
 *  Converts date strings to YYYYMMDDHH:mm:ss
 *
 * @param   {String} val  Date string
 * @returns {String}      Converted date string
 */
function convertDate(val) {
  var date = new Date(val);
  return (
    date.getUTCFullYear() +
    ('0' + (date.getUTCMonth() + 1)).slice(-2) +
    ('0' + date.getUTCDate()).slice(-2) +
    ('0' + date.getUTCHours()).slice(-2)
  );
}

/**
 * Validates the row values. Only numbers, boolean, date and strings are allowed
 *
 * @param   {Field} field The field declaration
 * @param   {Mixed} val   The value to validate
 * @returns {Mixed}       Either a string or number
 */
function validateValue(field, val) {
  if (field.getType() == 'YEAR_MONTH_DAY_HOUR') {
    val = convertDate(val);
  }

  switch (typeof val) {
    case 'string':
    case 'number':
    case 'boolean':
      return val;
    case 'object':
      return JSON.stringify(val);
  }
  return '';
}

/**
 * Returns the (nested) values for requested columns
 *
 * @param   {Object} valuePaths       Field name. If nested; field name and parent field name
 * @param   {Object} row              Current content row
 * @returns {Mixed}                   The field values for the columns
 */
function getColumnValue(valuePaths, row) {
  for (var index in valuePaths) {
    var currentPath = valuePaths[index];

    if (row[currentPath] === null) {
      return '';
    }

    if (row[currentPath] !== undefined) {
      row = row[currentPath];
      continue;
    }
    var keys = Object.keys(row);

    for (var index_keys in keys) {
      var key = keys[index_keys].replace(/\s/g, '_').toLowerCase();
      if (key == currentPath) {
        row = row[keys[index_keys]];
        break;
      }
    }
  }
  return row;
}

/**
 * Returns an object containing only the requested columns
 *
 * @param   {Object} content          The content object
 * @param   {Object} requestedFields  Fields requested in the getData request.
 * @returns {Object}                  An object only containing the requested columns.
 */
function getColumns(content, requestedFields) {
  if (!Array.isArray(content)) content = [content];

  return content.map(function(row) {
    var rowValues = [];

    requestedFields.asArray().forEach(function(field) {
      var valuePaths = field.getId().split('.');
      var fieldValue = row === null ? '' : getColumnValue(valuePaths, row);

      rowValues.push(validateValue(field, fieldValue));
    });
    return {values: rowValues};
  });
}

/**
 * Returns the tabular data for the given request.
 *
 * @param   {Object} request  Data request parameters.
 * @returns {Object}          Contains the schema and data for the given request.
 */
function getData(request) {
  var content = fetchData(request.configParams.url, request);
  var fields = getFields(request, content);
  var requestedFieldIds = request.fields.map(function(field) {
    return field.name;
  });
  var requestedFields = fields.forIds(requestedFieldIds);

  return {
    schema: requestedFields.build(),
    rows: getColumns(content, requestedFields)
  };
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

/**

* Throws and logs script exceptions.

* @param {String} message The exception message

function sendUserError(message) {

var cc = DataStudioApp.createCommunityConnector();

cc.newUserError()

.setText(message)

.throwException();

}

/**

* function `getAuthType()`

* @returns {Object} `AuthType` used by the connector.

function getAuthType() {

return {type: 'NONE'};

}

/**

* function `isAdminUser()`

* @returns {Boolean} Currently just returns false. Should return true if the current authenticated user at the time

* of function execution is an admin user of the connector.

function isAdminUser() {

return false;

}

/**

* Returns the user configurable options for the connector.

* Required function for Community Connector.

* @param {Object} request Config request parameters.

* @returns {Object} Connector configuration to be displayed to the user.

function getConfig(request) {

var cc = DataStudioApp.createCommunityConnector();

var config = cc.getConfig();

var option1 = config

.newOptionBuilder()

.setLabel('Text')

.setValue('text');

var option2 = config

.newOptionBuilder()

.setLabel('Inline')

.setValue('inline');

config

.newInfo()

.setId('instructions')

.setText('Fill out the form to connect to a JSON data source.');

config

.newTextInput()

.setId('url')

.setName('Enter the URL of a JSON data source')

.setHelpText('e.g. https://my-url.org/json')

.setPlaceholder('https://my-url.org/json');

config

.newTextInput()

.setId("reportid")

.setName("reportid")

.setAllowOverride(true);

config

.newTextInput()

.setId("department")

.setName("department")

.setAllowOverride(true);

config.setDateRangeRequired(false);

return config.build();

}

/**

* Gets UrlFetch response and parses JSON

* @param {string} url The URL to get the data from

* @returns {Object} The response object

function fetchJSON(url,request) {

try {

if(request.configParams.department){

url = url + '?reportid=' + request.configParams.reportid + '&department=' + request.configParams.department;

}

else{

url = url + '?reportid=' + request.configParams.reportid;

}

var response = UrlFetchApp.fetch(url);

} catch (e) {

sendUserError('"' + url + '" returned an error:' + e);

}

try {

var content = JSON.parse(response);

} catch (e) {

sendUserError('Invalid JSON format. ' + e);

}

return content;

}

/**

* Fetches data. Either by calling getCachedData or fetchJSON, depending on the cache configuration parameter.

* @param {String} url The URL to get the data from

* @param {Boolean} cache Parameter to determine whether the request should be cached

* @returns {Object} The response object

function fetchData(url,request) {

if (!url || !url.match(/^https?:\/\/.+$/g)) {

sendUserError('"' + url + '" is not a valid url.');

}

try {

var content = fetchJSON(url,request);

} catch (e) {

sendUserError(

'Your request could not be cached. The rows of your dataset probably exceed the 100KB cache limit.'

);

}

if (!content) sendUserError('"' + url + '" returned no content.');

return content;

}

/**

* Matches the field value to a semantic

* @param {Mixed} value The field value

* @param {Object} types The list of types

* @return {string} The semantic type

function getSemanticType(value, types) {

if (!isNaN(parseFloat(value)) && isFinite(value)) {

return types.NUMBER;

} else if (value === true || value === false) {

return types.BOOLEAN;

} else if (typeof value != 'object' && value != null) {

if (

value.match(

new RegExp(

/[-a-zA-Z0-9@:%_\+.~#?&//=]{2,256}\.[a-z]{2,4}\b(\/[-a-zA-Z0-9@:%_\+.~#?&//=]*)?/gi

)

) {

return types.URL;

} else if (!isNaN(Date.parse(value))) {

return types.YEAR_MONTH_DAY_HOUR;

}

return types.TEXT;

}

/**

* Creates the fields

* @param {Object} fields The list of fields

* @param {Object} types The list of types

* @param {String} key The key value of the current element

* @param {Mixed} value The value of the current element

function createField(fields, types, key, value) {

var semanticType = getSemanticType(value, types);

var field =

semanticType == types.NUMBER ? fields.newMetric() : fields.newDimension();

field.setType(semanticType);

field.setId(key.replace(/\s/g, '_').toLowerCase());

field.setName(key);

}

/**

* Handles keys for recursive fields

* @param {String} currentKey The key value of the current element

* @param {Mixed} key The key value of the parent element

* @returns {String} if true

function getElementKey(key, currentKey) {

if (currentKey == '' || currentKey == null) {

return;

}

if (key != null) {

return key + '.' + currentKey.replace('.', '_');

}

return currentKey.replace('.', '_');

}

/**

* Extracts the objects recursive fields and adds it to fields

* @param {Object} fields The list of fields

* @param {Object} types The list of types

* @param {String} key The key value of the current element

* @param {Mixed} value The value of the current element

* @param {boolean} isInline if true

function createFields(fields, types, key, value, isInline) {

if (typeof value === 'object' && !Array.isArray(value) && value !== null) {

Object.keys(value).forEach(function(currentKey) {

var elementKey = getElementKey(key, currentKey);

if (isInline && value[currentKey] != null) {

createFields(fields, types, elementKey, value[currentKey], isInline);

} else {

createField(fields, types, currentKey, value);

}

});

} else if (key !== null) {

createField(fields, types, key, value);

}

/**

* Parses first line of content to determine the data schema

* @param {Object} request getSchema/getData request parameter.

* @param {Object} content The content object

* @return {Object} An object with the connector configuration

function getFields(request, content) {

var cc = DataStudioApp.createCommunityConnector();

var fields = cc.getFields();

var types = cc.FieldType;

var aggregations = cc.AggregationType;

var isInline = request.configParams.nestedData === 'inline';

if (!Array.isArray(content)) content = [content];

if (typeof content[0] !== 'object' || content[0] === null) {

sendUserError('Invalid JSON format');

}

try {

createFields(fields, types, null, content[0], isInline);

} catch (e) {

sendUserError('Unable to identify the data format of one of your fields.');

}

return fields;

}

/**

* Returns the schema for the given request.

* @param {Object} request Schema request parameters.

* @returns {Object} Schema for the given request.

function getSchema(request) {

var content = fetchData(request.configParams.url, request);

var fields = getFields(request, content).build();

return {schema: fields};

}

/**

* Performs a deep merge of objects and returns new object. Does not modify

* objects (immutable) and merges arrays via concatenation.

* Thanks to jhildenbiddle https://stackoverflow.com/users/4903063/jhildenbiddle

* https://stackoverflow.com/questions/27936772/how-to-deep-merge-instead-of-shallow-merge

* @param Objects to merge

* @returns {object} New object with merged key/values

function mergeDeep() {

var objects = Array.prototype.slice.call(arguments);

return objects.reduce(function(prev, obj) {

Object.keys(obj).forEach(function(key) {

var pVal = prev[key];

var oVal = obj[key];

if (Array.isArray(pVal) && Array.isArray(oVal)) {

prev[key] = pVal.concat.apply(pVal, toConsumableArray(oVal));

} else if (pVal === Object(pVal) && oVal === Object(oVal)) {

prev[key] = mergeDeep(pVal, oVal);

} else {

prev[key] = oVal;

}

});

return prev;

}, {});

}

/**

* Converts date strings to YYYYMMDDHH:mm:ss

* @param {String} val Date string

* @returns {String} Converted date string

function convertDate(val) {

var date = new Date(val);

return (

date.getUTCFullYear() +

('0' + (date.getUTCMonth() + 1)).slice(-2) +

('0' + date.getUTCDate()).slice(-2) +

('0' + date.getUTCHours()).slice(-2)

);

}

/**

* Validates the row values. Only numbers, boolean, date and strings are allowed

* @param {Field} field The field declaration

* @param {Mixed} val The value to validate

* @returns {Mixed} Either a string or number

function validateValue(field, val) {

if (field.getType() == 'YEAR_MONTH_DAY_HOUR') {

val = convertDate(val);

}

switch (typeof val) {

case 'string':

case 'number':

case 'boolean':

return val;

case 'object':

return JSON.stringify(val);

}

return '';

}

/**

* Returns the (nested) values for requested columns

* @param {Object} valuePaths Field name. If nested; field name and parent field name

* @param {Object} row Current content row

* @returns {Mixed} The field values for the columns

function getColumnValue(valuePaths, row) {

for (var index in valuePaths) {

var currentPath = valuePaths[index];

if (row[currentPath] === null) {

return '';

}

if (row[currentPath] !== undefined) {

row = row[currentPath];

continue;

}

var keys = Object.keys(row);

for (var index_keys in keys) {

var key = keys[index_keys].replace(/\s/g, '_').toLowerCase();

if (key == currentPath) {

row = row[keys[index_keys]];

break;

}

return row;

}

/**

* Returns an object containing only the requested columns

* @param {Object} content The content object

* @param {Object} requestedFields Fields requested in the getData request.

* @returns {Object} An object only containing the requested columns.

function getColumns(content, requestedFields) {

if (!Array.isArray(content)) content = [content];

return content.map(function(row) {

var rowValues = [];

requestedFields.asArray().forEach(function(field) {

var valuePaths = field.getId().split('.');

var fieldValue = row === null ? '' : getColumnValue(valuePaths, row);

rowValues.push(validateValue(field, fieldValue));

});

return {values: rowValues};

});

}

/**

* Returns the tabular data for the given request.

* @param {Object} request Data request parameters.

* @returns {Object} Contains the schema and data for the given request.

function getData(request) {

var content = fetchData(request.configParams.url, request);

var fields = getFields(request, content);

var requestedFieldIds = request.fields.map(function(field) {

return field.name;

});

var requestedFields = fields.forIds(requestedFieldIds);

return {

schema: requestedFields.build(),

rows: getColumns(content, requestedFields)

};

}

For more information https://github.com/googledatastudio/community-connectors/tree/master/JSON-connect/src

Step 2: Deploy your code. After deployment, you get “Deployment id” and direct URL of your Data Source.

Step 3: Need to provide your source JSON URL and allow parameters to override by URL. Allow parameters for our need for data according to department wise and connect to the data source.

https://drive.google.com/file/d/1ys4RCPyWYC8wP31jWtCJcZpqt6SIKnMC/view?usp=sharing

So by using a custom connector, we can show data according to the role.

For publishing your Datasource script or otherwise, you can use less secure https://developers.google.com/datastudio/connector/pscc-requirements

Limits of embedding

Bookmark links can’t be used with embedded reports. If you need a customized view of the data, use editor filter properties and date ranges applied to the report.
Some websites may block the ability to embed their content.
Recursive embedding is not allowed: while you can embed a Data Studio report into itself, you won’t see the URL embed component in the embedded report.