Help with google sheets formatting through javascript

1

I’m trying to develop a test schedule through java script and I have enough code to pull the data into a semi organized raw form, but not to the specific format I want.

Heres the code:

// 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;
}

so, with that code right there i’m trying to also make it format my spread sheet to look exactly like this:

h.png1920×1050 99 KB

(Company and names are fake)

If someone can help walk me through on how to do that or has any recommendations I’m all ears.
(I’m very new to coding, please be gentle)

2

Hi,

Sorry to bother. I do not have an answer for you, just a question. Do you have any good resource on accessing google sheets? I didn´t know it could even attempted (I am just finishing the front end part.

Cheers!