Australian Research Council Grant Outcomes

function myFunction() {
  
}

  var logger = console; // Use Logger for immediate logs, console for StackDriver logs (better).
  var GLOBAL = {
    spreadsheetID: "1C_BeM8R4v5MS1mGl0NbTmuxEjMFVy1b-PDpGgBqSjYE",
    emailRecipients: "andrew.casey@monash.edu",
    rmsURL: "https://rms.arc.gov.au/RMS/Report/DataProvider/GetData"
  };

function checkOutcomes() {
  var response = UrlFetchApp.fetch(GLOBAL.rmsURL + "?providerID=announcedSchemeRoundListProvider");
  var schemes = JSON.parse(response.getContentText());

  // Check for any new schemes?
  logger.log("Status of RMS website: " + schemes.status);
  logger.log(response);
  
  // Get existing schemes from spreadsheet.
  var spreadsheet = SpreadsheetApp.openById(GLOBAL.spreadsheetID),
      schemesSheet = spreadsheet.getSheetByName("Schemes"),
      existingSchemeIDs = [].concat.apply([], schemesSheet.getRange("A2:A1000").getValues()).filter(Boolean),
      nextRowID = 2 + existingSchemeIDs.length;
  
  logger.log("Existing scheme IDs: " + existingSchemeIDs);
  logger.log("Next row ID: " + nextRowID);
  
  JSON.parse(schemes.data).forEach(function(scheme) {    
    if (existingSchemeIDs.indexOf(scheme.Id) < 0) {
      // New scheme  
      logger.log("New scheme: " + scheme.FullName + " (" + scheme.Name + "; " + scheme.Id + ")");
      
      // Send email about new scheme
      MailApp.sendEmail(
        GLOBAL.emailRecipients,
        "ARC Outcomes available for " + scheme.Name + " (" + scheme.FullName + ")",
        "Dear colleague,\n\n" + 
        "Outcomes are now available for " + scheme.FullName + " (" + scheme.Name + ")." + 
        "The outcomes have been populated in the following spreadsheet:\n\n" +
        "https://docs.google.com/spreadsheets/d/" + GLOBAL.spreadsheetID + "/edit?usp=sharing")

      // Prepare to create a sheet.
      var sheetName = scheme.Name.split(" ")[0],
          sheet = spreadsheet.getSheetByName(sheetName);
      
      if (sheet != null) {
        // Delete any existing sheet of the same name
        spreadsheet.deleteSheet(sheet);
      }
      
      // Create a fresh sheet and insert after Schemes
      sheet = spreadsheet.insertSheet(1);
      sheet.setName(sheetName);
      
      // Get outcomes for that scheme and put it in the sheet
      updateOutcomes(scheme.Id, scheme.Name);
      
      // Add to the summary scheme sheet now that we are done
      schemesSheet.getRange("A" + nextRowID).setValue(scheme.Id);
      schemesSheet.getRange("B" + nextRowID).setValue(scheme.Name);
      schemesSheet.getRange("C" + nextRowID).setValue(scheme.FullName);
      nextRowID = nextRowID + 1;
    }
    else {
      logger.log("Existing scheme: " + scheme.Name + " (" + scheme.FullName + "; " + scheme.Id + ")");
    }
  });
  
  // Update the 'last updated' time.
  schemesSheet.getRange("F1").setValue((new Date()).toLocaleString());
}

function updateOutcomes(schemeID, schemeName) {
  logger.log("Retrieving outcomes for scheme " + schemeID + " (" + schemeName + ")"); 

  var spreadsheet = SpreadsheetApp.openById(GLOBAL.spreadsheetID),
      sheetName = schemeName.split(" ")[0],
      sheet = spreadsheet.getSheetByName(sheetName);

  var response = UrlFetchApp.fetch(
    GLOBAL.rmsURL +
    "?providerID=publicAPIDataProvider&args[reportName]=SchemeRoundStatistics&args[schemeRoundId]=" + 
    schemeID);
  var projects = JSON.parse(JSON.parse(response.getContentText()).data).projects;

  var max_attempts = 5;
  var sleep_time = 3000; // milliseconds
  var outcomes_url = GLOBAL.rmsURL +
                     "?providerID=publicAPIDataProvider&args[reportName]=SchemeRoundStatistics&args[schemeRoundId]=" + 
                     schemeID;

  for (j=1; j <= max_attempts; j++) {        
    try {
      var response = UrlFetchApp.fetch(outcomes_url);
      logger.log('Success in fetching ' + outcomes_url + ' on attempt ' + j + ' of ' + max_attempts);
      logger.log(response);
      
      var parsed_response = JSON.parse(JSON.parse(response.getContentText()).data);
      logger.log("Status of parsed response: " + parsed_response);
      break;

    } catch (e) {
      logger.error('Attempt ' + j + ' of ' + max_attempts + ' to fetch URL ' + outcomes_url + '. Error: ' + e);
      Utilities.sleep(sleep_time);
    }    
  }
  
  var projects = parsed_response.projects;

  var showHeaders = [
    'Project Code',
    'Chief Investigators',
    'Summary',
    'Announced Date',
    'Admin Organisation',
    'Primary FOR',
    'Allocated Funding'
  ];

  for (j=0; j < showHeaders.length; j++) {
    sheet.getRange("A1:Z1").getCell(1, j + 1).setValue(showHeaders[j]);
  }
  // Set text style and column widths
  sheet.setActiveSelection("A1:Z1").setFontWeight("bold");
  
  sheet.setColumnWidth(2, 200); // Chief Investigators
  sheet.setColumnWidth(3, 600); // Summary
  sheet.setColumnWidth(4, 125); // Announced Date 
  sheet.setColumnWidth(5, 200); // Admin Organisation
  sheet.setColumnWidth(7, 125); // Allocated Funding
  
  // Freeze header row.
  sheet.setFrozenRows(1);
  
  // Put in the data.
  var offset = 2; 
  for (i = 0; i < projects.length; i++) {

    var project = projects[i];
    
    logger.log("Project " + i);
    for (j = 0; j < showHeaders.length; j++) {
      
      var header = showHeaders[j],
          cell = sheet.getRange("A" + (offset + i) + ":Z" + (offset + i)).getCell(1, j + 1);
      
      switch (header) {
        case 'Project Code':
          cell.setValue(project.ProjectCode);
          break;
          
        case 'Chief Investigators':
          cell.setValue(parseChiefInvestigators(project.ChiefInvestigators));
          break;
          
        case 'Announced Date':
          cell.setValue(project.AnnouncedDate);
          break;
          
        case 'Admin Organisation':
          cell.setValue(project.AdminOrganisation);
          break;
        
        case 'Primary FOR':
          cell.setValue(parsePrimaryFieldOfResearch(project.FieldsOfResearch));
          break;
          
        case 'Allocated Funding':
          cell.setValue(project.AllocatedFunding);
          break;
          
        default:
          cell.setValue(project[header]);

      }   
    }
  }
  // Set wrap and alignment strategy
  sheet.getRange("B1:C1000").setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
  sheet.getRange("A1:Z1000").setVerticalAlignment("top");
  
  // Set the rows to auto-resize to fit their data
  sheet.autoResizeRows(1, 1 + projects.length);
}

function parseChiefInvestigators(investigators) {
  var parsedInvestigators = [];
  for (k = 0; k < investigators.length; k++) {
    var investigator = investigators[k];

    // Check the PersonOrdinal
    if (investigator.PersonOrdinal != (k + 1)) {
      for (l = 0; l < investigators.length; l++) {
        if (investigators[l].PersonOrdinal == (k + 1)) {
          investigator = investigators[l];
          break;
        }
      }
    }    
    parsedInvestigators.push(investigator.Title 
                     + " " + investigator.FirstName 
                     + " " + investigator.FamilyName);
  }
  return parsedInvestigators.join("; ");
}

function parsePrimaryFieldOfResearch(FORCodesString) {
  // Just return the primary FOR code.
  // Note: Here we are assuming the first entry is the primary!
  var FORCodes = JSON.parse(FORCodesString);
  return String(FORCodes.data[0].code).substr(0, 4);
}