Skip to main content

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!

Comments

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

    If your son Robert is seeking for a position in an exciting software developments, please ask him to contact personnel@bromcom.com and refer to this article.

    ReplyDelete
  2. Happy to help!

    - Robert
    www.robertnixon.net

    ReplyDelete

Post a Comment

Popular posts from this blog

Delete a specific email using GAM

If a user send an inappropriate email to a loads of people or get stung by some sort of email exploit you can quickly delete the email from all of the recipients using a GAM command.
Step 1 - get the email header Go into Google Vault and search for the offending user or someone known to have got the message.
Click show details and grab the email ID. This will be a long string of characters followed by @mail.gmail.com
Step 2 - find out who has the email Go into Google Vault and find the original message sent by the offending user. Look at the details to see who got it. Copy the list and dump it into a spreadsheet. Clean up to just a list of emails with a column header 'mail'. Save as a csv file.
Step 3 - delete messages with GAM Put your CSV file in your GAM folder - this e.g. assumes its called mail.csv
Run:
gam csv mail.csv gam user ~mail delete messages query rfc822msgid:MESSAGEIDHERE doit

The alternative nuke option is:
gam all users delete messages query rfc822msgid:MESSAGEI…

My favorite GAM commands - well a few of them at least!

Where would be without GAM? Paying for expensive syncing tools or doing tedious manual tasks in the admin console. GAM can automate most things you might want to do in G Suite. So these are a few of my favourite commands - one I use either as part of a batch file - or just standalone. There are loads more - but these are ones that are used daily.
Classroom Create a spreadsheet of all your domains classes - gam print courses todrive

Create a spreadsheet of a teacher's classes: gam print courses teacher fred@mydomain.com todrive

Bulk create classes:
gam csv classes.csv gam create course alias ~alias name ~alias section ~subject teacher ~teacher status ACTIVE

where classes.csv is a list of classes you want to make.

Add teachers:
gam csv teachers.csv gam course ~alias add teacher ~teacher

Add students:
gam csv students.csv gam course ~alias add teacher ~student

Sync Students (in this example to a group - but could be an ou/csv file)
gam csv groups.csv gam course ~groupmail sync students g…

How to beat ChromeOS EOL and carry on getting updates

ChromeOS devices are great in loads of ways, but they have built-in obsolescence. Google will stop providing updates at a predetermined time according to the schedule you can find here. So the best you will achieve is 6.5 year if you buy the device on the launch day. In reality, it will be generally much less. It's something to watch as a good deal might not be such a good deal if the device only has two years left.

Once a device reaches its "due" date, you get a red pop up telling you its time to update every time you log in. The update section tells you there are no more updates. Now the device will work fine - for a while. You might get another 6 months use out of it before core services like Gmail stop working. However, if you are prepared to do a little work, you can install the OS of your choice onto the device and carry on using the device and not accept this. If you want ChromeOS, then you can install Cloudready from Neverware. I'll outline the basic steps be…