Array Length To Limit Number of Columns-CodePudding

I'm attempting to limit the number of columns pulled from a data source. The source has 10 columns, I only need the first 4 columns. Seems I am confusing myself on userData and var j plus var k and var l when attempting to create an HTML table of the output.

When I use var i = 0; i < values.length; i and var j = 0; j < header.length; j with var k = 0; k < userData.length; k and var l = 0; l < userData[0].length; l the HTML output returns all the columns.

How can I limit the output to just the first 4 columns?


var sheetID = 'example';
var dataSheet = 'SOP Update ACK';
var emailHeader = 'Email Address';
var activeUser = Session.getActiveUser();
var ss = SpreadsheetApp.openById(sheetID);

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Index').setTitle('My SOP Updates');
}

function currentUser() {
  if (activeUser !== '') {
    return activeUser.getEmail();
  } else {
    return "Couldn't detect user!!!";
  }
}

function getData() {
  var sheetName = dataSheet;
  var activeSheet = ss.getSheetByName(sheetName);
  var values = activeSheet.getDataRange().getDisplayValues();
  var header = values[0];
  var emailIndex = header.indexOf(emailHeader);
  var userData = [];
  for (var i = 0; i < values.length; i  ) {
    if (values[i][emailIndex] == activeUser) {
      userData.push(values[i]);
    }
  }
  if (userData.length > 0) {
    var tableStart = '\n<table >';
    var tableHead = '\n<thead>\n<tr>';
    for (var j = 0; j < header.length; j  ) {
      tableHead = tableHead   '\n<th>'   header[j]   '</th>';
    }
    tableHead = tableHead   '\n</tr>\n</thead>';
    var tableBody = '\n<tbody>';
    for (var k = 0; k < userData.length; k  ) {
      tableBody = tableBody   '\n<tr>';
      for (var l = 0; l < userData[0].length; l  ) {
        tableBody = tableBody   '\n<td>'   userData[k][l]   '</td>';
      }
      tableBody = tableBody   '\n</tr>\n';
    }
    var tableEnd = '</tbody>\n</table>';
    var tableHtml = tableStart   tableHead   tableBody   tableEnd;
    return tableHtml;
  } else {
    return '<table ><tbody><tr><td>No data found.</td></tr></tbody></table>';
  }
}

CodePudding user response：

I got this to work:

You will have to make some minor modification to get it to work for you because I tested it on my temp data since you did not provide any

function getData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const [header, ...values] = sh.getDataRange().getDisplayValues();
  const emailIndex = header.indexOf('Email Address');
  const userData = [];
  const activeUser = Session.getActiveUser();
  for (let i = 0; i < values.length; i  ) {
    if (values[i][emailIndex] == activeUser) {
      userData.push(values[i].slice(0,4));
    }
  }
  if (userData.length > 0) {
    let tableStart = '\n<table >';
    let tableHead = '\n<thead>\n<tr>';
    for (let j = 0; j < header.length; j  ) {
      tableHead = tableHead   '\n<th>'   header[j]   '</th>';
    }
    tableHead  = '\n</tr>\n</thead>';
    let tableBody = '\n<tbody>';
    for (let k = 0; k < userData.length; k  ) {
      tableBody = tableBody   '\n<tr>';
      for (let l = 0; l < userData[0].length; l  ) {
        tableBody = tableBody   '\n<td>'   userData[k][l]   '</td>';
      }
      tableBody = tableBody   '\n</tr>\n';
    }
    let tableEnd = '</tbody>\n</table>';
    let tableHtml = tableStart   tableHead   tableBody   tableEnd;
    return tableHtml;
  } else {
    return '<table ><tbody><tr><td>No data found.</td></tr></tbody></table>';
  }
}