Note: All pages below are subject to having relevant Roles and Permissions.

Step 1 – The Bromcom Report

Write your report (or pick an existing one). Preview the report and then select the Live Data Feed and Copy the URL from the Live Data Feed box. Note the Live Data Feed is only available on Custom Reports.

Step 2 – Your Username and Password to access the Report

You need the School Site ID which will be your Username. You also need your User Access Key, which can be found under your Profile, click on your image top right of the screen and select the My Account option from the dropdown list and scroll down to the General section, your User Access Key will be displayed here, make a note of these.

Step 3 – Setup the Google Sheet

Create the Google Sheet that is going to receive the Data.

Note: Please double check the code after pasting, as it may not copy and paste correctly, especially the double and single quotation marks.

Click on the Tools option and select Script Editor and paste in the following 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) {

studentDetails=studentDetails.map(s => s.trim()); 
     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(),

                    “Replace this text with your report URL“,

                    “Replace this text with your School Site ID“,

                    “Replace this text with you Access Key“);

}

Replace the red text with your report URL, your School Site ID and your Access Key.

Select Save and name the Sheet, this will Save the Script. Select the Main function, to the right of Debug and then click on Run. 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.

Note: Please double check the code after pasting, as it may not copy and paste correctly, especially the double and single quotation marks.