1. Home
  2. Reporting
  3. How to Import Data Directly into a Google Sheet

How to Import Data Directly into a Google Sheet

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) {
     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.

Updated on September 20, 2021

Was this article helpful?

Related Articles

Noticed a Missing Guide?
Can't find the guide you need?
Email Us

Comments

  1. Gives me an error when trying to save the code: SyntaxError: Invalid or unexpected token (line 9, file “Code.gs”)

    line 9 is ‘Authorization’: ‘Basic ‘ + Utilities.base64Encode(

    1. Hi Jon, please can you report this to the Bromcom Helpdesk and they’ll be able to look into this for you.

    2. For anyone else coming across this error, it’s because the code in HTML above gets a bit malformed when copying to the Sheets script editor.

      After copying go through the code and replace all single and double quotations.

        1. Hi James, the guide has been updated to suggest that you make sure the quotation marks copy and paste correctly (see section in red), you may need to manually amend them afterwards

  2. Same for me. Reported to help desk, exactly the same issue. But helpdesk were unable to help. Why was this help sheet not updated when Jon Dunn first reported this issue? This is a really powerful tool, but needs to work correctly.

    1. Hi Joseph, A note has been added to the page to check the pasted information. Thank you for bringing this to our attention.

  3. Hi, is there a MS Excel version of this? I used the code above via PowerQuery then tried both single and double quotes. I get an error “Expression.SyntaxError: Token Eof expected”.
    I’m guessing that there’s a difference in coding?

    1. Hi Alex

      Thank you for feedback , can you please contact the Help Desk with this error.
      Kind Regards
      Bromcom Training

  4. Hi

    Thanks for the script – it seems to work.
    However, it doesn’t seem to bring through the column headers. Is there a tweak to the code for this?

    1. Hi Declan
      Thank you for your comments. Unfortunately there is no ‘tweak’ in the code for this.
      Kind Regards
      Bromcom Training

Leave a Comment