Autodesk - Google Sheets Custom Integration

This is an example that uses Google Sheets and data from an Autodesk report sent through a custom integration

The Quickstart guide walked you through how to create an integration for a Hello World Custom Application in less than ten minutes. This guide will show you how to sync data downloaded from the Autodesk admin console about users and licenses into Torii.

Google Sheet template with AppScript code is provided at this link.

This guide in the Autodesk knowledge base provides more information on how the usage report can be downloaded.

Step 1 - Export the data from the Autodesk admin console

  1. This guide in the Autodesk knowledge base provides more information on how the usage report can be downloaded

Step 2 - Customize the Google Sheet example for your environment

  1. Make a copy of the provided google sheet so you gain full access.
  2. Import/merge the exported data from Step 1 where you downloaded an extract from the Autodesk admin console.
  3. Review the AppScript code that can be accessed from the Extensions --> AppScript top menu item.
  1. The Google AppScript code to process the Autodesk data is provided below. This code includes requests to calls you can test in the API Explorer with the Custom Integration Quickstart guide.
    The code does not need to be modified as it will pick up the license types from your data. Each sample will prompt for the API key and App ID specific to your environment when run.
    For more detailed documentation of the code within different examples, visit the Recipes section of the site.
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();
	let data = sheet.getDataRange().getValues();
	Logger.log(SpreadsheetApp.getActiveSheet().getName());
	Logger.log(data);
	data.shift();

	let fullDataSet = [];
	let userDataSet = [];
	let updateDataSets = true;


	let licensesMap = {}

	data.forEach(function(row) {

			let firstName = row[2];
			let lastName = row[3];
			let email = row[4];
			let licenses = row[6];
			let day_used = Utilities.formatDate(new Date(row[9]), "UTC", 'YYYY-MM-dd');


			let obj = fullDataSet.find(x => x.email === email && x.license === licenses);
			if (obj) {
				let index = fullDataSet.indexOf(obj);
				let dateFromDataSet = fullDataSet[index].lastUsedDate;
				if (day_used > dateFromDataSet) {
					fullDataSet[index].lastUsedDate = day_used;
				}
				updateDataSets = false;
			}

			if (updateDataSets === true) {
				fullDataSet.push({
					email: email,
					firstName: firstName,
					lastName: lastName,
					status: "active",
					license: licenses,
					lastUsedDate: day_used
				});
			}

			updateDataSets = true;

		}

	);

	for (var i = 0; i < Object.keys(fullDataSet).length; i++) {
		let users;
		if (licensesMap[fullDataSet[i].license]) {
			users = licensesMap[fullDataSet[i].license]
		} else {
			users = []
		}

		users.push({
			email: fullDataSet[i].email,
			licenseStatus: "active",
			lastUsedDate: fullDataSet[i].lastUsedDate
		})
		licensesMap[fullDataSet[i].license] = users;

		const isUserFound = userDataSet.some(element => {
			if (element.email === fullDataSet[i].email) {
				return true;
			}

			return false;
		});
		if (isUserFound === false) {
			userDataSet.push({
				email: fullDataSet[i].email,
				firstName: fullDataSet[i].firstName,
				lastName: fullDataSet[i].lastName,
				status: "active"
			});

		}


	}

	const licenses = Object.keys(licensesMap).map(licenseName => {
		const users = licensesMap[licenseName]
		return {
			name: licenseName,
			users: users
		}
	})


	let file = {
		users: userDataSet,
		licenses: licenses
	}
	// Logger.log(file);
	console.log(JSON.stringify(file));
	return file;
}


function uploadFile(file) {
  let response = UrlFetchApp.fetch('https://api.toriihq.com/v1.0/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/v1.0/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;
};

function syncIntegration(fileId) {
  response = UrlFetchApp.fetch('https://api.toriihq.com/v1.0/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);
}

Step 3 - Create the API Key and Custom Integration in your Torii console

  1. Create an API key from the Settings tab in the API Access submenu item.
  1. Create a custom integration in the Integrations tab and map it to the app you are creating the integration for.

🚧

Warning: Make sure to copy the App Account ID value that is returned as you will need it when you sync the data from the Google Sheet.

Step 4 - Sync the integration from the Google Sheet

  1. Using the Torii --> Sync Now menu option, run the AppScript to sync the data from the spreadsheet into Torii. This will execute the code, format the data, and push said data to the Torii database. The script will prompt you for both the API key and the App Account ID.

  1. The first run of the script in your environment will request authorization. Use your account or a service account in your environment to grant access to the necessary scopes.
  1. Once you've completed the authorization, you will have to select the Torii --> Sync Now menu item again. The script will prompt you for the API Key and App Account ID.

Step 5 - Confirm the custom integration synced successfully via the Torii console

  1. From the Integrations tab, check that the custom integration tile has a green checkmark.
  1. Check that the data passed in through the integration has been processed by the platform and is available in the Licenses view.

πŸ‘

Congratulations! You've successfully synced a custom integration. You can use this approach to sync data into Torii for any apps that don't have APIs but do have some form of downloadable extract of users, licenses, and usage data.