Need help with formatting google sheets (In Java script)

So I have this project and I need to format raw data in google sheets
Im super new to scripting and was hoping someone can walk me through it or just correct the code I have to match what I need.

Here’s the code I have:

<// constants
var reportsTabMeta = {
  'name': 'Reports',
  'colSavedName': 2,
  'maxCols': 2,
  'maxRows': 5
};

function onInstall(e) {
  // defaults
  var properties = {
    'host': 'https://myserver.com',
    'company':'myCompany',
    'user':'myUser',
    'psw':'secret'
  };
  PropertiesService.getScriptProperties().setProperties(properties, true);
  //

  onOpen(e);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('WFR Report Export')
      .addItem('Export Report List', 'menuExportReportList')
      .addSeparator()
      .addItem('Export Now', 'menuExportNow')
      .addToUi();
}

function menuExportNow() {
  var reportIds = getReportsSetup(reportsTabMeta.name);
  //SpreadsheetApp.getUi().alert('You clicked the Export Now! '+reportIds);

  if (reportIds.length == 0) {
    SpreadsheetApp.getUi().alert(
     'Nothing to load. Make sure '+reportsTabMeta.name+' tab exists and contains list of saved reports.');
    return;
  }

  SpreadsheetApp.getActiveSpreadsheet().toast('Logging in...');
  var token = loginWfr();

  for (var i = 0; i < reportIds.length; i++) {

    var reportSettingId = reportIds[i][0];
    var reportSavedName = reportIds[i][1];

    SpreadsheetApp.getActiveSpreadsheet().toast('Loading '+reportSavedName+' ...');
    var reportResult = getReport(token, reportSettingId);
    var sheet = getSheet(reportSavedName);

    if (reportResult.code == 200) {
      populateTab(reportResult.root, sheet);
    } else {
      populateExceptionMessage(reportResult.root, sheet);
    }

    fillReportUpdatedDate(reportSavedName);
  }

  SpreadsheetApp.getActiveSpreadsheet().toast('Done!');
}

function getReportsSetup(sheetName) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSpreadsheet.getSheetByName(sheetName);

  if (sheet == null) {
    return [];
  }

  var reports = [];
  var colSettingId = 1;

  var range = sheet.getRange(1, 1, reportsTabMeta.maxRows, reportsTabMeta.maxCols);
  for (var i = 1; i <= reportsTabMeta.maxRows; i++) {
    var reportSettingId = range.getCell(i, colSettingId).getValue();
    var reportSavedName = range.getCell(i, reportsTabMeta.colSavedName).getValue();

    if (reportSettingId != '' && reportSavedName != '') {
      reports.push([reportSettingId,reportSavedName]);
    }
  }

  return reports;
}

function menuExportReportList() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(reportsTabMeta.name);
  if (sheet != null) {
    var ui = SpreadsheetApp.getUi();

    var result = ui.alert(
      'Please confirm',
      'Are you sure you want to continue?\nIn case you proceed report list would be overridden.',
      ui.ButtonSet.YES_NO);

    if (result != ui.Button.YES) {
      return;
    }
  }

  SpreadsheetApp.getActiveSpreadsheet().toast('Logging in...');
  var token = loginWfr();
  SpreadsheetApp.getActiveSpreadsheet().toast('Loading report list...');
  var result = getReportList(token);
  var sheet = getSheet(reportsTabMeta.name);

  if (result.code == 200) {
    populateReportListTab(result.root, sheet);
  } else {
    populateExceptionMessage(result.root, sheet);
  }

  SpreadsheetApp.getActiveSpreadsheet().toast('Done!');
}

function loginWfr() {

  var props = PropertiesService.getScriptProperties();
  var host = props.getProperty('host');
  var company = props.getProperty('company');
  var user = props.getProperty('user');
  var psw = props.getProperty('psw');

  var options = {
    'method' : 'post',
    headers : {
      "Accept" : "application/json",
      "Content-Type" : "application/json;charset=ISO-8859-1"
    },
    'payload':'{"credentials": {"company":"'+company+'","username": "'+user+'","password": "'+psw+'"}}'
  };

  var response = UrlFetchApp.fetch(host+"/ta/rest/v1/login?origin=script", options);
  var strResponse = response.getContentText();

  var json = JSON.parse(strResponse);

  return json.token;
}

function getReportList(token) {
  var host = PropertiesService.getScriptProperties().getProperty('host');

  var options = {
    headers : {
      "Authentication" : "bearer "+token,
      "Accept" : "application/xml"
    },
    'muteHttpExceptions' : true
  };

  var response = UrlFetchApp.fetch(host+"/ta/rest/v1/reports?type=saved&origin=script", options);
  var responseCode = response.getResponseCode();
  var strResponse = response.getContentText();

  var document = XmlService.parse(strResponse);
  var root = document.getRootElement();

  return {'code': responseCode, 'root': root};
}

function getReport(token, reportId) {
  var host = PropertiesService.getScriptProperties().getProperty('host');
  var options = {
    headers : {
      "Authentication" : "bearer "+token,
      "Accept" : "application/xml"
    },
    'muteHttpExceptions' : true
  };
  var response = UrlFetchApp.fetch(host+"/ta/rest/v1/report/saved/"+reportId+"?origin=script", options);
  var responseCode = response.getResponseCode();

  var strResponse = response.getContentText();

  var document = XmlService.parse(strResponse);
  var root = document.getRootElement();

  return {'code': responseCode, 'root': root};
}

function populateExceptionMessage(root, sheet) {

  var errors = root.getChildren('error');
  for (var i = 0; i < errors.length; i++) {
    var errorMsg = errors[i].getChild('message').getText();
    sheet.appendRow([errorMsg]);
  }
}

function populateReportListTab(root, sheet) {

  var reports = root.getChildren('report');
  for (var i = 0; i < reports.length; i++) {
    var settingId = reports[i].getChild('SettingId').getText();
    var savedName = reports[i].getChild('SavedName').getText();

    sheet.appendRow([settingId, savedName]);
  }
}

function populateTab(root, sheet) {

  var headerRow = [];
  var headers = root.getChild('header').getChildren('col');
  for (var i = 0; i < headers.length; i++) {
    var label = headers[i].getChild('label').getText();
    headerRow.push(label);

  }
  sheet.appendRow(headerRow);

  // rows
  // TODO group processing (not just body > rows, but body > group > body)
  var rows = root.getChild('body').getChildren('row');
  for (var i = 0; i < rows.length; i++) {
    var row = rows[i];
    Logger.log(row);
    var regularRow = [];
    var cols = row.getChildren('col');
    for (var j = 0; j < cols.length; j++) {
      var val = cols[j].getText();

      regularRow.push(val);
    }
    sheet.appendRow(regularRow);

  }
  // TODO footer
}

function fillReportUpdatedDate(reportName) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(reportsTabMeta.name);

  // extra col for 'updated date'
  var numCols = reportsTabMeta.maxCols + 1;
  var range = sheet.getRange(1, 1, reportsTabMeta.maxRows, numCols);
  for (var i = 1; i <= reportsTabMeta.maxRows; i++) {
    var reportSavedName = range.getCell(i, reportsTabMeta.colSavedName).getValue();

    if (reportSavedName == reportName) {
      range.getCell(i, numCols).setValue(new Date());
    }
  }
}

function getSheet(sheetName) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSpreadsheet.getSheetByName(sheetName);

  if (sheet == null) {
    sheet = activeSpreadsheet.insertSheet();
    sheet.setName(sheetName);
  }
  sheet.clear(); //removing prev contents

  return sheet;
}">

and this is what I need

Now of course this is a fake company etc…
So if someone can help me edit my code to make it look like said picture that would be awesome, or give me pointers to good videos that would also be appreciated.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.