Importing Data from Bromcom directly into a Google Sheet and updating it automatically

I support one school in the Trust that used Bromcom and was finding it a bit tricky to get the data directly into Sheets. However, I've sorted this now and thought this might prove useful to someone.

Step 1 - The Bromcom Report
Write your report (or pick an existing one). Click Run Report and then select “Live Data Feed”.

Copy the URL it gives you.

Step 2 - Your user name and password to access the report

You need the site ID which is the username. You also need your “User Access Key”. To get this click top right and “My account” and you will see a box with this in. Make a note of these.

Step 3 - Setup the Google Sheet

Make a Google Sheet that is going to receive the data.

Click on “Tools” then Script and paste in this code:

function populateSheetWithCSV(sheet, csvUrl, user, pw) {

  var resp = UrlFetchApp.fetch(csvUrl, {

    headers: {

      // use basic auth

      'Authorization': 'Basic ' + Utilities.base64Encode(

      user + ':' + pw, Utilities.Charset.UTF_8)

    }

  });

  var doc = XmlService.parse(resp);

  var students = doc.getRootElement().getAllContent();

  var spreadsheetContent = [];

  var maxLength = 0;

  for (var j=0; j<students.length-1; j++)

  {

   var length = students[j].getValue().trim().split("\n").length;

   if (maxLength < length)

   {

     maxLength = length;

   }

  }

  // Get all the data into a 2D Array

  for (var i=0; i<students.length-1; i++)

  {

    var studentDetails = students[i].getValue().trim().split("\n");

    if (studentDetails.length > 1) {

      if (studentDetails.length < maxLength)

      {

        var diff = maxLength - studentDetails.length;

        for (var k = 0; k<diff; k++)

        {

          studentDetails.unshift("");

        }

      }

      spreadsheetContent.push(studentDetails);

    } 

  }

  Logger.log(spreadsheetContent);

// clear everything in the sheet

    sheet.clearContents().clearFormats();

// set the values in the sheet (as efficiently as we know how)

    sheet.getRange(

        1, 1,

        spreadsheetContent.length /* rows */,

        maxLength /* columns */).setValues(spreadsheetContent);

}

function main() 

{

  populateSheetWithCSV(SpreadsheetApp.getActiveSheet(), 

                     "YOURREPORTURLHERE",

                     "YOURSCHOOLNUMBER",

                     "YOURACCESSKEYHERE");

}

Put in your report URL, School ID and your access key in where the red text is.

Save the script and run the function "main". Your Bromcom report will now appear in your Sheet.

You can click the timer button to make "main" run on a schedule - to update the data as often as you like.

I'd like to thank my son, Robert Nixon (3rd Year Computer Science Student) for his extensive help with the code!