Report Maker

Web reports

Google Looker Studio

A SISfm Google Looker Studio web report requires:

A Report defined in Google Looker Studio.
A Connector defined in Google Apps Script to fetch data from SISfm, which the Report references. See example Apps Script files below.

The Connector must be able to access the SISfm database API, so the SISfm instance must be publicly accessible.

IFM maintains a suite of Google Looker Studio reports, as used by its SISfm Explorer website, which any public SISfm installation with the required database views can use, but customers can also use their own, which IFM can develop upon request.

IFM recommends the use of the Google Apps Script connector it has developed, but customers can also use their own if necessary, which IFM can develop upon request. Google Workspace includes a section about Apps Script development.

SISfm web report definition files for Google Looker Studio require a report URL of the form:

https://lookerstudio.google.com/embed/reporting/GUID?params=PARAMS

where:

GUID is the Google Looker Studio report ID (a Globally Unique Identifier)
PARAMS is the following set of parameters in JSON format:
- ds0.idpurl - the IdP (Identity Provider) Authorisation Server URL
- ds0.idpclientid - the IdP Client ID
- ds0.idpclientsecret - the IdP Client Secret
- ds0.apiurl - the SISfm database API URL
- ds0.viewname - the SISfm database table or view
- ds0.filter - the SISfm report filter, a list (with SISfm report parameter placeholders) of:
  - report parameter 1 filter as:
    1. the filter field name in the view
    2. the filter operator
    3. the filter value
  - etc.

The ds0 prefix is a dataset identifier, since Looker Studio supports multiple datasets, and multiple connectors.

Here is an example of a SISfm web report definition file for Looker Studio:

Sanitary Types Report
https://lookerstudio.google.com/embed/reporting/9d746124-9c88-4b67-ad1b-d6ea6aece6d3?params={"ds0.idpurl":"https://dev-10899144.okta.com/oauth2/default/v1/token","ds0.idpclientid":"0oa5q70oeuxZT1IJZ5d7","ds0.idpclientsecret":"DkBpwqsPB4uJ_uO7R-g_EMZUgdzWgcHMSvbkrNeq","ds0.apiurl":"https://afm.integratedfm.com.au/SISfm-Explorer/api/data","ds0.viewname":"sisfm_report_sanitary_types","ds0.filter":"[[\\\"site_id\\\",\\\"=\\\",\\\"$$1$$\\\"],[\\\"bl_name\\\",\\\"=\\\",\\\"$$2$$\\\"],[\\\"fl_id\\\",\\\"$$4$$\\\",\\\"$$3$$\\\"],[\\\"rm_cat\\\",\\\"=\\\",\\\"$$5$$\\\"],[\\\"rm_type\\\",\\\"=\\\",\\\"$$6$$\\\"]]"}
Site ($$site$$)
Building ($$bl,DESC$$)
Floor Code
Floor Operator (=,<,>,<=,>=,<>,like,not like)
Room Category ($$rmcat$$)
Room Type ($$rmtype$$)

whose lines contain:

LINE	CONTENTS
1	Sanitary Types Report
2	https://lookerstudio.google.com/embed/reporting/9d746124-9c88-4b67-ad1b-d6ea6aece6d3?params= { "ds0.idpurl":"https://dev-10899144.okta.com/oauth2/default/v1/token", "ds0.idpclientid":"0oa5q70oeuxZT1IJZ5d7", "ds0.idpclientsecret":"******", "ds0.apiurl":"$$APIURL$$", "ds0.viewname":"sisfm_report_sanitary_types", "ds0.filter":" [ [\\\"site_id\\\",\\\"=\\\",\\\"$$1$$\\\"], [\\\"bl_name\\\",\\\"=\\\",\\\"$$2$$\\\"], [\\\"fl_id\\\",\\\"$$4$$\\\",\\\"$$3$$\\\"], [\\\"rm_cat\\\",\\\"=\\\",\\\"$$5$$\\\"], [\\\"rm_type\\\",\\\"=\\\",\\\"$$6$$\\\"] ]" }
3	Site ($$site$$)
4	Building ($$bl,DESC$$)
5	Floor Code
6	Floor Operator (=,<,>,<=,>=,<>,like,not like)
7	Room Category ($$rmcat$$)
8	Room Type ($$rmtype$$)

the resulting user interface being:

Sanitary Types Report

Site
Building
Floor Code
Floor Operator
Room Category
Room Type

Google Apps Script - Example Connector

`appsscript.json`

{
  "timeZone": "Australia/Brisbane",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "dataStudio": {
    "name": "IFM Looker Studio SISfm API Connector",
    "company": "IFM",
    "logoUrl": "https://integratedfm.com.au/wp-content/uploads/2023/02/IFM-Logo-F9F9F9-BG-295x225-V1.jpg",
    "addonUrl": "https://www.integratedfm.com.au",
    "supportUrl": "https://www.integratedfm.com.au",
    "description": "A Looker Studio connector that will hit a REST API and GET some JSON data"
  }
}

`Code.js`

// Initialize the Community Connector using the DataStudioApp service
let cc = DataStudioApp.createCommunityConnector();
let schema = [];

// Return the defined schema to Data Studio
function getSchema(request) {
    if (JSON.stringify(request) == '{}') {
      schema.push( {name: 'site_id', label: 'Site Id', dataType: 'STRING', semantics: {conceptType: 'DIMENSION'}} );
    } else {
      let cols = request.configParams["fields"];
      cols.forEach(function (col) {
        let schemarow = {
          name: col,
          label : col,
          dataType: 'STRING',
          semantics: {conceptType: 'DIMENSION'}
        }
        schema.push(schemarow);
      });
    }

    return { schema: schema };
};

// Define the configuration settings for the connector, including user input fields
function getConfig(request) {
    let config = cc.getConfig();

    config.newInfo()
        .setId('Instructions')
        .setText('Enter Site Id01.');

    return config.build();
}

function getData(request) {
    // Get the fields requested by Looker Studio
    let dataSchema = [];
    request.fields.forEach(function (field) {
        for (const element of schema) {
            if (element.name == field.name) {
                dataSchema.push(element);
                break;
            }
        }
    });

    let TOKEN_URL = 'https://dev-10899144.okta.com/oauth2/default/v1/token';
    let bodyparms = {
      client_id: '0oa5q70oeuxZT1IJZ5d7',
      client_secret: 'DkBpwqsPB4uJ_uO7R-g_EMZUgdzWgcHMSvbkrNeq',
      grant_type: 'client_credentials',
    };
    let tokenOptions = {
        method: 'POST',
        payload: bodyparms,
    };
    let tokenResponse = UrlFetchApp.fetch(TOKEN_URL, tokenOptions);
    let tokenParsedResponse = JSON.parse(tokenResponse);
    let accessToken = tokenParsedResponse.access_token;

    // Base API endpoint for fetching data
    let BASE_URL = request.configParams["apiurl"];

    // Construct the API URL based on user input
    let datasourceParam = request.configParams.datasource;
    let url = BASE_URL + '?table=sisfm_report_building&where=1=1';

    // Fetch and parse the API response
    let options = { headers: {
        'Authorization': 'Bearer ' + accessToken
        }
    };
    let response = UrlFetchApp.fetch(url, options);
    let parsedResponse = JSON.parse(response);

    let rows = [];

    // Map the API response to the schema for Data Studio
    parsedResponse.forEach(function(datasource) {
        let row = [];
        request.fields.forEach(function(field) {
            row.push(datasource[field.name]);
        });
        rows.push({ values: row });
    });

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

// Specify the authentication type for the connector
function getAuthType() {
    // This connector does not require authentication
    return { type: 'NONE' };
}

// Check if the current user has administrative privileges
function isAdminUser() {
    // For this example, all users are treated as admin users
    return true;
}