Steve The Identity Guy

In my previous blog I walked through using a Google Sheet as a source to sync information with Torii with the Custom Integration API. In this blog, we are going to use a similar technique to sync Expense information with Torii.

Using the same premise as my previous blog, I’ve built a custom spreadsheet that contains some basic information:

• Description
• Date
• Amount
• Currency
• Reporting User.

When you use the API to sync expense reports with Torii we have to specify mapping (which you will see in the code) that tells Torii how to parse the spreadsheet. Google does some special date formatting so for the purpose of this sample, you will notice that I’ve hard coded the date column to be the second column so I can format the date in the right format.

As per the previous blog, you will need a Torii API Key. We don’t need to create an Application ID though for Expense Files.

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

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.

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');


  if (!API_KEY ) {
    readConfig();
    docProperties = PropertiesService.getDocumentProperties();
    API_KEY = docProperties.getProperty('api_key');
  }
let filename = convertRangeToCsvFile_("ToriiExpense.csv");
let fileId = uploadFile(filename);
parseFile(fileId);
}

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

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

}

function convertRangeToCsvFile_(csvFileName) {
  // get available data range in the spreadsheet
  let sheet = SpreadsheetApp.getActive();
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var csvFile = undefined;
    //Logger.log(Utilities.formatDate(new Date(testvar), "UTC", 'YYYY-MM-dd'));
  
   
    // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        if(row>0){
          data[row][1] = Utilities.formatDate(new Date(data[row][1].toString()), "UTC", 'dd/MM/YYYY');
        }
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;

    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);

  }
}


function uploadFile(filename) {
  let response = UrlFetchApp.fetch('https://api.toriihq.com/beta/files/url?name=expense_file.csv&type=text/csv',
    {
      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': 'text/csv',
      'payload' : filename
    }
  );
  
  // 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: 'expenseReport'
      }),
      headers: {
        'Authorization': `Bearer ${API_KEY}`
      }
    });

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


function parseFile(fileId) {
  response = UrlFetchApp.fetch('https://api.toriihq.com/beta/parsings/manual',
    {
      muteHttpExceptions: false,
      'method' : 'put',
      'contentType': 'application/json',
      'payload' : JSON.stringify({
          'parseConfig': {
          'idFile': fileId,
          'transactionDateColumn': "B",
          'descriptionColumn': "A",
          'amountColumn': "C",
          'dateFormat': "DD/MM/YYYY",
          'currencyColumn': "D",
          'reportingUserColumn': "E"
          }
      }),   
      headers: {
        'Authorization': `Bearer ${API_KEY}`
      }
    }
  );
  Logger.log(response);
}

If you everything goes well, you should see your expense file in Torii (Expenses -> Expense Files)

When you review the expense report, you will be able to view all the details and update the matching accordingly