Import Rackspace Bill into Google sheets using Google App Script

rackspace-bill-into-gsheets.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Rackspace API')
      .addItem('Fetch latest billing data','fetchBilling')
      .addToUi();
}

function fetchBilling() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  console.log('Task started');
  spreadsheet.toast('Task started', 'Status');
  
  if (auth() !== 200) {
    console.error('Task failed - unable to authenticate with Rackspace');
    spreadsheet.toast('Task failed - unable to authenticate with Rackspace', 'Status');
    return false;
  }
  
  var invoiceID = getLatestInvoice();  
  var csv = "";
 
  if (invoiceID !== false) {
   var csv = getDetailedReportAsCSV(invoiceID); 
  }
  
  if (csv) {
    
    spreadsheet.toast('CSV generated, importing to "Data" sheet', 'Status');
    console.log('CSV fetched');
    
    // Set the active sheet to the 'Data' sheet
    var dataSheet = spreadsheet.getSheetByName("Data");

    // Clear the existing data
    dataSheet.clearContents();

    // Parse the CSV data
    var csvData = Utilities.parseCsv(csv, ",");
    console.log(csvData);
    
    dataSheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  }
}

function auth() {
  var props = PropertiesService.getScriptProperties();
  var username = props.getProperty('username');
  var apiKey = props.getProperty('api_key');
  var endpoint = "https://identity.api.rackspacecloud.com/v2.0/tokens";
  var response = UrlFetchApp.fetch(
    endpoint,
    {
      'method': 'post',
      'contentType': 'application/json',
      'payload': JSON.stringify({
        'auth': {
          'RAX-KSKEY:apiKeyCredentials': {
            "username": username,
            "apiKey": apiKey
          } 
        }
      })
    }
  );
  if (response.getResponseCode() === 200) {
    var responseBody = JSON.parse(response.getContentText());
    var token = responseBody.access.token;
    props.setProperty("token", token.id);
    props.setProperty("token_expires", token.expires);
  }
  
  return response.getResponseCode();
}

function getLatestInvoice() {
  var props = PropertiesService.getScriptProperties();
  var ran = props.getProperty('ran');
  var endpoint = "https://billing.api.rackspacecloud.com/v2/accounts/" + ran + "/invoices/latest";
  var response = UrlFetchApp.fetch(
    endpoint,
    {
      'method': 'get',
      'contentType': 'application/json',
      'headers': {
        'accept': 'application/json',
        'X-Auth-Token': props.getProperty("token")
      }
    }
  );
  var invoiceID = false;
  if (response.getResponseCode() == 200) {
    var responseBody = JSON.parse(response.getContentText());
    var invoiceID = responseBody.invoice.id;
    return invoiceID;
  }
}

function getDetailedReportAsCSV(invoiceID) {
  var props = PropertiesService.getScriptProperties();
  var ran = props.getProperty('ran');
  var endpoint = "https://billing.api.rackspacecloud.com/v2/accounts/" + ran + "/invoices/" + invoiceID + "/detail";
  var response = UrlFetchApp.fetch(
    endpoint,
    {
      'method': 'get',
      'contentType': 'application/json',
      'headers': {
        'accept': 'text/csv',
        'X-Auth-Token': props.getProperty("token")
      }
    }
  );
  var invoiceCSV = "";
  if (response.getResponseCode() == 200) {
    var invoiceCSV = response.getContentText();
    return invoiceCSV;
  }
}