[bromcom] Query re importing data into Google Sheets

[CarterUSM]

Hi

I am interested in the possibility of importing data directly into a Google Data sheet. The current training sheet https://docs.bromcom.com/knowledge-base/how-to-import-data-directly-into-a-google-sheet/ incorporates an excerpt from a Google Data sheet, which indicates that it is possible to access Script Editor via the Tools dropodown. However, this option is not available in my version of Google Sheets, as illustrated by the attached screenprint. Is there another route by which I can access Script Editor?

 

Many thanks in anticipation of your assistance.

Script Editor not appearing in Tools dropdown in Google Sheets.pdf

[garbage46]

I can help with this. You are looking at the wrong menu. Check for Apps Script under the Extensions menu

 

To help further I have rewritten to Google Apps script they provide. I also have a script to pull from the API into Google Sheets.

[garbage46]

const sheetName = "Sheet1"; // replace with sheet/tab name
const rptLiveUrl = "LIVE_FEED_URL"; // replace with report Live Feed URL
const schoolID = "12345"; // replace with Bromcom 5 digit School ID
const userAccessKey = "U5ER-ACC355-K3Y-G0E5H3R3"; // replace with your Bromcom User Access Key from My Account

function letsGo() {
 populateSheetWithCSV(
   SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName),
   rptLiveUrl,
   schoolID,
   userAccessKey
 );
}

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 data = doc.getRootElement().getAllContent();
 var dataRows = [];
 var maxLength = 0;
 for (var j = 0; j < data.length - 1; j++) {
   var length = data[j].getValue().trim().split("\n").length;
   if (maxLength < length) {
     maxLength = length;
   }
 }

 // Get all the data into a 2D Array
 for (var i = 0; i < data.length - 1; i++) {
   var dataContent = data[i].getValue().trim().split("\n");
   if (dataContent.length > 1) {
     dataContent = dataContent.map((s) => s.trim());
     if (dataContent.length < maxLength) {
       var diff = maxLength - dataContent.length;
       for (var k = 0; k < diff; k++) {
         dataContent.unshift("");
       }
     }
     dataRows.push(dataContent);
   }
 }
 // clear existing data leaving the Header Row intact
 var columnToCheck = sheet.getLastRow();
 if (columnToCheck > 5) {
   sheet.deleteRows(2, columnToCheck - 1);
 }
 // set the values in the sheet starting with row 2 so the coloumn headers are left untouched
 sheet.getRange(2, 1, dataRows.length, maxLength).setValues(dataRows);
}