Adding a Custom Integration with Torii – Part 2: Using Google Sheets

In the second part of this blog series on creating a custom integration with Torii, we will walk through using Google Sheets as a source for creating and syncing users to an application in Torii.

In the first part of the blog series, we demonstrated the basics using Postman. I recommend you read Adding a Custom Integration with Torii – Part 1: Using Postman.

In Google Sheets, I have created a spreadsheet that has some basic attributes:

  • ID
  • First Name
  • Last Name
  • Email
  • Role
  • Status
  • Last Used Date
  • License Type

In this example, I’m not actually using the ID field. I’ve also decided to hard code the license to “Workspace ONE Enterprise” however you’ll be able to modify your script to support multiple licenses types.

As per the previous blog, you will need a Torii API Key and an Application Account ID.

In this blog, I’m going to be using App Script which is included in Google Workspace.

Inside your spreadsheet, launch App Script by going to Extensions -> App Script

Here is my code that I’m using to sync users from the Google Spreadsheet to Torii.

let API_KEY; 
let APP_ID; 

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('Torii')
      .addItem('Sync now','syncNow')
      .addItem('Reconfigure','readConfig')
      .addToUi();
}

function syncNow(){
  let docProperties = PropertiesService.getDocumentProperties();
  API_KEY = docProperties.getProperty('api_key');
  APP_ID = docProperties.getProperty('app_id');


  if (!API_KEY || !APP_ID) {
    readConfig();
    docProperties = PropertiesService.getDocumentProperties();
    API_KEY = docProperties.getProperty('api_key');
    APP_ID = docProperties.getProperty('app_id');
  }

  let file = createJsonFile();
  let fileId = uploadFile(file);  
  syncIntegration(fileId);
}

function readConfig() {
  let ui = SpreadsheetApp.getUi();
  let result = ui.prompt("Please enter Torii API Key");
  API_KEY = result.getResponseText();

  result = ui.prompt("Please enter the APP ID");
  APP_ID = result.getResponseText();

  let docProperties = PropertiesService.getDocumentProperties();
  docProperties.setProperty('api_key', API_KEY);
  docProperties.setProperty('app_id', APP_ID);
}

function createJsonFile() {
  let = sheet = SpreadsheetApp.getActive().getSheetByName("Users");
  let data = sheet.getDataRange().getValues(); 
  Logger.log(SpreadsheetApp.getActiveSheet().getName());
  Logger.log(data);
  data.shift();

  let users = [];
  let licensedUsers = [];
  data.forEach(function (row) {
    let firstName = row[1];
    let lastName  = row[2];
    let email = row[3];
    let role = row[4];
    //Check For Null Role and use User as default
    if (!role){ role = "User"};
    let extstatus = row[5];
    let lastUsedDate = row[6];
    //Check For Null Date and use 1999-12-31 as default
    if (!lastUsedDate){ lastUsedDate = "1999-12-31"};
    let licenseType = row[7];


    users.push(
        {
            email: email,
            firstName: firstName,
            lastName: lastName,
            status: "active",
            externalStatus: extstatus,
            roles: [role],
            lastUsedDate: lastUsedDate
        }
    );
    if (licenseType == "Workspace ONE Enterprise"){
    licensedUsers.push(
        {
            email: email,
            licenseStatus: "active"
        }
    );
    }

  });

  let file = {
    users: users,
    licenses: [{
      name: "Workspace ONE Enterprise",
      pricePerUser: 20000,
      unassignedAmount: 10,
      users: licensedUsers
    }
    
    ]
  }
  Logger.log(file);

  return file;
}

function syncIntegration(fileId) {
  response = UrlFetchApp.fetch('https://api.toriihq.com/beta/services/sync/custom',
    {
      muteHttpExceptions: false,
      'method' : 'put',
      'contentType': 'application/json',
      'payload' : JSON.stringify({
          idFile: fileId,
          idAppAccount: APP_ID
      }),   
      headers: {
        'Authorization': `Bearer ${API_KEY}`
      }
    }
  );
  Logger.log(response);
}

function uploadFile(file) {
  let response = UrlFetchApp.fetch('https://api.toriihq.com/beta/files/url?name=my_file.json&type=customIntegrationData',
    {
      headers: { 'Authorization': `Bearer ${API_KEY}` }
    });

  let jsonResponse = JSON.parse(response);
  let s3url = jsonResponse.uploadFileParameters.url;
  let filePath = jsonResponse.uploadFileParameters.filePath;  
  Logger.log(s3url);
  Logger.log(filePath);

  response = UrlFetchApp.fetch(s3url,
    {
      'method' : 'put',
      'contentType': 'customIntegrationData',
      'payload' : JSON.stringify(file)
    }
  );
  
  // Get file id
  response = UrlFetchApp.fetch('https://api.toriihq.com/beta/files',
    {
      method: 'post',
      contentType: 'application/json',
      headers: { 'Authorization': `Bearer ${API_KEY}` },
      payload: JSON.stringify({
        path: filePath,
        type: 'customIntegrationData'
      }),
      headers: {
        'Authorization': `Bearer ${API_KEY}`
      }
    });

    jsonResponse = JSON.parse(response);
    let fileId = jsonResponse.id;
    Logger.log(fileId);
    return fileId;
};

Notes:

  • In the createJsonFile(), I’m reading each line and associating it to a users array.
    • The “status” column from the spreadsheet will map to the External Status in Torii. You will see this status in Torii in the users Tab.
    • I’ve hard coded the actual “status” to “Active”. Torii will only accept two values here: active and deleted.
    • In the licenses block, I’m looking for the license value to match to “Workspace ONE Enterprise”.

When you first run the script (Torii- Reconfigure) you will need to authorize App Script with your Google Account. The script will then prompt you for your Torii API Key and your Application Account ID. These values will then be stored in your document properties.

You can then perform the sync by clicking Torii -> Sync Now. If there are no issues with your code you should see a Finished Script message.

In my environment, I created an application account ID and associated it with Workspace ONE. In Torii, I will see:

In the Users Tab I will see:


One thought on “Adding a Custom Integration with Torii – Part 2: Using Google Sheets

Comments are closed.